MySQL 4.0 AUTO_INCREMENT Behavior

This article is an experiment to see what happens when one runs out of numbers in a MySQL auto_increment column. Do the numbers wrap? Are the number reused?

To answer the question, run the simple test below. This was run on MySql 4.0.18-max-nt.

mysql> create table sml
     > (myint integer primary key auto_increment)
     > auto_increment = 2147483646;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into sml values (null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sml values (null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sml values (null);
ERROR 1062: Duplicate entry '2147483647' for key 1

As you can see, the auto_increment column does not wrap around back to zero or one. It also does not reuse previous entries. Instead, when a insertion of a new row is attempted, the database gives a duplicate entry error.

The above table only has 2 records, so there is actually room to insert more entries if the key can be reset somehow.

This is easy to do by inserting a specific number into the column, as shown below.

mysql> insert into sml values (1);

Query OK, 1 row affected (0.00 sec)

mysql> select * from sml;
+------------+
| myint      |
+------------+
|          1 |
| 2147483646 |
| 2147483647 |
+------------+
3 rows in set (0.00 sec)

Although we can re-insert a smaller value, we cannot insert NULL to get an automatic number again, otherwise we get the same duplicate key error. This is because auto_increment algorithm always returns one more than the highest auto_increment column value in the table. The only way

mysql> insert into sml values (NULL);

ERROR 1062: Duplicate entry '2147483647' for key 1

According to the MySQL manual, if you delete the highest auto_increment value from the MyISAM table, you still cannot re-use the lower values.

mysql> delete from sml where myint=2147483647;

Query OK, 1 row affected (0.05 sec)

mysql> insert into sml values (NULL);

Query OK, 1 row affected (0.00 sec)

mysql> select * from sml;

+------------+
| myint      |
+------------+
|          1 |
| 2147483646 |
| 2147483647 |
+------------+
3 rows in set (0.00 sec)

It appears that the value 2147483647 was reused, but it actually was not. MySQL actually is trying to insert 2147483648 into the column, but since the int column can not hold that value, the number is being truncated. MySQL 4.1 will issue a warning when this happens: Warning 1264 Data truncated, out of range for column 'myint' at row 1.

For more information, see MySQL Bug #3506: auto_increment reuse in MyISAM tables: