John Eppley wrote:

I had an error upgrading my galaxy instance. I got the following exception while migrating the db (during step 64->65):

sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fields FROM form_definition' at line 1") u'SELECT id, fields FROM form_definition' []

It seems my version (4.1.22-log) of MySQL did not like 'fields' as a column name. If I alias the formdefinition as f and us f.fields, the error goes away. I also had to modify migration 76 for the same reason.

Hi John, Thanks for the patch, I've committed it as 5619:b6689fb6532e. --nate

Here is my diff of the migrations dir: diff -r 50e249442c5a lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py --- a/lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py Thu Apr 07 08:39:07 2011 -0400 +++ b/lib/galaxy/model/migrate/versions/0065_add_name_to_form_fields_and_values.py Fri Apr 15 11:09:26 2011 -0400 @@ -39,7 +39,7 @@ return '' # Go through the entire table and add a 'name' attribute for each field # in the list of fields for each form definition - cmd = "SELECT id, fields FROM form_definition" + cmd = "SELECT f.id, f.fields FROM form_definition f" result = db_session.execute( cmd ) for row in result: form_definition_id = row[0] @@ -53,7 +53,7 @@ field[ 'helptext' ] = field[ 'helptext' ].replace("'", "''").replace('"', "") field[ 'label' ] = field[ 'label' ].replace("'", "''") fields_json = to_json_string( fields_list ) - cmd = "UPDATE form_definition SET fields='%s' WHERE id=%i" %( fields_json, form_definition_id ) + cmd = "UPDATE form_definition f SET f.fields='%s' WHERE f.id=%i" %( fields_json, form_definition_id ) db_session.execute( cmd ) # replace the values list in the content field of the form_values table with a name:value dict cmd = "SELECT form_values.id, form_values.content, form_definition.fields" \ @@ -112,7 +112,7 @@ cmd = "UPDATE form_values SET content='%s' WHERE id=%i" %( to_json_string( values_list ), form_values_id ) db_session.execute( cmd ) # remove name attribute from the field column of the form_definition table - cmd = "SELECT id, fields FROM form_definition" + cmd = "SELECT f.id, f.fields FROM form_definition f" result = db_session.execute( cmd ) for row in result: form_definition_id = row[0] @@ -124,5 +124,5 @@ for index, field in enumerate( fields_list ): if field.has_key( 'name' ): del field[ 'name' ] - cmd = "UPDATE form_definition SET fields='%s' WHERE id=%i" %( to_json_string( fields_list ), form_definition_id ) + cmd = "UPDATE form_definition f SET f.fields='%s' WHERE id=%i" %( to_json_string( fields_list ), form_definition_id ) db_session.execute( cmd ) diff -r 50e249442c5a lib/galaxy/model/migrate/versions/0076_fix_form_values_data_corruption.py --- a/lib/galaxy/model/migrate/versions/0076_fix_form_values_data_corruption.py Thu Apr 07 08:39:07 2011 -0400 +++ b/lib/galaxy/model/migrate/versions/0076_fix_form_values_data_corruption.py Fri Apr 15 11:09:26 2011 -0400 @@ -32,7 +32,7 @@ def upgrade(): print __doc__ metadata.reflect() - cmd = "SELECT form_values.id as id, form_values.content as field_values, form_definition.fields as fields " \ + cmd = "SELECT form_values.id as id, form_values.content as field_values, form_definition.fields as fdfields " \ + " FROM form_definition, form_values " \ + " WHERE form_values.form_definition_id=form_definition.id " \ + " ORDER BY form_values.id" @@ -46,7 +46,7 @@ except Exception, e: corrupted_rows = corrupted_rows + 1 # content field is corrupted - fields_list = from_json_string( _sniffnfix_pg9_hex( str( row['fields'] ) ) ) + fields_list = from_json_string( _sniffnfix_pg9_hex( str( row['fdfields'] ) ) ) field_values_str = _sniffnfix_pg9_hex( str( row['field_values'] ) ) try: #Encoding errors? Just to be safe.

-j ___________________________________________________________ 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:

http://lists.bx.psu.edu/