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 9:57	Modified:	16 Mar 2006 10:40
Reporter:	Andrejs Dubovskis
Status:	Verified
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	4.1.18, 5.0, 5.1	OS:	Any (*)
Assigned to:	Heikki Tuuri	Target Version:
Triage:	Triaged: D5 (Feature request)

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)