Description:
I create a empty table and then alter this table's "AUTO_INCREMENT" starts from 10000000.After a long time ,MySQL lost "AUTO_INCREMENT" definition when I show this table again.
addition:
"innodb_buffer_pool_size" = 61440M
How to repeat:
$mysql06
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 141028725
Server version: 5.6.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, 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> DROP TABLE IF EXISTS `test_bug` ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> CREATE TABLE test_bug (
-> `id` BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> `consult_order_id` BIGINT ,
-> `pay_channel` INT COMMENT ,
-> `price` BIGINT ,
-> `trade_no` VARCHAR (64) ,
-> `right_no` VARCHAR (64) ,
-> `item_id` BIGINT NOT NULL,
-> `source_type` INT NOT NULL ,
-> `gmt_create` DATETIME DEFAULT CURRENT_TIMESTAMP ,
-> `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> ) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> ALTER TABLE test_bug
-> ADD INDEX index_consult_order (consult_order_id) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> ALTER TABLE test_bug
-> ADD INDEX index_right_no (right_no) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> ALTER TABLE test_bug
-> AUTO_INCREMENT = 100000000 ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> show create table test_bug;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_bug | CREATE TABLE `test_bug` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`consult_order_id` bigint(20) DEFAULT NULL ,
`pay_channel` int(11) DEFAULT NULL COMMENT ,
`price` bigint(20) DEFAULT NULL COMMENT ,
`trade_no` varchar(64) DEFAULT NULL COMMENT ,
`right_no` varchar(64) DEFAULT NULL COMMENT ,
`item_id` bigint(20) NOT NULL COMMENT ,
`source_type` int(11) NOT NULL COMMENT ,
`gmt_create` datetime DEFAULT CURRENT_TIMESTAMP ,
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`),
KEY `index_consult_order` (`consult_order_id`),
KEY `index_right_no` (`right_no`)
) ENGINE=InnoDB AUTO_INCREMENT=100000000 DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
wait about 24 hours , then show create table :
mysql> show create table test_bug;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_bug | CREATE TABLE `test_bug` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`consult_order_id` bigint(20) DEFAULT NULL ,
`pay_channel` int(11) DEFAULT NULL ,
`price` bigint(20) DEFAULT NULL ,
`trade_no` varchar(64) DEFAULT NULL ,
`right_no` varchar(64) DEFAULT NULL ,
`item_id` bigint(20) NOT NULL ,
`source_type` int(11) NOT NULL ,
`gmt_create` datetime DEFAULT CURRENT_TIMESTAMP ,
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`),
KEY `index_consult_order` (`consult_order_id`),
KEY `index_right_no` (`right_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
AUTO_INCREMENT disappears
Suggested fix:
I have tested on many platforms - MariaDB has the same problem but Percona is OK
Description: I create a empty table and then alter this table's "AUTO_INCREMENT" starts from 10000000.After a long time ,MySQL lost "AUTO_INCREMENT" definition when I show this table again. addition: "innodb_buffer_pool_size" = 61440M How to repeat: $mysql06 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 141028725 Server version: 5.6.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> DROP TABLE IF EXISTS `test_bug` ; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> CREATE TABLE test_bug ( -> `id` BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT, -> `consult_order_id` BIGINT , -> `pay_channel` INT COMMENT , -> `price` BIGINT , -> `trade_no` VARCHAR (64) , -> `right_no` VARCHAR (64) , -> `item_id` BIGINT NOT NULL, -> `source_type` INT NOT NULL , -> `gmt_create` DATETIME DEFAULT CURRENT_TIMESTAMP , -> `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -> ) ENGINE = INNODB DEFAULT CHARSET = utf8 ; Query OK, 0 rows affected (0.01 sec) mysql> mysql> ALTER TABLE test_bug -> ADD INDEX index_consult_order (consult_order_id) ; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> ALTER TABLE test_bug -> ADD INDEX index_right_no (right_no) ; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> ALTER TABLE test_bug -> AUTO_INCREMENT = 100000000 ; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> show create table test_bug; +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_bug | CREATE TABLE `test_bug` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `consult_order_id` bigint(20) DEFAULT NULL , `pay_channel` int(11) DEFAULT NULL COMMENT , `price` bigint(20) DEFAULT NULL COMMENT , `trade_no` varchar(64) DEFAULT NULL COMMENT , `right_no` varchar(64) DEFAULT NULL COMMENT , `item_id` bigint(20) NOT NULL COMMENT , `source_type` int(11) NOT NULL COMMENT , `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP , `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY (`id`), KEY `index_consult_order` (`consult_order_id`), KEY `index_right_no` (`right_no`) ) ENGINE=InnoDB AUTO_INCREMENT=100000000 DEFAULT CHARSET=utf8 | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) wait about 24 hours , then show create table : mysql> show create table test_bug; +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_bug | CREATE TABLE `test_bug` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `consult_order_id` bigint(20) DEFAULT NULL , `pay_channel` int(11) DEFAULT NULL , `price` bigint(20) DEFAULT NULL , `trade_no` varchar(64) DEFAULT NULL , `right_no` varchar(64) DEFAULT NULL , `item_id` bigint(20) NOT NULL , `source_type` int(11) NOT NULL , `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP , `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY (`id`), KEY `index_consult_order` (`consult_order_id`), KEY `index_right_no` (`right_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) AUTO_INCREMENT disappears Suggested fix: I have tested on many platforms - MariaDB has the same problem but Percona is OK