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

[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:[e-mail]-20080820004056-g05a56h7y8n0zczb) (version

source revid:[e-mail]-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