-
Epic
-
Resolution: Unresolved
-
Major
-
rhos-16.2.z
-
Column ID in nova.instance_system_metadata reached the max of INT, AUTO_INCREMENT | Out of range value for column 'id' at row 1
-
False
-
-
False
-
Not Set
-
Not Set
-
Not Set
-
-
-
Compute
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:
]
(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@redhat.com | 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.
- external trackers