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: | |
| 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
[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.