[mysql bug]Failed to read auto-increment value from storage engine
mysql版本:5.5.19
据说这是mysql很早的一个bug,看来到目前这个版本还是没能彻底解决掉。
bug讨论地址:http://bugs.mysql.com/bug.php?id=52193
引用:
[18 Mar 2010 19:56] Louis Breda van
Description:
Hello,
The bug "Failed to read auto-increment value from storage engine" as reported earlier, is not / not completely fixed.
It is causing me a serieus problem. Need to work arround it, do not yet know how.
Below a futher explanation.
Sincerely,
Louis
How to repeat:
Envirement:
VISTA64, ODBC 5.1.6, STUDIO2010 RC with patches, 6.x (latest) .NET driver, Server 5.5.2. Using VB.NET with ADODB
Error Message
"[MySQL][ODBC 5.1 Driver][mysqld-5.5.2-m2-community]Failed to read auto-increment value from storage engine"
Occurs(1):
1) insert a record having an autonumber as PK (.AddNew ..... .Update)
- no explicit commits
- recordset is opend dynamic
- server or client site does not seems to matter (using serverside now)
- lock pessimistic or optimistic does not seem te matter (using optimistic now)
- having an unique index (in my case a string) "Unique Second Key" next to the PK
- error handler in place, on error goto xyz
2) try to add an record causing an "Unique Second Key" violation
- the code jumps to the error handler
- in a lot of cases the mentioned error will occur and not the expected duplicate key error
Occurs(2) I think I also noticed the following situation
3) insert a new record (no key violation) is OK
4) perform a requery (to make sure the new record is in the actual set)
5) try to fetch the generated autonumber PK
(By the way, I would love to get the generated autonumber PK back in case of an auto number)
I noticed that the problem temporarely disapears after truncating the related table. But that is hardly a solution :>
Louis
解决方案
在进行数据的插入时,系统提示Failed to read auto-increment value from storage engine(从存储引擎读取自增字段失败)错误,经查阅资料,解决方法如下:
1)把涉及数据表的自增字段的自增选项去掉,保存一下
2)重新将这个字段设置为自增字段
参考:http://blog.csdn.net/lhjtianji/article/details/6836451
解决:“Failed to read auto-increment value from storage engine”
今天遇到个问题,从其它平台上注册过来的用户经常出现“Failed to read auto-increment value from storage engine”错误。
自己尝试了下,重现了这个错误,查了下日志:
2008-11-26 13:45:12,815 ERROR [TP-Processor3] GoGoAction.createUser(368) | User 'lefteye123' already exists! Hibernate operation: could not insert: [game.joycube.common.model.User]; uncategorized SQLException for SQL [insert into user (address, city, country, postal_code, province, comment, createDate, account_enabled, joycube, joycube2, password, platform, registerDate, totalJoycube, totalJoycube2, usedJoycube, usedJoycube2, username, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; SQL state [HY000]; error code [1467]; Failed to read auto-increment value from storage engine; nested exception is java.sql.SQLException: Failed to read auto-increment value from storage engine
game.joycube.common.service.UserExistsException: User 'lefteye123' already exists! Hibernate operation: could not insert: [game.joycube.common.model.User]; uncategorized SQLException for SQL [insert into user (address, city, country, postal_code, province, comment, createDate, account_enabled, joycube, joycube2, password, platform, registerDate, totalJoycube, totalJoycube2, usedJoycube, usedJoycube2, username, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; SQL state [HY000]; error code [1467]; Failed to read auto-increment value from storage engine; nested exception is java.sql.SQLException: Failed to read auto-increment value from storage engine
从日志上来看,可能是mysql的BUG,不太可能是程序上的问题。
搜索找到一篇文件:
Bug #35602 "Failed to read auto-increment value from storage engine" with Innodb http://bugs.mysql.com/bug.php?id=35602
其中有以下几句:
[21 Aug 20:00] Bugs SystemPushed into 5.1.28 (revid:timothy.smith@sun.com-20080820004056-g05a56h7y8n0zczb) (version
source revid:azundris@mysql.com-20080821081500-f2d61fh4u61owz3p) (pib:3)[25 Aug 22:19] Paul DuBoisNoted in 5.1.28 changelog.大意是这个BUG已经在5.1.28修改了,并加入到了changelog.
然后查了下现在系统运行的mysql版本:5.1.28-rc。
呵呵,看来是由msyql的BUG引起的,更新到最新版本就可以解决
参考:http://hi.baidu.com/zhukunqian_/item/84bd01342d27995e81f1a719