Bug #91038 AUTO_INCREMENT does not increase automatically

Submitted: 28 May 2018 3:48 Modified: 5 Jun 2018 10:16
Reporter: KO WEI LEE Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:8.0.11 OS:Windows (WIN 10)
Assigned to: CPU Architecture:Any
Tags: regression

[28 May 2018 3:48] KO WEI LEE
Description:
I exported a table from MYSQL 5.7.15 (using MySQL Workbench with mysqldump.exe 5.7.15.0), and import into MYSQL 8.0.11 Database.

The id(Primary Key) of the table "temp_order"(attached file) is AUTO_INCREMENT using InnoDB.

I use the script below to check the value of AUTO_INCREMENT:

SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'temp_order'
AND table_schema = DATABASE();

Then I inserted a record, and then check AUTO_INCREMENT again.

The id is correct, since it increases by 1.

But AUTO_INCREMENT does not increase, even after I inserted many records, the AUTO_INCREMENT is still the same value.

That is to say, the id will greater than the AUTO_INCREMENT.

How to repeat:
Firstly, check the current AUTO_INCREMENT. Then to insert a record, and then check AUTO_INCREMENT again. You'll see the AUTO_INCREMENT does not increase.
[28 May 2018 4:12] KO WEI LEE
A creating table script

Attachment: mysql-bug-data-91038.zip (application/x-zip-compressed, text), 1.70 MiB.

[31 May 2018 9:08] MySQL Verification Team
Hello Lee!

Thank you for the report and test case.

Thanks,
Umesh
[31 May 2018 9:10] MySQL Verification Team
-- 8.0.11 - affected

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test < mysql-bug-data-91038/temp_order.sql
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select max(id) from temp_order;
+---------+
| max(id) |
+---------+
|   68840 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'temp_order' AND table_schema = DATABASE();
+----------------+
| AUTO_INCREMENT |
+----------------+
|          68841 |
+----------------+
1 row in set (0.10 sec)

mysql> show create table temp_order\G
*************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
.

) ENGINE=InnoDB AUTO_INCREMENT=68841 DEFAULT CHARSET=utf8 COMMENT='旅团订购'
1 row in set (0.00 sec)

mysql> INSERT INTO `temp_order` VALUES (NULL,'TO68841','C15CBR0724','LADEL','67220','DTCBR','XXXNA','2015-07-24 00:00:00','MR','Kevin','Hui','Hana Yu','ACTV1',7,0,0,0,0,0,0,0,24.30000,20.00000,0.00000,0.00000,0.00000,NULL,0.00000,NULL,0,'20150723120316',0.00000,0.00000,'','2015-07-23 12:03:16',0.00000,35.00000,NULL,'\0',NULL,NULL,NULL,NULL,NULL,'',0,'',0.00000);
Query OK, 1 row affected (0.02 sec)

mysql> select max(id) from temp_order;
+---------+
| max(id) |
+---------+
|   68841 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'temp_order' AND table_schema = DATABASE();
+----------------+
| AUTO_INCREMENT |
+----------------+
|          68841 |
+----------------+
1 row in set (0.01 sec)

^^ Which sounds unsual since in general this is value+1, where value is the largest value for the column currently in the table. 

mysql> show create table temp_order\G
*************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
.

) ENGINE=InnoDB AUTO_INCREMENT=68842 DEFAULT CHARSET=utf8 COMMENT='旅团订购'
1 row in set (0.01 sec)
[31 May 2018 9:11] MySQL Verification Team
-- 5.7.22 - looks fine

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select max(id) from temp_order;
+---------+
| max(id) |
+---------+
|   68840 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'temp_order' AND table_schema = DATABASE();
+----------------+
| AUTO_INCREMENT |
+----------------+
|          68841 |
+----------------+
1 row in set (0.00 sec)

mysql> show create table temp_order\G
*************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
.

) ENGINE=InnoDB AUTO_INCREMENT=68841 DEFAULT CHARSET=utf8 COMMENT='旅团订购'
1 row in set (0.00 sec)

mysql> INSERT INTO `temp_order` VALUES (NULL,'TO68841','C15CBR0724','LADEL','67220','DTCBR','XXXNA','2015-07-24 00:00:00','MR','Kevin','Hui','Hana Yu','ACTV1',7,0,0,0,0,0,0,0,24.30000,20.00000
    -> ,0.00000,0.00000,0.00000,NULL,0.00000,NULL,0,'20150723120316',0.00000,0.00000,'','2015-07-23 12:03:16',0.00000,35.00000,NULL,'\0',NULL,NULL,NULL,NULL,NULL,'',0,'',0.00000);
Query OK, 1 row affected (0.00 sec)

mysql> show create table temp_order\G                                                                                                                                                      *************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL COMMENT '旅团订购编号',
.
) ENGINE=InnoDB AUTO_INCREMENT=68842 DEFAULT CHARSET=utf8 COMMENT='旅团订购'
1 row in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'temp_order' AND table_schema = DATABASE();                                                                 +----------------+
| AUTO_INCREMENT |
+----------------+
|          68842 |
+----------------+
1 row in set (0.00 sec)

Which sounds all good since in general this is value+1, where value is the largest value for the column currently in the table. 

mysql> select max(id) from temp_order;
+---------+
| max(id) |
+---------+
|   68841 |
+---------+
1 row in set (0.00 sec)
[5 Jun 2018 9:21] Gopal Shankar
The behavior seem to be correct, based on the default setting
for information_schema_stats_expiry=86400 secs (= 1 day). This is
introduced in 8.0 and not present in 5.7. The expectation stated
in bug page can be observed with setting information_schema_stats_expiry=0.

Please refer following page for more info,
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_in
formation_schema_stats_expiry

Test results that demonstrates the behavior:
````````````````````````````````````````````
Note that the INFORMATION_SCHEMA.TABLES.AUTO_INCREMENT value remains same
until we set information_schema_stats_expiry=0. This will enable I_S to
stop using the cached statistics value and always get latest statistics.

...
CREATE TABLE `temp_order` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT
PRIMARY KEY,
`code` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=68842 ;
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68842
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68843
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68843
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68843
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68844
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68843
set session information_schema_stats_expiry=0;
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68845
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68846
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68846
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68847
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68847
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68848
DROP TABLE `temp_order`;
...
[7 Jun 2018 22:07] William Chiquito
In my test case the error persists:

* Windows 10

Z:\>mysql
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

...

mysql> DROP TABLE IF EXISTS `temp_order`;
Query OK, 0 rows affected (0.27 sec)

mysql> CREATE TABLE IF NOT EXISTS `temp_order` (
    ->   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.49 sec)

mysql> SELECT AUTO_INCREMENT
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_NAME = 'temp_order'
    -> AND TABLE_SCHEMA = DATABASE();
+----------------+
| AUTO_INCREMENT |
+----------------+
|           NULL |
+----------------+
1 row in set (0.05 sec)

mysql> SHOW CREATE TABLE `temp_order`\G
*************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> INSERT INTO `temp_order`
    -> VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT MAX(`id`)
    -> FROM `temp_order`;
+-----------+
| MAX(`id`) |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `temp_order`\G
*************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_NAME = 'temp_order';
+----------------+
| AUTO_INCREMENT |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

* Linux

See https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=31b3589425c24a9b869cb05bcef1fda6
[8 Jun 2018 6:22] Ståle Deraas
Posted by developer:
 
Hi William,

Even after setting information_schema_stats_expiry = 0 , like Gopal has suggested above? It did not show from your repro....
[8 Jun 2018 9:23] William Chiquito
It's correct, after setting information_schema_stats_expiry = 0 everything works as expected. See https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b21ee67940aca7816f528f8f9f527e58.
[8 Jun 2018 11:03] Ståle Deraas
Posted by developer:
 
OK, so it works as documented then.
[14 May 2019 21:35] K-A S
setting information_schema_stats_expiry = 0 only works for the current session. If I need to access the uncached value every time I query or every time my application queries information_schema, how would I achieve this?
[15 May 2019 10:35] Ståle Deraas
Posted by developer:
 
As you can see in https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc... , there is a global and session scope for this variable.
To get the global scope you can use SET GLOBAL, and if you want the setting to survive a server restart you can use SET PERSIST, see https://dev.mysql.com/doc/refman/8.0/en/set-variable.html .

So to get a permanent setting for the server you can use: SET PERSIST information-schema-stats-expiry=0
[16 May 2019 5:08] K-A S
I tried using both GLOBAL and PERSIST, and neither of those worked.
[8 Jul 2019 12:24] MySQL Verification Team
Bug #96124 marked as duplicate of this one
[5 Apr 2021 6:00] MySQL Verification Team
Related - Bug #103197

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.068 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.