mysql 无法从存储引擎读取自动增量值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7346934/
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
mysql Failed to read auto-increment value from storage engine
提问by ashu
I am having mysql table with one id field as auto-increment .
我有一个 id 字段作为自动增量的 mysql 表。
When I insert values to the table am getting error as
当我向表中插入值时出现错误
1467 - Failed to read auto-increment value from storage engine
1467 - 无法从存储引擎读取自动增量值
Also the show table statusshows me that the field with auto increment has
还show table status向我展示了具有自动增量的字段
18446744073709551615as Auto_increment value.
18446744073709551615作为 Auto_increment 值。
What would be the issue can any one help me ....?
有什么问题可以帮助我....？
采纳答案by Marco
Problem could absolutely be that: convert 18446744073709551615to hex and you'll find
$FFFF-FFFF-FFFF-FFFF.
If your field is an unsigned 64bit you reach its limit.
问题绝对是这样的：将18446744073709551615转换为十六进制，你会发现
$FFFF-FFFF-FFFF-FFFF。
如果您的字段是未签名的 64 位，则会达到其限制。
回答by Alex Rashkov
I had the same error but in my case I had about 1.5k records in the table. I fixed it by resetting the AUTO INCREMEN like that:
我有同样的错误，但在我的情况下，表中有大约 1.5k 条记录。我通过像这样重置 AUTO INCREMEN 来修复它：
ALTER TABLE `table_name` AUTO_INCREMENT = 1
回答by Mike
I started getting this error a couple of weeks back when running insert statements:
几周前，我在运行插入语句时开始收到此错误：
Duplicate entry '127' for key 'PRIMARY'
... even though my table was set to auto increment. I went in and changed the auto_increment value from 127 to 128 then I started getting this error:
...即使我的表设置为自动递增。我进入并将 auto_increment 值从 127 更改为 128 然后我开始收到此错误：
1467 - Failed to read auto-increment value from storage engine
I eventually figured out that the table had been initially created with tinyint columns for the ID not standard ints ... so basically it couldn't comprehend numbers bigger than 127. I switched the column type to proper integers and that solved the issue.
我最终发现该表最初是用 tinyint 列创建的，ID 不是标准整数……所以基本上它无法理解大于 127 的数字。我将列类型切换为适当的整数，从而解决了问题。
Hope that helps someone :)
希望对某人有所帮助:)
回答by che-azeh
For my part, I made a dumb mistake. I had earlier altered my table and changed the name of the AUTO_INCREMENTcolumn from IDto id. So, given column names are case-sensitive, subsequent inserts couldn't find the original column.
就我而言，我犯了一个愚蠢的错误。我早些时候更改了我的表并将AUTO_INCREMENT列的名称从更改ID为id。因此，给定列名区分大小写，后续插入无法找到原始列。
回答by Shinbo
Actually, you can simply alter the column to delete its auto_increamentproperty and set it as auto_incrementagain. On my side, this way did work.
实际上，您可以简单地更改列以删除其auto_increament属性并将其auto_increment重新设置。在我这边，这种方式确实有效。
回答by Deepak Sharma
I go the same error. I just alter the table and increase the size of my auto increment field and then run the following query -
我去同样的错误。我只是改变表并增加我的自动增量字段的大小，然后运行以下查询 -
ALTER TABLE `table_name` AUTO_INCREMENT = 6221;
where 6221 is the last value of the filed with Auto_increment.
其中 6221 是带有 Auto_increment 的字段的最后一个值。
回答by Robert
I experienced this error for the first time less than an hour ago. Resetting the auto_increment using a SQL statement in PHP MyAdmin failed. After looking for a solution I dropped the table and created a replacement. The error remained. Looking closer revealed the auto_increment was set to 0 even though I had specifically set the primary_key and auto_increment while creating the fields. Manually resetting auto_increment to 1, again using PHP MyAdmin, eliminated the error. Luckily for me I was only working with a 3-column table containing a few rows of test data.
不到一个小时前，我第一次遇到了这个错误。在 PHP MyAdmin 中使用 SQL 语句重置 auto_increment 失败。在寻找解决方案后，我放下了桌子并创建了一个替代品。错误仍然存在。仔细观察发现 auto_increment 设置为 0，即使我在创建字段时专门设置了 primary_key 和 auto_increment。再次使用 PHP MyAdmin 手动将 auto_increment 重置为 1，消除了错误。幸运的是，我只使用了一个包含几行测试数据的 3 列表。
回答by JakesIV
I fixed it by removing the auto increment , saving table and then add auto increment again.
我通过删除自动增量，保存表然后再次添加自动增量来修复它。
回答by Christian Lundahl
I had this problem today, too. I have a table with over two million rows and tried to add another 140K rows with LOAD DATA when this error occurred. I switched to the MyISAM engine and it all worked.
我今天也遇到了这个问题。我有一个包含超过 200 万行的表，并尝试在发生此错误时使用 LOAD DATA 添加另外 140K 行。我切换到 MyISAM 引擎，一切正常。
回答by Markus Grob
I had the problem, that the auto_increment was set to 0, but setting the auto_increment explicitly did not work (stayed at 0). I entered an entry manually with an ID and after this, the value was set right.
我遇到了问题，即 auto_increment 设置为 0，但显式设置 auto_increment 不起作用（保持在 0）。我手动输入了一个带有 ID 的条目，此后，该值设置正确。
