MySQL Bugs: #52193: Failed to read auto-increment value from storage engine

Bug #52193	Failed to read auto-increment value from storage engine
Submitted:	18 Mar 2010 19:56	Modified:	30 May 2013 6:21
Reporter:	Louis Breda van	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.6	OS:	Windows (VISTA64)
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any
Tags:	Auto-increment, inodb, ODBC5

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

Hello,

I just discoverd that:
- goting to the administrator gui
- table maintenance
- optimize tables

Solves the problem for ...... a little while (not for long)

Louis

Hi Louis and thanks for your report.

I'm thinking about this problem, will let you know when I come up with something meaningful.

Louis,

Can you please try with current GA so we can exclude MySQL server problem here? Also, I would require complete test case for this so we can try to repeat it.
	
Finally, I think you're referring to http://bugs.mysql.com/bug.php?id=35602 and http://forums.mysql.com/read.php?22,242704,242704#msg-242704. Posting new report in ODBC category while Sunny is waiting on you in original report makes little sense... Can you please respond to Sunny in Bug#35602 before we proceed.

Possibly makes sense to try not with different(GA) server version, but just with different engine if possible(i.e. MyISAM instead of InnoDB). People on forum say that they experienced problem with GA mysql server.
So I guess test with different engine would even be more indicative to exclude ODBC here(or exclude InnoDB :))

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.