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