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.
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