469,625 Members | 1,059 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

auto_increment reuse in MyISAM

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)
Jul 20 '05 #1
0 1601

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Paul Lamonby | last post: by
9 posts views Thread by Bart Van der Donck | last post: by
reply views Thread by Shailesh | last post: by
2 posts views Thread by hjyn | last post: by
1 post views Thread by Fábio Emilio Costa | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.