| Bug #116149 | Inconsistent auto_increment values | ||
|---|---|---|---|
| Submitted: | 18 Sep 2024 16:25 | Modified: | 19 Sep 2024 6:03 |
| Reporter: | xilin Chen | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.39 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 Sep 2024 6:03]
MySQL Verification Team
Hello xilin Chen, Thank you for the report and test case. Verified as described. regards, Umesh
[23 Oct 2024 2:51]
huahua xu
Hi all: By querying the definition of the system view `information_schema.tables`, it's column `AUTO_INCREMENT` comes from `coalesce(stat.auto_increment,0)`. Then, I change the definition of the column to `coalesce(stat.auto_increment,cast(0 as unsigned))`, like that: mysql> CREATE VIEW `MY_TABLES` AS select (`cat`.`name` collate utf8mb3_tolower_ci) AS `TABLE_CATALOG`,(`sch`.`name` collate utf8mb3_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8mb3_tolower_ci) AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,if((`tbl`.`type` = 'BASE TABLE'),`tbl`.`engine`,NULL) AS `ENGINE`,if((`tbl`.`type` = 'VIEW'),NULL,10) AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,if((`tbl`.`type` = 'VIEW'),NULL,internal_table_rows(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `TABLE_ROWS`,if((`tbl`.`type` = 'VIEW'),NULL,internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `AVG_ROW_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `DATA_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_max_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `MAX_DATA_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_index_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `INDEX_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_data_free(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `DATA_FREE`,if((`tbl`.`type` = 'VIEW'),NULL,internal_auto_increment(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,((0 <> is_visible_dd_object(`tbl`.`hidden`,false,`tbl`.`options`)) is false),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,cast(0 as unsigned)),coalesce(cast(`stat`.`cached_time` as unsigned),0),`tbl`.`se_private_data`)) AS `AUTO_INCREMENT`,`tbl`.`created` AS `CREATE_TIME`,if((`tbl`.`type` = 'VIEW'),NULL,internal_update_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `UPDATE_TIME`,if((`tbl`.`type` = 'VIEW'),NULL,internal_check_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,if((`tbl`.`type` = 'VIEW'),NULL,internal_checksum(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `CHECKSUM`,if((`tbl`.`type` = 'VIEW'),NULL,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'NOT_PART_TBL') = 'NOT_PART_TBL'),0,1),if((`sch`.`default_encryption` = 'YES'),1,0))) AS `CREATE_OPTIONS`,internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`) AS `TABLE_COMMENT` from (((((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) left join `mysql`.`collations` `col` on((`tbl`.`collation_id` = `col`.`id`))) left join `mysql`.`tablespaces` `ts` on((`tbl`.`tablespace_id` = `ts`.`id`))) left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))) where ((0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`))) ; For the test case: mysql> select TABLE_SCHEMA, TABLE_NAME, AUTO_INCREMENT from information_schema.tables where table_schema='mytest' and table_name='t'; +--------------+------------+---------------------+ | TABLE_SCHEMA | TABLE_NAME | AUTO_INCREMENT | +--------------+------------+---------------------+ | mytest | t | 9223372036854775807 | +--------------+------------+---------------------+ mysql> select TABLE_SCHEMA, TABLE_NAME, AUTO_INCREMENT from my_tables where table_schema='mytest' and table_name='t'; +--------------+------------+----------------------+ | TABLE_SCHEMA | TABLE_NAME | AUTO_INCREMENT | +--------------+------------+----------------------+ | mytest | t | 18446744073709551610 | +--------------+------------+----------------------+
Description: Using unsigned bigint type auto-increment fields, when the auto-increment ID > 2^63 - 1, the auto_increment values in information_schema.tables and mysql.table_stats are inconsistent. The maximum value of auto_increment in information_schema.tables is only 2^63-1 = 9223372036854775807, while the maximum value of unsigned bigint can be 2^64-1 How to repeat: My parameter settings: set global information_schema_stats_expiry = 86400; set session information_schema_stats_expiry = 86400; create database mytest; use mytest; create table t(id bigint unsigned auto_increment primary key, a int null); insert into t values(); alter table t auto_increment=18446744073709551610; analyze table t; show create table t\G Table: t Create Table: CREATE TABLE `t` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `a` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551610 DEFAULT CHARSET=utf8mb3 select * from information_schema.tables where table_schema='mytest' and table_name='t'\G TABLE_CATALOG: def TABLE_SCHEMA: mytest TABLE_NAME: t TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Dynamic TABLE_ROWS: 1 AVG_ROW_LENGTH: 16384 DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: 9223372036854775807 CREATE_TIME: 2024-09-19 00:15:17 UPDATE_TIME: 2024-09-19 00:15:10 CHECK_TIME: NULL TABLE_COLLATION: utf8mb3_general_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: set session debug='+d,skip_dd_table_access_check'; select * from mysql.table_stats where schema_name='mytest' and table_name='t'\G schema_name: mytest table_name: t table_rows: 1 avg_row_length: 16384 data_length: 16384 max_data_length: 0 index_length: 0 data_free: 0 auto_increment: 18446744073709551610 checksum: NULL update_time: 2024-09-19 00:15:10 check_time: NULL cached_time: 2024-09-19 00:15:22