MySQL报错 lost “AUTO_INCREMENT” after a long time解决办法
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 .
您可能感兴趣的文章：
MySQL报错 lost “AUTO_INCREMENT” after a long time解决办法
mysql提示MySQL server has gone away导入时报错2006错误
WDCP面板系统安装mysqli扩展的方法
mysqldump 导出发生Lost connection to MySQL server during query
mysql auto_increment 与 联合主键冲突问题
解决Lost connection to MySQL server during query错误方法
mysql 2013错误的解决方法
Can't connect to local MySQL server through socket 问题的解决方法
mysql repair 报错的解决办法
安装MYSQL-PYTHON包报错mysql_config not found解决办法