mysql 从存储引擎读取自增值失败
我有一个 mysql 表，其中一个 id 字段作为自动增量。
当我向表中插入值时出现错误
1467 - 无法从存储引擎读取自动增量值
显示表状态还显示具有自动增量的字段具有
18446744073709551615作为Auto_increment值。
任何人都可以帮助我......会出现什么问题吗？
I am having mysql table with one id field as auto-increment .
When I insert values to the table am getting error as
1467 - Failed to read auto-increment value from storage engine
Also the show table status shows me that the field with auto increment has
18446744073709551615 as Auto_increment value.
What would be the issue can any one help me ....?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（18）
我遇到了同样的错误，但就我而言，表中有大约 1.5k 条记录。
我通过像这样重置自动增量来修复它：
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:
几周前，我在运行插入语句时开始收到此错误：
...即使我的表设置为自动增量。我进去并将 auto_increment 值从 127 更改为 128，然后我开始收到此错误：
我最终发现该表最初是使用tinyint 列创建的，ID 不是标准整数......所以基本上它无法理解数字大于 127。我将列类型切换为正确的整数，这解决了问题。
希望对某人有帮助:)
I started getting this error a couple of weeks back when running insert statements:
... 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:
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.
Hope that helps someone :)
问题绝对可能是这样的：将 18446744073709551615 转换为十六进制，你会发现
$FFFF-FFFF-FFFF-FFFF。
如果您的字段是无符号 64 位，您就达到了其限制。
Problem could absolutely be that: convert 18446744073709551615 to hex and you'll find
$FFFF-FFFF-FFFF-FFFF.
If your field is an unsigned 64bit you reach its limit.
就我而言，我犯了一个愚蠢的错误。我之前更改了表，并将
AUTO_INCRMENT列的名称从ID更改为id。因此，给定的列名区分大小写，后续插入无法找到原始列。For my part, I made a dumb mistake. I had earlier altered my table and changed the name of the
AUTO_INCREMENTcolumn fromIDtoid. So, given column names are case-sensitive, subsequent inserts couldn't find the original column.实际上，您可以简单地更改该列以删除其
auto_increment属性，然后再次将其设置为auto_increment。在我这边，这种方法确实有效。Actually, you can simply alter the column to delete its
auto_increamentproperty and set it asauto_incrementagain. On my side, this way did work.我也犯同样的错误。我只是更改表并增加自动增量字段的大小，然后运行以下查询 -
其中 6221 是 Auto_increment 字段的最后一个值。
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 -
where 6221 is the last value of the filed with Auto_increment.
不到一个小时前，我第一次遇到这个错误。在 PHP MyAdmin 中使用 SQL 语句重置 auto_increment 失败。在寻找解决方案后，我删除了该表并创建了一个替换表。错误依然存在。仔细观察发现，即使我在创建字段时专门设置了 Primary_key 和 auto_increment，auto_increment 也设置为 0。再次使用 PHP MyAdmin 手动将 auto_increment 重置为 1，消除了错误。幸运的是，我只使用包含几行测试数据的 3 列表。
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.
我通过删除自动增量、保存表然后再次添加自动增量来修复它。
I fixed it by removing the auto increment , saving table and then add auto increment again.
我今天也遇到这个问题了。我有一个包含超过 200 万行的表，并在发生此错误时尝试使用 LOAD DATA 添加另外 140K 行。我切换到 MyISAM 引擎，一切正常。
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.
我遇到了同样的问题，解决方案是将列从smallint(6) 更改为int。
I had the same problem and the solution was to change the column from smallint(6) to int.
我遇到了问题，auto_increment 设置为 0，但显式设置 auto_increment 不起作用（保持为 0）。
我使用 ID 手动输入一个条目，之后该值就设置正确了。
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_INCRMENT 并再次检查
I resolve this problem uncheck the option AUTO_INCREMENT of the field and check again
我的解决方法是更改表并将其重命名为 orignal_backup 之类的名称并保存，然后再次将其重命名回原始表，这个技巧对我有用。
My workaround was to alter the table and rename it to something like orignal_backup and save, then again rename it back to orignal one, this trick worked for me.
今天，当我尝试将记录插入名为 mytablename 的表时，出现同样的错误：
在表中我只有 52269 条记录，max(id) = 52269。
我已尝试在 mysql 命令控制台下执行以下命令，但没有任何反应，错误仍然存在：
我必须找到另一个解决方案。当我这样做时：
它显示我的 Auto_Increment 值为 0，这显然是错误的，它必须大于或等于 1。
我修改了表中的任何列，如下所示：
我再次执行 show table status 命令，
Auto_Increment 值已自动更改为52270，非常有价值。
一切从那时起...
Today, I've the same error when I tried to insert a record into a table named mytablename:
In the table I've only 52269 records, max(id) = 52269.
I've tried following command under mysql command console, but nothing takes place, error persists:
I must find another solution. When I do:
It shows that my Auto_Increment value is 0, that is clearly in error, it must be greater or equal to 1.
I modified any colum in the table like:
I do again the show table status command,
Auto_Increment value has been automatically changed to 52270, that is good value.
All goes since then...
我也有同样的问题。
原因：我用主键和自动增量属性更改了表字段名称，因此自动增量停止工作并出现错误1467。
对我有用的是重置 phpmyadmin 中的主键，如下所示：
它对我有用。
I had the same problem.
Reason: I have changed the name of my table field's name with primary key and auto increment property, due to which auto increment stopped working and the error 1467 appeared.
What worked for me is resetting the primary key in phpmyadmin as shown below:
It worked for me.
我发现该参数已启用。根据数据库专家的说法，它不应超过“1”……
在我的例子中，它被设置为“4”，如下所示。
mysql>选择@@innodb_force_recovery；
+------------------------------------+
| @@innodb_force_recovery |
+------------------------------------+
| 4 |
+------------------------------------+
因此，我确实在启动过程中没有传递参数的情况下重新启动了mysql，
后来我能够插入或更新表...希望它能帮助某人..
I have found the parameter was enabled. it should not be more than '1' as per DB-experts....
In my case it was set to '4', shown below.
mysql> select @@innodb_force_recovery;
+-------------------------+
| @@innodb_force_recovery |
+-------------------------+
| 4 |
+-------------------------+
Hence, i did restart the mysql without passing the parameter during startup ,
later i was able to insert or update the tables ...hope it will help someone ..
很容易就修好了。截断表并再次上传数据，错误消失。
fixed it very easily. Truncated the table and uploaded the data again and the error disappears.
我有同样的问题。我有一个包含数千条记录的表。我刚刚将字段的类型从 int 更改为 bigint
I have the same issue. I have a table with thousands of records. I just changed the type of the field from int to bigint