Bug #71208 Manual does not note that ARCHIVE AUTO_INCREMENT columns cannot decrement
Submitted: 22 Dec 2013 23:52 Modified: 15 Jan 2014 1:09
Reporter: Stewart Smith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0 and above OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: archive, auto_increment

[22 Dec 2013 23:52] Stewart Smith 
Description:
The ARCHIVE storage engine does not support an AUTO_INCREMENT column being decremented (although you can reset the AUTO_INCREMENT value through other methods).

The specific bit of code in ARCHIVE that gets hit is:
    /*
      We don't support decremening auto_increment. They make the performance
      just cry.
    */
    if (temp_auto <= share->archive_write.auto_increment && 
        mkey->flags & HA_NOSAME)
    {
      rc= HA_ERR_FOUND_DUPP_KEY;
      goto error;
    }

That is there for a very good reason, as otherwise the ARCHIVE engine could not have an "index" of the auto_increment column.

How to repeat:
CREATE TABLE b (a int auto_increment primary key) ENGINE=ARCHIVE;
INSERT INTO b values (5);
INSERT INTO b values (4);

Suggested fix:
Update the documentation and/or return a better error message.
[23 Dec 2013 5:23] MySQL Verification Team 
Hello Stewart,

Thank you for the bug report.

Thanks,
Umesh
[15 Jan 2014 1:09] Paul DuBois 
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added text:

ARCHIVE does not support inserting a value into an AUTO_INCREMENT
column less than the current maximum column value. Attempts to do so
result in an ER_DUP_KEY error.