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/
___________________________________________________________
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/
