MySQL Bugs: #18274: InnoDB auto_increment field reset on OPTIMIZE TABLE

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

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';

verified, also happens with 5.0 and 5.1

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

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.

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)

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.

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)

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)

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)

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.

This bug still happens. I have tried in the 5.1.66 version, exactly as the same example in this bug.

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)