Bug 2164247

Description of problem:
Column ID in nova.instance_system_metadata reached the max of INT, AUTO_INCREMENT | Out of range value for column 'id' at row 1

[SQL: INSERT INTO instance_system_metadata (created_at, updated_at, deleted_at, deleted, `key`, value, instance_uuid) VALUES (%(created_at)s, %(updated_at)s, %(deleted_at)s, %(deleted)s, %(key)s, %(value)s, %(instance_uuid)s)]
[parameters: {'created_at': datetime.datetime(2023, 1, 24, 13, 41, 3, 358921), 'updated_at': None, 'deleted_at': None, 'deleted': 0, 'key': 'clean_attempts', 'value': '1', 'instance_uuid': 'a0aab297-9a8c-4115-9cf1-1aff60a6ce8d'}]
(Background on this error at: http://sqlalche.me/e/2j85): pymysql.err.InternalError: (167, "Out of range value for column 'id' at row 1")

Version-Release number of selected component (if applicable):
RHOSP 16.1

How reproducible:

RHOSP over few years usage till the ID in nova.instance_system_metadata reached the max of INT

MariaDB [nova]> select * from instance_system_metadata order by created_at desc limit 10;
+---------------------+------------+---------------------+------------+--------------------------------------+-------------------------+--------------------------------------+------------+
| created_at          | updated_at | deleted_at          | id         | instance_uuid                        | key                     | value                                | deleted    |
+---------------------+------------+---------------------+------------+--------------------------------------+-------------------------+--------------------------------------+------------+
| 2023-01-24 12:42:55 | NULL       | 2023-01-24 12:42:58 | 2147483599 | f041e0e7-ec1d-46e1-9b31-ebe4508c6c2d | clean_attempts          | 1                                    | 2147483599 |
| 2023-01-24 12:42:50 | NULL       | NULL                | 2147483596 | 823b2040-d9e0-4e3f-9470-de0db306055e | boot_roles              | _member_                             |          0 |
| 2023-01-24 12:42:49 | NULL       | NULL                | 2147483566 | 823b2040-d9e0-4e3f-9470-de0db306055e | image_meta_compose_type | nightly                              |          0 |
| 2023-01-24 12:42:49 | NULL       | NULL                | 2147483569 | 823b2040-d9e0-4e3f-9470-de0db306055e | image_meta_contact      | lpol                      |          0 |
| 2023-01-24 12:42:49 | NULL       | NULL                | 2147483572 | 823b2040-d9e0-4e3f-9470-de0db306055e | image_meta_os_distro    | RHEL-8.8.0-20230114.0                |          0 |
| 2023-01-24 12:42:49 | NULL       | NULL                | 2147483575 | 823b2040-d9e0-4e3f-9470-de0db306055e | image_min_ram           | 0                                    |          0 |
| 2023-01-24 12:42:49 | NULL       | NULL                | 2147483578 | 823b2040-d9e0-4e3f-9470-de0db306055e | image_min_disk          | 20                                   |          0 |
| 2023-01-24 12:42:49 | NULL       | NULL                | 2147483581 | 823b2040-d9e0-4e3f-9470-de0db306055e | image_disk_format       | qcow2                                |          0 |
| 2023-01-24 12:42:49 | NULL       | NULL                | 2147483584 | 823b2040-d9e0-4e3f-9470-de0db306055e | image_container_format  | bare                                 |          0 |
| 2023-01-24 12:42:49 | NULL       | NULL                | 2147483587 | 823b2040-d9e0-4e3f-9470-de0db306055e | image_base_image_ref    | 4ad367f2-be54-464b-8cea-c0f3fca3baaa |          0 |
+---------------------+------------+---------------------+------------+--------------------------------------+-------------------------+--------------------------------------+------------+

MariaDB [nova]> explain instance_system_metadata;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| created_at    | datetime     | YES  |     | NULL    |                |
| updated_at    | datetime     | YES  |     | NULL    |                |
| deleted_at    | datetime     | YES  |     | NULL    |                |
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| instance_uuid | varchar(36)  | NO   | MUL | NULL    |                |
| key           | varchar(255) | NO   |     | NULL    |                |
| value         | varchar(255) | YES  |     | NULL    |                |
| deleted       | int(11)      | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

DROP TABLE IF EXISTS `instance_system_metadata`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `instance_system_metadata` (
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `instance_uuid` varchar(36) NOT NULL,
  `key` varchar(255) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  `deleted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `instance_uuid` (`instance_uuid`),
  CONSTRAINT `instance_system_metadata_ibfk_1` FOREIGN KEY (`instance_uuid`) REFERENCES `instances` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;



Steps to Reproduce:
1.
2.
3.

Actual results:
Instances are not being scheduled and DB errors appearing in Nova conductor logs
~~~
2023-01-24 13:41:03.359 37 ERROR oslo_db.sqlalchemy.exc_filters pymysql.err.InternalError: (167, "Out of range value for column 'id' at row 1")
~~~

Expected results:


Additional info:

Workaround: The issue could be fixed by re-indexing instance_system_metadata table.