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:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.39 OS:Any
Assigned to: CPU Architecture:Any

[18 Sep 2024 16:25] xilin Chen
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
[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.