| 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: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1.41 | OS: | Any |
| Assigned to: | Vasil Dimov | CPU Architecture: | Any |
[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.
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.