cinder-manage db purge may fail due to foreign key errors, as the timestamp used for purging deleted rows is recalculated for every table [1] and can be slightly different between primary and secondary tables.

[1] https://opendev.org/openstack/cinder/src/branch/master/cinder/db/sqlalchemy/api.py#L8113

To demonstrate the issue, I've included a debug message to print the SQL to be executed for each table. These DEBUG messages are not part of the original cinder-manage code.

Note that there is a 52'' secs gap between the timestamp used for deleting rows from tables ``volumes`` (volumes.deleted_at < '2025-04-21 19:25:55.674489') and ``volume_glance_metadata`` (volume_glance_metadata.deleted_at < '2025-04-21 19:25:03.512573'). This results in the bulk delete operation failing for the ``volumes`` table, as it queries for a longer interval.

(venv) stack@devstack-7390da39-7c5f-498b-b77e-712d6823f556:~/cinder$ cinder-manage --debug db purge 30 | tee /tmp/cinder-manage_db-purge_$(date "+%F_%H%M%S").out
INFO dbcounter [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Registered counter for database cinder
DEBUG oslo_db.sqlalchemy.engines [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] MySQL server mode set to STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION (pid=17340) _check_effective_sql_mode /opt/stack/data/venv/lib/python3.10/site-packages/oslo_db/sqlalchemy/engines.py:397
DEBUG dbcounter [-] [17340] Writer thread running (pid=17340) stat_writer /opt/stack/data/venv/lib/python3.10/site-packages/dbcounter.py:102
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=volume_glance_metadata
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM volume_glance_metadata WHERE volume_glance_metadata.deleted IS true AND volume_glance_metadata.deleted_at < '2025-04-21 19:25:03.512573' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Deleted 16007 rows from table=volume_glance_metadata
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=snapshot_metadata
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM snapshot_metadata WHERE snapshot_metadata.deleted IS true AND snapshot_metadata.deleted_at < '2025-04-21 19:25:13.493686' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=attachment_specs
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM attachment_specs WHERE attachment_specs.deleted IS true AND attachment_specs.deleted_at < '2025-04-21 19:25:13.495459' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=volume_type_projects
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM volume_type_projects WHERE volume_type_projects.deleted IS true AND volume_type_projects.deleted_at < '2025-04-21 19:25:13.496641' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=volume_type_extra_specs
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM volume_type_extra_specs WHERE volume_type_extra_specs.deleted IS true AND volume_type_extra_specs.deleted_at < '2025-04-21 19:25:13.497693' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=volume_metadata
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM volume_metadata WHERE volume_metadata.deleted IS true AND volume_metadata.deleted_at < '2025-04-21 19:25:13.498823' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Deleted 572 rows from table=volume_metadata
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=volume_attachment
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM volume_attachment WHERE volume_attachment.deleted IS true AND volume_attachment.deleted_at < '2025-04-21 19:25:13.616407' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
DEBUG dbcounter [-] [17340] Writing DB stats cinder:SHOW=36,cinder:SELECT=21,cinder:DELETE=7 (pid=17340) stat_writer /opt/stack/data/venv/lib/python3.10/site-packages/dbcounter.py:115
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Deleted 4003 rows from table=volume_attachment
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=volume_admin_metadata
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM volume_admin_metadata WHERE volume_admin_metadata.deleted IS true AND volume_admin_metadata.deleted_at < '2025-04-21 19:25:55.656580' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=transfers
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM transfers WHERE transfers.deleted IS true AND transfers.deleted_at < '2025-04-21 19:25:55.669345' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=snapshots
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM snapshots WHERE snapshots.deleted IS true AND snapshots.deleted_at < '2025-04-21 19:25:55.670983' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=group_volume_type_mapping
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM group_volume_type_mapping WHERE group_volume_type_mapping.deleted IS true AND group_volume_type_mapping.deleted_at < '2025-04-21 19:25:55.672191' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=workers
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM workers WHERE workers.deleted IS true AND workers.deleted_at < '2025-04-21 19:25:55.673246' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
INFO cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Purging deleted rows older than age=30 days from table=volumes
DEBUG cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] Delete Stmt: DELETE FROM volumes WHERE volumes.deleted IS true AND volumes.deleted_at < '2025-04-21 19:25:55.674489' (pid=17340) purge_deleted_rows /opt/stack/cinder/cinder/db/sqlalchemy/api.py:8134
ERROR cinder.db.sqlalchemy.api [None req-9f390afc-d44a-45b5-b04c-261c695bae9a None None] DBError detected when purging from volumes: (pymysql.err.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`cinder`.`volume_glance_metadata`, CONSTRAINT `volume_glance_metadata_ibfk_1` FOREIGN KEY (`volume_id`) REFERENCES `volumes` (`id`))')
[SQL: DELETE FROM volumes WHERE volumes.deleted IS true AND volumes.deleted_at < %(deleted_at_1)s]
[parameters: \{'deleted_at_1': datetime.datetime(2025, 4, 21, 19, 25, 55, 674489)}]
(Background on this error at: https://sqlalche.me/e/20/gkpj).: oslo_db.exception.DBReferenceError: (pymysql.err.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`cinder`.`volume_glance_metadata`, CONSTRAINT `volume_glance_metadata_ibfk_1` FOREIGN KEY (`volume_id`) REFERENCES `volumes` (`id`))')
DEBUG dbcounter [-] [17340] Writing DB stats cinder:DELETE=6 (pid=17340) stat_writer /opt/stack/data/venv/lib/python3.10/site-packages/dbcounter.py:115
Purge command failed, check cinder-manage logs for more details.

Steps to reproduce:
1) Make sure you have a populated Cinder database. To reproduce the issue, we need records from two related tables created with the same timestamp (e.g., volume and volume_attachment).

2) Run "cinder-manage db-purge <days>" to clean up the database.