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.cock@googlemail.com>wrote:
On Thu, May 9, 2013 at 11:03 AM, Peter Cock <p.j.a.cock@googlemail.com> wrote:
On Wed, May 8, 2013 at 10:19 PM, Dannon Baker <dannon.baker@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