List:Commits« Previous MessageNext Message »
From:guilhem Date:June 22 2006 8:27pm
Subject:bk commit into 5.1 tree (guilhem:1.2178) BUG#20188
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 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.2178 06/06/22 22:27:36 guilhem@stripped +33 -0
  [3rd version] WL#3146 "less locking in auto_increment":
  this is a cleanup patch for our current auto_increment handling:
  new names for auto_increment variables in THD, new methods to manipulate them
  (see sql_class.h).
  This makes the logic hopefully clearer, less work is is needed in
  mysql_insert().
  This fixes a bunch of bugs, which someone may want to fix in 5.0 too:
  BUG#20339 "stored procedure using LAST_INSERT_ID() does not replicate
  statement-based"
  BUG#6880 "LAST_INSERT_ID() value changes during multi-row INSERT"
  (already fixed differently by Ramil in 4.1)
  BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY UPDATE"
  (now if a row is updated, LAST_INSERT_ID() will return its id)
  BUG#20392 "INSERT_ID session variable has weird value"
  (just fixed by Bar in 5.0)
  BUG#20188 "REPLACE in auto_increment column gives different rows on slave"
  (will be fixed by me in 5.0).
  Test of documented behaviour of mysql_insert_id() (there was no test).
  This is a patch after Monty's first review.
  The behaviour changes introduced are:
  - LAST_INSERT_ID() now returns "the first autogenerated auto_increment value
  successfully inserted", instead of "the first autogenerated auto_increment
  value if any row was successfully inserted", see auto_increment.test.
  Same for mysql_insert_id(), see mysql_client_test.c.
  - LAST_INSERT_ID() returns the id of the updated row if ON DUPLICATE KEY
  UPDATE, see auto_increment.test. Same for mysql_insert_id(), see
  mysql_client_test.c.
  - LAST_INSERT_ID() does not change if no autogenerated value was successfully 
  inserted (it used to then be 0), see auto_increment.test.
  - if in INSERT SELECT no autogenerated value was successfully inserted,
  mysql_insert_id() now returns the id of the last inserted row (it already
  did this for INSERT VALUES), see mysql_client_test.c.
  - if INSERT SELECT uses LAST_INSERT_ID(X), mysql_insert_id() now returns X
  (it already did this for INSERT VALUES), see mysql_client_test.c.
  - NDB now behaves like other engines wrt SET INSERT_ID and INSERT IGNORE:
  the id passed in SET INSERT_ID is re-used until a row succeeds.

  tests/mysql_client_test.c
    1.191 06/06/22 22:27:30 guilhem@stripped +188 -1
    tests of behaviour of mysql_insert_id(): there were no such tests, while in
    our manual we document its behaviour. In comments you'll notice the behaviour
    changes introduced (there are 5).

  sql/sql_update.cc
    1.192 06/06/22 22:27:30 guilhem@stripped +16 -6
    update for new variable names.
    Even though this is UPDATE, an insert id can be generated (by
    LAST_INSERT_ID(X)) and should be recorded because mysql_insert_id() wants
    to know about it.

  sql/sql_table.cc
    1.341 06/06/22 22:27:30 guilhem@stripped +3 -4
    next_insert_id not needed in mysql_alter_table().
    restore_auto_increment() is removed and replace by a simple
    "save next_insert_id before inserting the row, restore it if the insertion
    failed".

  sql/sql_show.cc
    1.343 06/06/22 22:27:30 guilhem@stripped +4 -3
    fix for compiler warning (about a string constant implicitely cast to char*);
    when I merge I'll use the recently introduced macro C_STRING_WITH_LEN instead

  sql/sql_select.cc
    1.410 06/06/22 22:27:29 guilhem@stripped +7 -3
    update for new variable names

  sql/sql_parse.cc
    1.555 06/06/22 22:27:29 guilhem@stripped +9 -4
    update to new variable names.
    Assertion that reset_thd_for_next_command() is not called for every
    substatement of a routine (I'm not against it, but if we do this change,
    statement-based binlogging needs some adjustments).

  sql/sql_load.cc
    1.100 06/06/22 22:27:29 guilhem@stripped +0 -20
    no need to fiddle with "id", THD maintains
    THD::first_successful_insert_id_in_cur_stmt by itself and correctly now.

  sql/sql_insert.cc
    1.204 06/06/22 22:27:29 guilhem@stripped +74 -55
    the "id" variable is not changed for each row now; it used to compensate for
    this contradiction:
    - thd->last_insert_id supposed job was to keep the id of the first row
    - but it was updated for every row
    - so mysql_insert() made sure to catch its first value and restore it at the end of stmt.
    Now THD keeps the first value in first_successful_insert_id_in_cur_stmt,
    and value of the row in insert_id_for_cur_row. So "id" only serves to fill
    mysql_insert_id(), as depending on some conditions, "id" must be different
    values.
    Prev_insert_id moves from THD to write_record().
    We fix BUG#20188 "REPLACE in auto_increment column gives different rows on
    slave" but it will be fixed by me in 5.0 too (patch pending) so I'll remove
    it from this changeset.
    We now set LAST_INSERT_ID() in ON DUPLICATE KEY UPDATE too (BUG#19243).

  sql/sql_class.h
    1.296 06/06/22 22:27:29 guilhem@stripped +141 -35
    new variables and methods for auto_increment, with hopefully better names:
    next_insert_id, clear_next_insert_id are unchanged
    last_insert_id becomes four: first_successful_insert_id_in_cur_stmt,
    first_insert_id_in_cur_stmt_for_binlog, insert_id_for_cur_row,
    first_successful_insert_id_in_prev_stmt.
    current_insert_id becomes: first_successful_insert_id_in_prev_stmt_for_binlog
    prev_insert_id is removed, write_record() manages it locally
    last_insert_id_used becomes: stmt_depends_on_first_row_in_prev_stmt
    insert_id_used is removed (equivalent to insert_id_for_cur_row > 0)
    Comments to explain each of them.
    select_insert::last_insert_id becomes autoinc_value_of_last_inserted_row.

  sql/sql_class.cc
    1.264 06/06/22 22:27:29 guilhem@stripped +87 -9
    new variables for insert_id. In THD::cleanup_after_query() we fix BUG#20339
    "stored procedure using LAST_INSERT_ID() does not replicate statement-based"
    (will we want to fix it in 5.0?). Many comments about what stored functions
    do to auto_increment.

  sql/set_var.cc
    1.179 06/06/22 22:27:29 guilhem@stripped +12 -4
    new variable names. The last change fixes BUG#20392 "INSERT_ID session
    variable has weird value" (but Bar just fixed it in 5.0)

  sql/log_event.cc
    1.225 06/06/22 22:27:29 guilhem@stripped +20 -4
    new variable names, comments. Preparing for when master's won't binlog
    LAST_INSERT_ID if it was 0.

  sql/log.cc
    1.212 06/06/22 22:27:29 guilhem@stripped +22 -21
    new variable names for insert_ids. Removing some unused variables in the slow
    log.

  sql/item_func.cc
    1.295 06/06/22 22:27:29 guilhem@stripped +10 -3
    new names for variables

  sql/handler.h
    1.215 06/06/22 22:27:29 guilhem@stripped +0 -2
    restore_auto_increment unneeded.
    handler::auto_increment_changed can be replaced by (THD::insert_id_for_cur_row > 0).

  sql/handler.cc
    1.237 06/06/22 22:27:29 guilhem@stripped +39 -61
    restore_auto_increment() not needed.
    More comments, use of new methods and variables. Hopes to be clearer than current code.
    thd->prev_insert_id not in THD anymore (handled locally by write_record()).

  sql/ha_partition.h
    1.19 06/06/22 22:27:29 guilhem@stripped +0 -7
    unneeded

  sql/ha_partition.cc
    1.54 06/06/22 22:27:28 guilhem@stripped +0 -8
    unneeded

  sql/ha_ndbcluster.cc
    1.316 06/06/22 22:27:28 guilhem@stripped +1 -1
    handler::auto_increment_column_changed not needed, equivalent to (thd->insert_id_for_cur_row > 0).

  sql/ha_federated.cc
    1.62 06/06/22 22:27:28 guilhem@stripped +1 -1
    update for new variables.

  mysql-test/t/auto_increment.test
    1.27 06/06/22 22:27:28 guilhem@stripped +71 -0
    Importing Ramil's tescase for BUG#6880 to see I don't re-introduce the bug;
    will be removed and merged instead. 
    A testcase of BUG#19243: if ON DUPLICATE KEY UPDATE updates a row,
    LAST_INSERT_ID() now returns the id of the row.
    Testcase for BUG#20392 (will be removed and merged from Bar's cset instead).
    Test of new behaviour of last_insert_id() when no autogenerated value was
    inserted, or when only some autogenerated value (not the first of them) was
    inserted

  mysql-test/r/rpl_stm_log.result
    1.69 06/06/22 22:27:28 guilhem@stripped +17 -0
    result update

  mysql-test/r/rpl_row_log_innodb.result
    1.9 06/06/22 22:27:28 guilhem@stripped +16 -0
    result update

  mysql-test/r/rpl_row_log.result
    1.9 06/06/22 22:27:28 guilhem@stripped +16 -0
    result update

  mysql-test/r/rpl_ndb_log.result
    1.10 06/06/22 22:27:28 guilhem@stripped +16 -0
    result update

  mysql-test/r/rpl_ndb_auto_inc.result
    1.3 06/06/22 22:27:28 guilhem@stripped +0 -4
    ndb's behaviour is now like other engines wrt SET INSERT_ID and INSERT
    IGNORE: the id passed in SET INSERT_ID is re-used until a row succeeds.

  mysql-test/r/rpl_insert_id.result
    1.16 06/06/22 22:27:28 guilhem@stripped +24 -0
    result update

  mysql-test/r/binlog_stm_binlog.result
    1.11 06/06/22 22:27:28 guilhem@stripped +18 -0
    result update

  mysql-test/r/binlog_row_binlog.result
    1.4 06/06/22 22:27:28 guilhem@stripped +20 -0
    result update

  mysql-test/r/auto_increment.result
    1.42 06/06/22 22:27:28 guilhem@stripped +92 -2
    behaviour change: when INSERT totally fails (not even succeeds partially and
    then rolls back), don't change last_insert_id().

  mysql-test/extra/rpl_tests/rpl_log.test
    1.38 06/06/22 22:27:28 guilhem@stripped +16 -0
    Manually importing Ramil's testcase for BUG#6880 to check that my patch
    also fixes the bug.
    Will be removed and merged from Ramil's.

  mysql-test/extra/rpl_tests/rpl_insert_id.test
    1.7 06/06/22 22:27:28 guilhem@stripped +18 -0
    testcase to make sure we fix BUG#20339 "stored procedure using
    LAST_INSERT_ID() does not replicate statement-based" (some may want to fix
    it in 5.0)

  mysql-test/extra/binlog_tests/binlog.test
    1.11 06/06/22 22:27:28 guilhem@stripped +17 -0
    testing binlogging of INSERT_ID with INSERT DELAYED, to be sure that 
    we binlog an INSERT_ID event only for the delayed rows which use one.

# 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.1-autoinc-cleanup3

--- 1.236/sql/handler.cc	2006-06-02 22:22:50 +02:00
+++ 1.237/sql/handler.cc	2006-06-22 22:27:29 +02:00
@@ -1572,7 +1572,10 @@
 }
 
 /*
-  Generate the next auto-increment number based on increment and offset
+  Generate the next auto-increment number based on increment and offset:
+  computes the lowest number
+  - strictly greater than "nr"
+  - of the form: auto_increment_offset + N * auto_increment_increment
 
   In most cases increment= offset= 1, in which case we get:
   1,2,3,4,5,...
@@ -1583,6 +1586,8 @@
 inline ulonglong
 next_insert_id(ulonglong nr,struct system_variables *variables)
 {
+  if (variables->auto_increment_increment == 1)
+    return (nr+1); // optimization of the formula below
   nr= (((nr+ variables->auto_increment_increment -
          variables->auto_increment_offset)) /
        (ulonglong) variables->auto_increment_increment);
@@ -1643,6 +1648,11 @@
 
     thd->next_insert_id is cleared after it's been used for a statement.
 
+    This function's "outputs" are: the table's auto_increment field is filled
+    with a value, thd->next_insert_id is filled with the value to use for the
+    next row, if a value was autogenerated for the current row it is stored in
+    thd->insert_id_for_cur_row.
+
    TODO
 
     Replace all references to "next number" or NEXT_NUMBER to
@@ -1665,11 +1675,6 @@
   bool result= 0;
   DBUG_ENTER("handler::update_auto_increment");
 
-  /*
-    We must save the previous value to be able to restore it if the
-    row was not inserted
-  */
-  thd->prev_insert_id= thd->next_insert_id;
   auto_increment_field_not_null= table->auto_increment_field_not_null;
   table->auto_increment_field_not_null= FALSE; // to reset for next row
 
@@ -1678,26 +1683,14 @@
       thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO)
   {
     /*
-      The user did specify a value for the auto_inc column, we don't generate
-      a new value, write it down.
-    */
-    auto_increment_column_changed=0;
-
-    /*
       Update next_insert_id if we had already generated a value in this
       statement (case of INSERT VALUES(null),(3763),(null):
       the last NULL needs to insert 3764, not the value of the first NULL plus
       1).
     */
     if (thd->clear_next_insert_id && nr >= thd->next_insert_id)
-    {
-      if (variables->auto_increment_increment != 1)
-        nr= next_insert_id(nr, variables);
-      else
-        nr++;
-      thd->next_insert_id= nr;
-      DBUG_PRINT("info",("next_insert_id: %lu", (ulong) nr));
-    }
+      thd->set_next_insert_id(next_insert_id(nr, variables));
+    thd->record_insert_id_for_cur_row_is_zero(); // didn't generate anything
     DBUG_RETURN(0);
   }
   if (!(nr= thd->next_insert_id))
@@ -1746,63 +1739,48 @@
       result= 1;                                // Mark failure
 
     /*
-      That should not be needed when engines actually use offset and increment
-      above.
-    */
-    if (variables->auto_increment_increment != 1)
-      nr= next_insert_id(nr-1, variables);
-    /*
-      Update next row based on the found value. This way we don't have to
-      call the handler for every generated auto-increment value on a
-      multi-row statement
+      That rounding below should not be needed when engines actually use
+      offset and increment in get_auto_increment():
     */
-    thd->next_insert_id= nr;
+    nr= next_insert_id(nr-1, variables);
+    /* instead of this rounding above we should just have: */
+    DBUG_ASSERT(((nr - variables->auto_increment_offset) %
+                 variables->auto_increment_increment) == 0 || result);
   }
 
   DBUG_PRINT("info",("auto_increment: %lu", (ulong) nr));
 
-  /* 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))
-    thd->insert_id((ulonglong) nr);
-  else
-    thd->insert_id(table->next_number_field->val_int());
-
+  if (unlikely(table->next_number_field->store((longlong) nr, TRUE)))
+  {
+    /*
+      field refused this value (overflow) and truncated it, use the result of
+      the truncation (which is going to be inserted).
+    */
+    nr= table->next_number_field->val_int();
+  }
   /*
-    We can't set next_insert_id if the auto-increment key is not the
-    first key part, as there is no guarantee that the first parts will be in
-    sequence
+    Record this autogenerated value. If the caller then
+    succeeds to insert this value, it will call
+    insert_id_insertion_succeeded_in_cur_stmt()
+    which will set insert_id_of_first_row_in_cur_stmt_was if it's not
+    already set.
   */
+  thd->record_insert_id_for_cur_row((ulonglong) nr);
+
   if (!table->s->next_number_key_offset)
   {
     /*
       Set next insert id to point to next auto-increment value to be able to
-      handle multi-row statements
-      This works even if auto_increment_increment > 1
+      handle multi-row statements.
     */
-    thd->next_insert_id= next_insert_id(nr, variables);
+    thd->set_next_insert_id(next_insert_id(nr, variables));
   }
   else
+  {
+    DBUG_PRINT("info",("auto_increment: not-first-in-index"));
     thd->next_insert_id= 0;
-
-  /* Mark that we generated a new value */
-  auto_increment_column_changed=1;
+  }
   DBUG_RETURN(result);
-}
-
-/*
-  restore_auto_increment
-
-  In case of error on write, we restore the last used next_insert_id value
-  because the previous value was not used.
-*/
-
-void handler::restore_auto_increment()
-{
-  THD *thd= table->in_use;
-  if (thd->next_insert_id)
-    thd->next_insert_id= thd->prev_insert_id;
 }
 
 

--- 1.214/sql/handler.h	2006-06-02 22:22:50 +02:00
+++ 1.215/sql/handler.h	2006-06-22 22:27:29 +02:00
@@ -877,7 +877,6 @@
   uint block_size;			/* index block size */
   FT_INFO *ft_handler;
   enum {NONE=0, INDEX, RND} inited;
-  bool  auto_increment_column_changed;
   bool implicit_emptied;                /* Can be !=0 only if HEAP */
   const COND *pushed_cond;
   MY_BITMAP *read_set;
@@ -1310,7 +1309,6 @@
                                   ulonglong *first_value,
                                   ulonglong *nb_reserved_values);
   virtual void release_auto_increment() { return; };
-  virtual void restore_auto_increment();
 
   /*
     Reset the auto-increment counter to the given value, i.e. the next row

--- 1.294/sql/item_func.cc	2006-05-20 04:00:55 +02:00
+++ 1.295/sql/item_func.cc	2006-06-22 22:27:29 +02:00
@@ -3293,12 +3293,19 @@
   if (arg_count)
   {
     longlong value= args[0]->val_int();
-    thd->insert_id(value);
     null_value= args[0]->null_value;
-    return value;                       // Avoid side effect of insert_id()
+    /*
+      LAST_INSERT_ID(X) must affect the client's mysql_insert_id() (as
+      documented), the only simple way to do it is to pretend that X is the
+      generated id for the current row (however, later settings of this id in
+      handler::update_auto_increment() take precedence).
+    */
+    thd->record_insert_id_for_cur_row_no_binlog(value);
+    thd->first_successful_insert_id_in_prev_stmt= value;
+    return value;
   }
   thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
-  return thd->insert_id();
+  return thd->read_first_successful_insert_id_in_prev_stmt();
 }
 
 /* This function is just used to test speed of different functions */

--- 1.211/sql/log.cc	2006-06-02 22:22:51 +02:00
+++ 1.212/sql/log.cc	2006-06-22 22:27:29 +02:00
@@ -416,16 +416,21 @@
     table->field[6]->set_notnull();
   }
 
-  if (thd->last_insert_id_used)
+  if (thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt)
   {
-    table->field[7]->store((longlong) thd->current_insert_id);
+    table->field[7]->store((longlong) thd->first_successful_insert_id_in_prev_stmt_for_binlog);
     table->field[7]->set_notnull();
   }
 
-  /* set value if we do an insert on autoincrement column */
-  if (thd->insert_id_used)
+  /*
+    Set value if we do an insert on autoincrement column. Note that for
+    some engines (those for which get_auto_increment() does not leave a
+    table lock until the statement ends), this is just the first value and
+    the next ones used may not be contiguous to it.
+  */
+  if (thd->first_insert_id_in_cur_stmt_for_binlog > 0)
   {
-    table->field[8]->store((longlong) thd->last_insert_id);
+    table->field[8]->store((longlong) thd->first_insert_id_in_cur_stmt_for_binlog);
     table->field[8]->set_notnull();
   }
 
@@ -710,7 +715,6 @@
   Security_context *sctx= thd->security_ctx;
   uint message_buff_len= 0, user_host_len= 0;
   longlong query_time= 0, lock_time= 0;
-  longlong last_insert_id= 0, insert_id= 0;
 
   /*
     Print the message to the buffer if we have slow log enabled
@@ -740,13 +744,6 @@
       lock_time= (longlong) (thd->time_after_lock - query_start_arg);
     }
 
-    if (thd->last_insert_id_used)
-      last_insert_id= (longlong) thd->current_insert_id;
-
-    /* set value if we do an insert on autoincrement column */
-    if (thd->insert_id_used)
-      insert_id= (longlong) thd->last_insert_id;
-
     if (!query)
     {
       is_command= TRUE;
@@ -2906,21 +2903,22 @@
     {
       if (!thd->current_stmt_binlog_row_based)
       {
-        if (thd->last_insert_id_used)
+        if (thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt)
         {
           Intvar_log_event e(thd,(uchar) LAST_INSERT_ID_EVENT,
-                             thd->current_insert_id);
+                             thd->first_successful_insert_id_in_prev_stmt_for_binlog);
           if (e.write(file))
             goto err;
         }
-        if (thd->insert_id_used)
+        if (thd->first_insert_id_in_cur_stmt_for_binlog > 0)
         {
           /*
             If the auto_increment was second in a table's index (possible with
             MyISAM or BDB) (table->next_number_key_offset != 0), such event is
             in fact not necessary. We could avoid logging it.
           */
-          Intvar_log_event e(thd,(uchar) INSERT_ID_EVENT,thd->last_insert_id);
+          Intvar_log_event e(thd,(uchar) INSERT_ID_EVENT,
+                             thd->first_insert_id_in_cur_stmt_for_binlog);
           if (e.write(file))
             goto err;
         }
@@ -3247,18 +3245,21 @@
         tmp_errno=errno;
       strmov(db,thd->db);
     }
-    if (thd->last_insert_id_used)
+    if (thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt)
     {
       end=strmov(end,",last_insert_id=");
-      end=longlong10_to_str((longlong) thd->current_insert_id,end,-10);
+      end=longlong10_to_str((longlong)
+                            thd->first_successful_insert_id_in_prev_stmt_for_binlog,
+                            end, -10);
     }
     // Save value if we do an insert.
-    if (thd->insert_id_used)
+    if (thd->first_insert_id_in_cur_stmt_for_binlog > 0)
     {
       if (!(specialflag & SPECIAL_SHORT_LOG_FORMAT))
       {
         end=strmov(end,",insert_id=");
-        end=longlong10_to_str((longlong) thd->last_insert_id,end,-10);
+        end=longlong10_to_str((longlong)
+                              thd->first_insert_id_in_cur_stmt_for_binlog, end,-10);
       }
     }
 

--- 1.224/sql/log_event.cc	2006-06-02 22:22:51 +02:00
+++ 1.225/sql/log_event.cc	2006-06-22 22:27:29 +02:00
@@ -1919,7 +1919,17 @@
   thd->query= 0;			// just to be sure
   thd->query_length= thd->db_length =0;
   VOID(pthread_mutex_unlock(&LOCK_thread_count));
-  close_thread_tables(thd);      
+  close_thread_tables(thd);
+  /*
+    As a disk space optimization, future masters will not log an event for
+    LAST_INSERT_ID() if that function returned 0 (and thus they will be able
+    to replace the THD::stmt_depends_on_first_successful_insert_id_in_prev_stmt
+    variable by (THD->first_successful_insert_id_in_prev_stmt > 0) ; with the
+    resetting below we are ready to support that.
+  */
+  thd->first_successful_insert_id_in_prev_stmt_for_binlog= 0;
+  thd->first_successful_insert_id_in_prev_stmt= 0;
+  thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt= 0;
   free_root(thd->mem_root,MYF(MY_KEEP_PREALLOC));
   /*
     If there was an error we stop. Otherwise we increment positions. Note that
@@ -3398,11 +3408,17 @@
 {
   switch (type) {
   case LAST_INSERT_ID_EVENT:
-    thd->last_insert_id_used = 1;
-    thd->last_insert_id = val;
+    thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt = 1;
+    thd->first_successful_insert_id_in_prev_stmt= val;
     break;
   case INSERT_ID_EVENT:
-    thd->next_insert_id = val;
+    /*
+      don't set clear_next_insert_id, because it's not really autogenerated,
+      i.e. handler::update_auto_increment() is not allowed to bump it
+      silently. TODO: find a better name for clear_next_insert_id, to
+      account for this hidden meaning.
+    */
+    thd->next_insert_id= val;
     break;
   }
   rli->inc_event_relay_log_pos();

--- 1.263/sql/sql_class.cc	2006-06-01 22:32:24 +02:00
+++ 1.264/sql/sql_class.cc	2006-06-22 22:27:29 +02:00
@@ -208,9 +208,14 @@
 #endif /*HAVE_ROW_BASED_REPLICATION*/
    global_read_lock(0), is_fatal_error(0),
    rand_used(0), time_zone_used(0),
-   last_insert_id_used(0), insert_id_used(0), clear_next_insert_id(0),
+   next_insert_id(0),
+   first_successful_insert_id_in_prev_stmt(0),
+   first_successful_insert_id_in_prev_stmt_for_binlog(0),
+   first_successful_insert_id_in_cur_stmt(0),
+   insert_id_for_cur_row(0), first_insert_id_in_cur_stmt_for_binlog(0),
    in_lock_tables(0), bootstrap(0), derived_tables_processing(FALSE),
-   spcont(NULL)
+   stmt_depends_on_first_successful_insert_id_in_prev_stmt(FALSE),
+   clear_next_insert_id(FALSE), spcont(NULL)
 {
   stmt_arena= this;
   thread_stack= 0;
@@ -224,7 +229,6 @@
   killed= NOT_KILLED;
   db_length= col_access=0;
   query_error= tmp_table_used= 0;
-  next_insert_id=last_insert_id=0;
   hash_clear(&handler_tables_hash);
   tmp_table=0;
   used_tables=0;
@@ -602,6 +606,26 @@
 
 void THD::cleanup_after_query()
 {
+  /*
+    If in stored function or trigger, where statement-based binlogging logs
+    only the caller, the insert_id/last_insert_id stored in binlog must
+    describe their first values inside the routine or caller (the values when
+    they were first set). Otherwise (e.g. stored procedure) it must describe
+    their values for the current substatement.
+  */
+  if (!prelocked_mode)
+  {
+    stmt_depends_on_first_successful_insert_id_in_prev_stmt= 0;
+    first_insert_id_in_cur_stmt_for_binlog= 0;
+  }
+  if (first_successful_insert_id_in_cur_stmt > 0 )
+  {
+    /* set what LAST_INSERT_ID() will return */
+    first_successful_insert_id_in_prev_stmt= 
+      first_successful_insert_id_in_cur_stmt;
+    first_successful_insert_id_in_cur_stmt= 0;
+  }
+  insert_id_for_cur_row= 0;
   if (clear_next_insert_id)
   {
     clear_next_insert_id= 0;
@@ -2118,9 +2142,49 @@
   backup->in_sub_stmt=     in_sub_stmt;
   backup->no_send_ok=      net.no_send_ok;
   backup->enable_slow_log= enable_slow_log;
-  backup->last_insert_id=  last_insert_id;
+
+  /*
+    TODO: these are many comments only for insert_id, they clutter this
+    place, move the part about insert_id into a specific function.
+    Maybe even have a class grouping all insert_id vars.
+  */
+
+  /*
+    We may have cached an autoinc generated value to store in the row, but now
+    we're going into a substatement which may update a different table, so
+    we'll save the value of next_insert_id so that we can use this value again
+    when we exit the substatement and come back to the first table.
+    Same logic for clear_next_insert_id which goes with next_insert_id.
+  */
   backup->next_insert_id=  next_insert_id;
-  backup->insert_id_used=  insert_id_used;
+  /*
+    Substatements should not influence what caller considers as previous
+    statement, or as current statement:
+  */
+  backup->first_successful_insert_id_in_prev_stmt= 
+    first_successful_insert_id_in_prev_stmt;
+  backup->first_successful_insert_id_in_cur_stmt= 
+    first_successful_insert_id_in_cur_stmt;
+  backup->insert_id_for_cur_row= insert_id_for_cur_row;
+  /*
+    There are some bugs in statement-based replication of stored
+    functions/triggers with auto_increment (BUG#19630, BUG#20341) which make
+    such replication break. However, by backing up and restoring the following
+    variable, which is logically correct, we make those bugs less frequent.
+    Here's how: it will help if
+    all tables involved in the stored function have the same auto_increment
+    internal counter on master and slave. Assume then a INSERT like this:
+    "INSERT INTO t1 SELECT myfunc()"
+    where myfunc() is a stored function which inserts into t2. t1 and t2 have
+    auto_increment columns.
+    10 is inserted into t1 and then 6 is inserted into t2. If we didn't
+    restore the variable below, it would be 6 at the end of the function, 6
+    would go into binlog. Then slave would set next_insert_id to 6 before
+    starting the INSERT, and pick this 6 as the value to insert into t1.
+    With the backup below, the binlog will contain 10.
+  */
+  backup->first_insert_id_in_cur_stmt_for_binlog= 
+    first_insert_id_in_cur_stmt_for_binlog;
   backup->clear_next_insert_id= clear_next_insert_id;
   backup->limit_found_rows= limit_found_rows;
   backup->examined_row_count= examined_row_count;
@@ -2135,8 +2199,16 @@
   /* Disable result sets */
   client_capabilities &= ~CLIENT_MULTI_RESULTS;
   in_sub_stmt|= new_state;
+  /*
+    In this sub statement, we may be updating a different table from the one
+    we ran get_auto_increment() on, so we must forget thd->next_insert_id.
+    OTOH this very much cancels SET INSERT_ID (BUG#20341) :(
+  */
   next_insert_id= 0;
-  insert_id_used= 0;
+  first_successful_insert_id_in_cur_stmt= 0;
+  insert_id_for_cur_row= 0;
+  clear_next_insert_id= 0;
+
   examined_row_count= 0;
   sent_row_count= 0;
   cuted_fields= 0;
@@ -2168,9 +2240,15 @@
   in_sub_stmt=      backup->in_sub_stmt;
   net.no_send_ok=   backup->no_send_ok;
   enable_slow_log=  backup->enable_slow_log;
-  last_insert_id=   backup->last_insert_id;
-  next_insert_id=   backup->next_insert_id;
-  insert_id_used=   backup->insert_id_used;
+
+  next_insert_id= backup->next_insert_id;
+  first_successful_insert_id_in_prev_stmt= 
+    backup->first_successful_insert_id_in_prev_stmt;
+  first_successful_insert_id_in_cur_stmt= 
+    backup->first_successful_insert_id_in_cur_stmt;
+  insert_id_for_cur_row= backup->insert_id_for_cur_row;
+  first_insert_id_in_cur_stmt_for_binlog= 
+  backup->first_insert_id_in_cur_stmt_for_binlog;
   clear_next_insert_id= backup->clear_next_insert_id;
   limit_found_rows= backup->limit_found_rows;
   sent_row_count=   backup->sent_row_count;

--- 1.295/sql/sql_class.h	2006-05-23 20:26:11 +02:00
+++ 1.296/sql/sql_class.h	2006-06-22 22:27:29 +02:00
@@ -761,12 +761,14 @@
 {
 public:
   ulonglong options;
-  ulonglong last_insert_id, next_insert_id;
+  ulonglong next_insert_id, first_successful_insert_id_in_prev_stmt;
+  ulonglong first_successful_insert_id_in_cur_stmt, insert_id_for_cur_row;
+  ulonglong first_insert_id_in_cur_stmt_for_binlog;
   ulonglong limit_found_rows;
   ha_rows    cuted_fields, sent_row_count, examined_row_count;
   ulong client_capabilities;
   uint in_sub_stmt;
-  bool enable_slow_log, insert_id_used, clear_next_insert_id;
+  bool enable_slow_log, clear_next_insert_id;
   my_bool no_send_ok;
   SAVEPOINT *savepoints;
 };
@@ -1057,23 +1059,141 @@
   */
   Query_arena *stmt_arena;
   /*
-    next_insert_id is set on SET INSERT_ID= #. This is used as the next
-    generated auto_increment value in handler.cc
+    ALL OVER THIS FILE, "insert_id" means "*automatically generated* value for
+    insertion into an auto_increment column".
+    next_insert_id is the next value which should be inserted into the
+    auto_increment column: in a inserting-multi-row statement (like INSERT
+    SELECT), for the first row where the autoinc value is not specified by the
+    statement, the handler is called and asked to generate a value,
+    next_insert_id is set to the next value, then for all other rows
+    next_insert_id is used (and increased each time) without calling the
+    handler.
+    In a slave replica, next_insert_id is set from the master's binlog to make
+    sure that the generated numbers are like those on master.
+    next_insert_id is also set by SET INSERT_ID=# (same logic as replication;
+    used by mysqlbinlog).
   */
   ulonglong  next_insert_id;
-  /* Remember last next_insert_id to reset it if something went wrong */
-  ulonglong  prev_insert_id;
   /*
-    The insert_id used for the last statement or set by SET LAST_INSERT_ID=#
-    or SELECT LAST_INSERT_ID(#).  Used for binary log and returned by
-    LAST_INSERT_ID()
-  */
-  ulonglong  last_insert_id;
-  /*
-    Set to the first value that LAST_INSERT_ID() returned for the last
-    statement.  When this is set, last_insert_id_used is set to true.
-  */
-  ulonglong  current_insert_id;
+    This is the first autogenerated insert id which was *successfully*
+    inserted by the previous statement (exactly, if the previous statement
+    didn't successfully insert an autogenerated insert id, then it's the one
+    of the statement before, etc).
+    It can also be set by SET LAST_INSERT_ID=# or SELECT LAST_INSERT_ID(#).
+    It is returned by LAST_INSERT_ID().
+  */
+  ulonglong  first_successful_insert_id_in_prev_stmt;
+  /*
+    Variant of the above, used for storing in statement-based binlog. The
+    difference is that the one above can change as the execution of a stored
+    function progresses, while the one below is set once and then does not
+    change (which is the value which statement-based binlog needs).
+  */
+  ulonglong  first_successful_insert_id_in_prev_stmt_for_binlog;
+  /*
+    This is the first autogenerated insert id which was *successfully*
+    inserted by the current statement. It is maintained only to set
+    first_successful_insert_id_in_prev_stmt when statement ends.
+  */
+  ulonglong  first_successful_insert_id_in_cur_stmt;
+  /*
+    insert id for the current row (again, *autogenerated*; if not
+    autogenerated, it's 0).
+    It is also set by LAST_INSERT_ID(X), to X, in this case it will be
+    superseded by handler::update_auto_increment() if that method is called
+    (i.e. auto_increment is stronger than LAST_INSERT_ID(X)).
+    At first successful insertion, this variable becomes
+    first_successful_insert_id_in_cur_stmt.
+  */
+  ulonglong  insert_id_for_cur_row;
+  /*
+    Variant of the above: is set to insert_id_for_cur_row the first time that
+    insert_id_for_cur_row is set.
+    Used for storage in the statement-based binlog: assuming a table with an
+    autoinc column, and this happens:
+    INSERT INTO ... VALUES(3);
+    SET INSERT_ID=3; INSERT IGNORE ... VALUES (NULL);
+    then the latter INSERT will insert no rows
+    (first_successful_insert_id_in_cur_stmt == 0), but storing "INSERT_ID=3" in
+    the binlog is still needed; first_insert_id_in_cur_stmt_for_binlog will
+    contain 3.
+  */
+  ulonglong first_insert_id_in_cur_stmt_for_binlog;
+  /*
+    We follow this logic:
+    - when stmt starts, first_successful_insert_id_in_prev_stmt contains the
+    first insert id successfully inserted by the previous stmt.
+    - as stmt makes progress, insert_id_for_cur_row changes; the first time it
+    becomes non-zero, first_insert_id_in_cur_stmt_for_binlog is set.
+    - at first successful insertion of an autogenerated value,
+    first_successful_insert_id_in_cur_stmt is set to insert_id_for_cur_row.
+    - when stmt goes to binlog, first_insert_id_in_cur_stmt_for_binlog is
+    binlogged if non-zero.
+    - when stmt ends, first_successful_insert_id_in_prev_stmt is set to
+    first_successful_insert_id_in_cur_stmt.
+  */
+  /*
+    stmt_depends_on_first_successful_insert_id_in_prev_stmt is set when
+    LAST_INSERT_ID() is used by a statement.
+    If it is set, first_successful_insert_id_in_prev_stmt_for_binlog will be
+    stored in the statement-based binlog.
+    This variable is CUMULATIVE along the execution of a stored function or
+    trigger: if one substatement sets it to 1 it will stay 1 until the
+    function/trigger ends, thus making sure that
+    first_successful_insert_id_in_prev_stmt_for_binlog does not change anymore
+    and is propagated to the caller for binlogging.
+  */
+  bool       stmt_depends_on_first_successful_insert_id_in_prev_stmt;
+  /*
+    Tells if at least one value has been autogenerated by the current
+    statement; it is not duplicate of
+    (first_insert_id_in_cur_stmt_for_binlog > 0) if in stored function.
+  */
+  bool       clear_next_insert_id;
+  inline void   record_insert_id_for_cur_row_no_binlog(ulonglong id_arg)
+  {
+    insert_id_for_cur_row= id_arg;
+  }
+  inline void   record_insert_id_for_cur_row(ulonglong id_arg)
+  {
+    /* Record the very first one, for binlog */
+    if (first_insert_id_in_cur_stmt_for_binlog == 0)
+      first_insert_id_in_cur_stmt_for_binlog= id_arg;
+    record_insert_id_for_cur_row_no_binlog(id_arg);
+  }
+  inline void   record_insert_id_for_cur_row_is_zero()
+  {
+    /* optimized version of the previous function when id_arg==0 */
+    insert_id_for_cur_row= 0;
+  }
+  inline void   record_first_successful_insert_id_in_cur_stmt()
+  {
+    if (first_successful_insert_id_in_cur_stmt == 0)
+      first_successful_insert_id_in_cur_stmt= insert_id_for_cur_row;
+  }
+  inline ulonglong read_first_successful_insert_id_in_prev_stmt(void)
+  {
+    if (!stmt_depends_on_first_successful_insert_id_in_prev_stmt)
+    {
+      /* It's the first time we read it */
+      first_successful_insert_id_in_prev_stmt_for_binlog=
+        first_successful_insert_id_in_prev_stmt;
+      stmt_depends_on_first_successful_insert_id_in_prev_stmt= 1;
+    }
+    return first_successful_insert_id_in_prev_stmt;
+  }
+  inline void set_next_insert_id(ulonglong next_id)
+  {
+    next_insert_id= next_id;
+    DBUG_PRINT("info",("auto_increment: next value %lu", (ulong)next_id));
+    /*
+      Mark that we should clear next_insert_id before next stmt (because next
+      statement needs to regenerate a new value, for example if it inserts into
+      a different table).
+    */
+    clear_next_insert_id= 1;
+  }
+
   ulonglong  limit_found_rows;
   ulonglong  options;           /* Bitmap of states */
   longlong   row_count_func;	/* For the ROW_COUNT() function */
@@ -1142,7 +1262,6 @@
   bool       last_cuted_field;
   bool	     no_errors, password, is_fatal_error;
   bool	     query_start_used, rand_used, time_zone_used;
-  bool	     last_insert_id_used,insert_id_used, clear_next_insert_id;
   bool	     in_lock_tables;
   bool       query_error, bootstrap, cleanup_done;
   bool	     tmp_table_used;
@@ -1170,9 +1289,10 @@
   /* Used by the sys_var class to store temporary values */
   union
   {
-    my_bool my_bool_value;
-    long    long_value;
-    ulong   ulong_value;
+    my_bool   my_bool_value;
+    long      long_value;
+    ulong     ulong_value;
+    ulonglong ulonglong_value;
   } sys_var_tmp;
   
   struct {
@@ -1273,20 +1393,6 @@
   inline void	end_time()    { time(&start_time); }
   inline void	set_time(time_t t) { time_after_lock=start_time=user_time=t; }
   inline void	lock_time()   { time(&time_after_lock); }
-  inline void	insert_id(ulonglong id_arg)
-  {
-    last_insert_id= id_arg;
-    insert_id_used=1;
-  }
-  inline ulonglong insert_id(void)
-  {
-    if (!last_insert_id_used)
-    {
-      last_insert_id_used=1;
-      current_insert_id=last_insert_id;
-    }
-    return last_insert_id;
-  }
   inline ulonglong found_rows(void)
   {
     return limit_found_rows;
@@ -1557,7 +1663,7 @@
   TABLE_LIST *table_list;
   TABLE *table;
   List<Item> *fields;
-  ulonglong last_insert_id;
+  ulonglong autoinc_value_of_last_inserted_row;
   COPY_INFO info;
   bool insert_into_view;
 

--- 1.203/sql/sql_insert.cc	2006-06-02 22:22:51 +02:00
+++ 1.204/sql/sql_insert.cc	2006-06-22 22:27:29 +02:00
@@ -411,7 +411,6 @@
   table->next_number_field=table->found_next_number_field;
 
   error=0;
-  id=0;
   thd->proc_info="update";
   if (duplic != DUP_ERROR || ignore)
     table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
@@ -517,16 +516,6 @@
     else
 #endif
       error=write_record(thd, table ,&info);
-    /*
-      If auto_increment values are used, save the first one
-       for LAST_INSERT_ID() and for the update log.
-       We can't use insert_id() as we don't want to touch the
-       last_insert_id_used flag.
-    */
-    if (! id && thd->insert_id_used)
-    {						// Get auto increment value
-      id= thd->last_insert_id;
-    }
     if (error)
       break;
     thd->row_count++;
@@ -544,7 +533,6 @@
   {
     if (!error)
     {
-      id=0;					// No auto_increment id
       info.copied=values_list.elements;
       end_delayed_insert(thd);
     }
@@ -558,11 +546,6 @@
       table->file->print_error(my_errno,MYF(0));
       error=1;
     }
-    if (id && values_list.elements != 1)
-      thd->insert_id(id);			// For update log
-    else if (table->next_number_field && info.copied)
-      id=table->next_number_field->val_int();	// Return auto_increment value
-
     transactional_table= table->file->has_transactions();
 
     if ((changed= (info.copied || info.deleted || info.updated)))
@@ -611,18 +594,25 @@
     }
   }
   thd->proc_info="end";
+  /*
+    We'll report to the client this id:
+    - if the table contains an autoincrement column and we successfully
+    inserted an autogenerated value, the autogenerated value.
+    - if the table contains no autoincrement column and LAST_INSERT_ID(X) was
+    called, X.
+    - if the table contains an autoincrement column, and some rows were
+    inserted, the id of the last "inserted" row (if IGNORE, that value may not
+    have been really inserted but ignored).
+  */
+  id= (thd->first_successful_insert_id_in_cur_stmt > 0) ?
+    thd->first_successful_insert_id_in_cur_stmt :
+    ((table->next_number_field && info.copied) ?
+     table->next_number_field->val_int() : 0);
   table->next_number_field=0;
   thd->count_cuted_fields= CHECK_FIELD_IGNORE;
-  thd->next_insert_id=0;			// Reset this if wrongly used
   if (duplic != DUP_ERROR || ignore)
     table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
 
-  /* Reset value of LAST_INSERT_ID if no rows where inserted */
-  if (!info.copied && thd->insert_id_used)
-  {
-    thd->insert_id(0);
-    id=0;
-  }
   if (error)
     goto abort;
   if (values_list.elements == 1 && (!(thd->options & OPTION_WARNINGS) ||
@@ -964,6 +954,7 @@
 {
   int error, trg_error= 0;
   char *key=0;
+  ulonglong prev_insert_id= thd->next_insert_id, insert_id_for_cur_row= 0;
   DBUG_ENTER("write_record");
 
   info->records++;
@@ -974,9 +965,18 @@
     while ((error=table->file->ha_write_row(table->record[0])))
     {
       uint key_nr;
+      /*
+        If we do more than one iteration of this loop, from the second one the
+        row will have an explicit value in the autoinc field, which was set at
+        the first call of handler::update_auto_increment(). So we must save
+        the autogenerated value to avoid thd->insert_id_for_cur_row to become 0
+      */
+      if (thd->insert_id_for_cur_row > 0)
+        insert_id_for_cur_row= thd->insert_id_for_cur_row;
+      else
+        thd->insert_id_for_cur_row= insert_id_for_cur_row;
       if (error != HA_WRITE_SKIP)
 	goto err;
-      table->file->restore_auto_increment(); // it's too early here! BUG#20188
       if ((int) (key_nr = table->file->get_dup_key(error)) < 0)
       {
 	error=HA_WRITE_SKIP;			/* Database can't find key */
@@ -990,7 +990,7 @@
       if (info->handle_duplicates == DUP_REPLACE &&
           table->next_number_field &&
           key_nr == table->s->next_number_index &&
-	  table->file->auto_increment_column_changed)
+	  (insert_id_for_cur_row > 0))
 	goto err;
       if (table->file->table_flags() & HA_DUPP_POS)
       {
@@ -1049,12 +1049,6 @@
         if (res == VIEW_CHECK_ERROR)
           goto before_trg_err;
 
-        if (thd->clear_next_insert_id)
-        {
-          /* Reset auto-increment cacheing if we do an update */
-          thd->clear_next_insert_id= 0;
-          thd->next_insert_id= 0;
-        }
         if ((error=table->file->ha_update_row(table->record[1],table->record[0])))
 	{
 	  if ((error == HA_ERR_FOUND_DUPP_KEY) && info->ignore)
@@ -1062,6 +1056,23 @@
           goto err;
 	}
         info->updated++;
+        /*
+          If ON DUP KEY UPDATE updates a row instead of inserting one, and
+          there is an auto_increment column, then SELECT LAST_INSERT_ID()
+          returns the id of the updated row:
+        */
+        if (table->next_number_field)
+        {
+          /*
+            We call thd->record_insert_id_for_cur_row_no_binlog() to not touch
+            first_insert_id_in_cur_stmt_for_binlog, for this case:
+            INSERT (auto_inc_col,other_col) VALUES
+                (null,dup_value),(null,not_dup_value);
+            then it is the value of NULL which we want to store in binlog.
+          */
+          thd->record_insert_id_for_cur_row_no_binlog(table->next_number_field->val_int());
+          thd->record_first_successful_insert_id_in_cur_stmt();
+        }
 
         trg_error= (table->triggers &&
                     table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
@@ -1088,16 +1099,11 @@
               table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
                                                 TRG_ACTION_BEFORE, TRUE))
             goto before_trg_err;
-          if (thd->clear_next_insert_id)
-          {
-            /* Reset auto-increment cacheing if we do an update */
-            thd->clear_next_insert_id= 0;
-            thd->next_insert_id= 0;
-          }
           if ((error=table->file->ha_update_row(table->record[1],
 					        table->record[0])))
             goto err;
           info->deleted++;
+          thd->record_first_successful_insert_id_in_cur_stmt();
           trg_error= (table->triggers &&
                       table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
                                                         TRG_ACTION_AFTER,
@@ -1132,17 +1138,19 @@
     trg_error= (table->triggers &&
                 table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
                                                   TRG_ACTION_AFTER, TRUE));
+    thd->record_first_successful_insert_id_in_cur_stmt();
   }
   else if ((error=table->file->ha_write_row(table->record[0])))
   {
     if (!info->ignore ||
 	(error != HA_ERR_FOUND_DUPP_KEY && error != HA_ERR_FOUND_DUPP_UNIQUE))
       goto err;
-    table->file->restore_auto_increment();
+    thd->next_insert_id= prev_insert_id;
   }
   else
   {
     info->copied++;
+    thd->record_first_successful_insert_id_in_cur_stmt();
     trg_error= (table->triggers &&
                 table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
                                                   TRG_ACTION_AFTER, TRUE));
@@ -1156,6 +1164,7 @@
   DBUG_RETURN(trg_error);
 
 err:
+  thd->next_insert_id= prev_insert_id;
   info->last_errno= error;
   /* current_select is NULL if this is a delayed insert */
   if (thd->lex->current_select)
@@ -1222,8 +1231,8 @@
   char *record;
   enum_duplicates dup;
   time_t start_time;
-  bool query_start_used,last_insert_id_used,insert_id_used, ignore, log_query;
-  ulonglong last_insert_id;
+  bool query_start_used, stmt_depends_on_first_successful_insert_id_in_prev_stmt, ignore, log_query;
+  ulonglong first_successful_insert_id_in_prev_stmt;
   timestamp_auto_set_type timestamp_field_type;
 
   delayed_row(enum_duplicates dup_arg, bool ignore_arg, bool log_query_arg)
@@ -1616,9 +1625,14 @@
   di->set_query(query, query_length);
   row->start_time=		thd->start_time;
   row->query_start_used=	thd->query_start_used;
-  row->last_insert_id_used=	thd->last_insert_id_used;
-  row->insert_id_used=		thd->insert_id_used;
-  row->last_insert_id=		thd->last_insert_id;
+  /*
+    those are for the binlog: LAST_INSERT_ID() has been evaluated at this
+    time, so record does not need it, but binlogging of the
+    INSERT will need when the row is actually inserted.
+    As for SET INSERT_ID, DELAYED does not honour it (to be documented).
+  */
+  row->stmt_depends_on_first_successful_insert_id_in_prev_stmt=	thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt;
+  row->first_successful_insert_id_in_prev_stmt=		thd->first_successful_insert_id_in_prev_stmt;
   row->timestamp_field_type=    table->timestamp_field_type;
 
   di->rows.push_back(row);
@@ -1990,9 +2004,9 @@
 
     thd.start_time=row->start_time;
     thd.query_start_used=row->query_start_used;
-    thd.last_insert_id=row->last_insert_id;
-    thd.last_insert_id_used=row->last_insert_id_used;
-    thd.insert_id_used=row->insert_id_used;
+    thd.first_insert_id_in_cur_stmt_for_binlog= 0; // forget previous rows
+    thd.first_successful_insert_id_in_prev_stmt= row->first_successful_insert_id_in_prev_stmt;
+    thd.stmt_depends_on_first_successful_insert_id_in_prev_stmt= row->stmt_depends_on_first_successful_insert_id_in_prev_stmt;
     table->timestamp_field_type= row->timestamp_field_type;
 
     info.ignore= row->ignore;
@@ -2156,7 +2170,7 @@
                              enum_duplicates duplic,
                              bool ignore_check_option_errors)
   :table_list(table_list_par), table(table_par), fields(fields_par),
-   last_insert_id(0),
+   autoinc_value_of_last_inserted_row(0),
    insert_into_view(table_list_par && table_list_par->view != 0)
 {
   bzero((char*) &info,sizeof(info));
@@ -2370,12 +2384,16 @@
     if (table->next_number_field)
     {
       /*
+        If no value has been autogenerated so far, we need to remember the
+        value we just saw, we may need to send it to client in the end.
+      */
+      if (thd->first_successful_insert_id_in_cur_stmt == 0) // optimization
+        autoinc_value_of_last_inserted_row= table->next_number_field->val_int();
+      /*
         Clear auto-increment field for the next record, if triggers are used
         we will clear it twice, but this should be cheap.
       */
       table->next_number_field->reset();
-      if (!last_insert_id && thd->insert_id_used)
-        last_insert_id= thd->insert_id();
     }
   }
   table->file->release_auto_increment();
@@ -2440,8 +2458,6 @@
   if ((info.copied || info.deleted || info.updated) &&
       !table->file->has_transactions())
   {
-    if (last_insert_id)
-      thd->insert_id(last_insert_id);		// For binary log
     if (mysql_bin_log.is_open())
     {
       thd->binlog_query(THD::ROW_QUERY_TYPE, thd->query, thd->query_length,
@@ -2462,6 +2478,7 @@
 bool select_insert::send_eof()
 {
   int error,error2;
+  ulonglong id;
   DBUG_ENTER("select_insert::send_eof");
 
   error= (!thd->prelocked_mode) ? table->file->ha_end_bulk_insert():0;
@@ -2484,8 +2501,6 @@
   else
     thd->options&= ~OPTION_STATUS_NO_TRANS_UPDATE;
 
-  if (last_insert_id)
-    thd->insert_id(last_insert_id);		// For binary log
   /*
     Write to binlog before commiting transaction.  No statement will
     be written by the binlog_query() below in RBR mode.  All the
@@ -2515,7 +2530,11 @@
     sprintf(buff, ER(ER_INSERT_INFO), (ulong) info.records,
 	    (ulong) (info.deleted+info.updated), (ulong) thd->cuted_fields);
   thd->row_count_func= info.copied+info.deleted+info.updated;
-  ::send_ok(thd, (ulong) thd->row_count_func, last_insert_id, buff);
+
+  id= (thd->first_successful_insert_id_in_cur_stmt > 0) ?
+    thd->first_successful_insert_id_in_cur_stmt :
+    (info.copied ? autoinc_value_of_last_inserted_row : 0);
+  ::send_ok(thd, (ulong) thd->row_count_func, id, buff);
   DBUG_RETURN(0);
 }
 

--- 1.99/sql/sql_load.cc	2006-06-02 22:22:51 +02:00
+++ 1.100/sql/sql_load.cc	2006-06-22 22:27:29 +02:00
@@ -643,14 +643,6 @@
     thd->no_trans_update= no_trans_update;
    
     /*
-      If auto_increment values are used, save the first one
-       for LAST_INSERT_ID() and for the binary/update log.
-       We can't use insert_id() as we don't want to touch the
-       last_insert_id_used flag.
-    */
-    if (!id && thd->insert_id_used)
-      id= thd->last_insert_id;
-    /*
       We don't need to reset auto-increment field since we are restoring
       its default value at the beginning of each loop iteration.
     */
@@ -666,8 +658,6 @@
     thd->row_count++;
 continue_loop:;
   }
-  if (id && !read_info.error)
-    thd->insert_id(id);			// For binary/update log
   DBUG_RETURN(test(read_info.error));
 }
 
@@ -811,14 +801,6 @@
     if (write_record(thd, table, &info))
       DBUG_RETURN(1);
     /*
-      If auto_increment values are used, save the first one
-       for LAST_INSERT_ID() and for the binary/update log.
-       We can't use insert_id() as we don't want to touch the
-       last_insert_id_used flag.
-    */
-    if (!id && thd->insert_id_used)
-      id= thd->last_insert_id;
-    /*
       We don't need to reset auto-increment field since we are restoring
       its default value at the beginning of each loop iteration.
     */
@@ -837,8 +819,6 @@
     thd->row_count++;
 continue_loop:;
   }
-  if (id && !read_info.error)
-    thd->insert_id(id);			// For binary/update log
   DBUG_RETURN(test(read_info.error));
 }
 

--- 1.554/sql/sql_parse.cc	2006-06-01 15:27:34 +02:00
+++ 1.555/sql/sql_parse.cc	2006-06-22 22:27:29 +02:00
@@ -3332,8 +3332,9 @@
     res= mysql_insert(thd, all_tables, lex->field_list, lex->many_values,
 		      lex->update_list, lex->value_list,
                       lex->duplicates, lex->ignore);
+    /* do not show last insert ID if VIEW does not have auto_inc */
     if (first_table->view && !first_table->contain_auto_increment)
-      thd->last_insert_id= 0; // do not show last insert ID if VIEW have not it
+      thd->first_successful_insert_id_in_cur_stmt= 0;
     break;
   }
   case SQLCOM_REPLACE_SELECT:
@@ -3394,9 +3395,9 @@
       /* revert changes for SP */
       select_lex->table_list.first= (byte*) first_table;
     }
-
+    /* do not show last insert ID if VIEW does not have auto_inc */
     if (first_table->view && !first_table->contain_auto_increment)
-      thd->last_insert_id= 0; // do not show last insert ID if VIEW have not it
+      thd->first_successful_insert_id_in_cur_stmt= 0;
     break;
   }
   case SQLCOM_TRUNCATE:
@@ -5692,6 +5693,7 @@
  DESCRIPTION
    This needs to be called before execution of every statement
    (prepared or conventional).
+   It is not called by substatements of routines.
 
  TODO
    Make it a method of THD and align its name with the rest of
@@ -5702,9 +5704,12 @@
 void mysql_reset_thd_for_next_command(THD *thd)
 {
   DBUG_ENTER("mysql_reset_thd_for_next_command");
+  DBUG_ASSERT(!thd->spcont); /* not for substatements of routines */
   thd->free_list= 0;
   thd->select_number= 1;
-  thd->last_insert_id_used= thd->query_start_used= thd->insert_id_used=0;
+  thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt= 
+    thd->first_insert_id_in_cur_stmt_for_binlog= 
+    thd->query_start_used= 0;
   thd->is_fatal_error= thd->time_zone_used= 0;
   thd->server_status&= ~ (SERVER_MORE_RESULTS_EXISTS | 
                           SERVER_QUERY_NO_INDEX_USED |

--- 1.409/sql/sql_select.cc	2006-06-02 22:22:51 +02:00
+++ 1.410/sql/sql_select.cc	2006-06-22 22:27:29 +02:00
@@ -7863,7 +7863,7 @@
       Field *field=((Item_field*) args[0])->field;
       if (field->flags & AUTO_INCREMENT_FLAG && !field->table->maybe_null &&
 	  (thd->options & OPTION_AUTO_IS_NULL) &&
-	  thd->insert_id())
+	  (thd->first_successful_insert_id_in_prev_stmt > 0))
       {
 #ifdef HAVE_QUERY_CACHE
 	query_cache_abort(&thd->net);
@@ -7871,7 +7871,7 @@
 	COND *new_cond;
 	if ((new_cond= new Item_func_eq(args[0],
 					new Item_int("last_insert_id()",
-						     thd->insert_id(),
+                                                     thd->read_first_successful_insert_id_in_prev_stmt(),
 						     21))))
 	{
 	  cond=new_cond;
@@ -7882,7 +7882,11 @@
           */
 	  cond->fix_fields(thd, &cond);
 	}
-	thd->insert_id(0);		// Clear for next request
+        /*
+          IS NULL should be mapped to LAST_INSERT_ID only for first row, so
+          clear for next row
+        */
+	thd->first_successful_insert_id_in_prev_stmt= 0;
       }
       /* fix to replace 'NULL' dates with '0' (shreeve@stripped) */
       else if (((field->type() == FIELD_TYPE_DATE) ||

--- 1.342/sql/sql_show.cc	2006-06-01 15:27:35 +02:00
+++ 1.343/sql/sql_show.cc	2006-06-22 22:27:30 +02:00
@@ -3103,9 +3103,10 @@
     if (!(wild && wild[0] &&
           wild_case_compare(scs, plugin->name.str,wild)))
     {
-      LEX_STRING state[2]={{STRING_WITH_LEN("ENABLED")},
-                           {STRING_WITH_LEN("DISABLED")}};
-      LEX_STRING yesno[2]={{STRING_WITH_LEN("NO")}, {STRING_WITH_LEN("YES")}};
+      LEX_STRING state[2]={{(char *) STRING_WITH_LEN("ENABLED")},
+                           {(char *) STRING_WITH_LEN("DISABLED")}};
+      LEX_STRING yesno[2]={{(char *) STRING_WITH_LEN("NO")},
+                           {(char *) STRING_WITH_LEN("YES")}};
       LEX_STRING *tmp;
       restore_record(table, s->default_values);
 

--- 1.340/sql/sql_table.cc	2006-06-02 22:22:52 +02:00
+++ 1.341/sql/sql_table.cc	2006-06-22 22:27:30 +02:00
@@ -4883,7 +4883,6 @@
   char path[FN_REFLEN];
   char reg_path[FN_REFLEN+1];
   ha_rows copied,deleted;
-  ulonglong next_insert_id;
   uint db_create_options, used_fields;
   handlerton *old_db_type, *new_db_type;
   uint need_copy_table= 0;
@@ -5676,7 +5675,6 @@
   thd->count_cuted_fields= CHECK_FIELD_WARN;	// calc cuted fields
   thd->cuted_fields=0L;
   thd->proc_info="copy to tmp table";
-  next_insert_id=thd->next_insert_id;		// Remember for logging
   copied=deleted=0;
   if (new_table && !(new_table->file->table_flags() & HA_NO_COPY_ON_ALTER))
   {
@@ -5687,7 +5685,6 @@
 				   handle_duplicates, ignore,
 				   order_num, order, &copied, &deleted);
   }
-  thd->last_insert_id=next_insert_id;		// Needed for correct log
   thd->count_cuted_fields= CHECK_FIELD_IGNORE;
 
   /* If we did not need to copy, we might still need to add/drop indexes. */
@@ -6117,6 +6114,7 @@
   ha_rows examined_rows;
   bool auto_increment_field_copied= 0;
   ulong save_sql_mode;
+  ulonglong prev_insert_id;
   DBUG_ENTER("copy_data_between_tables");
 
   /*
@@ -6228,6 +6226,7 @@
     {
       copy_ptr->do_copy(copy_ptr);
     }
+    prev_insert_id= thd->next_insert_id;
     if ((error=to->file->ha_write_row((byte*) to->record[0])))
     {
       if ((!ignore &&
@@ -6252,7 +6251,7 @@
 	to->file->print_error(error,MYF(0));
 	break;
       }
-      to->file->restore_auto_increment();
+      thd->next_insert_id= prev_insert_id;
       delete_count++;
     }
     else

--- 1.191/sql/sql_update.cc	2006-05-30 13:24:28 +02:00
+++ 1.192/sql/sql_update.cc	2006-06-22 22:27:30 +02:00
@@ -135,7 +135,8 @@
   SQL_SELECT	*select;
   READ_RECORD	info;
   SELECT_LEX    *select_lex= &thd->lex->select_lex;
-  bool need_reopen;
+  bool          need_reopen;
+  ulonglong     id;
   DBUG_ENTER("mysql_update");
 
   LINT_INIT(timestamp_query_id);
@@ -541,6 +542,11 @@
 	{
 	  updated++;
           thd->no_trans_update= !transactional_table;
+          /*
+            We inserted nothing, but if we used LAST_INSERT_ID(X) we need to
+            report it.
+          */
+          thd->record_first_successful_insert_id_in_cur_stmt();
 
           if (table->triggers &&
               table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
@@ -685,6 +691,9 @@
     thd->lock=0;
   }
 
+  /* If LAST_INSERT_ID(X) was used, report X */
+  id= thd->first_successful_insert_id_in_cur_stmt;
+
   if (error < 0)
   {
     char buff[STRING_BUFFER_USUAL_SIZE];
@@ -692,8 +701,7 @@
 	    (ulong) thd->cuted_fields);
     thd->row_count_func=
       (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
-    send_ok(thd, (ulong) thd->row_count_func,
-	    thd->insert_id_used ? thd->insert_id() : 0L,buff);
+    send_ok(thd, (ulong) thd->row_count_func, id, buff);
     DBUG_PRINT("info",("%d records updated",updated));
   }
   thd->count_cuted_fields= CHECK_FIELD_IGNORE;		/* calc cuted fields */
@@ -1429,6 +1437,7 @@
 	}
         else
         {
+          thd->record_first_successful_insert_id_in_cur_stmt();
           if (!table->file->has_transactions())
             thd->no_trans_update= 1;
           if (table->triggers &&
@@ -1571,6 +1580,7 @@
 	    goto err;
 	}
 	updated++;
+        thd->record_first_successful_insert_id_in_cur_stmt();
 
         if (table->triggers &&
             table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
@@ -1618,6 +1628,7 @@
 bool multi_update::send_eof()
 {
   char buff[STRING_BUFFER_USUAL_SIZE];
+  ulonglong id;
   thd->proc_info="updating reference tables";
 
   /* Does updates for the last n - 1 tables, returns 0 if ok */
@@ -1670,12 +1681,11 @@
     return TRUE;
   }
 
-
+  id= thd->first_successful_insert_id_in_cur_stmt;
   sprintf(buff, ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated,
 	  (ulong) thd->cuted_fields);
   thd->row_count_func=
     (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated;
-  ::send_ok(thd, (ulong) thd->row_count_func,
-	    thd->insert_id_used ? thd->insert_id() : 0L,buff);
+  ::send_ok(thd, (ulong) thd->row_count_func, id, buff);
   return FALSE;
 }

--- 1.2/mysql-test/r/rpl_ndb_auto_inc.result	2006-02-10 16:00:33 +01:00
+++ 1.3/mysql-test/r/rpl_ndb_auto_inc.result	2006-06-22 22:27:28 +02:00
@@ -120,8 +120,6 @@
 502
 503
 600
-603
-604
 610
 611
 ******* Select from Slave *************
@@ -137,8 +135,6 @@
 502
 503
 600
-603
-604
 610
 611
 drop table t1;

--- 1.9/mysql-test/r/rpl_ndb_log.result	2006-05-31 16:15:55 +02:00
+++ 1.10/mysql-test/r/rpl_ndb_log.result	2006-06-22 22:27:28 +02:00
@@ -132,3 +132,19 @@
 DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t3;
+create table t1(a int auto_increment primary key, b int);
+insert into t1 values (NULL, 1);
+reset master;
+set insert_id=5;
+insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id());
+show binlog events;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+slave-bin.000001	#	Format_desc	2	#	Server ver: VERSION, Binlog ver: 4
+slave-bin.000001	#	Table_map	2	#	table_id: # (test.t1)
+slave-bin.000001	#	Write_rows	2	#	table_id: # flags: STMT_END_F
+select * from t1;
+a	b
+1	1
+5	1
+6	1
+drop table t1;

--- 1.8/mysql-test/r/rpl_row_log_innodb.result	2006-05-20 12:35:01 +02:00
+++ 1.9/mysql-test/r/rpl_row_log_innodb.result	2006-06-22 22:27:28 +02:00
@@ -108,3 +108,19 @@
 DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t3;
+create table t1(a int auto_increment primary key, b int);
+insert into t1 values (NULL, 1);
+reset master;
+set insert_id=5;
+insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id());
+show binlog events;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+slave-bin.000001	#	Format_desc	2	#	Server ver: VERSION, Binlog ver: 4
+slave-bin.000001	#	Table_map	2	#	table_id: # (test.t1)
+slave-bin.000001	#	Write_rows	2	#	table_id: # flags: STMT_END_F
+select * from t1;
+a	b
+1	1
+5	1
+6	1
+drop table t1;

--- 1.6/mysql-test/extra/rpl_tests/rpl_insert_id.test	2006-05-31 10:18:51 +02:00
+++ 1.7/mysql-test/extra/rpl_tests/rpl_insert_id.test	2006-06-22 22:27:28 +02:00
@@ -144,6 +144,23 @@
 # This should be exactly one greater than in the previous call.
 select last_insert_id();
 
+# BUG#20339 - stored procedure using LAST_INSERT_ID() does not
+# replicate statement-based
+--disable_warnings
+drop procedure if exists foo;
+--enable_warnings
+delimiter |;
+create procedure foo()
+begin
+  declare res int;
+  insert into t2 (last_id) values (bug15728());
+  insert into t1 (last_id) values (bug15728());
+end|
+delimiter ;|
+call foo();
+
+select * from t1;
+select * from t2;
 save_master_pos;
 connection slave;
 sync_with_master;
@@ -153,6 +170,7 @@
 
 drop function bug15728;
 drop function bug15728_insert;
+drop procedure foo;
 drop table t1, t2;
 
 # End of 5.0 tests

--- 1.3/mysql-test/r/binlog_row_binlog.result	2006-05-20 12:34:53 +02:00
+++ 1.4/mysql-test/r/binlog_row_binlog.result	2006-06-22 22:27:28 +02:00
@@ -235,3 +235,23 @@
 show binlog events in 'master-bin.000002' from 102;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000002	#	Query	1	#	use `test`; drop table t1
+create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
+reset master;
+set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
+insert delayed into t1 values (207);
+insert delayed into t1 values (null);
+insert delayed into t1 values (300);
+select * from t1;
+a
+207
+208
+300
+show binlog events from 102;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Table_map	1	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Table_map	1	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows	1	#	table_id: # flags: STMT_END_F
+drop table t1;

--- 1.8/mysql-test/r/rpl_row_log.result	2006-05-16 15:37:48 +02:00
+++ 1.9/mysql-test/r/rpl_row_log.result	2006-06-22 22:27:28 +02:00
@@ -100,3 +100,19 @@
 DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t3;
+create table t1(a int auto_increment primary key, b int);
+insert into t1 values (NULL, 1);
+reset master;
+set insert_id=5;
+insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id());
+show binlog events;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+slave-bin.000001	#	Format_desc	2	#	Server ver: VERSION, Binlog ver: 4
+slave-bin.000001	#	Table_map	2	#	table_id: # (test.t1)
+slave-bin.000001	#	Write_rows	2	#	table_id: # flags: STMT_END_F
+select * from t1;
+a	b
+1	1
+5	1
+6	1
+drop table t1;

--- 1.315/sql/ha_ndbcluster.cc	2006-06-02 22:22:50 +02:00
+++ 1.316/sql/ha_ndbcluster.cc	2006-06-22 22:27:28 +02:00
@@ -2405,7 +2405,7 @@
       update_auto_increment();
       /* Ensure that handler is always called for auto_increment values */
       thd->next_insert_id= 0;
-      m_skip_auto_increment= !auto_increment_column_changed;
+      m_skip_auto_increment= (thd->insert_id_for_cur_row == 0);
     }
   }
 

--- 1.41/mysql-test/r/auto_increment.result	2006-05-18 09:42:31 +02:00
+++ 1.42/mysql-test/r/auto_increment.result	2006-06-22 22:27:28 +02:00
@@ -153,7 +153,7 @@
 ERROR 23000: Duplicate entry '255' for key 'PRIMARY'
 select last_insert_id();
 last_insert_id()
-0
+255
 drop table t1;
 create table t1 (i tinyint unsigned not null auto_increment, key (i));
 insert into t1 set i = 254;
@@ -181,7 +181,7 @@
 ERROR 23000: Duplicate entry '10' for key 'b'
 select last_insert_id();
 last_insert_id()
-0
+2
 drop table t1;
 create table t1(a int auto_increment,b int null,primary key(a));
 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
@@ -424,3 +424,93 @@
 ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
 ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
 DROP TABLE t1;
+create table t1(a int not null auto_increment primary key);
+create table t2(a int not null auto_increment primary key, t1a int);
+insert into t1 values(NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
+insert into t1 values (NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
+(NULL, LAST_INSERT_ID());
+insert into t1 values (NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
+(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
+select * from t2;
+a	t1a
+1	1
+2	1
+3	2
+4	2
+5	2
+6	3
+7	3
+8	3
+9	3
+drop table t2;
+CREATE TABLE `t2` (
+`k` int(11) NOT NULL auto_increment,
+`a` int(11) default NULL,
+`c` int(11) default NULL,
+PRIMARY KEY  (`k`),
+UNIQUE KEY `idx_1` (`a`)
+) ENGINE=InnoDB;
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+insert into t2 ( a ) values ( 7 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+last_insert_id()
+2
+select * from t2;
+k	a	c
+1	6	NULL
+2	7	NULL
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+last_insert_id()
+1
+select * from t2;
+k	a	c
+1	6	1
+2	7	NULL
+insert ignore into t2 values (null,6,1),(10,8,1);
+select last_insert_id();
+last_insert_id()
+1
+insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
+select last_insert_id();
+last_insert_id()
+11
+select * from t2;
+k	a	c
+1	6	1
+2	7	NULL
+10	8	1
+11	15	1
+12	20	1
+drop table t1, t2;
+create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
+insert into t1 values(null,1,1,now());
+insert into t1 values(null,0,0,null);
+replace into t1 values(null,1,0,null);
+select last_insert_id();
+last_insert_id()
+3
+drop table t1;
+set session insert_id=20;
+select @@session.insert_id;
+@@session.insert_id
+20
+set session last_insert_id=100;
+select @@session.insert_id;
+@@session.insert_id
+20
+select @@session.last_insert_id;
+@@session.last_insert_id
+100
+select @@session.insert_id;
+@@session.insert_id
+20

--- 1.26/mysql-test/t/auto_increment.test	2006-05-12 15:00:46 +02:00
+++ 1.27/mysql-test/t/auto_increment.test	2006-06-22 22:27:28 +02:00
@@ -286,3 +286,74 @@
 --error ER_DUP_ENTRY
 ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
 DROP TABLE t1;
+
+#
+# Bug #6880: LAST_INSERT_ID() within a statement
+#
+
+create table t1(a int not null auto_increment primary key);
+create table t2(a int not null auto_increment primary key, t1a int);
+insert into t1 values(NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
+insert into t1 values (NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
+(NULL, LAST_INSERT_ID());
+insert into t1 values (NULL);
+insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
+(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
+select * from t2;
+drop table t2;
+
+# Fix for BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY
+# UPDATE": now LAST_INSERT_ID() will return the id of the updated
+# row.
+CREATE TABLE `t2` (
+  `k` int(11) NOT NULL auto_increment,
+  `a` int(11) default NULL,
+  `c` int(11) default NULL,
+  PRIMARY KEY  (`k`),
+  UNIQUE KEY `idx_1` (`a`)
+) ENGINE=InnoDB;
+ insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+insert into t2 ( a ) values ( 7 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+select * from t2;
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+select * from t2;
+
+# Autogenerated will fail, last_insert_id() should not change
+insert ignore into t2 values (null,6,1),(10,8,1);
+select last_insert_id();
+# First and second autogenerated will fail, last_insert_id() should
+# point to third
+insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
+select last_insert_id();
+select * from t2;
+
+drop table t1, t2;
+
+# Test of REPLACE when it does INSERT+DELETE and not UPDATE,
+# see if it sets LAST_INSERT_ID() ok
+create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
+insert into t1 values(null,1,1,now());
+insert into t1 values(null,0,0,null);
+# this will delete two rows
+replace into t1 values(null,1,0,null);
+select last_insert_id();
+
+drop table t1;
+
+# BUG#20392 INSERT_ID session variable has weird value
+set session insert_id=20;
+select @@session.insert_id;
+set session last_insert_id=100;
+select @@session.insert_id;
+select @@session.last_insert_id;
+select @@session.insert_id;

--- 1.15/mysql-test/r/rpl_insert_id.result	2006-04-25 16:20:39 +02:00
+++ 1.16/mysql-test/r/rpl_insert_id.result	2006-06-22 22:27:28 +02:00
@@ -117,6 +117,14 @@
 select last_insert_id();
 last_insert_id()
 5
+drop procedure if exists foo;
+create procedure foo()
+begin
+declare res int;
+insert into t2 (last_id) values (bug15728());
+insert into t1 (last_id) values (bug15728());
+end|
+call foo();
 select * from t1;
 id	last_id
 1	0
@@ -124,10 +132,26 @@
 3	2
 4	1
 5	4
+6	3
 select * from t2;
 id	last_id
 1	3
 2	4
+3	5
+select * from t1;
+id	last_id
+1	0
+2	1
+3	2
+4	1
+5	4
+6	3
+select * from t2;
+id	last_id
+1	3
+2	4
+3	5
 drop function bug15728;
 drop function bug15728_insert;
+drop procedure foo;
 drop table t1, t2;

--- 1.178/sql/set_var.cc	2006-05-31 18:07:27 +02:00
+++ 1.179/sql/set_var.cc	2006-06-22 22:27:29 +02:00
@@ -2554,7 +2554,8 @@
 
 bool sys_var_last_insert_id::update(THD *thd, set_var *var)
 {
-  thd->insert_id(var->save_result.ulonglong_value);
+  thd->first_successful_insert_id_in_prev_stmt= 
+    var->save_result.ulonglong_value;
   return 0;
 }
 
@@ -2562,8 +2563,13 @@
 byte *sys_var_last_insert_id::value_ptr(THD *thd, enum_var_type type,
 					LEX_STRING *base)
 {
-  thd->sys_var_tmp.long_value= (long) thd->insert_id();
-  return (byte*) &thd->last_insert_id;
+  /*
+    this tmp var makes it robust againt change of type of 
+    read_first_successful_insert_id_in_prev_stmt().
+  */
+  thd->sys_var_tmp.ulonglong_value= 
+    thd->read_first_successful_insert_id_in_prev_stmt();
+  return (byte*) &thd->sys_var_tmp.ulonglong_value;
 }
 
 
@@ -2577,7 +2583,9 @@
 byte *sys_var_insert_id::value_ptr(THD *thd, enum_var_type type,
 				   LEX_STRING *base)
 {
-  return (byte*) &thd->current_insert_id;
+  thd->sys_var_tmp.ulonglong_value= 
+    thd->next_insert_id;
+  return (byte*) &thd->sys_var_tmp.ulonglong_value;
 }
 
 

--- 1.53/sql/ha_partition.cc	2006-06-02 22:22:50 +02:00
+++ 1.54/sql/ha_partition.cc	2006-06-22 22:27:28 +02:00
@@ -5262,14 +5262,6 @@
                 MODULE auto increment
 ****************************************************************************/
 
-void ha_partition::restore_auto_increment()
-{
-  DBUG_ENTER("ha_partition::restore_auto_increment");
-
-  DBUG_VOID_RETURN;
-}
-
-
 /*
   This method is called by update_auto_increment which in turn is called
   by the individual handlers as part of write_row. We will always let

--- 1.18/sql/ha_partition.h	2006-06-02 22:21:26 +02:00
+++ 1.19/sql/ha_partition.h	2006-06-22 22:27:29 +02:00
@@ -806,15 +806,8 @@
     MODULE auto increment
     -------------------------------------------------------------------------
     This module is used to handle the support of auto increments.
-
-    This variable in the handler is used as part of the handler interface
-    It is maintained by the parent handler object and should not be
-    touched by child handler objects (see handler.cc for its use).
-
-    auto_increment_column_changed
      -------------------------------------------------------------------------
   */
-  virtual void restore_auto_increment();
   virtual void get_auto_increment(ulonglong offset, ulonglong increment,
                                   ulonglong nb_desired_values,
                                   ulonglong *first_value,

--- 1.61/sql/ha_federated.cc	2006-06-01 22:25:18 +02:00
+++ 1.62/sql/ha_federated.cc	2006-06-22 22:27:28 +02:00
@@ -1696,7 +1696,7 @@
   THD *thd= current_thd;
   DBUG_ENTER("ha_federated::update_auto_increment");
 
-  thd->insert_id(mysql->last_used_con->insert_id);
+  thd->first_successful_insert_id_in_cur_stmt= mysql->last_used_con->insert_id;
   DBUG_PRINT("info",("last_insert_id %d", auto_increment_value));
 
   DBUG_VOID_RETURN;

--- 1.68/mysql-test/r/rpl_stm_log.result	2006-05-16 15:37:39 +02:00
+++ 1.69/mysql-test/r/rpl_stm_log.result	2006-06-22 22:27:28 +02:00
@@ -98,3 +98,20 @@
 DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t3;
+create table t1(a int auto_increment primary key, b int);
+insert into t1 values (NULL, 1);
+reset master;
+set insert_id=5;
+insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id());
+show binlog events;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+slave-bin.000001	#	Format_desc	2	#	Server ver: VERSION, Binlog ver: 4
+slave-bin.000001	#	Intvar	2	#	LAST_INSERT_ID=1
+slave-bin.000001	#	Intvar	2	#	INSERT_ID=5
+slave-bin.000001	#	Query	2	#	use `test`; insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id())
+select * from t1;
+a	b
+1	1
+5	1
+6	1
+drop table t1;

--- 1.37/mysql-test/extra/rpl_tests/rpl_log.test	2006-05-16 15:37:17 +02:00
+++ 1.38/mysql-test/extra/rpl_tests/rpl_log.test	2006-06-22 22:27:28 +02:00
@@ -130,3 +130,19 @@
 DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t3;
+
+#
+# Bug #6880: LAST_INSERT_ID() within a statement
+#
+
+create table t1(a int auto_increment primary key, b int);
+insert into t1 values (NULL, 1);
+reset master;
+set insert_id=5;
+insert into t1 values (NULL, last_insert_id()), (NULL, last_insert_id());
+--replace_result $VERSION VERSION
+--replace_column 2 # 5 #
+--replace_regex /table_id: [0-9]+/table_id: #/
+show binlog events;
+select * from t1;
+drop table t1;

--- 1.10/mysql-test/r/binlog_stm_binlog.result	2006-05-17 15:04:49 +02:00
+++ 1.11/mysql-test/r/binlog_stm_binlog.result	2006-06-22 22:27:28 +02:00
@@ -145,3 +145,21 @@
 show binlog events in 'master-bin.000002' from 102;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000002	#	Query	1	#	use `test`; drop table t1
+create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
+reset master;
+set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
+insert delayed into t1 values (207);
+insert delayed into t1 values (null);
+insert delayed into t1 values (300);
+select * from t1;
+a
+207
+208
+300
+show binlog events from 102;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	1	#	use `test`; insert delayed into t1 values (207)
+master-bin.000001	#	Intvar	1	#	INSERT_ID=208
+master-bin.000001	#	Query	1	#	use `test`; insert delayed into t1 values (null)
+master-bin.000001	#	Query	1	#	use `test`; insert delayed into t1 values (300)
+drop table t1;

--- 1.10/mysql-test/extra/binlog_tests/binlog.test	2006-05-17 22:43:18 +02:00
+++ 1.11/mysql-test/extra/binlog_tests/binlog.test	2006-06-22 22:27:28 +02:00
@@ -49,3 +49,20 @@
 --replace_column 2 # 5 #
 --replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\//
 show binlog events in 'master-bin.000002' from 102;
+
+# Test of binlogging of INSERT_ID with INSERT DELAYED
+create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
+reset master;
+# First, avoid BUG#20627:
+set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
+# Verify that only one INSERT_ID event is binlogged.
+# The inserted values are however strange.
+insert delayed into t1 values (207);
+insert delayed into t1 values (null);
+insert delayed into t1 values (300);
+sleep 1; # time for the delayed queries to reach disk
+select * from t1;
+--replace_column 2 # 5 #
+--replace_regex /table_id: [0-9]+/table_id: #/
+show binlog events from 102;
+drop table t1;

--- 1.190/tests/mysql_client_test.c	2006-05-24 14:33:38 +02:00
+++ 1.191/tests/mysql_client_test.c	2006-06-22 22:27:30 +02:00
@@ -14947,7 +14947,193 @@
 
   rc= mysql_query(mysql, "drop table t1");
   myquery(rc);
-}/*
+}
+
+
+/*
+   Test that mysql_insert_id() behaves as documented in our manual
+*/
+static void test_mysql_insert_id()
+{
+  my_ulonglong res;
+  int rc;
+
+  myheader("test_mysql_insert_id");
+
+  rc= mysql_query(mysql, "drop table if exists t1");
+  myquery(rc);
+  /* table without auto_increment column */
+  rc= mysql_query(mysql, "create table t1 (f1 int, f2 varchar(255), key(f1))");
+  myquery(rc);
+  rc= mysql_query(mysql, "insert into t1 values (1,'a')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "insert into t1 values (null,'b')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "insert into t1 select 5,'c'");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "insert into t1 select null,'d'");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "insert into t1 values (null,last_insert_id(300))");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 300);
+  rc= mysql_query(mysql, "insert into t1 select null,last_insert_id(400)");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  /*
+    Behaviour change: old code used to return 0; but 400 is consistent
+    with INSERT VALUES, and the manual's section of mysql_insert_id() does not
+    say INSERT SELECT should be different.
+  */
+  DIE_UNLESS(res == 400);
+
+  /* table with auto_increment column */
+  rc= mysql_query(mysql, "create table t2 (f1 int not null primary key auto_increment, f2 varchar(255))");
+  myquery(rc);
+  rc= mysql_query(mysql, "insert into t2 values (1,'a')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 1);
+  /* this should not influence next INSERT if it doesn't have auto_inc */
+  rc= mysql_query(mysql, "insert into t1 values (10,'e')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+
+  rc= mysql_query(mysql, "insert into t2 values (null,'b')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 2);
+  rc= mysql_query(mysql, "insert into t2 select 5,'c'");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  /*
+    Manual says that for multirow insert this should have been 5, but does not
+    say for INSERT SELECT. This is a behaviour change: old code used to return
+    0. We try to be consistent with INSERT VALUES.
+  */
+  DIE_UNLESS(res == 5);
+  rc= mysql_query(mysql, "insert into t2 select null,'d'");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 6);
+  /* with more than one row */
+  rc= mysql_query(mysql, "insert into t2 values (10,'a'),(11,'b')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 11);
+  rc= mysql_query(mysql, "insert into t2 select 12,'a' union select 13,'b'");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  /*
+    Manual says that for multirow insert this should have been 13, but does
+    not say for INSERT SELECT. This is a behaviour change: old code used to
+    return 0. We try to be consistent with INSERT VALUES.
+  */
+  DIE_UNLESS(res == 13);
+  rc= mysql_query(mysql, "insert into t2 values (null,'a'),(null,'b')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 14);
+  rc= mysql_query(mysql, "insert into t2 select null,'a' union select null,'b'");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 16);
+  rc= mysql_query(mysql, "insert into t2 select 12,'a' union select 13,'b'");
+  myquery_r(rc);
+  rc= mysql_query(mysql, "insert ignore into t2 select 12,'a' union select 13,'b'");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "insert into t2 values (12,'a'),(13,'b')");
+  myquery_r(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "insert ignore into t2 values (12,'a'),(13,'b')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  /* mixing autogenerated and explicit values */
+  rc= mysql_query(mysql, "insert into t2 values (null,'e'),(12,'a'),(13,'b')");
+  myquery_r(rc);
+  rc= mysql_query(mysql, "insert into t2 values (null,'e'),(12,'a'),(13,'b'),(25,'g')");
+  myquery_r(rc);
+  rc= mysql_query(mysql, "insert into t2 values (null,last_insert_id(300))");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  /*
+    according to the manual, this might be 20 or 300, but it looks like
+    auto_increment column takes priority over last_insert_id().
+  */
+  DIE_UNLESS(res == 20);
+  /* If first autogenerated number fails and 2nd works: */
+  rc= mysql_query(mysql, "drop table t2");
+  myquery(rc);
+  rc= mysql_query(mysql, "create table t2 (f1 int not null primary key "
+                  "auto_increment, f2 varchar(255), unique (f2))");
+  myquery(rc);
+  rc= mysql_query(mysql, "insert into t2 values (null,'e')");
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 1);
+  rc= mysql_query(mysql, "insert ignore into t2 values (null,'e'),(null,'a'),(null,'e')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 2);
+  /* If autogenerated fails and explicit works: */
+  rc= mysql_query(mysql, "insert ignore into t2 values (null,'e'),(12,'c'),(null,'d')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  /*
+    Behaviour change: old code returned 3 (first autogenerated, even if it
+    fails); we now return first successful autogenerated.
+  */
+  DIE_UNLESS(res == 13);
+  /* UPDATE may update mysql_insert_id() if it uses LAST_INSERT_ID(#) */
+  rc= mysql_query(mysql, "update t2 set f1=14 where f1=12");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "update t2 set f1=NULL where f1=14");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "update t2 set f2=last_insert_id(372) where f1=0");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 372);
+  /* check that LAST_INSERT_ID() does not update mysql_insert_id(): */
+  rc= mysql_query(mysql, "insert into t2 values (null,'g')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 15);
+  rc= mysql_query(mysql, "update t2 set f2=(@li:=last_insert_id()) where f1=15");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  /*
+    Behaviour change: now if ON DUPLICATE KEY UPDATE updates a row,
+    mysql_insert_id() returns the id of the row, instead of not being
+    affected.
+  */
+  rc= mysql_query(mysql, "insert into t2 values (null,@li) on duplicate key "
+                  "update f2=concat('we updated ',f2)");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 15);
+
+  rc= mysql_query(mysql, "drop table t1,t2");
+  myquery(rc);
+}
+
+/*
   Read and parse arguments and MySQL options from my.cnf
 */
 
@@ -15214,6 +15400,7 @@
   { "test_bug15613", test_bug15613 },
   { "test_bug14169", test_bug14169 },
   { "test_bug17667", test_bug17667 },
+  { "test_mysql_insert_id", test_mysql_insert_id },
   { 0, 0 }
 };
 
Thread
bk commit into 5.1 tree (guilhem:1.2178) BUG#20188guilhem22 Jun