Re: [galaxy-dev] migration error

2013-05-09 Thread Peter Cock
On Wed, May 8, 2013 at 10:19 PM, Dannon Baker dannon.ba...@gmail.com wrote:

 Hey Robert,

 I assume this is sqlite?  And, when you say you ran this without any
 existing database -- was this was a completely new clone of galaxy, or did
 you update a prior installation and delete database/universe.sqlite manually
 before running?

 -Dannon

Hi guys,

I've just hit this bug too, using SQLite on a working development machine
which was running with schema 114 until I grabbed the latest galaxy-central
just now, revision 80ab774559f8405a46082286c6cf35db420db002

$ sh manage_db.sh upgrade
114 - 115...
Altering password column failed
Traceback (most recent call last):
  File lib/galaxy/model/migrate/versions/0115_longer_user_password_field.py,
line 15, in upgrade
user.c.password.alter(type=String(255))
  File 
/mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/schema.py,
line 491, in alter
return alter_column(self, *p, **k)
  File 
/mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/schema.py,
line 136, in alter_column
engine._run_visitor(visitorcallable, delta)
  File 
/mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
line 2302, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
  File 
/mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
line 1972, in _run_visitor
**kwargs).traverse_single(element)
  File 
/mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/ansisql.py,
line 53, in traverse_single
ret = super(AlterTableVisitor, self).traverse_single(elem)
  File 
/mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/sql/visitors.py,
line 106, in traverse_single
return meth(obj, **kw)
  File 
/mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/databases/sqlite.py,
line 53, in visit_column
self.recreate_table(table,column,delta)
  File 
/mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/databases/sqlite.py,
line 36, in recreate_table
self.execute()
  File 
/mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/ansisql.py,
line 42, in execute
return self.connection.execute(self.buffer.getvalue())
  File 
/mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
line 1449, in execute
params)
  File 
/mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
line 1628, in _execute_text
statement, parameters
  File 
/mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
line 1698, in _execute_context
context)
  File 
/mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
line 1691, in _execute_context
context)
  File 
/mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/default.py,
line 331, in do_execute
cursor.execute(statement, parameters)
OperationalError: (OperationalError) there is already another table or
index with this name: migration_tmp 'ALTER TABLE galaxy_user RENAME TO
migration_tmp' ()
done

Presumably one of the previous migration scripts has left an old
migration_tmp table in place?

Peter
___
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/

To search Galaxy mailing lists use the unified search at:
  http://galaxyproject.org/search/mailinglists/


Re: [galaxy-dev] migration error

2013-05-09 Thread Peter Cock
On Thu, May 9, 2013 at 11:03 AM, Peter Cock p.j.a.c...@googlemail.com wrote:
 On Wed, May 8, 2013 at 10:19 PM, Dannon Baker dannon.ba...@gmail.com wrote:

 Hey Robert,

 I assume this is sqlite?  And, when you say you ran this without any
 existing database -- was this was a completely new clone of galaxy, or did
 you update a prior installation and delete database/universe.sqlite manually
 before running?

 -Dannon

 Hi guys,

 I've just hit this bug too, using SQLite on a working development machine
 which was running with schema 114 until I grabbed the latest galaxy-central
 just now, revision 80ab774559f8405a46082286c6cf35db420db002

 $ sh manage_db.sh upgrade
 114 - 115...
 Altering password column failed
 Traceback (most recent call last):
   File lib/galaxy/model/migrate/versions/0115_longer_user_password_field.py,
 line 15, in upgrade
 user.c.password.alter(type=String(255))
   File 
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/schema.py,
 line 491, in alter
 return alter_column(self, *p, **k)
   File 
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/schema.py,
 line 136, in alter_column
 engine._run_visitor(visitorcallable, delta)
   File 
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
 line 2302, in _run_visitor
 conn._run_visitor(visitorcallable, element, **kwargs)
   File 
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
 line 1972, in _run_visitor
 **kwargs).traverse_single(element)
   File 
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/ansisql.py,
 line 53, in traverse_single
 ret = super(AlterTableVisitor, self).traverse_single(elem)
   File 
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/sql/visitors.py,
 line 106, in traverse_single
 return meth(obj, **kw)
   File 
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/databases/sqlite.py,
 line 53, in visit_column
 self.recreate_table(table,column,delta)
   File 
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/databases/sqlite.py,
 line 36, in recreate_table
 self.execute()
   File 
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/ansisql.py,
 line 42, in execute
 return self.connection.execute(self.buffer.getvalue())
   File 
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
 line 1449, in execute
 params)
   File 
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
 line 1628, in _execute_text
 statement, parameters
   File 
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
 line 1698, in _execute_context
 context)
   File 
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
 line 1691, in _execute_context
 context)
   File 
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/default.py,
 line 331, in do_execute
 cursor.execute(statement, parameters)
 OperationalError: (OperationalError) there is already another table or
 index with this name: migration_tmp 'ALTER TABLE galaxy_user RENAME TO
 migration_tmp' ()
 done

 Presumably one of the previous migration scripts has left an old
 migration_tmp table in place?

Note that attempting to re-run the migration appeared to do nothing:
$ sh manage_db.sh upgrade
$ sh run.sh
...

This isn't a big issue for my development machine, but it would seem that
this migration step aborted halfway (having marked the schema as being
updated to version 115), and did not roll back the database to the previous
state labelled as version 114. If that happens just on SQLite that's tolerable,
but if there is no transaction integrity used on MySQL or PostgreSQL the
migration framework seems very fragile.

Regards,

Peter
___
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/

To search Galaxy mailing lists use the unified search at:
  http://galaxyproject.org/search/mailinglists/


Re: [galaxy-dev] migration error

2013-05-09 Thread Dannon Baker
This isn't an issue with postgresql or mysql; they don't use a temporary
table for table alterations.  Can you open up the sqlite database if you
still have it and see what the contents of the migrate_tmp table were?
 They'll be a copy of a prior migrated table, I'm just curious what from --
it should have been cleaned up previously in the event of successful
migrations.

My hunch is that previously various scripts threw a lot of errors for mysql
and so this table wasn't automatically cleaned up after after the execution
of one of those migration scripts, but the old version of
sqlalchemy-migrate didn't care if it existed or not and would overwrite it,
while the new version is more reticent to delete the table (which is
probably more reasonable, should you need to recover data after an
unsuccessful migration).

In any event, to fix it, you'll want to make sure that the contents of that
migrate_tmp table aren't something you want and drop it, downgrade to 114,
then upgrade to 115 again.  If I can recreate this I'll work on a more
reasonable solution.

Dannon


On Thu, May 9, 2013 at 6:12 AM, Peter Cock p.j.a.c...@googlemail.comwrote:

 On Thu, May 9, 2013 at 11:03 AM, Peter Cock p.j.a.c...@googlemail.com
 wrote:
  On Wed, May 8, 2013 at 10:19 PM, Dannon Baker dannon.ba...@gmail.com
 wrote:
 
  Hey Robert,
 
  I assume this is sqlite?  And, when you say you ran this without any
  existing database -- was this was a completely new clone of galaxy, or
 did
  you update a prior installation and delete database/universe.sqlite
 manually
  before running?
 
  -Dannon
 
  Hi guys,
 
  I've just hit this bug too, using SQLite on a working development machine
  which was running with schema 114 until I grabbed the latest
 galaxy-central
  just now, revision 80ab774559f8405a46082286c6cf35db420db002
 
  $ sh manage_db.sh upgrade
  114 - 115...
  Altering password column failed
  Traceback (most recent call last):
File
 lib/galaxy/model/migrate/versions/0115_longer_user_password_field.py,
  line 15, in upgrade
  user.c.password.alter(type=String(255))
File
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/schema.py,
  line 491, in alter
  return alter_column(self, *p, **k)
File
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/schema.py,
  line 136, in alter_column
  engine._run_visitor(visitorcallable, delta)
File
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
  line 2302, in _run_visitor
  conn._run_visitor(visitorcallable, element, **kwargs)
File
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
  line 1972, in _run_visitor
  **kwargs).traverse_single(element)
File
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/ansisql.py,
  line 53, in traverse_single
  ret = super(AlterTableVisitor, self).traverse_single(elem)
File
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/sql/visitors.py,
  line 106, in traverse_single
  return meth(obj, **kw)
File
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/databases/sqlite.py,
  line 53, in visit_column
  self.recreate_table(table,column,delta)
File
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/databases/sqlite.py,
  line 36, in recreate_table
  self.execute()
File
 /mnt/galaxy/galaxy-central/eggs/sqlalchemy_migrate-0.7.2-py2.6.egg/migrate/changeset/ansisql.py,
  line 42, in execute
  return self.connection.execute(self.buffer.getvalue())
File
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
  line 1449, in execute
  params)
File
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
  line 1628, in _execute_text
  statement, parameters
File
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
  line 1698, in _execute_context
  context)
File
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py,
  line 1691, in _execute_context
  context)
File
 /mnt/galaxy/galaxy-central/eggs/SQLAlchemy-0.7.9-py2.6-linux-x86_64-ucs4.egg/sqlalchemy/engine/default.py,
  line 331, in do_execute
  cursor.execute(statement, parameters)
  OperationalError: (OperationalError) there is already another table or
  index with this name: migration_tmp 'ALTER TABLE galaxy_user RENAME TO
  migration_tmp' ()
  done
 
  Presumably one of the previous migration scripts has left an old
  migration_tmp table in place?

 Note that attempting to re-run the migration appeared to do nothing:
 $ sh manage_db.sh upgrade
 $ sh run.sh
 ...

 This isn't a big issue for 

Re: [galaxy-dev] migration error

2013-05-09 Thread Peter Cock
On Thu, May 9, 2013 at 12:56 PM, Dannon Baker dannon.ba...@gmail.com wrote:
 This isn't an issue with postgresql or mysql; they don't use a temporary
 table for table alterations.

Oh good :)

 Can you open up the sqlite database if you
 still have it and see what the contents of the migrate_tmp table were?
 They'll be a copy of a prior migrated table, I'm just curious what from --
 it should have been cleaned up previously in the event of successful
 migrations.

It looks like whatever was there has been lost (which doesn't really
worry me on this machine - it is a test setup only):

$ sqlite3 database/universe.sqlite
SQLite version 3.6.20
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite select * from migrate_tmp;
Error: no such table: migrate_tmp

 My hunch is that previously various scripts threw a lot of errors for mysql
 and so this table wasn't automatically cleaned up after after the execution
 of one of those migration scripts, but the old version of sqlalchemy-migrate
 didn't care if it existed or not and would overwrite it, while the new
 version is more reticent to delete the table (which is probably more
 reasonable, should you need to recover data after an unsuccessful
 migration).

That makes sense.

 In any event, to fix it, you'll want to make sure that the contents of that
 migrate_tmp table aren't something you want and drop it, downgrade to 114,
 then upgrade to 115 again.  If I can recreate this I'll work on a more
 reasonable solution.

One idea might be to name the migration_tmp tables using the schema
revision to avoid clashes, e.g. migration_115_tmp in this case?

Peter
___
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/

To search Galaxy mailing lists use the unified search at:
  http://galaxyproject.org/search/mailinglists/


Re: [galaxy-dev] migration error

2013-05-08 Thread Dannon Baker
Hey Robert,

I assume this is sqlite?  And, when you say you ran this without any
existing database -- was this was a completely new clone of galaxy, or did
you update a prior installation and delete database/universe.sqlite
manually before running?

-Dannon




On Wed, May 8, 2013 at 2:07 PM, Robert Baertsch
robert.baert...@gmail.comwrote:

 I upgraded to the latest galaxy-central and got an error when running
 migration script 115 which lengthens the password field from 40-255.

 It failed saying that the table migration_tmp already exists.  I ran this
 without any existing database so I don't think it is anything on my end.
 Any pointers?

 .schema migration_tmp
 CREATE TABLE migration_tmp (
 id INTEGER NOT NULL,
 create_time TIMESTAMP,
 update_time TIMESTAMP,
 tool_shed_repository_id INTEGER NOT NULL,
 name VARCHAR(255),
 version VARCHAR(40),
 type VARCHAR(40),
 uninstalled BOOLEAN, error_message TEXT,
 PRIMARY KEY (id),
  FOREIGN KEY(tool_shed_repository_id) REFERENCES
 tool_shed_repository (id)
 );


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

 To search Galaxy mailing lists use the unified search at:
   http://galaxyproject.org/search/mailinglists/

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

To search Galaxy mailing lists use the unified search at:
  http://galaxyproject.org/search/mailinglists/

Re: [galaxy-dev] Migration error: fields in MySQL

2011-06-02 Thread Nate Coraor
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/