Archive

Posts Tagged ‘optimize table’

Did you know optimize table can reset your auto-increment ?

July 12th, 2011 No comments

I’ve used the OPTIMIZE command for a long time, in particular for disk space problems.

But I recently had a problem with this command and an auto-incremented table, you can reproduce this problem like that :

Create a table with an auto-incremented column :

mysql> use test
Database changed
mysql> create table test_optimize (id int(8) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)) engine=InnoDB;
Query OK, 0 rows affected (1.09 sec)

mysql> insert into test_optimize values (1),(2),(3),(4);
Query OK, 4 rows affected (0.28 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show table status like ‘test_optimize’\G
*************************** 1. row ***************************
Name: test_optimize
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 4
Avg_row_length: 4096
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 5
Create_time: 2011-07-12 10:21:28
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.02 sec)

4 rows are inserted in this table, the auto increment is set to 5.

Delete all rows and then optimize :

mysql> delete from test_optimize;
Query OK, 4 rows affected (0.00 sec)

mysql> select count(*) from test_optimize;
+———-+
| count(*) |
+———-+
|        0 |
+———-+
1 row in set (0.00 sec)

mysql> optimize table test_optimize;

mysql> show table status like ‘test_optimize’\G
*************************** 1. row ***************************
Name: test_optimize
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
 Auto_increment: 1
Create_time: 2011-07-12 10:23:39
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

After the delete and optimize commands, the auto increment drop to 1 !
And it can be a real problem for your applications software.

This is a MySQL bug : http://bugs.mysql.com/bug.php?id=18274

You can avoid this problem by installing the 5.1.55, 5.5.9 or 5.6.1 patchs (or more recent patchs).

Note that this problem occurs even if you delete only few rows.

So, another reason to migrate to MySQL 5.5 ?

Categories: Admin