Bug #49497 Error 1467 (ER_AUTOINC_READ_FAILED) on inserting a negative value
Submitted: 7 Dec 2009 10:15 Modified: 11 Aug 2010 21:58
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1.41 OS:Any
Assigned to: Vasil Dimov
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[7 Dec 2009 10:15] Elena Stepanova
Description:
On an attempt to insert a negative value into an auto-increment column, ERROR 1467 (Failed to read auto-increment value from storage engine) is still returned and insert fails (although from change log for 5.1.41, different behavior could be expected).

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1  (pk INTEGER AUTO_INCREMENT, PRIMARY KEY (pk)) ENGINE = InnoDB;
INSERT INTO  t1 VALUES (NULL) ,  ('-685113344') ,  (NULL) ;
SELECT * FROM t1;

# mysql> INSERT INTO  t1 VALUES (NULL) ,  ('-685113344') ,  (NULL) ;
# ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
# mysql> SELECT * FROM t1;
# Empty set (0.00 sec) 

Suggested fix:
Here is Sunny's analysis:

InnoDB does ignore -ve values because it looks at the type of the
autoinc column when comparing. However, this function/macro from
MySQL   set_if_bigger(*first_value, autoinc); doesn't. We need to
replace the use of this function in ha_innobase::get_auto_increment()
with code that does the right thing.

The above failure is because MySQL passes in -685113343 as *first_value
to ha_innobase::get_auto_increment() and the type of *first_value is ulonglong
the set_if_bigger() macro does a simple compare using the C types whereas what
we want is to check for the column type in the equivalent of set_if_bigger().

The options are:

1. I will have to get rid of set_if_bigger() and roll our own version.

2. Check *first_value on entry everytim against the column max value
   and  set *first_value to next autoinc if it's > col max value. ie.
   not rely on what is passed in from MySQL.
[28 Apr 2010 4:14] James Day
The fix for this is in the InnoDB Plugin version 1.0.7 which was included with MySQL 5.1.46.
[22 Jul 2010 15:54] Vasil Dimov
Sorry, it is too late to add a retrospective automatic message. The fix went into the mysql tree in this changeset from 2010-02-26:

mysql-5.1$ bzr log -csvoj@sun.com-20100226090257-klzwfw40j5cxhge5
------------------------------------------------------------
revno: 3351.5.4
revision-id: svoj@sun.com-20100226090257-klzwfw40j5cxhge5
parent: svoj@sun.com-20100226090226-rraaj0tet759yt2f
committer: Sergey Vojtovich <svoj@sun.com>
branch nick: mysql-5.1-bugteam
timestamp: Fri 2010-02-26 13:02:57 +0400
message:
  Applying InnoDB snapshot 
  
  Detailed revision comments:
  
  r6538 | sunny | 2010-01-30 00:43:06 +0200 (Sat, 30 Jan 2010) | 6 lines
  branches/5.1: Check *first_value every time against the column max
  value and  set *first_value to next autoinc if it's > col max value.
  ie.  not rely on what is passed in from MySQL.
  
  [49497] Error 1467 (ER_AUTOINC_READ_FAILED) on inserting a negative value
  rb://236
[11 Aug 2010 21:58] John Russell
Adding to the 5.1.46 change log:

        InnoDB would return an error
        when inserting a negative value into an
        auto-increment column.