The command "nova-manage db archive_deleted_rows" doesn't move the entries to the shadow tables.

[Oleksandr Bryndzii]

Once an instance is deleted from the cloud its entry in the database is still present.
When trying to archive some number of deleted rows with "nova-manage db archive_deleted_rows --max-rows 1", a constraint error is displayed in nova logs.

Expected results:
Specified deleted rows number should be moved from production tables to shadow tables.

Actual result:
db-archiving fails to archive specified number of deleted rows with "nova-manage db archive_deleted_rows --max-rows 1", a constraint error is displayed in nova logs:

2016-03-28 12:31:43.751 14147 ERROR oslo_db.sqlalchemy.exc_filters [req-0fd152a6-b298-4c3c-9aa7-ebdadc752b89 - - - - -] DBAPIError exception wrapped from (IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`nova`.`block_device_mapping`, CONSTRAINT `block_device_mapping_instance_uuid_fkey` FOREIGN KEY (`instance_uuid`) REFERENCES `instances` (`uuid`))') 'DELETE FROM instances WHERE instances.id in (SELECT T1.id FROM (SELECT instances.id \nFROM instances \nWHERE instances.deleted != %s ORDER BY instances.id \n LIMIT %s) as T1)' (0, 1)
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters context)
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 436, in do_execute
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 205, in execute
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters self.errorhandler(self, exc, value)
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters raise errorclass, errorvalue
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`nova`.`block_device_mapping`, CONSTRAINT `block_device_mapping_instance_uuid_fkey` FOREIGN KEY (`instance_uuid`) REFERENCES `instances` (`uuid`))')
2016-03-28 12:31:43.751 14147 TRACE oslo_db.sqlalchemy.exc_filters
2016-03-28 12:31:43.753 14147 WARNING nova.db.sqlalchemy.api [req-0fd152a6-b298-4c3c-9aa7-ebdadc752b89 - - - - -] IntegrityError detected when archiving table instances

Steps to reproduce:
1) create instance
2) delete instance
3) run command 'nova-manage db archive_deleted_rows --max_rows 1'

Description of the environment:
    MOS: 7.0
    OS: Ubuntu 14.04
    Reference architecture: HA
    Network model: Neutron VLAN

[Matt Riedemann (mriedem)]

This is a known broken issue, see the mailing list thread here:

http://lists.openstack.org/pipermail/openstack-dev/2015-November/079701.html

[Roman Rufanov (rrufanov)]

This affected multiple MOS 7 customers. Please provide fix or workaround.

[Andrii Petrenko (aplsms)]

Matt, CT1 customer is complaining about this issue. We need speed up resolution of this bug.
Thank you.

[Vitaly Sedelnik (vsedelnik)]

Removed duplicate mark to upstream nova bug https://bugs.launchpad.net/nova/+bug/1183523. Targeted to 7.0-mu-6 and 8.0-updates. Invalid for 9.1 as there is fix to stable/mitaka which was consumed via sync.

[Matt Riedemann (mriedem)]

I'm not sure if I'm the Matt being addressed here, but assume I am. Bug 1183523 is fixed, has been for awhile:

https://review.openstack.org/#/q/Ia0fe130a35a6c9bb16a940f3f4cea6b1aaee9668,n,z

[Sergii Rizvan (srizvan)]

The patches from upstream [1], [2] provide only partial fix of the original issue. Before applying patches the command "nova-manage db archive_deleted_rows" doesn't move the entries to the shadow tables at all because of foreign key constraint issue. After applying patch [1] nova-manage firstly process tables that don't have foreign keys. Patch [2] fixes issue with archiving instance_actions and instance_actions_event. And after applying those two patches we are able to archive some tables, but archiving tables with foreign key constraint issue, like 'instances', still fails.

Here is the output from MOS 9.1: http://paste.openstack.org/show/577672/

So, as we can see, 'instances' table still contain rows with 'deleted' non-zero values, but, for example 'instance_actions' and 'instance_actions_event' tables are empty after running "nova-manage db archive_deleted_rows".

[1] https://review.openstack.org/#/c/246635/
[2] https://review.openstack.org/#/c/326730/

[Sergii Rizvan (srizvan)]

We've created other bug which describe issue, that was fully fixed with patches from upstream https://bugs.launchpad.net/mos/+bug/1624363

Setting Confirmed status for all milestones (including 9.x) because this bug wasn't fully fixed.

[Sergii Rizvan (srizvan)]

Due to inability to fully fix the bug (reasons described in comments #6, #7), we decided to set status for the bus as Won't Fix.