It's that previous versions of sqlalchemy-migrate didn't clean up these tables under sqlite and didn't mind overwriting them if they existed, but the new version both does clean up after itself, and expects the table to not exist, failing miserably if it does.

I think there's a Trello card for fixing this somewhere, but yes, the temporary workaround is to just drop the migration_tmp table if this happens and migrate again.

On Jul 1, 2013, at 3:16 PM, Brad Chapman <chapmanb@50mail.com> wrote:

>
> Lee;
>
>> Thanks Brad!  I updated with hg pull -u and then ran "sh manage_db.sh
>> upgrade" but I have an error.  Could you help?  I backed up my database
>> before I ran the upgrade script, so I can go back in time.
>
> I think there is something funky going on with the version of
> SQLAlchemy-migrate currently in use in Galaxy, at least on SQLite
> databases. It creates temporary tables but then they are not getting
> cleaned up.
>
> The best fix is to reset yourself back to the original version (minus
> 1 to take into account the LIMS tables):
>
> sh manage_db.sh downgrade --version=108
>
> Then redo the upgrade:
>
> sh manage_db.sh uprade
>
> Anytime you run into the intermittent error, you can manually fix with:
>
> $ sqlite3 database/universe.sqlite
> sqlite> drop table migration_tmp;
>
> Keep your backup around and you can do this with the new version. Sorry
> for the ugly hack but that was a reasonable workaround. There is some
> discussion on the mailing list about this previously but I don't think
> there was a good conclusion:
>
> http://lists.bx.psu.edu/pipermail/galaxy-dev/2013-May/014570.html
>
> Hope this cleans it up for you,
> Brad
>
>>
>> (sorry about the wall of text)
>>
>> 109 -> 110...
>> /home/galaxy/bin/chapman-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/dialects/sqlite/base.py:723:
>> SAWarning: Did not recognize type 'BIGINT' of column 'bytes'
>>
>> Add UUID column to dataset table
>>
>> done
>> 110 -> 111...
>>
>> Add support for job destinations to the job table
>>
>> done
>> 111 -> 112...
>>
>> Migration script to add the data_manager_history_association table and
>> data_manager_job_association.
>>
>> 0112_add_data_manager_history_association_and_data_manager_job_association_tables
>> DEBUG 2013-07-01 14:46:25,549 Created data_manager_history_association table
>> 0112_add_data_manager_history_association_and_data_manager_job_association_tables
>> DEBUG 2013-07-01 14:46:25,549 Created data_manager_history_association table
>> Created data_manager_history_association table
>> 0112_add_data_manager_history_association_and_data_manager_job_association_tables
>> DEBUG 2013-07-01 14:46:25,890 Created data_manager_job_association table
>> 0112_add_data_manager_history_association_and_data_manager_job_association_tables
>> DEBUG 2013-07-01 14:46:25,890 Created data_manager_job_association table
>> Created data_manager_job_association table
>> done
>> 112 -> 113...
>>
>> Migration script to update the migrate_tools.repository_path column to
>> point to the new location lib/tool_shed/galaxy_install/migrate.
>>
>> done
>> 113 -> 114...
>>
>> Migration script to update the migrate_tools.repository_path column to
>> point to the new location lib/tool_shed/galaxy_install/migrate.
>>
>> done
>> 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
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/schema.py",
>> line 491, in alter
>>    return alter_column(self, *p, **k)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/schema.py",
>> line 136, in alter_column
>>    engine._run_visitor(visitorcallable, delta)
>>  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 2302,
>> in _run_visitor
>>    conn._run_visitor(visitorcallable, element, **kwargs)
>>  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1972,
>> in _run_visitor
>>    **kwargs).traverse_single(element)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/ansisql.py",
>> line 53, in traverse_single
>>    ret = super(AlterTableVisitor, self).traverse_single(elem)
>>  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/visitors.py", line 106,
>> in traverse_single
>>    return meth(obj, **kw)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/databases/sqlite.py",
>> line 53, in visit_column
>>    self.recreate_table(table,column,delta)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/databases/sqlite.py",
>> line 36, in recreate_table
>>    self.execute()
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/changeset/ansisql.py",
>> line 42, in execute
>>    return self.connection.execute(self.buffer.getvalue())
>>  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1449,
>> in execute
>>    params)
>>  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1628,
>> in _execute_text
>>    statement, parameters
>>  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1698,
>> in _execute_context
>>    context)
>>  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1691,
>> in _execute_context
>>    context)
>>  File "build/bdist.linux-x86_64/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 u'ALTER TABLE galaxy_user RENAME TO
>> migration_tmp' ()
>> done
>> 115 -> 116...
>>
>> Tables for a next generation sequencing lims tied to requests.
>>
>> Traceback (most recent call last):
>>  File "./scripts/manage_db.py", line 65, in <module>
>>    main( repository=repo, url=db_url )
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/shell.py",
>> line 207, in main
>>    ret = command_func(**kwargs)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/api.py",
>> line 186, in upgrade
>>    return _migrate(url, repository, version, upgrade=True, err=err, **opts)
>>  File "<string>", line 2, in _migrate
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/util/__init__.py",
>> line 159, in with_engine
>>    return f(*a, **kw)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/api.py",
>> line 366, in _migrate
>>    schema.runchange(ver, change, changeset.step)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/schema.py",
>> line 91, in runchange
>>    change.run(self.engine, step)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/script/py.py",
>> line 145, in run
>>    script_func(engine)
>>  File "lib/galaxy/model/migrate/versions/0116_seq_lims.py", line 52, in
>> upgrade
>>    for table in _get_tables(metadata):
>>  File "lib/galaxy/model/migrate/versions/0116_seq_lims.py", line 34, in
>> _get_tables
>>    primary_key = True),
>>  File "build/bdist.linux-x86_64/egg/sqlalchemy/schema.py", line 305, in
>> __new__
>> sqlalchemy.exc.InvalidRequestError: Table 'sample_request_map' is already
>> defined for this MetaData instance.  Specify 'extend_existing=True' to
>> redefine options and columns on an existing Table object.
>> Error in sys.excepthook:
>> Traceback (most recent call last):
>>  File "/usr/lib/python2.7/dist-packages/apport_python_hook.py", line 64,
>> in apport_excepthook
>>    from apport.fileutils import likely_packaged, get_recent_crashes
>> ImportError: No module named apport.fileutils
>>
>> Original exception was:
>> Traceback (most recent call last):
>>  File "./scripts/manage_db.py", line 65, in <module>
>>    main( repository=repo, url=db_url )
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/shell.py",
>> line 207, in main
>>    ret = command_func(**kwargs)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/api.py",
>> line 186, in upgrade
>>    return _migrate(url, repository, version, upgrade=True, err=err, **opts)
>>  File "<string>", line 2, in _migrate
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/util/__init__.py",
>> line 159, in with_engine
>>    return f(*a, **kw)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/api.py",
>> line 366, in _migrate
>>    schema.runchange(ver, change, changeset.step)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/schema.py",
>> line 91, in runchange
>>    change.run(self.engine, step)
>>  File
>> "/home/galaxy/bin/chapman-dist/eggs/sqlalchemy_migrate-0.7.2-py2.7.egg/migrate/versioning/script/py.py",
>> line 145, in run
>>    script_func(engine)
>>  File "lib/galaxy/model/migrate/versions/0116_seq_lims.py", line 52, in
>> upgrade
>>    for table in _get_tables(metadata):
>>  File "lib/galaxy/model/migrate/versions/0116_seq_lims.py", line 34, in
>> _get_tables
>>    primary_key = True),
>>  File "build/bdist.linux-x86_64/egg/sqlalchemy/schema.py", line 305, in
>> __new__
>> sqlalchemy.exc.InvalidRequestError: Table 'sample_request_map' is already
>> defined for this MetaData instance.  Specify 'extend_existing=True' to
>> redefine options and columns on an existing Table object.
>>
>>
>> On Mon, Jul 1, 2013 at 12:46 PM, Brad Chapman <chapmanb@50mail.com> wrote:
>>
>>>
>>> Lee;
>>>
>>>> Hi, I would like to remove the "multiplexed" menu from the nglims sample
>>>> information form.  How would I do that?  In our lab, we don't want
>>>> submitters making their own libraries since they might accidentally use
>>> the
>>>> same barcodes or somehow mess up others' libraries.  We want to remove
>>> that
>>>> option altogether.
>>>
>>> Good idea. I added a flag to the nglims.yaml file so you can specify
>>> whether barcode multiplexing is available or not:
>>>
>>>
>>> https://bitbucket.org/chapmanb/galaxy-central/src/62f7dc3864535e68af7aa2a7ef0c37e45eb9707a/tool-data/nglims.yaml?at=default#cl-97
>>>
>>> setting `available: false` will turn off the option for the user to
>>> select and add this.
>>>
>>> If you update from the latest in bitbucket it will have this change. I
>>> also synchronized with the most recent galaxy-dist, so an update will
>>> pull in those changes as well.
>>>
>>>> Actually along the same lines, we would like to remove all "services
>>>> needed" check boxes too and simply have all of the data entry on one
>>> page.
>>>
>>> Moving to a single page entry would be a big change from the current
>>> wizard approach. You can control what services are available by default
>>> using the `default` flag in the nglims.yaml:
>>>
>>>
>>> https://bitbucket.org/chapmanb/galaxy-central/src/62f7dc3864535e68af7aa2a7ef0c37e45eb9707a/tool-data/nglims.yaml?at=default#cl-133
>>>
>>> Currently sequencing is the only one selected by default but you can add
>>> others if they're commonly used to make the submission process easier
>>> for users.
>>>
>>> Hope this helps,
>>> Brad
>>>
>>
>>
>>
>> --
>> Lee Katz, Ph.D.

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