database migration issue 64 ->65 - postgresql
Good Afternoon, Firstly am a massive fan of galaxy, really like the things that it can do, but I am a bit stuck updating our instance here and its starting to hold us back. I was hoping someone could offer me some assistance with a database migration (I am pulling my hair out). I am using postgresql and galaxy is updated to … galaxy@jic55119:~/software/galaxy-ceneral$ hg tip changeset: 5751:e132a1398caa tag: tip user: jeremy goecks <jeremy.goecks@emory.edu> date: Mon Jun 27 17:25:32 2011 -0400 summary: Additional security for loading visualizations. And I receive the following error which is blocking me, galaxy@jic55119:~/software/galaxy-ceneral$ sh manage_db.sh upgrade 64 -> 65... Migration script to add 'name' attribute to the JSON dict which describes a form definition field and the form values in the database. In the 'form_values' table, the 'content' column is now a JSON dict instead of a list. Traceback (most recent call last): File "./scripts/manage_db.py", line 63, in <module> main( repository=repo, url=db_url ) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/shell.py", line 150, in main ret = command_func(**kwargs) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 221, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 349, in _migrate schema.runchange(ver, change, changeset.step) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/schema.py", line 184, in runchange change.run(self.engine, step) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/script/py.py", line 101, in run func() File "lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py", line 57, in upgrade db_session.execute( cmd ) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/scoping.py", line 127, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/session.py", line 755, in execute clause, params or {}) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 824, in execute return Connection.executors[c](self, object, multiparams, params) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement return self.__execute_context(context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "f" of relation "form_definition" does not exist LINE 1: UPDATE form_definition AS f SET f.fields='[{"layout": "", "n... ^ 'UPDATE form_definition AS f SET f.fields=\'[{"layout": "", "name": "field_0", "default": "yer nom", "required": "required", "label": "Sample Name", "visible": true, "helptext": "??", "type": "TextField"}, {"layout": "", "name": "field_1", "default": "", "required": "required", "label": "run_this?", "visible": true, "helptext": "??", "type": "CheckboxField"}]\' WHERE f.id=2' {} Thank you for all your assistance. Michael Burrell.
Hi Michael, I'm not exactly sure why this migration script is not working for you( and unfortunately, er maybe fortunately, I didn't write it ), so we may be forced to try a few things. How are you bumping into this error? Are you trying to upgrade a clean Galaxy instance (where you have not yet seen this error), or are you trying multiple times to upgrade the same instance, and you're continually seeing this issue? Can you paste the result of the following sql query against your db? select * from migrate_version; I'm not sure the following will help, but look at line # 56 in ~/lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py - it should look like this: cmd = "UPDATE form_definition AS f SET f.fields='%s' WHERE f.id=%i" %( fields_json, form_definition_id ) Change the line to look like this, and try the migration. cmd = "UPDATE form_definition AS f SET f.fields='%s' WHERE id=%i" %( fields_json, form_definition_id ) Greg Von Kuster On Jun 28, 2011, at 6:58 AM, michael burrell (TSL) wrote:
Good Afternoon,
Firstly am a massive fan of galaxy, really like the things that it can do, but I am a bit stuck updating our instance here and its starting to hold us back.
I was hoping someone could offer me some assistance with a database migration (I am pulling my hair out). I am using postgresql and galaxy is updated to …
galaxy@jic55119:~/software/galaxy-ceneral$ hg tip changeset: 5751:e132a1398caa tag: tip user: jeremy goecks <jeremy.goecks@emory.edu> date: Mon Jun 27 17:25:32 2011 -0400 summary: Additional security for loading visualizations.
And I receive the following error which is blocking me,
galaxy@jic55119:~/software/galaxy-ceneral$ sh manage_db.sh upgrade 64 -> 65...
Migration script to add 'name' attribute to the JSON dict which describes a form definition field and the form values in the database. In the 'form_values' table, the 'content' column is now a JSON dict instead of a list.
Traceback (most recent call last): File "./scripts/manage_db.py", line 63, in <module> main( repository=repo, url=db_url ) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/shell.py", line 150, in main ret = command_func(**kwargs) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 221, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 349, in _migrate schema.runchange(ver, change, changeset.step) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/schema.py", line 184, in runchange change.run(self.engine, step) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/script/py.py", line 101, in run func() File "lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py", line 57, in upgrade db_session.execute( cmd ) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/scoping.py", line 127, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/session.py", line 755, in execute clause, params or {}) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 824, in execute return Connection.executors[c](self, object, multiparams, params) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement return self.__execute_context(context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "f" of relation "form_definition" does not exist LINE 1: UPDATE form_definition AS f SET f.fields='[{"layout": "", "n... ^ 'UPDATE form_definition AS f SET f.fields=\'[{"layout": "", "name": "field_0", "default": "yer nom", "required": "required", "label": "Sample Name", "visible": true, "helptext": "??", "type": "TextField"}, {"layout": "", "name": "field_1", "default": "", "required": "required", "label": "run_this?", "visible": true, "helptext": "??", "type": "CheckboxField"}]\' WHERE f.id=2' {}
Thank you for all your assistance.
Michael Burrell.
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Greg Von Kuster Galaxy Development Team greg@bx.psu.edu
Hell all, Michael discovered a bug in the lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py migration script. To get the fix, you can pull from our development repo at https://bitbucket.org/galaxy/galaxy-central/wiki/Home. As another option, I've attached a corrected script to this message. Ypou'll need to place it in ~/lib/galaxy/model/migrate/versions, replacing the script with the bug in it. Let us know if you have any problems with this. Thanks! Greg Von Kuster On Jun 28, 2011, at 6:58 AM, michael burrell (TSL) wrote:
Good Afternoon,
Firstly am a massive fan of galaxy, really like the things that it can do, but I am a bit stuck updating our instance here and its starting to hold us back.
I was hoping someone could offer me some assistance with a database migration (I am pulling my hair out). I am using postgresql and galaxy is updated to …
galaxy@jic55119:~/software/galaxy-ceneral$ hg tip changeset: 5751:e132a1398caa tag: tip user: jeremy goecks <jeremy.goecks@emory.edu> date: Mon Jun 27 17:25:32 2011 -0400 summary: Additional security for loading visualizations.
And I receive the following error which is blocking me,
galaxy@jic55119:~/software/galaxy-ceneral$ sh manage_db.sh upgrade 64 -> 65...
Migration script to add 'name' attribute to the JSON dict which describes a form definition field and the form values in the database. In the 'form_values' table, the 'content' column is now a JSON dict instead of a list.
Traceback (most recent call last): File "./scripts/manage_db.py", line 63, in <module> main( repository=repo, url=db_url ) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/shell.py", line 150, in main ret = command_func(**kwargs) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 221, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 349, in _migrate schema.runchange(ver, change, changeset.step) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/schema.py", line 184, in runchange change.run(self.engine, step) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/script/py.py", line 101, in run func() File "lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py", line 57, in upgrade db_session.execute( cmd ) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/scoping.py", line 127, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/session.py", line 755, in execute clause, params or {}) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 824, in execute return Connection.executors[c](self, object, multiparams, params) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement return self.__execute_context(context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "f" of relation "form_definition" does not exist LINE 1: UPDATE form_definition AS f SET f.fields='[{"layout": "", "n... ^ 'UPDATE form_definition AS f SET f.fields=\'[{"layout": "", "name": "field_0", "default": "yer nom", "required": "required", "label": "Sample Name", "visible": true, "helptext": "??", "type": "TextField"}, {"layout": "", "name": "field_1", "default": "", "required": "required", "label": "run_this?", "visible": true, "helptext": "??", "type": "CheckboxField"}]\' WHERE f.id=2' {}
Thank you for all your assistance.
Michael Burrell.
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Greg Von Kuster Galaxy Development Team greg@bx.psu.edu
Sorry, for some reason, I attached the compiled version of the script to my initial message - here's the code... On Jun 30, 2011, at 12:04 PM, Greg Von Kuster wrote:
Hell all,
Michael discovered a bug in the lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py migration script. To get the fix, you can pull from our development repo at https://bitbucket.org/galaxy/galaxy-central/wiki/Home. As another option, I've attached a corrected script to this message. Ypou'll need to place it in ~/lib/galaxy/model/migrate/versions, replacing the script with the bug in it. Let us know if you have any problems with this.
Thanks!
Greg Von Kuster
<0065_add_name_to_form_fields_and_values.pyc>
On Jun 28, 2011, at 6:58 AM, michael burrell (TSL) wrote:
Good Afternoon,
Firstly am a massive fan of galaxy, really like the things that it can do, but I am a bit stuck updating our instance here and its starting to hold us back.
I was hoping someone could offer me some assistance with a database migration (I am pulling my hair out). I am using postgresql and galaxy is updated to …
galaxy@jic55119:~/software/galaxy-ceneral$ hg tip changeset: 5751:e132a1398caa tag: tip user: jeremy goecks <jeremy.goecks@emory.edu> date: Mon Jun 27 17:25:32 2011 -0400 summary: Additional security for loading visualizations.
And I receive the following error which is blocking me,
galaxy@jic55119:~/software/galaxy-ceneral$ sh manage_db.sh upgrade 64 -> 65...
Migration script to add 'name' attribute to the JSON dict which describes a form definition field and the form values in the database. In the 'form_values' table, the 'content' column is now a JSON dict instead of a list.
Traceback (most recent call last): File "./scripts/manage_db.py", line 63, in <module> main( repository=repo, url=db_url ) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/shell.py", line 150, in main ret = command_func(**kwargs) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 221, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/api.py", line 349, in _migrate schema.runchange(ver, change, changeset.step) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/schema.py", line 184, in runchange change.run(self.engine, step) File "/home/galaxy/software/galaxy-ceneral/eggs/sqlalchemy_migrate-0.5.4-py2.5.egg/migrate/versioning/script/py.py", line 101, in run func() File "lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py", line 57, in upgrade db_session.execute( cmd ) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/scoping.py", line 127, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/orm/session.py", line 755, in execute clause, params or {}) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 824, in execute return Connection.executors[c](self, object, multiparams, params) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement return self.__execute_context(context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File "/home/galaxy/software/galaxy-ceneral/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "f" of relation "form_definition" does not exist LINE 1: UPDATE form_definition AS f SET f.fields='[{"layout": "", "n... ^ 'UPDATE form_definition AS f SET f.fields=\'[{"layout": "", "name": "field_0", "default": "yer nom", "required": "required", "label": "Sample Name", "visible": true, "helptext": "??", "type": "TextField"}, {"layout": "", "name": "field_1", "default": "", "required": "required", "label": "run_this?", "visible": true, "helptext": "??", "type": "CheckboxField"}]\' WHERE f.id=2' {}
Thank you for all your assistance.
Michael Burrell.
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Greg Von Kuster Galaxy Development Team greg@bx.psu.edu
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
Greg Von Kuster Galaxy Development Team greg@bx.psu.edu
participants (2)
-
Greg Von Kuster -
michael burrell (TSL)