MySQL导入数据遇到Error Number: 1467 Failed to read auto-increment value from storage engine错误

创建表的语句

CREATE TABLE `test`

( `id` int unsigned auto_increment not null comment 'id',

`uuid` varchar(255) NULL COMMENT 'uuid',

 `ctime` timestamp NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'create_time’,

 PRIMARY KEY (`id`)

)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT 'use for test';

导入数据的过程

load data local infile "/hah/test.txt" ignore into table test fields terminated by '\t';

错误提示

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

原因

Google了一下，在这里找到了一个答案:

use the command SHOW CREATE TABLE tablename;, I get

table name {

`id` int(11) NOT NULL AUTO_INCREMENT,

 ......

}ENGINE=InnoDB AUTO_INCREMENT=100000000000 DEFAULT CHARSET=utf8

You will see the length of 100000000000 is 12, beyond the limit 11.

于是我看了一下自己的表结构，发现：

`id` int(10)

...

ENGINE=InnoDB AUTO_INCREMENT=4294967295

找到了原因，修改一下这里就好了：

ALTER TABLE `YOUR_TABLE`  AUTO_INCREMENT =1