As the MySQL Documentation under http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html points out, InnoDB stores AUTO_INCREMENT values only in memory. This has the impact, that auto_increment values don’t remain stable due to server restarts. To clearify the problem, we consider the following table structure with a starting auto_increment of 1000000:

1
2
3
4
CREATE TABLE auto_increment_test (
  id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  some_content VARCHAR(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1000000;

Now insert a new record, delete it afterwards and restart the Server:

1
2
mysql> INSERT INTO auto_increment_test (some_content) VALUES ('this is a test');
Query OK, 1 ROW affected (0.00 sec)
1
2
3
4
5
6
7
mysql> SELECT * FROM auto_increment_test;
+---------+----------------+
| id      | some_content   |
+---------+----------------+
| 1000000 | this IS a test |
+---------+----------------+
1 ROW IN SET (0.00 sec)
1
2
mysql> DELETE FROM auto_increment_test;
Query OK, 1 ROW affected (0.02 sec)
1
# /etc/init.d/mysql restart

Now redo the example from above:

1
2
mysql> INSERT INTO auto_increment_test (some_content) VALUES ('this is a test');
Query OK, 1 ROW affected (0.01 sec)
1
2
3
4
5
6
7
mysql> SELECT * FROM auto_increment_test;
+----+----------------+
| id | some_content   |
+----+----------------+
|  1 | this IS a test |
+----+----------------+
1 ROW IN SET (0.00 sec)

This is really weird, because the auto_increment starts from now on by 1. This is not what I wanted with the CREATE TABLE … AUTO_INCREMENT=1000000 statement. You don’t even get a notification, that things don’t work as you expect.

My solution for this problem is a trigger, that verifies the auto_increment value and sets it appropriate. It uses the mysql information_schema to lookup the current auto_increment value, so this should really be a really small overhead. Have look at it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
delimiter //

CREATE TRIGGER trigger_auto_increment_test BEFORE INSERT ON auto_increment_test
FOR EACH ROW
BEGIN
DECLARE auto_incr BIGINT;

SELECT AUTO_INCREMENT INTO auto_incr FROM information_schema.TABLES WHERE table_schema=DATABASE() AND TABLE_NAME='auto_increment_test';
  IF (auto_incr < 1000000) THEN
      SET NEW.id = 1000000;
  END IF;
END;//

delimiter ;

All in all MySQL should really print a warning about that, because the syntax of the CREATE TABLE statement suggest another behaviour. Nevertheless you can use the above mentioned solution to avoid the problem.