MySQL Bugs: #36411: "Failed to read auto-increment value from storage engine" in 5.1.24 auto-inc

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

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?

see also bug #35602 for a different testcase (that never sets the auto-inc explicitly)

Thank you for the report.

Verified as described.

With MyISAM storage engine bug is not repeatable.

Sunny, please look at this, too.

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

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

This is a minor bug, only to be fixed in 6.0.

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

Bug: http://bugs.mysql.com/bug.php?id=41841 marked as duplicate of this one.

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.

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!

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

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?

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.

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.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

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)

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)

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)