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

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 ?

Fatal timeout !

There are several parameters to set a timeout on MySQL :

But I would like to focus on wait_timeout (or interactive_timeout depending on how you connect)

This timeout allows MySQL to automatically close a connection in case of non-activity during the time defined by this parameter (default value is 28000 seconds).

The problem I wish to explain here may happen when this timeout is set to a low value (about 10 to 30 seconds).

Indeed, in this case, this timeout may have serious consequences, look at that :
[The wait_timeout parameter is set to 10 seconds in this case]

You are connected through the standard MySQL client and you need to run a delete query (bypass autocommit) :

  • mysql> start transaction;
  • [ You wait more than 10s because a friend calls your attention about the latest news on iphone 5 ! ]
  • mysql> delete from most_valuable_table;

Your problem starts here because you forgot the where clause !
After the delete command, MySQL said :

ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect... Connection id:    98 Current database: masterQuery OK, 33698541 rows affected (0.01 sec)

This simply means that the connection was lost and the last order re-submitted.
Then, you try to cancel your incomplete “delete” with a rollback command (your heart beats faster now…) :

  • mysql> rollback;
  • mysql> select * from most_valuable_table;

This select returns 0 rows, cruel !

It isn’t a mistake, the “start transaction” order was lost when MySQL has gone away.
The rollback is no longer possible. Bye bye to your 33.000.000 of rows !

It’s time to go to vacation…

Does mpstat can replace vmstat ?

I prefer to say right now, the answer is no !
However, mpstat can provide a minimum service to show essentials information about your CPU usage :

Show usage for all CPU (every 1s) : mpstat 1

Show usage for CPU number 2 (every 1s) : mpstat -P 2 1

It’s possible to retrieve CPU load (user/sys) and IO waits via a simple interface.
And I like the opportunity to focus on a single CPU. It can be a usefull tool for monitoring and scripting.

But there isn’t any information about the run queue processes and this is the bad news here !

So, let me know if you’ve used this tool, all comments are welcome.