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. MariaDB has the same problem but Percona Server is fine.
$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> 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; 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; 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 .
Bug has been reported :https://bugs.mysql.com/bug.php?id=77743