Bug #18274 InnoDB auto_increment field reset on OPTIMIZE TABLE
Submitted: 16 Mar 2006 8:57 Modified: 12 Jan 2011 22:13
Reporter: Andrejs Dubovskis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:4.1.18, 5.0, 5.1 OS:Any (*)
Assigned to: Sunny Bains CPU Architecture:Any
Triage: Triaged: D5 (Feature request) / R2 (Low) / E2 (Low)

[16 Mar 2006 8:57] Andrejs Dubovskis
Description:
We have InnoDB table with auto_increment field.
This table primary used for inserts and deletes only. In any moment it keeps few records only (typically - 0). After some time "show table status" displays very large Data_length while "select count(*)" shows 0. To reuse space we had tried "OPTIMIZE table X" and found, that auto_increment field was droped to 1.
This behaviour brokes our data integrity.

(Yes, we know, that on MYSQLD restart auto_increment field in InnoDB table set to 1).

Is any other way to compress InnoDB tablespace without touching auto_increment field?

How to repeat:
create table a (a integer primary key not null auto_increment) engine=innodb;

insert into a values ();

delete from a ;

show table status like 'a';

optimize table a;

show table status like 'a';
[16 Mar 2006 9:27] Hartmut Holzgraefe
verified, also happens with 5.0 and 5.1
[16 Mar 2006 9:34] Heikki Tuuri
OPTIMIZE TABLE recreates the whole table. That is why the counter gets reset.

Workaround: use ALTER TABLE ... AUTO_INCREMENT=... to set the correct value after the OPTIMIZE.

Fix: InnoDB should store the value of the internal auto-inc counter and restore it after OPTIMIZE TABLE  (or an ALTER TABLE where it should not change).

Regards,

Heikki
[16 Mar 2006 9:44] Heikki Tuuri
I am putting this as a feature request. This can be bundled with another feature request: InnoDB should remember the value of the internal auto-inc counter also after a mysqld shutdown.
[15 Feb 2007 17:02] Tobias Asplund
Similar situation that could potentially be bad, when a fix for this comes, maybe make sure that the following works too?

mysql> CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT) ENGINE = MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t1 VALUES (), (), (), (), ();
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;    
+---+
| a |
+---+
| 1 | 
| 2 | 
| 3 | 
| 4 | 
| 5 | 
+---+
5 rows in set (0.00 sec)

mysql> DELETE FROM t1 WHERE a > 3;
Query OK, 2 rows affected (0.00 sec)

mysql> ALTER TABLE t1 ENGINE = InnoDB;
Query OK, 3 rows affected (0.28 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 VALUES (), (), (), (), ();
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+---+
| a |
+---+
| 1 | 
| 2 | 
| 3 | 
| 4 | 
| 5 | 
| 6 | 
| 7 | 
| 8 | 
+---+
8 rows in set (0.00 sec)
[12 Nov 2010 0:47] Sunny Bains
Making the next autoinc column persistent is a separate issue from resetting
the value after an OPTIMIZE TABLE. We already have a check for ALTER TABLE
and CREATE INDEX to preserve the value on table recreate. We should be able to
add an additional check for OPTIMIZE TABLE to preserve the next value.
[22 Dec 2010 21:31] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101222212842-y0t3ibtd32wd9qaw) (version source revid:alexander.nozdrin@oracle.com-20101222212842-y0t3ibtd32wd9qaw) (merge vers: 5.6.1) (pib:24)
[8 Jan 2011 15:10] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:vasil.dimov@oracle.com-20110108150508-gpanhz48z8069qot) (version source revid:vasil.dimov@oracle.com-20110108150048-b1y9m8xe72hay0ch) (merge vers: 5.5.9) (pib:24)
[8 Jan 2011 15:11] Bugs System
Pushed into mysql-5.1 5.1.55 (revid:vasil.dimov@oracle.com-20110108145923-0tjqdpa5w3d6tuwn) (version source revid:vasil.dimov@oracle.com-20110108145923-0tjqdpa5w3d6tuwn) (merge vers: 5.1.55) (pib:24)
[12 Jan 2011 22:13] John Russell
Added to doc change log:

The OPTIMIZE TABLE statement would reset the auto-increment counter
for an InnoDB table. Now the auto-increment value is preserved across
this operation.
[24 Jan 2013 11:14] Gleidson Gleidson
This bug still happens. I have tried in the 5.1.66 version, exactly as the same example in this bug.
[27 Mar 2013 16:55] Marcelo Altmann
I've tested it on 5.1.66 and still the same problem, on 5.5 seems to be fixed (at least on 5.5.29)