Bug #116149 Inconsistent auto_increment values
| Submitted: | 18 Sep 2024 16:25 | Modified: | 14 Jul 15:25 |
|---|---|---|---|
| Reporter: | xilin Chen | Email Updates: | |
| Status: | Closed | 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 | +--------------+------------+----------------------+
[21 Apr 9:09]
Dyre Tjeldvoll
Posted by developer: Thank you for the bug report. Fix has now been pushed to mysql-trunk, and we expect that it will be part of the 10.0 release.
[28 May 1:13]
MySQL Admin
Posted by developer: Bug status updated to 'Documenting'
[14 Jul 15:25]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Server 26.7.0 release notes: Large AUTO_INCREMENT values (>INT64_MAX) were truncated in information_schema.tables.auto_increment.
[11 Sep 16:15]
Edward Gilmore
Posted by developer: reopened by merge. closing.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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