(OperationalError) no such column: history_dataset_association.extended_metadata_id
Hi all, Earlier today I updated my development server, which uses SQLite, and ran the update schema script (now at v118). With hindsight there could have been a warning that I missed - because afterwards despite updating to the galaxy-central tip, trying to use any of the tools failed with an SQL error: OperationalError: (OperationalError) no such column: history_dataset_association.extended_metadata_id u'SELECT history_dataset_association.id AS history_dataset_association_id, ... $ ./manage_db.sh db_version 118 $ ./manage_db.sh version 118 Attempting a downgrade/upgrade suggested I had a stale migration_tmp table blocking this, Since this is only a development instance, I removed the SQLite database (and the old data files) to allow a clean regeneration: $ ./run.sh ... galaxy.model.migrate.check INFO 2014-02-18 13:25:52,248 Migrating 117 -> 118... galaxy.model.migrate.check INFO 2014-02-18 13:25:54,176 galaxy.model.migrate.check INFO 2014-02-18 13:25:54,177 Add link from history_dataset_association to the extended_metadata table galaxy.model.migrate.check INFO 2014-02-18 13:25:54,177 galaxy.model.migrate.check INFO 2014-02-18 13:25:54,177 migrate.versioning.repository DEBUG 2014-02-18 13:25:54,206 Loading repository lib/tool_shed/galaxy_install/migrate... ... This fixed the history_dataset_association.extended_metadata_id error - so is the most likely explanation a failed schema update? Might a stale migration_tmp table have been to blame? Thanks, Peter
On Tue, Feb 18, 2014 at 8:30 AM, Peter Cock <p.j.a.cock@googlemail.com>wrote:
This fixed the history_dataset_association.extended_metadata_id error - so is the most likely explanation a failed schema update? Might a stale migration_tmp table have been to blame?
Yes, I've seen this before when I've killed (or otherwise crashed) a migration in process; migrate_tmp doesn't get automatically cleaned up -- and, to allow for recovery, probably shouldn't. Any idea what may have caused it in your case?For a development database I've most commonly just deleted the migrate_tmp table manually and rerun the migration. It's worth noting that *only* sqlite can have this problem, due to the way migrations work. -Dannon
On Tue, Feb 18, 2014 at 1:40 PM, Dannon Baker <dannon.baker@gmail.com> wrote:
On Tue, Feb 18, 2014 at 8:30 AM, Peter Cock <p.j.a.cock@googlemail.com> wrote:
This fixed the history_dataset_association.extended_metadata_id error - so is the most likely explanation a failed schema update? Might a stale migration_tmp table have been to blame?
Yes, I've seen this before when I've killed (or otherwise crashed) a migration in process; migrate_tmp doesn't get automatically cleaned up -- and, to allow for recovery, probably shouldn't.
Tricky one - perhaps what would be best is to abort with an explicit error if there is already a (stale) migrate_tmp table present?
Any idea what may have caused it in your case?For a development database I've most commonly just deleted the migrate_tmp table manually and rerun the migration.
I don't know what went wrong here - it could have happened a while back, with the stale migrate_tmp table waiting harmlessly until I ran a schema update today.
It's worth noting that *only* sqlite can have this problem, due to the way migrations work.
I thought that might be the case. Thanks, Peter
Hi Dannon, I'm facing the same problem now. Could you help me with the steps to delete the migrate_tmp table manually? I'm trying to use sqlite from command line but get the following error: Unable to open database "universe.sqlite": file is encrypted or is not a database Thanks and regards, Pieter. From: galaxy-dev-bounces@lists.bx.psu.edu [mailto:galaxy-dev-bounces@lists.bx.psu.edu] On Behalf Of Dannon Baker Sent: dinsdag 18 februari 2014 14:40 To: Peter Cock Cc: Galaxy Dev Subject: Re: [galaxy-dev] (OperationalError) no such column: history_dataset_association.extended_metadata_id On Tue, Feb 18, 2014 at 8:30 AM, Peter Cock <p.j.a.cock@googlemail.com<mailto:p.j.a.cock@googlemail.com>> wrote: This fixed the history_dataset_association.extended_metadata_id error - so is the most likely explanation a failed schema update? Might a stale migration_tmp table have been to blame? Yes, I've seen this before when I've killed (or otherwise crashed) a migration in process; migrate_tmp doesn't get automatically cleaned up -- and, to allow for recovery, probably shouldn't. Any idea what may have caused it in your case?For a development database I've most commonly just deleted the migrate_tmp table manually and rerun the migration. It's worth noting that *only* sqlite can have this problem, due to the way migrations work. -Dannon
Hey Pieter, sure. The sqlite database is in sqlite3 format, so you'll need to use 'sqlite3 database/universe.sqlite' to access it. The following should work: sqlite3 database/universe.sqlite '.dump migrate_tmp' > temporary_backup.sql sqlite3 database/universe.sqlite 'drop table migrate_tmp;' And, once that's done, verify that everything works as expected and that whatever table is in temporary_backup.sql actually did get migrated. -Dannon On Tue, Apr 22, 2014 at 8:41 AM, Lukasse, Pieter <pieter.lukasse@wur.nl>wrote:
Hi Dannon,
I’m facing the same problem now. Could you help me with the steps to delete the migrate_tmp table manually? I’m trying to use sqlite from command line but get the following error:
Unable to open database "universe.sqlite": file is encrypted or is not a database
Thanks and regards,
Pieter.
*From:* galaxy-dev-bounces@lists.bx.psu.edu [mailto: galaxy-dev-bounces@lists.bx.psu.edu] *On Behalf Of *Dannon Baker *Sent:* dinsdag 18 februari 2014 14:40 *To:* Peter Cock *Cc:* Galaxy Dev *Subject:* Re: [galaxy-dev] (OperationalError) no such column: history_dataset_association.extended_metadata_id
On Tue, Feb 18, 2014 at 8:30 AM, Peter Cock <p.j.a.cock@googlemail.com> wrote:
This fixed the history_dataset_association.extended_metadata_id error - so is the most likely explanation a failed schema update? Might a stale migration_tmp table have been to blame?
Yes, I've seen this before when I've killed (or otherwise crashed) a migration in process; migrate_tmp doesn't get automatically cleaned up -- and, to allow for recovery, probably shouldn't. Any idea what may have caused it in your case?For a development database I've most commonly just deleted the migrate_tmp table manually and rerun the migration. It's worth noting that *only* sqlite can have this problem, due to the way migrations work.
-Dannon
Hi Dannon, Thanks, this helped. Just for the record: I did find a small typo in my mail and in your script : should be migration_tmp instead of migrate_tmp ;) Best regards, Pieter. From: Dannon Baker [mailto:dannon.baker@gmail.com] Sent: dinsdag 22 april 2014 14:59 To: Lukasse, Pieter Cc: Peter Cock; Galaxy Dev Subject: Re: [galaxy-dev] (OperationalError) no such column: history_dataset_association.extended_metadata_id Hey Pieter, sure. The sqlite database is in sqlite3 format, so you'll need to use 'sqlite3 database/universe.sqlite' to access it. The following should work: sqlite3 database/universe.sqlite '.dump migrate_tmp' > temporary_backup.sql sqlite3 database/universe.sqlite 'drop table migrate_tmp;' And, once that's done, verify that everything works as expected and that whatever table is in temporary_backup.sql actually did get migrated. -Dannon On Tue, Apr 22, 2014 at 8:41 AM, Lukasse, Pieter <pieter.lukasse@wur.nl<mailto:pieter.lukasse@wur.nl>> wrote: Hi Dannon, I’m facing the same problem now. Could you help me with the steps to delete the migrate_tmp table manually? I’m trying to use sqlite from command line but get the following error: Unable to open database "universe.sqlite": file is encrypted or is not a database Thanks and regards, Pieter. From: galaxy-dev-bounces@lists.bx.psu.edu<mailto:galaxy-dev-bounces@lists.bx.psu.edu> [mailto:galaxy-dev-bounces@lists.bx.psu.edu<mailto:galaxy-dev-bounces@lists.bx.psu.edu>] On Behalf Of Dannon Baker Sent: dinsdag 18 februari 2014 14:40 To: Peter Cock Cc: Galaxy Dev Subject: Re: [galaxy-dev] (OperationalError) no such column: history_dataset_association.extended_metadata_id On Tue, Feb 18, 2014 at 8:30 AM, Peter Cock <p.j.a.cock@googlemail.com<mailto:p.j.a.cock@googlemail.com>> wrote: This fixed the history_dataset_association.extended_metadata_id error - so is the most likely explanation a failed schema update? Might a stale migration_tmp table have been to blame? Yes, I've seen this before when I've killed (or otherwise crashed) a migration in process; migrate_tmp doesn't get automatically cleaned up -- and, to allow for recovery, probably shouldn't. Any idea what may have caused it in your case?For a development database I've most commonly just deleted the migrate_tmp table manually and rerun the migration. It's worth noting that *only* sqlite can have this problem, due to the way migrations work. -Dannon
Ok, great, glad that worked for you. And, thanks for the heads up on the actual table name :) -Dannon On Tue, Apr 22, 2014 at 9:14 AM, Lukasse, Pieter <pieter.lukasse@wur.nl>wrote:
Hi Dannon,
Thanks, this helped.
Just for the record: I did find a small typo in my mail and in your script : should be migration_tmp instead of migrate_tmp ;)
Best regards,
Pieter.
*From:* Dannon Baker [mailto:dannon.baker@gmail.com] *Sent:* dinsdag 22 april 2014 14:59 *To:* Lukasse, Pieter *Cc:* Peter Cock; Galaxy Dev
*Subject:* Re: [galaxy-dev] (OperationalError) no such column: history_dataset_association.extended_metadata_id
Hey Pieter, sure. The sqlite database is in sqlite3 format, so you'll need to use 'sqlite3 database/universe.sqlite' to access it.
The following should work:
sqlite3 database/universe.sqlite '.dump migrate_tmp' > temporary_backup.sql
sqlite3 database/universe.sqlite 'drop table migrate_tmp;'
And, once that's done, verify that everything works as expected and that whatever table is in temporary_backup.sql actually did get migrated.
-Dannon
On Tue, Apr 22, 2014 at 8:41 AM, Lukasse, Pieter <pieter.lukasse@wur.nl> wrote:
Hi Dannon,
I’m facing the same problem now. Could you help me with the steps to delete the migrate_tmp table manually? I’m trying to use sqlite from command line but get the following error:
Unable to open database "universe.sqlite": file is encrypted or is not a database
Thanks and regards,
Pieter.
*From:* galaxy-dev-bounces@lists.bx.psu.edu [mailto: galaxy-dev-bounces@lists.bx.psu.edu] *On Behalf Of *Dannon Baker *Sent:* dinsdag 18 februari 2014 14:40 *To:* Peter Cock *Cc:* Galaxy Dev *Subject:* Re: [galaxy-dev] (OperationalError) no such column: history_dataset_association.extended_metadata_id
On Tue, Feb 18, 2014 at 8:30 AM, Peter Cock <p.j.a.cock@googlemail.com> wrote:
This fixed the history_dataset_association.extended_metadata_id error - so is the most likely explanation a failed schema update? Might a stale migration_tmp table have been to blame?
Yes, I've seen this before when I've killed (or otherwise crashed) a migration in process; migrate_tmp doesn't get automatically cleaned up -- and, to allow for recovery, probably shouldn't. Any idea what may have caused it in your case?For a development database I've most commonly just deleted the migrate_tmp table manually and rerun the migration. It's worth noting that *only* sqlite can have this problem, due to the way migrations work.
-Dannon
participants (3)
-
Dannon Baker -
Lukasse, Pieter -
Peter Cock