MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:guilhem Date:June 20 2006 2:57pm
Subject:bk commit into 5.0 tree (guilhem:1.2191) BUG#20524
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of guilhem. When guilhem does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2191 06/06/20 16:57:42 guilhem@stripped +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, so now we "round down" this max possible value
  to honour auto_increment_* variables, before inserting it.

  sql/handler.cc
    1.213 06/06/20 16:57:37 guilhem@stripped +51 -2
    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, before trying the insertion.

  mysql-test/t/rpl_auto_increment.test
    1.3 06/06/20 16:57:37 guilhem@stripped +22 -0
    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.

  mysql-test/r/rpl_auto_increment.result
    1.5 06/06/20 16:57:37 guilhem@stripped +31 -0
    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.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	guilhem
# Host:	gbichot3.local
# Root:	/home/mysql_src/mysql-5.0-20524

--- 1.212/sql/handler.cc	2006-04-13 09:25:52 +02:00
+++ 1.213/sql/handler.cc	2006-06-20 16:57:37 +02:00
@@ -1472,6 +1472,46 @@
 
 
 /*
+  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 @@
   /* 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

--- 1.4/mysql-test/r/rpl_auto_increment.result	2004-11-21 15:42:49 +01:00
+++ 1.5/mysql-test/r/rpl_auto_increment.result	2006-06-20 16:57:37 +02:00
@@ -183,3 +183,34 @@
 32
 42
 drop table t1;
+create table t1 (a tinyint not null auto_increment primary key) engine=myisam;
+insert into t1 values(103);
+set auto_increment_increment=11;
+set auto_increment_offset=4;
+insert into t1 values(null);
+insert into t1 values(null);
+insert into t1 values(null);
+ERROR 23000: Duplicate entry '125' for key 1
+select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a;
+a	mod(a-@@auto_increment_offset,@@auto_increment_increment)
+103	0
+114	0
+125	0
+create table t2 like t1;
+set auto_increment_increment=1000;
+set auto_increment_offset=700;
+insert into t2 values(null);
+Warnings:
+Warning	1264	Out of range value adjusted for column 'a' at row 1
+select * from t2;
+a
+127
+select a from t1 order by a;
+a
+103
+114
+125
+select * from t2;
+a
+127
+drop table t1,t2;

--- 1.2/mysql-test/t/rpl_auto_increment.test	2004-11-21 15:42:49 +01:00
+++ 1.3/mysql-test/t/rpl_auto_increment.test	2006-06-20 16:57:37 +02:00
@@ -99,6 +99,28 @@
 connection master;
 
 drop table t1;
+create table t1 (a tinyint not null auto_increment primary key) engine=myisam;
+insert into t1 values(103);
+set auto_increment_increment=11;
+set auto_increment_offset=4;
+insert into t1 values(null);
+insert into t1 values(null);
+--error 1062
+insert into t1 values(null);
+select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a;
+# An offset so big that even first value does not fit
+create table t2 like t1;
+set auto_increment_increment=1000;
+set auto_increment_offset=700;
+insert into t2 values(null);
+select * from t2;
+sync_slave_with_master;
+select a from t1 order by a;
+select * from t2;
+
+connection master;
+
+drop table t1,t2;
 
 # End cleanup
 sync_slave_with_master;
Thread
bk commit into 5.0 tree (guilhem:1.2191) BUG#20524guilhem20 Jun