Error in database after trying the innocently named, but devastatingly evil "test" in manage_db.sh

List overview All Threads
Download

newer

older

Galaxy February 8, 2013...

Extract genomic DNA job error

Thon de Boer

9 Feb 2013 9 Feb '13

3:32 a.m.

Hi,

I had a small issue with the total usage of my galaxy system showing some negative random number so I figured my database was a little “off”.

I snooped around for some database management tools and found “manage_db.sh” which had an option innocently called “test”.

I ran it, without reading the little fine print that states: “Performs the upgrade and downgrade option on the given database. This is not a real test and may leave the database in a bad state. You should therefore better run the test on a copy of your database.”

Well…It surely did leave my database in a bad state since now I get this when I try to access the toolshed installed tools:

Error Traceback:

View as: http://srv151/admin_toolshed/browse_repositories Interactive | http://srv151/admin_toolshed/browse_repositories Text | http://srv151/admin_toolshed/browse_repositories XML http://srv151/admin_toolshed/browse_repositories (full)

⇝ ProgrammingError: (ProgrammingError) relation "repository_repository_dependency_association" does not exist LINE 2: FROM repository_repository_dependency_association ^ 'SELECT repository_repository_dependency_association.id AS repository_repository_dependency_association_id, repository_repository_dependency_association.create_time AS repository_repository_dependency_association_create_time, repository_repository_dependency_association.update_time AS repository_repository_dependency_association_update_time, repository_repository_dependency_association.tool_shed_repository_id AS repository_repository_dependency_association_tool_shed_re_1, repository_repository_dependency_association.repository_dependency_id AS repository_repository_dependency_association_repository_d_2 \nFROM repository_repository_dependency_association \nWHERE %(param_1)s = repository_repository_dependency_association.tool_shed_repository_id' {'param_1': 5}

URL: http://srv151/admin_toolshed/browse_repositories http://srv151/admin_toolshed/browse_repositories

Is there ANY way I can rescue this fiasco? Suffice it to say I did not do it on a copy and did not do a backup (yes, I learned my lesson), but maybe some SQL skullduggery can help me?

Thanks

Thon

Attachments:

attachment.htm (text/html — 7.0 KB)

Show replies by date

Daniel Blankenberg

9 Feb 9 Feb

4:31 a.m.

New subject: Error in database after trying the innocently named, but devastatingly evil "test" in manage_db.sh

Hi Thon,

The test option runs the upgrade and downgrade of the latest migration script. I'm assuming that you had already run the latest migrations available and the server was up and running OK (other than the dataset size issue) before trying the test command.

On a COPY of your database, you can try this:

$ sh manage_db.sh version XYZ $ sh manage_db.sh downgrade [XYZ -1] ...... done sh manage_db.sh upgrade XYZ .... done

If it works, then you can try it on your real database. Of course, this will destroy whatever data existed in the table/columns of the latest migration (but it looks like you already lost them.)

Thanks for using Galaxy,

Dan

On Feb 8, 2013, at 2:32 PM, Thon de Boer wrote:

...

Hi,

I had a small issue with the total usage of my galaxy system showing some negative random number so I figured my database was a little “off”.

I snooped around for some database management tools and found “manage_db.sh” which had an option innocently called “test”.

I ran it, without reading the little fine print that states: “Performs the upgrade and downgrade option on the given database. This is not a real test and may leave the database in a bad state. You should therefore better run the test on a copy of your database.”

Well…It surely did leave my database in a bad state since now I get this when I try to access the toolshed installed tools:

Error Traceback: View as: Interactive | Text | XML (full) ⇝ ProgrammingError: (ProgrammingError) relation "repository_repository_dependency_association" does not exist LINE 2: FROM repository_repository_dependency_association ^ 'SELECT repository_repository_dependency_association.id AS repository_repository_dependency_association_id, repository_repository_dependency_association.create_time AS repository_repository_dependency_association_create_time, repository_repository_dependency_association.update_time AS repository_repository_dependency_association_update_time, repository_repository_dependency_association.tool_shed_repository_id AS repository_repository_dependency_association_tool_shed_re_1, repository_repository_dependency_association.repository_dependency_id AS repository_repository_dependency_association_repository_d_2 \nFROM repository_repository_dependency_association \nWHERE %(param_1)s = repository_repository_dependency_association.tool_shed_repository_id' {'param_1': 5} URL: http://srv151/admin_toolshed/browse_repositories

Is there ANY way I can rescue this fiasco? Suffice it to say I did not do it on a copy and did not do a backup (yes, I learned my lesson), but maybe some SQL skullduggery can help me?

Thanks

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

Thon de Boer

5:23 a.m.

Hi Dan,

I tried that but it failed ofcourse since the table it tries to drop from 109 -> 108 does not exisit.

sh manage_db.sh downgrade 108

109 -> 108...

0109_add_repository_dependency_tables DEBUG 2013-02-08 13:04:33,464 Dropping repository_repository_dependency_association table failed: (ProgrammingError) table "repository_repository_dependency_association" does not exist

'\nDROP TABLE repository_repository_dependency_association' {}

0109_add_repository_dependency_tables DEBUG 2013-02-08 13:04:33,467 Dropping repository_dependency table failed: (ProgrammingError) table "repository_dependency" does not exist

'\nDROP TABLE repository_dependency' {}

0109_add_repository_dependency_tables DEBUG 2013-02-08 13:04:33,467 Dropping repository_dependency table failed: (ProgrammingError) table "repository_dependency" does not exist

'\nDROP TABLE repository_dependency' {}

done

(galaxy_env)[svcgalaxy@srv151 g]$ sh manage_db.sh version

109

I just went into the database and create the two dummy tables and tried it again and it seem to work..

I downgraded and upgraded to 109…

Then I restarted Galaxy, reset the metatdata (which failed for 2 of my 13 tools) and then I see this in the manage toolshed:

Error Traceback:

⇝ ProgrammingError: (ProgrammingError) column repository_repository_dependency_association.id does not exist LINE 1: ..._7f8dcc0e81d0_de8f CURSOR WITHOUT HOLD FOR SELECT repository... ^ 'SELECT repository_repository_dependency_association.id AS repository_repository_dependency_association_id, repository_repository_dependency_association.create_time AS repository_repository_dependency_association_create_time, repository_repository_dependency_association.update_time AS repository_repository_dependency_association_update_time, repository_repository_dependency_association.tool_shed_repository_id AS repository_repository_dependency_association_tool_shed_re_1, repository_repository_dependency_association.repository_dependency_id AS repository_repository_dependency_association_repository_d_2 \nFROM repository_repository_dependency_association \nWHERE %(param_1)s = repository_repository_dependency_association.tool_shed_repository_id' {'param_1': 5}

Slightly different error, but an error nonetheless…

Any more ideas?

I am (somewhat) OK with having to reload the toolshed tools but I can’t even install or manage any new toolsheds…

Thanks

Thon

From: Daniel Blankenberg [mailto:dan@bx.psu.edu] Sent: Friday, February 08, 2013 12:32 PM To: Thon de Boer Cc: galaxy-dev@lists.bx.psu.edu Subject: Re: [galaxy-dev] Error in database after trying the innocently named, but devastatingly evil "test" in manage_db.sh

Hi Thon,

On a COPY of your database, you can try this:

$ sh manage_db.sh version

XYZ

$ sh manage_db.sh downgrade [XYZ -1]

......

done

sh manage_db.sh upgrade XYZ

....

done

If it works, then you can try it on your real database. Of course, this will destroy whatever data existed in the table/columns of the latest migration (but it looks like you already lost them.)

Thanks for using Galaxy,

Dan

On Feb 8, 2013, at 2:32 PM, Thon de Boer wrote:

Hi,

I had a small issue with the total usage of my galaxy system showing some negative random number so I figured my database was a little “off”.

I snooped around for some database management tools and found “manage_db.sh” which had an option innocently called “test”.

Well…It surely did leave my database in a bad state since now I get this when I try to access the toolshed installed tools:

Error Traceback:

URL: http://srv151/admin_toolshed/browse_repositories http://srv151/admin_toolshed/browse_repositories

Is there ANY way I can rescue this fiasco? Suffice it to say I did not do it on a copy and did not do a backup (yes, I learned my lesson), but maybe some SQL skullduggery can help me?

Thanks

Thon

___________________________________________________________ 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/ http://lists.bx.psu.edu/

Thon de Boer

7:32 a.m.

OK..I tried it a few more times and this time with different results! HA! I knew I wasn’t insane…As long as you try anything often enough in the same manner, you WILL get a different outcome…Yea uncertainty principle!

I also had to resort to downloading the whole complete galaxy-dist from 11-Jan since the current development version of galaxy-dist is broken, but I’m back in business and first order is to create a backup plan

Thon

From: galaxy-dev-bounces@lists.bx.psu.edu [mailto:galaxy-dev-bounces@lists.bx.psu.edu] On Behalf Of Thon de Boer Sent: Friday, February 08, 2013 1:23 PM To: 'Daniel Blankenberg' Cc: galaxy-dev@lists.bx.psu.edu Subject: Re: [galaxy-dev] Error in database after trying the innocently named, but devastatingly evil "test" in manage_db.sh

Hi Dan,

I tried that but it failed ofcourse since the table it tries to drop from 109 -> 108 does not exisit.

sh manage_db.sh downgrade 108

109 -> 108...

'\nDROP TABLE repository_repository_dependency_association' {}

0109_add_repository_dependency_tables DEBUG 2013-02-08 13:04:33,467 Dropping repository_dependency table failed: (ProgrammingError) table "repository_dependency" does not exist

'\nDROP TABLE repository_dependency' {}

0109_add_repository_dependency_tables DEBUG 2013-02-08 13:04:33,467 Dropping repository_dependency table failed: (ProgrammingError) table "repository_dependency" does not exist

'\nDROP TABLE repository_dependency' {}

done

(galaxy_env)[svcgalaxy@srv151 g]$ sh manage_db.sh version

109

I just went into the database and create the two dummy tables and tried it again and it seem to work..

I downgraded and upgraded to 109…

Then I restarted Galaxy, reset the metatdata (which failed for 2 of my 13 tools) and then I see this in the manage toolshed:

Error Traceback:

Slightly different error, but an error nonetheless…

Any more ideas?

I am (somewhat) OK with having to reload the toolshed tools but I can’t even install or manage any new toolsheds…

Thanks

Thon

From: Daniel Blankenberg [mailto:dan@bx.psu.edu] Sent: Friday, February 08, 2013 12:32 PM To: Thon de Boer Cc: galaxy-dev@lists.bx.psu.edu mailto:galaxy-dev@lists.bx.psu.edu Subject: Re: [galaxy-dev] Error in database after trying the innocently named, but devastatingly evil "test" in manage_db.sh

Hi Thon,

On a COPY of your database, you can try this:

$ sh manage_db.sh version

XYZ

$ sh manage_db.sh downgrade [XYZ -1]

......

done

sh manage_db.sh upgrade XYZ

....

done

If it works, then you can try it on your real database. Of course, this will destroy whatever data existed in the table/columns of the latest migration (but it looks like you already lost them.)

Thanks for using Galaxy,

Dan

On Feb 8, 2013, at 2:32 PM, Thon de Boer wrote:

Hi,

I had a small issue with the total usage of my galaxy system showing some negative random number so I figured my database was a little “off”.

I snooped around for some database management tools and found “manage_db.sh” which had an option innocently called “test”.

Well…It surely did leave my database in a bad state since now I get this when I try to access the toolshed installed tools:

Error Traceback:

URL: http://srv151/admin_toolshed/browse_repositories http://srv151/admin_toolshed/browse_repositories

Is there ANY way I can rescue this fiasco? Suffice it to say I did not do it on a copy and did not do a backup (yes, I learned my lesson), but maybe some SQL skullduggery can help me?

Thanks

Thon

http://lists.bx.psu.edu/ http://lists.bx.psu.edu/

4118

Age (days ago)

4118

Last active (days ago)

galaxy-dev@lists.galaxyproject.org

3 comments

2 participants

tags (0)

participants (2)

Daniel Blankenberg
Thon de Boer