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.


mysql-test/r/rpl_auto_increment.result:
  result update. Before the fix, the result was that master inserted 127 in t1
  (which didn't honour auto_increment_* variables!),
  instead of failing with "duplicate key 125" like now.
mysql-test/t/rpl_auto_increment.test:
  Test for BUG#20524 "auto_increment_* not observed when inserting
  a too large value".
  We also check the pathological case (table t2) where it's impossible to
  "round down".
  The fixer of BUG#20573 will be able to use table t2 for testing his fix.
sql/handler.cc:
  If handler::update_auto_increment() generates a value larger than the field's
  max possible value, we used to simply insert this max possible value
  (after pushing a warning). Now we "round down" this max possible value to
  honour auto_increment_* variables (if at all possible), before trying the
  insertion.

1 files changed, 51 insertions, 2 deletions

diff --git a/sql/handler.cc b/sql/handler.cc
index b40934ea194..9259bc85aa0 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -1472,6 +1472,46 @@ next_insert_id(ulonglong nr,struct system_variables *variables)
 
 
 /*
+  Computes the largest number X:
+  - smaller than or equal to "nr"
+  - of the form: auto_increment_offset + N * auto_increment_increment
+  where N>=0.
+
+  SYNOPSIS
+    prev_insert_id
+      nr            Number to "round down"
+      variables     variables struct containing auto_increment_increment and
+                    auto_increment_offset
+
+  RETURN
+    The number X if it exists, "nr" otherwise.
+*/
+
+inline ulonglong
+prev_insert_id(ulonglong nr, struct system_variables *variables)
+{
+  if (unlikely(nr < variables->auto_increment_offset))
+  {
+    /*
+      There's nothing good we can do here. That is a pathological case, where
+      the offset is larger than the column's max possible value, i.e. not even
+      the first sequence value may be inserted. User will receive warning.
+    */
+    DBUG_PRINT("info",("auto_increment: nr: %lu cannot honour "
+                       "auto_increment_offset: %lu",
+                       nr, variables->auto_increment_offset));
+    return nr;
+  }
+  if (variables->auto_increment_increment == 1)
+    return nr; // optimization of the formula below
+  nr= (((nr - variables->auto_increment_offset)) /
+       (ulonglong) variables->auto_increment_increment);
+  return (nr * (ulonglong) variables->auto_increment_increment +
+          variables->auto_increment_offset);
+}
+
+
+/*
   Update the auto_increment field if necessary
 
   SYNOPSIS
@@ -1580,10 +1620,19 @@ bool handler::update_auto_increment()
   /* Mark that we should clear next_insert_id before next stmt */
   thd->clear_next_insert_id= 1;
 
-  if (!table->next_number_field->store((longlong) nr, TRUE))
+  if (likely(!table->next_number_field->store((longlong) nr, TRUE)))
     thd->insert_id((ulonglong) nr);
   else
-    thd->insert_id(table->next_number_field->val_int());
+  {
+    /*
+      overflow of the field; we'll use the max value, however we try to
+      decrease it to honour auto_increment_* variables:
+    */
+    nr= prev_insert_id(table->next_number_field->val_int(), variables);
+    thd->insert_id(nr);
+    if (unlikely(table->next_number_field->store((longlong) nr, TRUE)))
+      thd->insert_id(nr= table->next_number_field->val_int());
+  }
 
   /*
     We can't set next_insert_id if the auto-increment key is not the