I made a MyISAM table on mysql 4.0.18 NT with auto_increment column
started at 2147483646. The third row I insert fails as expected
because the integer range is maxed out. However, if I delete the
topmost row, and then re-insert a row, the topmost value is reused.
This contradicts the documentation.
http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
"If you delete the row containing the maximum value for an
AUTO_INCREMENT column, the value will be reused for an ISAM or BDB
table, but not for a MyISAM or InnoDB table."
------------------------------------------------------------
mysql> create table testauto (myint int primary key auto_increment)
-> auto_increment=2147483646;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into testauto values (null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into testauto values (null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into testauto values (null);
ERROR 1062: Duplicate entry '2147483647' for key 1
mysql> delete from testauto where myint=2147483647;
Query OK, 1 row affected (0.00 sec)
mysql> insert into testauto values (null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from testauto;
+------------+
| myint |
+------------+
| 2147483646 |
| 2147483647 |
+------------+
2 rows in set (0.00 sec)
mysql> show table status from test like 'testauto';
+----------+--------+------------+------+----------------+-------------+--------
---------+--------------+-----------+----------------+---------------------+----
-----------------+------------+----------------+---------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length
| Max_dat
a_length | Index_length | Data_free | Auto_increment | Create_time
| Upd
ate_time | Check_time | Create_options | Comment |
+----------+--------+------------+------+----------------+-------------+--------
---------+--------------+-----------+----------------+---------------------+----
-----------------+------------+----------------+---------+
| testauto | MyISAM | Fixed | 2 | 5 | 10
| 214
74836479 | 2048 | 0 | 2147483647 | 2004-04-17
00:34:58 | 200
4-04-17 00:35:49 | NULL | | |
+----------+--------+------------+------+----------------+-------------+--------
---------+--------------+-----------+----------------+---------------------+----
-----------------+------------+----------------+---------+
1 row in set (0.00 sec)