[mysql bug]Failed to read auto-increment value from storage engine

htbzwd

　　
　　[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