migration error

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

migration error

Robert Baertsch-2
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/
Reply | Threaded
Open this post in threaded view
|

Re: migration error

Dannon Baker-2
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 <[hidden email]> wrote:
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/
Reply | Threaded
Open this post in threaded view
|

Re: migration error

Peter Cock
On Wed, May 8, 2013 at 10:19 PM, Dannon Baker <[hidden email]> 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/
Reply | Threaded
Open this post in threaded view
|

Re: migration error

Peter Cock
On Thu, May 9, 2013 at 11:03 AM, Peter Cock <[hidden email]> wrote:

> On Wed, May 8, 2013 at 10:19 PM, Dannon Baker <[hidden email]> 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/
Reply | Threaded
Open this post in threaded view
|

Re: migration error

Dannon Baker-2
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 <[hidden email]> wrote:
On Thu, May 9, 2013 at 11:03 AM, Peter Cock <[hidden email]> wrote:
> On Wed, May 8, 2013 at 10:19 PM, Dannon Baker <[hidden email]> 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/
Reply | Threaded
Open this post in threaded view
|

Re: migration error

Peter Cock
On Thu, May 9, 2013 at 12:56 PM, Dannon Baker <[hidden email]> 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/