Bug #36411 "Failed to read auto-increment value from storage engine" in 5.1.24 auto-inc
Submitted: 29 Apr 2008 19:46 Modified: 18 Jun 2010 12:59
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1.24, 5.1.25 OS:Any
Assigned to: Sunny Bains CPU Architecture:Any
Tags: auto_increment, autoinc, error 1467

[29 Apr 2008 19:46] Shane Bester
Description:
This may or may not be a bug, but I'll open the bug report anyway, since 5.1.23 didn't have this problem.  

5.1.23 and 5.0.58 behaviour differs to 5.1.24.
Using the statements in "How to repeat", we have the output in 5.1.24:

mysql> insert into `qa05` set `id` = 2;
Query OK, 1 row affected (0.00 sec)

mysql> insert into `qa05` set `id` = -1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into `qa05` set `id` = -1;
ERROR 1062 (23000): Duplicate entry '-1' for key 'PRIMARY'
mysql> insert into `qa05` set `id` = -1;
ERROR 1062 (23000): Duplicate entry '-1' for key 'PRIMARY'
mysql> insert into `qa05` values ();
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

The auto_increment value in show table status says "18446744073709551615".

And this output in 5.0.58 and 5.1.23:

mysql> insert into `qa05` set `id` = -1;
ERROR 1062 (23000): Duplicate entry '-1' for key 1
mysql> insert into `qa05` set `id` = -1;
ERROR 1062 (23000): Duplicate entry '-1' for key 1
mysql> insert into `qa05` values ();
Query OK, 1 row affected (0.00 sec)

For what reason does the 1st and 2nd insert give error 1062, and the
third insert return error 1467 on 5.1.24?  

Then, if you comment out the 1st show table status, the results are
different.  Why does the show table status have any effect on this - given the fact the auto-inc should be initialized by the first insert.

I read:

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html 
"The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type." 

Should we define the behaviour better?  Should user-mode sql statements result in storage engine errors?

How to repeat:
drop table if exists `qa05`;
create table `qa05` (`id` int auto_increment primary key)engine=innodb;
insert into `qa05` set `id` = 1;
show table status like 'qa05';
insert into `qa05` set `id` = 2;
insert into `qa05` set `id` = -1;
insert into `qa05` set `id` = -1;
insert into `qa05` set `id` = -1;
insert into `qa05` values ();
show table status like 'qa05';

Suggested fix:
we have some explanation, because 5.1.23->5.1.24 behaviour changed, at least.
Perhaps the fix for bug #34335 causes this new behaviour?
[29 Apr 2008 20:18] MySQL Verification Team
see also bug #35602 for a different testcase (that never sets the auto-inc explicitly)
[29 Apr 2008 20:42] Sveta Smirnova
Thank you for the report.

Verified as described.

With MyISAM storage engine bug is not repeatable.
[30 Apr 2008 14:19] Heikki Tuuri
Sunny, please look at this, too.
[1 May 2008 23:24] Sunny Bains
Heikki, Shane,

This change in behavior is indeed due to the fix for bug #34335.  Also, if in the test you add another 

   INSERT INTO t values();

You will get the following error: 

   ERROR 1062 (23000): Duplicate entry '0--' for key 'PRIMARY'

This change in behavior is because InnoDB internally now treats the AUTOINC
counter as an unsigned long long int, previously it used a signed type. The
part that's not clear to me is what is the desired behavior, should an
increment of -1 return 0 for signed columns or not ? The problem is that
in a sense that's a wrap around and I'm not sure that is desirable behavior
and/or whether it's correct. However I admit it's a (radical) departure from
past behavior, so I'm open to suggestions.

Regards,
-sunny
[23 May 2008 14:01] Sunny Bains
I have a patch for this, when users set -ve values explicitly for auto-increment columns it will not affect the internal table level counter.

Regards,
-sunny
[3 Jun 2008 13:08] Heikki Tuuri
This is a minor bug, only to be fixed in 6.0.
[10 Jun 2008 0:54] Sunny Bains
The proposed fix for this bug will also fix all init issues e.g., Bug# 35498. 
The proposed fix reads the AUTOINC value on table open, unlike the current
scheme  where it reads it on demand. Previously (before the new AUTOINC code),
we read the AUTOINC code using full txn semantics and because of that we
couldn't read the value during open. The new AUTOINC code uses InnoDB's low
level functions to read the value and so the AUTOINC value can be read when
the table is first opened. This not only simplifies the code but also gets
rid of a lot non-determinism and some unnecessary locking.

Regards,
-sunny
[3 Jan 2009 18:21] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=41841 marked as duplicate of this one.
[7 Jan 2009 15:13] Sam Kimmel
I'm sorry Heikki, but I respectfully disagree and wouldn't call this bug minor.  If Sunny has a patch I'd like to see it integrated into the MySQL 5 line.  Customers of shared hosts often do not have control over the version of MySQL that is available to them.  These are the same customers who don't do much programming themselves and will not understand why their open source/boxed programs aren't working.  Many developers use negative ids for special case db records.  All installations of programs that make use of this technique will fail for versions 5.1.24 thru 5.1.30 (current version as of today).

I like Sunny's idea for simply ignoring negative inserted ids.
[7 Jan 2009 15:34] Peter Laursen
I agree that there should not be change in behaviour that breaks this true statement "Many developers use negative ids for special case db records."

Also (to the extent possible) different storage engines should behave identically!

Also I think it is quite a serious bug!
[8 Jan 2009 1:47] Sunny Bains
Peter, Sam,

This bug was fixed in Oct 2008 and -ve values will be ignored
when updating the table AUTOINC counter. Inserting -ve values
explicitly in INSERT statements is mentioned in the MySQL
documentation and the results are actually undefined.

  http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

In case you can't see the private comments, the snapshot containing
the fix was sent on 23 Oct 2008 (innodb-5.1-ss2858.tar.gz).

The state "Patch queued" indicates that the patch is queued within
MySQL's work queue.

Regards,
-sunny
[8 Jan 2009 2:26] Sam Kimmel
Sorry, I'm not familiar with the MySQL patching process.  How long does it take to work the patch into a release?  If the patch has been ready since mid-October, should I expect it in the MySQL 5 series?  Or is it queued because it is waiting for a v6 release as previously mentioned?
[5 Feb 2009 6:39] Sveta Smirnova
Bug #42566 was marked as duplicate of this one.

Fix is in version 5.1.31 and 5.0.67, but not reflected in the report for some reason.
[12 Mar 2009 21:23] Paul Dubois
Noted in 5.1.31, 6.0.10 changelogs.

InnoDB could fail to generate AUTO_INCREMENT values if rows
previously had been inserted containing literal values for the
AUTO_INCREMENT column.
[5 May 2010 15:17] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 14:58] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 6:04] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:33] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:00] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 23:19] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[15 Jun 2010 8:17] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 12:08] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:55] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:36] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)