| Bug #91906 | ALTER TABLE AUTO_INCREMENT is not as good as expected | ||
|---|---|---|---|
| Submitted: | 6 Aug 2018 8:19 | Modified: | 6 Aug 2018 10:52 |
| Reporter: | Jan jan | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Storage Engines | Severity: | S5 (Performance) |
| Version: | 5.6.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[6 Aug 2018 9:43]
MySQL Verification Team
Thank you for taking the time to report a problem. Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/
Also we don't fix bugs in old versions, don't backport bug fixes, so need to check with latest version anyway. So, please, upgrade and inform us if problem still exists.
-- 5.6.41
mysql> create table b (id int(10) primary key auto_increment,name varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into b (name) values('aa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into b (name) values('bb');
Query OK, 1 row affected (0.00 sec)
mysql> select * from b;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)
mysql> show create table b\G
*************************** 1. row ***************************
Table: b
Create Table: CREATE TABLE `b` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table b change id ID int(10) auto_increment;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table b\G
*************************** 1. row ***************************
Table: b
Create Table: CREATE TABLE `b` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from b;
+----+------+
| ID | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)
mysql> insert into b (name) values('cc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from b;
+----+------+
| ID | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+------+
3 rows in set (0.00 sec)
mysql> alter table b auto_increment=100;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table b\G
*************************** 1. row ***************************
Table: b
Create Table: CREATE TABLE `b` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from b;
+----+------+
| ID | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+------+
3 rows in set (0.00 sec)
mysql> insert into b (name) values('dd');
Query OK, 1 row affected (0.00 sec)
mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='b';
+----------------+
| auto_increment |
+----------------+
| 101 |
+----------------+
1 row in set (0.00 sec)
mysql> analyze table test.b;
+--------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.b | analyze | status | OK |
+--------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> alter table test.b auto_increment=200;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test.b\G
*************************** 1. row ***************************
Table: b
Create Table: CREATE TABLE `b` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from test.b;
+-----+------+
| ID | name |
+-----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 100 | dd |
+-----+------+
4 rows in set (0.00 sec)
mysql> alter table test.b auto_increment=4;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test.b\G
*************************** 1. row ***************************
Table: b
Create Table: CREATE TABLE `b` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> insert into test.b (name) values('dd');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test.b (name) values('dd');
Query OK, 1 row affected (0.00 sec)
mysql> show create table test.b\G
*************************** 1. row ***************************
Table: b
Create Table: CREATE TABLE `b` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=103 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> \q
[6 Aug 2018 10:52]
Jan jan
thank you very much for your reply.i have read MySQL 5.6 Release Notes before you answer me,but don't seems to find it in the Bugs Fixed.so i'm not sure if it's a bug or not.could you point out it?thank you very much again.
Description: when i do this operation，ALTER TABLE TEST.A AUTO_INCREMENT=100,the initial AUTO_INCREMENT key values disappears my mysql version is 5.6.20，and i do this operation by 5.7.16、5.7.22 and 8.0.11,but they work well,it just appears by 5.6.20,you can fix it with doing "alter table test.a engine=innodb"， i want to know why it is happen?if it is a bug? i found these messages in errorlog: 2018-08-06 04:08:08 7fa309776700 InnoDB: AUTOINC next value generation is disabled for 'test/a' 2018-08-06 14:22:34 7fa3097b7700 InnoDB: MySQL and InnoDB data dictionaries are out of sync. InnoDB: Unable to find the AUTOINC column ID in the InnoDB table test/b. InnoDB: We set the next AUTOINC column value to 0, InnoDB: in effect disabling the AUTOINC next value generation. InnoDB: You can either set the next AUTOINC value explicitly using ALTER TABLE InnoDB: or fix the data dictionary by recreating the table. 2018-08-06 14:59:55 7fa3097b7700 InnoDB: MySQL and InnoDB data dictionaries are out of sync. InnoDB: Unable to find the AUTOINC column ID in the InnoDB table test/b. InnoDB: We set the next AUTOINC column value to 0, InnoDB: in effect disabling the AUTOINC next value generation. InnoDB: You can either set the next AUTOINC value explicitly using ALTER TABLE InnoDB: or fix the data dictionary by recreating the table. 2018-08-06 15:00:17 7fa3097b7700 InnoDB: MySQL and InnoDB data dictionaries are out of sync. How to repeat: do just like this, and it will repeat mysql> create table b (id int(10) primary key auto_increment,name varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql> insert into b (name) values('aa'); Query OK, 1 row affected (0.00 sec) mysql> insert into b (name) values('bb'); Query OK, 1 row affected (0.00 sec) mysql> select * from b; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | +----+------+ 2 rows in set (0.01 sec) mysql> show create table b; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | b | CREATE TABLE `b` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table b change id ID int(10) auto_increment; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table b; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | b | CREATE TABLE `b` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from b; +----+------+ | ID | name | +----+------+ | 1 | aa | | 2 | bb | +----+------+ 2 rows in set (0.00 sec) mysql> insert into b (name) values('cc'); Query OK, 1 row affected (0.01 sec) mysql> select * from b; +----+------+ | ID | name | +----+------+ | 1 | aa | | 2 | bb | | 3 | cc | +----+------+ 3 rows in set (0.00 sec) mysql> alter table b auto_increment=100; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table b; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | b | CREATE TABLE `b` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from b; +----+------+ | ID | name | +----+------+ | 1 | aa | | 2 | bb | | 3 | cc | +----+------+ 3 rows in set (0.00 sec) mysql> insert into b (name) values('dd'); ERROR 1467 (HY000): Failed to read auto-increment value from storage engine mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='b'; +----------------+ | auto_increment | +----------------+ | 0 | +----------------+ 1 row in set (0.01 sec) mysql> analyze table test.b; +--------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+---------+----------+----------+ | test.b | analyze | status | OK | +--------+---------+----------+----------+ 1 row in set (0.01 sec) mysql> alter table test.b auto_increment=200; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test.b; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | b | CREATE TABLE `b` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from test.b; +----+------+ | ID | name | +----+------+ | 1 | aa | | 2 | bb | | 3 | cc | +----+------+ 3 rows in set (0.00 sec) mysql> alter table test.b auto_increment=4; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test.b; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ | b | CREATE TABLE `b` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into test.b (name) values('dd'); ERROR 1467 (HY000): Failed to read auto-increment value from storage engine mysql> alter table test.b engine=innodb; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into test.b (name) values('dd'); Query OK, 1 row affected (0.01 sec) mysql> show create table test.b; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | b | CREATE TABLE `b` ( `ID` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)