Description:
AUTO_INCREMENT out of sync in table status and in information_schema.
On a OSX system the following bug occurred where a tables AUTO_INCREMENT is out of sync when looking in `information_schema.tables` and in `TABLE STATUS`.
This output shows AUTO_INCREMENT as `28` while the correct is `AUTO_INCREMENT=44` is the output from a dump.
I am unaware on how to reproduce, but figured it would be better to report it to see if something comes of it. Below are some output from said commands.
mysql> SHOW TABLE STATUS LIKE '<table-name>'\G
*************************** 1. row ***************************
Name: <table-name>
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 49152
Data_free: 0
Auto_increment: 28
Create_time: 2019-06-27 23:18:24
Update_time: 2019-07-05 00:43:24
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> SELECT * FROM information_schema.tables WHERE (table_schema, table_name) = ('ticketsolve_dev', 'upload_attachments')\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: <schema_name>
TABLE_NAME: <table_name>
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 49152
DATA_FREE: 0
AUTO_INCREMENT: 28
CREATE_TIME: 2019-06-27 23:18:24
UPDATE_TIME: 2019-07-05 00:43:24
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.00 sec)
CREATE TABLE `<table-name>` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`resource_type` varchar(255) NOT NULL,
`resource_id` int(11) NOT NULL,
`account_id` smallint(5) unsigned NOT NULL,
`user_id` int(11) NOT NULL,
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY ...,
KEY ...,
KEY ...
(`resource_id`,`resource_type`),
CONSTRAINT `fk_rails_52d76d763e` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
How to repeat:
Currently not able to reproduce.
Description: AUTO_INCREMENT out of sync in table status and in information_schema. On a OSX system the following bug occurred where a tables AUTO_INCREMENT is out of sync when looking in `information_schema.tables` and in `TABLE STATUS`. This output shows AUTO_INCREMENT as `28` while the correct is `AUTO_INCREMENT=44` is the output from a dump. I am unaware on how to reproduce, but figured it would be better to report it to see if something comes of it. Below are some output from said commands. mysql> SHOW TABLE STATUS LIKE '<table-name>'\G *************************** 1. row *************************** Name: <table-name> Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 49152 Data_free: 0 Auto_increment: 28 Create_time: 2019-06-27 23:18:24 Update_time: 2019-07-05 00:43:24 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> SELECT * FROM information_schema.tables WHERE (table_schema, table_name) = ('ticketsolve_dev', 'upload_attachments')\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: <schema_name> TABLE_NAME: <table_name> TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 49152 DATA_FREE: 0 AUTO_INCREMENT: 28 CREATE_TIME: 2019-06-27 23:18:24 UPDATE_TIME: 2019-07-05 00:43:24 CHECK_TIME: NULL TABLE_COLLATION: utf8mb4_0900_ai_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 1 row in set (0.00 sec) CREATE TABLE `<table-name>` ( `id` int(11) NOT NULL AUTO_INCREMENT, `resource_type` varchar(255) NOT NULL, `resource_id` int(11) NOT NULL, `account_id` smallint(5) unsigned NOT NULL, `user_id` int(11) NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY ..., KEY ..., KEY ... (`resource_id`,`resource_type`), CONSTRAINT `fk_rails_52d76d763e` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; How to repeat: Currently not able to reproduce.