"nova-manage db archive_deleted_rows" returns error re foreign key constraints

Bug #1137977 reported by Stuart Stent
16
This bug affects 3 people
Affects Status Importance Assigned to Milestone
OpenStack Compute (nova)
Fix Released
High
David Ripton

Bug Description

nova version : Master @ commit 3ca9690fb072715cc1991990d52053e488ee35a7
command: nova-manage db archive_deleted_rows
database version: 157

running the archive_deleted_rows command produces the following error

2013-03-01 10:02:46 nova: CRITICAL [req-008f92b7-7c7a-4c6f-b0e2-49c2a10aee07 None None] (IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`nova`.`instance_info_caches`, CONSTRAINT `instance_info_caches_instance_uuid_fkey` FOREIGN KEY (`instance_uuid`) REFERENCES `instances` (`uuid`))') 'DELETE FROM instances WHERE instances.id IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' (1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L, 23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L, 32L, 33L, 34L, 35L, 36L, 37L, 38L, 39L, 40L, 41L, 42L, 43L, 44L, 45L, 46L, 47L, 48L, 49L, 50L, 51L, 52L, 53L, 54L, 55L, 56L, 57L, 58L, 59L, 60L, 61L, 62L, 63L, 64L, 65L, 66L, 67L, 68L, 69L, 70L, 71L, 72L, 73L, 74L, 75L, 76L, 77L, 78L, 79L, 80L, 81L, 82L, 83L, 84L, 85L, 86L, 87L, 88L, 89L, 90L, 91L, 92L, 93L, 94L, 95L, 96L, 97L, 98L, 99L, 100L, 101L, 102L, 103L, 104L, 105L, 106L, 107L, 108L, 109L, 110L, 111L, 112L, 113L, 114L, 115L, 116L, 117L, 118L, 119L, 120L, 121L, 122L, 123L, 124L, 125L, 126L, 127L, 128L, 129L, 130L, 131L, 132L, 133L, 134L, 135L, 136L, 137L, 138L, 139L, 140L, 141L, 142L, 143L, 145L, 146L, 147L, 148L, 149L, 150L, 151L, 152L, 153L, 154L, 155L, 156L, 157L, 158L, 159L, 160L, 161L, 162L, 163L, 164L, 165L, 166L, 167L, 168L, 169L, 170L, 171L, 172L, 173L, 174L, 175L, 176L, 177L, 178L, 179L, 180L, 181L, 182L, 183L, 184L, 185L, 187L, 188L, 189L, 190L)
2013-03-01 10:02:46.087 4239 TRACE nova Traceback (most recent call last):
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/bin/nova-manage", line 5, in <module>
2013-03-01 10:02:46.087 4239 TRACE nova pkg_resources.run_script('nova==2013.1.a4557.g3ca9690', 'nova-manage')
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 461, in run_script
2013-03-01 10:02:46.087 4239 TRACE nova self.require(requires)[0].run_script(script_name, ns)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 1194, in run_script
2013-03-01 10:02:46.087 4239 TRACE nova execfile(script_filename, namespace, namespace)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/nova-2013.1.a4557.g3ca9690-py2.6.egg/EGG-INFO/scripts/nova-manage", line 1247, in <module>
2013-03-01 10:02:46.087 4239 TRACE nova main()
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/nova-2013.1.a4557.g3ca9690-py2.6.egg/EGG-INFO/scripts/nova-manage", line 1239, in main
2013-03-01 10:02:46.087 4239 TRACE nova fn(*fn_args, **fn_kwargs)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/nova-2013.1.a4557.g3ca9690-py2.6.egg/EGG-INFO/scripts/nova-manage", line 800, in archive_deleted_rows
2013-03-01 10:02:46.087 4239 TRACE nova db.archive_deleted_rows(admin_context, max_rows)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/nova-2013.1.a4557.g3ca9690-py2.6.egg/nova/db/api.py", line 1732, in archive_deleted_rows
2013-03-01 10:02:46.087 4239 TRACE nova return IMPL.archive_deleted_rows(context, max_rows=max_rows)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/nova-2013.1.a4557.g3ca9690-py2.6.egg/nova/db/sqlalchemy/api.py", line 126, in wrapper
2013-03-01 10:02:46.087 4239 TRACE nova return f(*args, **kwargs)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/nova-2013.1.a4557.g3ca9690-py2.6.egg/nova/db/sqlalchemy/api.py", line 4838, in archive_deleted_rows
2013-03-01 10:02:46.087 4239 TRACE nova max_rows=max_rows - rows_archived)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/nova-2013.1.a4557.g3ca9690-py2.6.egg/nova/db/sqlalchemy/api.py", line 126, in wrapper
2013-03-01 10:02:46.087 4239 TRACE nova return f(*args, **kwargs)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/nova-2013.1.a4557.g3ca9690-py2.6.egg/nova/db/sqlalchemy/api.py", line 4816, in archive_deleted_rows_for_table
2013-03-01 10:02:46.087 4239 TRACE nova result = conn.execute(delete_statement)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1449, in execute
2013-03-01 10:02:46.087 4239 TRACE nova params)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
2013-03-01 10:02:46.087 4239 TRACE nova compiled_sql, distilled_params
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1698, in _execute_context
2013-03-01 10:02:46.087 4239 TRACE nova context)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1691, in _execute_context
2013-03-01 10:02:46.087 4239 TRACE nova context)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 331, in do_execute
2013-03-01 10:02:46.087 4239 TRACE nova cursor.execute(statement, parameters)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute
2013-03-01 10:02:46.087 4239 TRACE nova self.errorhandler(self, exc, value)
2013-03-01 10:02:46.087 4239 TRACE nova File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2013-03-01 10:02:46.087 4239 TRACE nova raise errorclass, errorvalue
2013-03-01 10:02:46.087 4239 TRACE nova IntegrityError: (IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`nova`.`instance_info_caches`, CONSTRAINT `instance_info_caches_instance_uuid_fkey` FOREIGN KEY (`instance_uuid`) REFERENCES `instances` (`uuid`))') 'DELETE FROM instances WHERE instances.id IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' (1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L, 23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L, 32L, 33L, 34L, 35L, 36L, 37L, 38L, 39L, 40L, 41L, 42L, 43L, 44L, 45L, 46L, 47L, 48L, 49L, 50L, 51L, 52L, 53L, 54L, 55L, 56L, 57L, 58L, 59L, 60L, 61L, 62L, 63L, 64L, 65L, 66L, 67L, 68L, 69L, 70L, 71L, 72L, 73L, 74L, 75L, 76L, 77L, 78L, 79L, 80L, 81L, 82L, 83L, 84L, 85L, 86L, 87L, 88L, 89L, 90L, 91L, 92L, 93L, 94L, 95L, 96L, 97L, 98L, 99L, 100L, 101L, 102L, 103L, 104L, 105L, 106L, 107L, 108L, 109L, 110L, 111L, 112L, 113L, 114L, 115L, 116L, 117L, 118L, 119L, 120L, 121L, 122L, 123L, 124L, 125L, 126L, 127L, 128L, 129L, 130L, 131L, 132L, 133L, 134L, 135L, 136L, 137L, 138L, 139L, 140L, 141L, 142L, 143L, 145L, 146L, 147L, 148L, 149L, 150L, 151L, 152L, 153L, 154L, 155L, 156L, 157L, 158L, 159L, 160L, 161L, 162L, 163L, 164L, 165L, 166L, 167L, 168L, 169L, 170L, 171L, 172L, 173L, 174L, 175L, 176L, 177L, 178L, 179L, 180L, 181L, 182L, 183L, 184L, 185L, 187L, 188L, 189L, 190L)
2013-03-01 10:02:46.087 4239 TRACE nova

Tags: db
Changed in nova:
status: New → Confirmed
importance: Undecided → High
milestone: none → grizzly-rc1
assignee: nobody → David Ripton (dripton)
Revision history for this message
Joe Gordon (jogo) wrote :

Once this is fixed an integration test can be added to https://github.com/openstack/tempest/tree/master/cli

aeva black (tenbrae)
tags: added: db
Changed in nova:
status: Confirmed → Triaged
Revision history for this message
Russell Bryant (russellb) wrote :

I just spoke to David Ripton about this. We both thought that this could be moved to the "grizzly-rc-potential" list. David still hopes to work on it soon.

If it doesn't get fixed in time for grizzly, we will need to document it as a known issue in the release notes.

tags: added: grizzly-rc-potential
Changed in nova:
milestone: grizzly-rc1 → none
Revision history for this message
Russell Bryant (russellb) wrote :

I went ahead and listed this on: https://wiki.openstack.org/wiki/ReleaseNotes/Grizzly#Known_Issues

If this gets fixed for grizzly, that page will need to be updated to remove it.

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to nova (master)

Fix proposed to branch: master
Review: https://review.openstack.org/24458

Changed in nova:
status: Triaged → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to nova (master)

Reviewed: https://review.openstack.org/24458
Committed: http://github.com/openstack/nova/commit/bb1977718d697565d79322f305a401caf82d5553
Submitter: Jenkins
Branch: master

commit bb1977718d697565d79322f305a401caf82d5553
Author: David Ripton <email address hidden>
Date: Thu Mar 14 13:41:25 2013 -0400

    Fix db archiving bug with foreign key constraints.

    Fixes bug 1137977

    A few Nova tables have foreign key constraints on other tables.
    Deleting a row whose id is referenced as an enforced foreign key for
    another table results in an error.

    The fix is to detect the IntegrityError when we attempt to delete a row,
    and exit from archive_deleted_rows_for_table early. The parent
    archive_deleted_rows function will continue deleting from other tables,
    including the one whose dependence on this row caused the IntegrityError,
    and things will eventually sort themselves out.

    Change-Id: I0afdfe0778057abc5fd269a20bfa4c4ca3a7b003

Changed in nova:
status: In Progress → Fix Committed
Thierry Carrez (ttx)
tags: removed: grizzly-rc-potential
Thierry Carrez (ttx)
Changed in nova:
milestone: none → grizzly-rc1
status: Fix Committed → Fix Released
Thierry Carrez (ttx)
Changed in nova:
milestone: grizzly-rc1 → 2013.1
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.