Database integrity error when installing new tools from toolshed

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

Database integrity error when installing new tools from toolshed

Michael Ta
Hi,

I updated a local Galaxy installation a while back and I followed the steps listed on one of the wiki pages. The update included changes to the database schema and I was careful to backup and restore it according to the directions. However, when I try to install new tools from the tool shed or update a tool to a new revision it does not complete successfully. The following error appears in the log file. 

File '/home/galaxy/galaxy-dist/lib/tool_shed/util/tool_util.py', line 761 in handle_tool_versions
context.flush()
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/scoping.py',        line 114 in do
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/session.py',        line 1718 in flush
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/session.py',        line 1789 in _flush
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/unitofwork.p       y', line 331 in execute
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/unitofwork.p       y', line 475 in execute
 File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/persistence.       py', line 64 in save_obj
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/persistence.       py', line 558 in _emit_insert_statements
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1449 in execute
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1584 in _execute_clauseelement
 File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1698 in _execute_context
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1691 in _execute_context
 File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/default.p       y', line 331 in do_execute
IntegrityError: (IntegrityError) duplicate key value violates unique constraint "tool_version_association_pk       ey"
DETAIL:  Key (id)=(83) already exists.
'INSERT INTO tool_version_association (tool_id, parent_id) VALUES (%(tool_id)s, %(parent_id)s) RETURNING to       ol_version_association.id' {'parent_id': 440, 'tool_id': 439}

A few of the keys appear to be colliding with values already in the database, did I miss something when I updated and restored the database that is causing this?

Thanks,
--
Michael Ta


___________________________________________________________
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: Database integrity error when installing new tools from toolshed

Nate Coraor (nate@bx.psu.edu)
On Aug 21, 2014, at 3:48 PM, Michael Ta <[hidden email]> wrote:

Hi,

I updated a local Galaxy installation a while back and I followed the steps listed on one of the wiki pages. The update included changes to the database schema and I was careful to backup and restore it according to the directions. However, when I try to install new tools from the tool shed or update a tool to a new revision it does not complete successfully. The following error appears in the log file. 

File '/home/galaxy/galaxy-dist/lib/tool_shed/util/tool_util.py', line 761 in handle_tool_versions
context.flush()
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/scoping.py',        line 114 in do
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/session.py',        line 1718 in flush
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/session.py',        line 1789 in _flush
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/unitofwork.p       y', line 331 in execute
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/unitofwork.p       y', line 475 in execute
 File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/persistence.       py', line 64 in save_obj
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/persistence.       py', line 558 in _emit_insert_statements
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1449 in execute
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1584 in _execute_clauseelement
 File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1698 in _execute_context
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1691 in _execute_context
 File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/default.p       y', line 331 in do_execute
IntegrityError: (IntegrityError) duplicate key value violates unique constraint "tool_version_association_pk       ey"
DETAIL:  Key (id)=(83) already exists.
'INSERT INTO tool_version_association (tool_id, parent_id) VALUES (%(tool_id)s, %(parent_id)s) RETURNING to       ol_version_association.id' {'parent_id': 440, 'tool_id': 439}

A few of the keys appear to be colliding with values already in the database, did I miss something when I updated and restored the database that is causing this?

Hi Michael,

The documentation should say to back up, but a restore is not necessary unless you encounter some problem during the migration. Could you point me at the documentation in question so I can update it?

Could you check that your backup included the sequences and that when you restored the database, you restored the sequences? It looks like the sequence in question (tool_version_association_id_seq) is not actually set to the max(id) on the tool_version_association table, which means that the nextval selected from that sequence would conflict with an id already in the table.

--nate


Thanks,
--
Michael Ta

___________________________________________________________
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: Database integrity error when installing new tools from toolshed

Michael Ta
Hi Nate,

I'm thinking I misread the documentation on the restore part. What happened was I pulled and updated that galaxy-dist repository and then when I got it running after migrating the tools and updating the database schema to the new version, it either didn't display my previous histories or I couldn't download the files correctly (I'm thinking its the former but I don't remember). This is where I tried restoring the database and once I did that everything worked except for the tool installation part.

I checked the backup and it looks like the tool_version_association_id was set to 79 instead of the current max in the table which should be 160. I found these lines in my postgres backup.

 --
 -- Name: tool_version_association_id_seq; Type: SEQUENCE SET; Schema: public; Owner: galaxy
 --

 SELECT pg_catalog.setval('tool_version_association_id_seq', 79, true);

Is it possible for me to backup and update the value of the sequence in the database to resolve this?

Thanks for the help,



On Fri, Aug 22, 2014 at 8:46 AM, Nate Coraor <[hidden email]> wrote:
On Aug 21, 2014, at 3:48 PM, Michael Ta <[hidden email]> wrote:

Hi,

I updated a local Galaxy installation a while back and I followed the steps listed on one of the wiki pages. The update included changes to the database schema and I was careful to backup and restore it according to the directions. However, when I try to install new tools from the tool shed or update a tool to a new revision it does not complete successfully. The following error appears in the log file. 

File '/home/galaxy/galaxy-dist/lib/tool_shed/util/tool_util.py', line 761 in handle_tool_versions
context.flush()
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/scoping.py',        line 114 in do
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/session.py',        line 1718 in flush
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/session.py',        line 1789 in _flush
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/unitofwork.p       y', line 331 in execute
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/unitofwork.p       y', line 475 in execute
 File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/persistence.       py', line 64 in save_obj
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/orm/persistence.       py', line 558 in _emit_insert_statements
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1449 in execute
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1584 in _execute_clauseelement
 File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1698 in _execute_context
File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/base.py',        line 1691 in _execute_context
 File '/home/galaxy/galaxy-dist/eggs/SQLAlchemy-0.7.9-py2.7-linux-x86_64-ucs4.egg/sqlalchemy/engine/default.p       y', line 331 in do_execute
IntegrityError: (IntegrityError) duplicate key value violates unique constraint "tool_version_association_pk       ey"
DETAIL:  Key (id)=(83) already exists.
'INSERT INTO tool_version_association (tool_id, parent_id) VALUES (%(tool_id)s, %(parent_id)s) RETURNING to       ol_version_association.id' {'parent_id': 440, 'tool_id': 439}

A few of the keys appear to be colliding with values already in the database, did I miss something when I updated and restored the database that is causing this?

Hi Michael,

The documentation should say to back up, but a restore is not necessary unless you encounter some problem during the migration. Could you point me at the documentation in question so I can update it?

Could you check that your backup included the sequences and that when you restored the database, you restored the sequences? It looks like the sequence in question (tool_version_association_id_seq) is not actually set to the max(id) on the tool_version_association table, which means that the nextval selected from that sequence would conflict with an id already in the table.

--nate


Thanks,
--
Michael Ta

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




--
Michael Ta
Help Desk Support and Web Design

Phone: 949.812.6902 ext 135

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