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

[18 Jun 2006 20:00] Guilhem Bichot
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)
[20 Jun 2006 14:59] Bugs System
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
[6 Jul 2006 12:38] Bugs System
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
[11 Jul 2006 8:50] Guilhem Bichot
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.
[1 Aug 2006 3:19] Paul DuBois
Noted in 5.0.24, 5.1.12 changelogs.