Bug #3506 auto_increment reuse in MyISAM tables
Submitted: 19 Apr 2004 11:14 Modified: 28 Apr 2004 19:43
Reporter: Shailesh Humbad Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0.x OS:Linux (Debian, Windows XP)
Assigned to: Sergei Golubchik CPU Architecture:Any

[19 Apr 2004 11:14] Shailesh Humbad
Description:
I made a MyISAM table on mysql 4.0.18 NT Windows XP 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.  If I do not set the auto_increment option, insert three rows, delete the third, and insert another, then the table contains 1,2,4 as expected.  This error is repeatable on Debian Linux i386 MySQL 4.0.14-log.  Show table status reports the tables as MyISAM.

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."

How to repeat:
create table testauto (myint int primary key auto_increment) auto_increment=2147483646;
insert into testauto values (null);
insert into testauto values (null);
insert into testauto values (null);
# ERROR 1062: Duplicate entry '2147483647' for key 1, as expected.
select * from testauto;
# table contains 2147483646 and 2147483647
delete from testauto where myint=2147483647;
select * from testauto;
# table contains 2147483646
insert into testauto values (null);
select * from testauto;
# table contains 2147483646 and 2147483647
# 2147483647 value was reused.
[26 Apr 2004 1:22] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

It's an expected behaviour.

What happens, in fact, is that auto_increment value is not reused, but a new value of 2147483648 is inserted. But the way MySQL works, if the value inserted is outside the valid column range (and 2147483648 is not something INTEGER can store) it is truncated - replaced by the max possible value in your case. Same happens when you insert 1000 into TINYINT column - it has nothing to do with auto_increment,
[26 Apr 2004 11:48] Shailesh Humbad
I see why you think it is expected behavior, but from an end user's point of view, this explanation does not make any sense.  I have inserted and then deleted the highest value, 2147483647, and upon reinserting, it reappears.  That means the value has been reused, and that contradicts the documentation.  At least, the documentation is incomplete.  If MySQL truncates a hidden auto_increment value and inserts, it is still possibly reusing the highest value of the column range.  I think this is bad behavior.  If the value of the column is exceeded, then the insert statement should fail with a special auto_increment out of bounds error message, not (effectively and possibly) reuse the highest value.  I feel that the concept of auto_increment reuse should follow a set-theoretic notion of reuse, not something peculiar to a particular db implementation.

I tested this situation with signed and unsigned BIGINT columns, and there is worse behaviour.  It actually is wrapping around the value.  See below.  All this unexpected behavior needs to be properly documented.  I understand that the circumstances for these conditions to exist are rare, but their documentation is necessary for completeness.

Just as I expected, SQL Server 2000 gives the error "Arithmetic overflow error converting IDENTITY to data type int." when inserting an out of range auto_increment value into an integer column, and also when re-inserting after deleting the highest value.  Something like this would be more 'correct' behavior, but in the absence of that, the peculiarities should be documented.  It would not be a bad idea to have a whole section on the mechanics of the auto_increment field type in each table engine.

mysql> create table tbl (myint bigint auto_increment primary key) auto_increment
=9223372036854775806;
mysql> insert into tbl values (null);
mysql> insert into tbl values (null);
mysql> insert into tbl values (null);
mysql> insert into tbl values (null);
ERROR 1062: Duplicate entry '-9223372036854775808' for key 1
mysql> select * from tbl;
+----------------------+
| myint                |
+----------------------+
| -9223372036854775808 |
|  9223372036854775806 |
|  9223372036854775807 |
+----------------------+
3 rows in set (0.02 sec)

----------------------------------------

mysql> create table tbl (myint bigint auto_increment primary key) auto_increment
=18446744073709551614;
mysql> insert into tbl values (null);
mysql> insert into tbl values (null);
ERROR 1062: Duplicate entry '-2' for key 1
mysql> select * from tbl;
+-------+
| myint |
+-------+
|    -2 |
+-------+
1 row in set (0.00 sec)

----------------------------------------

mysql> create table tbl (myint tinyint unsigned auto_increment primary key) auto_increment=1000;                                                                                                                                                                                       mysql> insert into tbl values (null);
mysql> insert into tbl values (null);
ERROR 1062: Duplicate entry '255' for key 1
[28 Apr 2004 19:43] Sergei Golubchik
Nothing we can do in 4.0 - it's not a bugfix but a behavioural change that would affect too big part of the code to let it go into the stable version.

In 4.1 MySQL already issues a warning when you insert a value outside of the column's valid range:

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), (null);
Query OK, 2 rows affected (0.00 sec)
mysql> delete from testauto where myint=2147483647;
Query OK, 1 row affected (0.04 sec)
mysql> insert into testauto values (null);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1264 | Data truncated, out of range for column 'myint' at row 1 |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

We also plan to implement so called "strict" mode that will change the above warning (and many other constraint violation warnings) into an error.