How to do a complex migration?
37 views
Skip to first unread message
toomim
Nov 14, 2009, 8:08:12 PM11/14/09
to web2py-users
I routinely run into migration problems. I suspect this happens when I
change a column's datatype, or when I remove a table from db.py and
then later make a new table with the same name as the old one.
In these situations, the migrations get messed up and I get
stacktraces in sql.py with errors like:
ProgrammingError: column "study__tmp" is of type integer but
expression is of type text
LINE 1: UPDATE actions SET study__tmp=study;
^
HINT: You will need to rewrite or cast the expression.
How can I migrate in these situations?
As an aside, I love the automatic migrations when they work, but I
don't mind writing sql to fix the underlying database when they don't.
Could web2py just tell me what schema it expects, let me fix the
database, and then let me say "OK web2py, the database is ready to go!"
change a column's datatype, or when I remove a table from db.py and
then later make a new table with the same name as the old one.
In these situations, the migrations get messed up and I get
stacktraces in sql.py with errors like:
ProgrammingError: column "study__tmp" is of type integer but
expression is of type text
LINE 1: UPDATE actions SET study__tmp=study;
^
HINT: You will need to rewrite or cast the expression.
How can I migrate in these situations?
As an aside, I love the automatic migrations when they work, but I
don't mind writing sql to fix the underlying database when they don't.
Could web2py just tell me what schema it expects, let me fix the
database, and then let me say "OK web2py, the database is ready to go!"
Thadeus Burgess
Nov 14, 2009, 9:37:39 PM11/14/09
to web...@googlegroups.com
You can check sql.log in the databases folder for the actual sql that has been executed to the database. That might help?
It would be nice to have a migration system like in rails, to define your own migration steps.
-Thadeus
It would be nice to have a migration system like in rails, to define your own migration steps.
-Thadeus
mdipierro
Nov 14, 2009, 11:04:00 PM11/14/09
to web2py-users
You are changing a "text" column (Field) into an "ineteger" column. My
changing the type web2py assumes you want to keep the data and yet it
cannot convert "text" to "integert". You have to do the migration in
two steps: 1) remove the column (comment it and run appadmin) 2) add
the column again with the new type. In this case web2py understands
you do not want to keep the data and will not attempt to do it.
changing the type web2py assumes you want to keep the data and yet it
cannot convert "text" to "integert". You have to do the migration in
two steps: 1) remove the column (comment it and run appadmin) 2) add
the column again with the new type. In this case web2py understands
you do not want to keep the data and will not attempt to do it.
toomim
Nov 18, 2009, 3:03:51 AM11/18/09
to web2py-users
Thanks! Would you accept a patch that makes the error messages more
obvious in these situations where you need to manually edit the
tables?
I think ROR and Django's approaches to migrations (fully-specifying
deltas vs. maintaining static schemas) are both more burdensome than
web2py's system of "semi-automatic for most changes (adding columns &
tables) but manual for major changes". But the "manual for major
changes" part needs better error messages with suggestions for how to
proceed.
On Nov 14, 8:04 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> You are changing a "text" column (Field) into an "ineteger" column. My
> changing the type web2py assumes you want to keep the data and yet it
> cannot convert "text" to "integert". You have to do the migration in
> two steps: 1) remove the column (comment it and run appadmin) 2) add
> the column again with the new type. In this case web2py understands
> you do not want to keep the data and will not attempt to do it.
>
obvious in these situations where you need to manually edit the
tables?
I think ROR and Django's approaches to migrations (fully-specifying
deltas vs. maintaining static schemas) are both more burdensome than
web2py's system of "semi-automatic for most changes (adding columns &
tables) but manual for major changes". But the "manual for major
changes" part needs better error messages with suggestions for how to
proceed.
On Nov 14, 8:04 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> You are changing a "text" column (Field) into an "ineteger" column. My
> changing the type web2py assumes you want to keep the data and yet it
> cannot convert "text" to "integert". You have to do the migration in
> two steps: 1) remove the column (comment it and run appadmin) 2) add
> the column again with the new type. In this case web2py understands
> you do not want to keep the data and will not attempt to do it.
>
mdipierro
Nov 18, 2009, 2:46:21 PM11/18/09
to web2py-users
Yes, I would.
Michael Toomim
Jan 6, 2010, 4:14:08 AM1/6/10
to web2py-users
Here's what I propose:
In define_table, at this point:
if migrate:
sql_locker.acquire()
try:
t._create(migrate=migrate, fake_migrate=fake_migrate)
finally:
sql_locker.release()
At the end, it can read the database schema and see if it contains all
the columns needed in the cPickle .table file. If not, it can output
something like "Sorry, database table %s needs column %s to look like
%s, this needs a manual fix".
Is this right? I'm not sure how to read a database table schema
programmatically. Is this different for each db? Hopefully it's at
least easy for postgresql.
On Nov 18 2009, 11:46 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> Yes, I would.
>
mdipierro
Jan 6, 2010, 11:26:55 AM1/6/10
to web2py-users
You are correct and that is the future. It is different for every db.
The new DAL will have hooks to implement it.
The new DAL will have hooks to implement it.
Massimo
Michael Toomim
Jan 6, 2010, 4:43:31 PM1/6/10
to web2py-users
So I should just wait?
mdipierro
Jan 6, 2010, 7:42:07 PM1/6/10
to web2py-users
The new DAL is a priority and we are close to completion (will need
testing). But the first version will just be a rewrite of the current
one. New features will come later. When the new DAL is out we may need
you help to this done.
testing). But the first version will just be a rewrite of the current
one. New features will come later. When the new DAL is out we may need
you help to this done.
Jayadevan M
Feb 9, 2014, 6:40:26 AM2/9/14
to
I am running into issues when changing data from varchar to decimal -
S'<class \'psycopg2.ProgrammingError\'> column "percentage__tmp" is of type numeric but expression is of type character varying\nLINE 1: UPDATE user_academics SET percentage__tmp=percentage;\n
^\nHINT: You will need to rewrite or cast the expression.\n'
I took the approach of commenting out the column, then removing the comment.
Was the fix accepted?
S'<class \'psycopg2.ProgrammingError\'> column "percentage__tmp" is of type numeric but expression is of type character varying\nLINE 1: UPDATE user_academics SET percentage__tmp=percentage;\n
^\nHINT: You will need to rewrite or cast the expression.\n'
I took the approach of commenting out the column, then removing the comment.
Was the fix accepted?
Reply all
Reply to author
Forward
0 new messages