db purge of snapshots tables fails because of fk to snapshot_metadata

[György Szombathelyi]

2018-04-04 12:58:09.905 20813 INFO cinder.db.sqlalchemy.api [req-b3f7afb6-9feb-447c-9de8-19858819b647 - - - - -] Purging deleted rows older than age=30 days from table=snapshots
2018-04-04 12:58:09.910 20813 ERROR cinder.db.sqlalchemy.api [req-b3f7afb6-9feb-447c-9de8-19858819b647 - - - - -] DBError detected when purging from snapshots: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`cinder`.`snapshot_metadata`, CONSTRAINT `snapshot_metadata_ibfk_1` FOREIGN KEY (`snapshot_id`) REFERENCES `snapshots` (`id`))') [SQL: u'DELETE FROM snapshots WHERE snapshots.deleted_at < %(deleted_at_1)s'] [parameters: {u'deleted_at_1': datetime.datetime(2018, 3, 5, 10, 58, 9, 905889)}].: DBReferenceError: (pymysql.err.IntegrityError) (1451, u'Cannot delete or update a parent row: a foreign key constraint fails (`cinder`.`snapshot_metadata`, CONSTRAINT `snapshot_metadata_ibfk_1` FOREIGN KEY (`snapshot_id`) REFERENCES `snapshots` (`id`))') [SQL: u'DELETE FROM snapshots WHERE snapshots.deleted_at < %(deleted_at_1)s'] [parameters: {u'deleted_at_1': datetime.datetime(2018, 3, 5, 10, 58, 9, 905889)}]

[Eric Harney (eharney)]

Can reproduce by creating a snapshot with metadata, deleting the snapshot, adjust updated_at and deleted_at in the DB to X days in the past, and then running "cinder-manage db purge 30".

[Boxiang Zhu (bxzhu-5355)]

@Eric Harney, I think the test case is not very correct. If you adjust the 'update_at' and 'delete_at' of snapshots, you should also adjust them of snapshot_metadata. Because when you delete the snapshot, the metadata of this snapshot will be deleted at the same time. So if you adjust all of them, you will not meet the problem. So I think it is not a bug.

[György Szombathelyi (gyurco)]

But the problem is that the metadata doesn't deleted by purge at all.

[Boxiang Zhu (bxzhu-5355)]

Operate as I described above, the 'deleted_time' and 'updated_time' are set seven days ago. Then run command like 'cinder-manage db purge 5'. So the data of snapshots and snapshot_metadata from five days ago were deleted from DB.

[Sean McGinnis (sean-mcginnis)]

It sounds from Boxiang like this is no longer an issue. If it is, then I think we need to do something like:

https://github.com/openstack/cinder/blob/6e21dc1a545039638cb2b46d821b64a570940948/cinder/db/sqlalchemy/api.py#L6536

Unless there is a better way to get a list of tables and ensure they are in the proper order such that FK items are deleted before the referencing tables.

Closing, but if this is still seen, please reopen.