MySQL Bugs: #20524: auto_increment_* not observed when inserting a too large value

Bug #20524	auto_increment_* not observed when inserting a too large value
Submitted:	18 Jun 2006 20:00	Modified:	1 Aug 2006 3:19
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (Linux)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
Found by Monty.
When autogenerating a value for an auto_increment column, if that value would be larger than the maximum of the column's type, the generated value is this maximum, which may violate auto_increment* variables if auto_increment_increment > 1.

How to repeat:
set auto_increment_increment=2;
set auto_increment_offset=2;
# so we want to generate even numbers
create table auto(a tinyint auto_increment primary key);
insert into auto values(120);
insert into auto values(null);
insert into auto values(null);
insert into auto values(null);
select * from auto; # 120,122,124,126
insert into auto values(null);
select * from auto; # 127 was inserted, which is not even!
On top of violating the variables' purpose, this can cause a problem in a master<->master replication, where 127 would be separately inserted on each master, causing a replication conflict.
Behaviour which would have been expected, is that this 127 would be automatically rounded "down" to the closest even number: 126. Then this 126 would have produced "duplicate key" error.

Suggested fix:
I'll fix this together with other patches I have to push into 5.0 (BUG#20188)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7930

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8823

pushed into replication team trees 5.0 and 5.1, will be in 5.0.24 and 5.1.12.
ChangeSet
  guilhem@mysql.com|ChangeSet|20060706123709|51758    2006/07/06 14:37:09+02:00 guilhem@mysql.com +3 -0
  Fix for BUG#20524 "auto_increment_* not observed when inserting
  a too large value": the bug was that if MySQL generated a value for an
  auto_increment column, based on auto_increment_* variables, and this value
  was bigger than the column's max possible value, then that max possible
  value was inserted (after issuing a warning). But this didn't honour
  auto_increment_* variables (and so could cause conflicts in a master-master
  replication where one master is supposed to generated only even numbers,
  and the other only odd numbers), so now we "round down" this max possible
  value to honour auto_increment_* variables, before inserting it.

Noted in 5.0.24, 5.1.12 changelogs.