Cinder Service Unavailable (HTTP 503)

Issue

• All Cinder volume creation/deletion started failing with error Service Unavailable (HTTP 503).

• Cinder database is somewhat large:

MariaDB [cinder]> SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "cinder";
+----------------------------+-----------+
| Table                      | Size (MB) |
+----------------------------+-----------+
| attachment_specs           |      0.05 |
| backup_metadata            |      0.05 |
| backups                    |      0.03 |
| cgsnapshots                |      0.05 |
| clusters                   |      0.03 |
| consistencygroups          |      0.02 |
| driver_initiator_data      |      0.05 |
| encryption                 |      0.02 |
| group_snapshots            |      0.05 |
| group_type_projects        |      0.03 |
| group_type_specs           |      0.05 |
| group_types                |      0.02 |
| group_volume_type_mapping  |      0.08 |
| groups                     |      0.05 |
| image_volume_cache_entries |      0.05 |
| messages                   |      0.30 |
| migrate_version            |      0.02 |
| quality_of_service_specs   |      0.05 |
| quota_classes              |      0.03 |
| quota_usages               |    190.13 |
| quotas                     |     37.56 |
| reservations               |   2477.89 |
| services                   |      0.03 |
| snapshot_metadata          |      0.05 |
| snapshots                  |      6.05 |
| transfers                  |      0.05 |
| volume_admin_metadata      |      5.11 |
| volume_attachment          |   1752.03 |
| volume_glance_metadata     |    902.70 |
| volume_metadata            |     28.08 |
| volume_type_extra_specs    |      0.05 |
| volume_type_projects       |      0.03 |
| volume_types               |      0.03 |
| volumes                    |    844.30 |
| workers                    |      0.14 |
+----------------------------+-----------+

• cinder-manage db purge 30 fails as i executed the command which you shared :

[root@overcloud-controller-0 ~]# podman exec -ti cinder_api cinder-manage db purge 30
Purge command failed, check cinder-manage logs for more details.
...
2024-04-09 13:44:53.042 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=volume_glance_metadata
2024-04-09 13:45:01.005 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Deleted 36163 rows from table=volume_glance_metadata
2024-04-09 13:45:01.006 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=snapshot_metadata
2024-04-09 13:45:01.012 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=attachment_specs
2024-04-09 13:45:01.014 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=volume_type_projects
2024-04-09 13:45:01.021 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=volume_type_extra_specs
2024-04-09 13:45:01.024 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=volume_metadata
2024-04-09 13:45:02.684 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Deleted 1070 rows from table=volume_metadata
2024-04-09 13:45:02.685 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=volume_attachment
2024-04-09 13:45:05.802 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Deleted 7690 rows from table=volume_attachment
2024-04-09 13:45:05.803 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=volume_admin_metadata
2024-04-09 13:45:05.841 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Deleted 59 rows from table=volume_admin_metadata
2024-04-09 13:45:05.841 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=transfers
2024-04-09 13:45:05.848 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=snapshots
2024-04-09 13:45:05.863 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=group_volume_type_mapping
2024-04-09 13:45:05.876 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=workers
2024-04-09 13:45:05.884 62043 INFO cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] Purging deleted rows older than age=30 days from table=volumes
2024-04-09 13:46:10.639 62043 ERROR cinder.db.sqlalchemy.api [req-a87697cd-9859-4331-b767-cfa1e46489f8 - - - - -] 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_at < %(deleted_at_1)s]
[parameters: {'deleted_at_1': datetime.datetime(2024, 3, 10, 13, 45, 5, 884650)}]
(Background on this error at: http://sqlalche.me/e/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`))')

• While manually cleaning the cinder database, we got this error:

ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction

and after that error we started getting this error each time:

2024-04-17 01:39:17.557 166190 CRITICAL cinder [req-191045ab-b090-4c6a-9e12-3eff8e863bb8 - - - - -] Unhandled error: sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1180, 'Got error 90 "Message too long" during COMMIT')
(Background on this error at: http://sqlalche.me/e/e3q8)
2024-04-17 01:39:17.557 166190 ERROR cinder Traceback (most recent call last):
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 761, in _commit_impl
2024-04-17 01:39:17.557 166190 ERROR cinder     self.engine.dialect.do_commit(self.connection)
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib64/python3.6/site-packages/sqlalchemy/dialects/mysql/base.py", line 2215, in do_commit
2024-04-17 01:39:17.557 166190 ERROR cinder     dbapi_connection.commit()
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 422, in commit
2024-04-17 01:39:17.557 166190 ERROR cinder     self._read_ok_packet()
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 396, in _read_ok_packet
2024-04-17 01:39:17.557 166190 ERROR cinder     pkt = self._read_packet()
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib/python3.6/site-packages/pymysql/connections.py", line 676, in _read_packet
2024-04-17 01:39:17.557 166190 ERROR cinder     packet.raise_for_error()
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib/python3.6/site-packages/pymysql/protocol.py", line 223, in raise_for_error
2024-04-17 01:39:17.557 166190 ERROR cinder     err.raise_mysql_exception(self._data)
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
2024-04-17 01:39:17.557 166190 ERROR cinder     raise errorclass(errno, errval)
2024-04-17 01:39:17.557 166190 ERROR cinder pymysql.err.OperationalError: (1180, 'Got error 90 "Message too long" during COMMIT')
2024-04-17 01:39:17.557 166190 ERROR cinder 
2024-04-17 01:39:17.557 166190 ERROR cinder The above exception was the direct cause of the following exception:
2024-04-17 01:39:17.557 166190 ERROR cinder 
2024-04-17 01:39:17.557 166190 ERROR cinder Traceback (most recent call last):
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/bin/cinder-manage", line 10, in <module>
2024-04-17 01:39:17.557 166190 ERROR cinder     sys.exit(main())
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib/python3.6/site-packages/cinder/cmd/manage.py", line 850, in main
2024-04-17 01:39:17.557 166190 ERROR cinder     fn(**fn_kwargs)
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib/python3.6/site-packages/cinder/cmd/manage.py", line 318, in purge
2024-04-17 01:39:17.557 166190 ERROR cinder     db.purge_deleted_rows(ctxt, age_in_days)
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib/python3.6/site-packages/cinder/db/api.py", line 1616, in purge_deleted_rows
2024-04-17 01:39:17.557 166190 ERROR cinder     return IMPL.purge_deleted_rows(context, age_in_days=age_in_days)
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib/python3.6/site-packages/cinder/db/sqlalchemy/api.py", line 174, in wrapper
2024-04-17 01:39:17.557 166190 ERROR cinder     return f(*args, **kwargs)
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib/python3.6/site-packages/cinder/db/sqlalchemy/api.py", line 6605, in purge_deleted_rows
2024-04-17 01:39:17.557 166190 ERROR cinder     .where(table.c.deleted_at < deleted_age))
2024-04-17 01:39:17.557 166190 ERROR cinder   File "/usr/lib64/python3.6/site-packages/sqlalchemy/orm/session.py", line 603, in __exit__
2024-04-17 01:39:17.557 166190 ERROR cinder     self.rollback()