Confusing error code when doing auto-increment insert for out-of-range values

Description

When one inserts a row into a table with auto-increment, the error message
can be confusing:

create table t1 (a smallint primary key auto_increment);
insert into t1 values(32767);
insert into t1 values(NULL);
ERROR 1062 (23000): Duplicate entry '32767' for key 'PRIMARY

In some cases on gets:
ERROR 1264 (22003): Out of range value for column 'id' at row 1
or there is a warning about the "out of range".
This depends on SQL Mode, engine used or if one inserts one or multiple row at a time.
Inserting negative numbers for the auto increment columns also works differently between engines.

The task is to ensure that all engines works exactly the same way and that one will always get:
ERROR 1264 (22003): Out of range value for column
when an auto increment overflows.

To fix this one need to do changes in sql/handler.cc
and in innodb/xtradb.

Because of how the auto_increment checking is done, we can fix all issues
except for the case of inserting a generated max bigint unsigned value (18446744073709551615).

In this case all engines will report:
"Failed to read auto-increment value from storage engine"
To fix this would require a handler interface change that I am not confident in doing in 5.5
In additional this would be hard to fix this in InnoDB as stores 'the next number to be used'. This means we can't distinguish between inserting the max possible number or if there is still room for the max number.

Another effect of this is that for columns of type bigint unsigned, the max generated auto increment value is 18446744073709551614

Environment

All

Status

Assignee

Michael Widenius

Reporter

Michael Widenius

Labels

None

Time tracking

24h

Fix versions

Affects versions

Due date

2012/09/18

Priority

Blocker
Configure