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

[Sergii Rizvan]

Detailed bug description:
The command "nova-manage db archive_deleted_rows" doesn't archive any entries now because of foreign key constraint issue in some tables, for example 'instances' table:

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)

As we know from discussion in upstream bug https://bugs.launchpad.net/nova/+bug/1183523 it's very difficult to completely fix the issue. A huge redesign required in order to do this. But in upstream we have 2 patches, which fix the issue partially. Those patches allow us to archive at least tables without foreign key constraint issue. So archiving tables should work for most tables, except tables with above-mentioned issue.

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

Expected results:
 Command 'nova-manage db archive_deleted_rows --max_rows 1' archive at least tables without foreign key constraint issue.

Actual result:
 Command 'nova-manage db archive_deleted_rows --max_rows 1' doesn't archive any table.

Reproducibility:
 Always

Description of the environment:
    MOS: 7.0, 8.0

[Sergii Rizvan (srizvan)]

Here is the patches:
7.0: https://review.fuel-infra.org/#/c/26426/
8.0: https://review.fuel-infra.org/#/c/26231/

[TatyanaGladysheva (tgladysheva)]

Verified on MOS 7.0 + MU6 updates.

Before updates:
Command 'nova-manage db archive_deleted_rows --max_rows 10' doesn't archive any table:

mysql> select id,instance_uuid,action,message,deleted from nova.instance_actions;
+----+--------------------------------------+--------+---------+---------+
| id | instance_uuid | action | message | deleted |
+----+--------------------------------------+--------+---------+---------+
| 1 | 15906abe-7443-427a-88b9-5f8dbe9e3046 | create | NULL | 0 |
| 2 | 15906abe-7443-427a-88b9-5f8dbe9e3046 | delete | NULL | 0 |
+----+--------------------------------------+--------+---------+---------+
2 rows in set (0.00 sec)

mysql> select id,created_at,deleted_at,event,deleted from nova.instance_actions_events;
+----+---------------------+------------+------------------------------------+---------+
| id | created_at | deleted_at | event | deleted |
+----+---------------------+------------+------------------------------------+---------+
| 1 | 2016-10-19 07:50:09 | NULL | compute__do_build_and_run_instance | 0 |
| 2 | 2016-10-19 07:50:49 | NULL | compute_terminate_instance | 0 |
+----+---------------------+------------+------------------------------------+---------+

After updates:
Command 'nova-manage db archive_deleted_rows --max_rows 10' archive at least tables without foreign key constraint issue:

mysql> select id,instance_uuid,action,message,deleted from nova.instance_actions;
Empty set (0.00 sec)

mysql> select id,created_at,deleted_at,event,deleted from nova.instance_actions_events;
Empty set (0.00 sec)

[TatyanaGladysheva (tgladysheva)]

Verified on MOS 8.0 + MU4 updates.

After updates:
Command 'nova-manage db archive_deleted_rows --max_rows 10' archive at least rows in tables without foreign key constraint issue.