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: | |
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
[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.