MySQL 无法从存储引擎读取自动增量值，错误号：1467
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17690926/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Failed to read auto-increment value from storage engine, Error Number: 1467
提问by MJVM
When inserting data in mysql i get this error:
在mysql中插入数据时出现此错误：
Error Number: 1467 Failed to read auto-increment value from storage engine
错误编号：1467 无法从存储引擎读取自动增量值
I don't now how to solve this issue please any help will be appreciated.
我现在不知道如何解决这个问题，请提供任何帮助。
回答by MJVM
After some searching i found the answer and it solved my problem.
经过一番搜索，我找到了答案并解决了我的问题。
run this sql query it will fix the problem
运行此 sql 查询它将解决问题
ALTER TABLE `YOUR_TABLE` AUTO_INCREMENT =1
回答by swe
I received this message too. My problem was that my table was not sorted by index. Try using the following:
我也收到了这个消息。我的问题是我的表没有按索引排序。尝试使用以下方法：
ALTER TABLE `YOUR-TABLE` ORDER BY `index` ;
回答by risoldi
To add a little comment to kiddingmu's answer: it is not just a question of the number of digits, but also of the range of the datatype. If the column is INT(11), the 11 says that 11 digits are used for display; but this does not release the constraint that INTcan only encode the range -2147483648:2147483647 when signed, and 0:4294967295 when unsigned.
给kiddingmu的回答加一点评论：这不仅仅是位数的问题，也是数据类型范围的问题。如果列是 INT(11)，则 11 表示使用 11 位数字进行显示；但这并没有解除INT只能编码范围 -2147483648:2147483647（有符号时）和 0:4294967295（无符号）的限制。
So: for an INT(11) column, an AUTO_INCREMENT of 10000000000 will work; an AUTO_INCREMENT of 90000000000 will not, despite it being 11 digits.
所以：对于 INT(11) 列，10000000000 的 AUTO_INCREMENT 将起作用；90000000000 的 AUTO_INCREMENT 不会，尽管它是 11 位数字。
If a larger range is needed, then another type should be used, like BIGINT.
如果需要更大的范围，则应使用另一种类型，例如BIGINT。
回答by spencer7593
One possible explanation for this behavior is that the autoincrement value has reached the maximum value for the datatype, and its not possible for the database engine to increment it by one.
对此行为的一种可能解释是自动增量值已达到数据类型的最大值，并且数据库引擎不可能将其加一。
I suggest you check the current value. One relatively easy way to do that is to run a SHOW CREATE TABLE mytable;, the table definition will show the current value. (You can also query the information_schema.tables view, to get the same information.)
我建议您检查当前值。一种相对简单的方法是运行 a SHOW CREATE TABLE mytable;，表定义将显示当前值。（您也可以查询 information_schema.tables 视图，以获取相同的信息。）
回答by kiddingmu
As spencer7593 says, the autoincrement value has reached the maximum value for the datatype
正如 spencer7593 所说，自动增量值已达到数据类型的最大值
I just came to the problem.
我刚刚遇到问题。
use the command SHOW CREATE TABLE tablename;, I get
使用命令SHOW CREATE TABLE tablename; ，我得到
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.
你会看到100000000000的长度是12，超出了11的限制。
回答by Debanjan Roy
Check your database structure properly.I have also faced this problem, but I found some error in database structure. After fix the structure, problems were resolved.
正确检查您的数据库结构。我也遇到过这个问题，但是我发现数据库结构有一些错误。修复结构后，问题解决。
回答by Stephen
Another possibility with this error is that you've hit the max value in the ID field (generally an INT). We had this error when our ID values got close to 4294967295. We ended up having to drop the ID from the table in order to get past the issue. The various INT fields are mentioned in this answer: https://stackoverflow.com/a/5634147/4573630
此错误的另一种可能性是您已达到 ID 字段中的最大值（通常为 INT）。当我们的 ID 值接近 4294967295 时，我们遇到了这个错误。为了解决这个问题，我们最终不得不从表中删除 ID。此答案中提到了各种 INT 字段：https: //stackoverflow.com/a/5634147/4573630
回答by y77
I had the same problem, after changing ID column to id, and after exporting my db, so I just switched the column back to ID and then back to id again, and every thing worked fine after. Working with elequent orm in laravel, it expects column id and I had column ID, that is why I changed it in the first place
我遇到了同样的问题，在将 ID 列更改为 id 并导出我的数据库后，我只是将列切换回 ID，然后再次切换回 id，之后一切正常。在 laravel 中使用 elequent orm，它需要列 id 并且我有列 ID，这就是我首先更改它的原因
回答by Sakerbook
ps aux | grep mysql
sudo kill (your pid)
/etc/init.d/mysql restart
