List:Commits« Previous MessageNext Message »
From:guilhem Date:June 12 2006 10:04pm
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/13 00:04:27 guilhem@stripped +26 -0
  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 variables are 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" though not fixed as the users
  would want it to be: to be discussed
  BUG#20392 "INSERT_ID session variable has weird value"
  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 for a first review, not for push.
  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() does not change if no autogenerated value was successfully inserted (it used to then be 0), see auto_increment.test.
  - mysql_insert_id() now returns 0 if inserting into a table without auto_increment and LAST_INSERT_ID(#) was used
  (used to return the value #), see mysql_client_test.c.

  tests/mysql_client_test.c
    1.191 06/06/13 00:04:21 guilhem@stripped +144 -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.

  sql/sql_update.cc
    1.192 06/06/13 00:04:21 guilhem@stripped +2 -2
    update for new variable names.
    thd->insert_id_used should not be tested there; how could an UPDATE have this variable
    non-zero (called functions/triggers excluded) ?

  sql/sql_table.cc
    1.341 06/06/13 00:04:21 guilhem@stripped +3 -4
    next_insert_id not needed.
    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/13 00:04:21 guilhem@stripped +4 -3
    fix for compiler warning (about a string constant implicitely cast to char*).

  sql/sql_select.cc
    1.410 06/06/13 00:04:21 guilhem@stripped +7 -3
    update for new variable names

  sql/sql_parse.cc
    1.555 06/06/13 00:04:20 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/13 00:04:20 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/13 00:04:20 guilhem@stripped +58 -56
    the "id" variable is not needed; 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 we don't need it, THD keeps the first value in first_successful_insert_id_in_cur_stmt,
    and value of the row in insert_id_for_cur_row.
    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).
    Proposal for setting LAST_INSERT_ID() in ON DUPLICATE KEY UPDATE too (BUG#19243).

  sql/sql_class.h
    1.296 06/06/13 00:04:20 guilhem@stripped +120 -25
    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.
    last_insert_id is not needed in class select_insert, THD now manages this naturally.

  sql/sql_class.cc
    1.264 06/06/13 00:04:20 guilhem@stripped +86 -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/13 00:04:20 guilhem@stripped +7 -4
    new variable names. The last change fixes BUG#20392 "INSERT_ID session variable has weird value"
    (will someone want to fix it in 5.0 ?).

  sql/log_event.cc
    1.225 06/06/13 00:04:20 guilhem@stripped +7 -3
    new variable names, comments

  sql/log.cc
    1.212 06/06/13 00:04:20 guilhem@stripped +32 -19
    new variable names for insert_ids

  sql/item_func.cc
    1.295 06/06/13 00:04:20 guilhem@stripped +2 -2
    new names for variables

  sql/handler.h
    1.215 06/06/13 00:04:20 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/13 00:04:20 guilhem@stripped +36 -59
    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/13 00:04:20 guilhem@stripped +0 -7
    unneeded

  sql/ha_partition.cc
    1.54 06/06/13 00:04:20 guilhem@stripped +0 -8
    unneeded

  sql/ha_federated.cc
    1.62 06/06/13 00:04:20 guilhem@stripped +1 -1
    update for new variables.

  mysql-test/t/auto_increment.test
    1.27 06/06/13 00:04:20 guilhem@stripped +70 -0
    Importing Ramil's tescase for BUG#6880 to see I don't re-introduce it;
    will be removed and merged instead. 
    A testcase of BUG#19243 to see what this patch accomplishes about it (it's better than the current code,
    which returns a non-existing id to the ON DUPLICATE KEY UPDATE; it's still not what the users
    asked for, which was the id of the updated row).
    Testcase for BUG#20392.
    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/13 00:04:20 guilhem@stripped +17 -0
    result update

  mysql-test/r/rpl_row_log.result
    1.9 06/06/13 00:04:20 guilhem@stripped +16 -0
    result update

  mysql-test/r/rpl_insert_id.result
    1.16 06/06/13 00:04:19 guilhem@stripped +24 -0
    result update

  mysql-test/r/auto_increment.result
    1.42 06/06/13 00:04:19 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/13 00:04:19 guilhem@stripped +16 -0
    Manually importing Ramil's testcase for BUG#6880 to check that this 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/13 00:04:19 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)

# 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-cleanup2

--- 1.236/sql/handler.cc	2006-06-02 22:22:50 +02:00
+++ 1.237/sql/handler.cc	2006-06-13 00:04:20 +02:00
@@ -1572,7 +1572,9 @@
 }
 
 /*
-  Generate the next auto-increment number based on increment and offset
+  Generate the next auto-increment number based on increment and offset:
+  takes nr+1 and pushes it to the right until finding a number 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 +1585,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 +1647,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 +1674,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 +1682,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(0); // didn't generate anything
     DBUG_RETURN(0);
   }
   if (!(nr= thd->next_insert_id))
@@ -1749,60 +1741,45 @@
       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
-    */
-    thd->next_insert_id= nr;
+    nr= next_insert_id(nr-1, variables);
+    /* instead 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-13 00:04:20 +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-13 00:04:20 +02:00
@@ -3293,12 +3293,12 @@
   if (arg_count)
   {
     longlong value= args[0]->val_int();
-    thd->insert_id(value);
+    thd->first_successful_insert_id_in_prev_stmt= value;
     null_value= args[0]->null_value;
     return value;                       // Avoid side effect of insert_id()
   }
   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-13 00:04:20 +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,7 @@
   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;
+  longlong last_insert_id= 0, insert_id;
 
   /*
     Print the message to the buffer if we have slow log enabled
@@ -740,12 +745,16 @@
       lock_time= (longlong) (thd->time_after_lock - query_start_arg);
     }
 
-    if (thd->last_insert_id_used)
-      last_insert_id= (longlong) thd->current_insert_id;
+    if (thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt)
+      last_insert_id= (longlong) thd->first_successful_insert_id_in_prev_stmt_for_binlog;
 
-    /* set value if we do an insert on autoincrement column */
-    if (thd->insert_id_used)
-      insert_id= (longlong) thd->last_insert_id;
+    /*
+      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.
+    */
+    insert_id= (longlong) thd->first_insert_id_in_cur_stmt_for_binlog;
 
     if (!query)
     {
@@ -2906,21 +2915,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 +3257,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-13 00:04:20 +02:00
@@ -3398,11 +3398,15 @@
 {
   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
+    */
+    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-13 00:04:20 +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,25 @@
 
 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;
+  }
   if (clear_next_insert_id)
   {
     clear_next_insert_id= 0;
@@ -2118,9 +2141,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 +2198,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 +2239,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-13 00:04:20 +02:00
@@ -761,12 +761,16 @@
 {
 public:
   ulonglong options;
-  ulonglong last_insert_id, next_insert_id;
+  ulonglong next_insert_id,
+    first_successful_insert_id_in_prev_stmt,
+    first_successful_insert_id_in_cur_stmt,
+    insert_id_for_cur_row,
+    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 +1061,77 @@
   */
   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()
+    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
+    insert_id_of_first_row_in_prev_stmt_was 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).
+    At first successful insertion, this becomes
+    first_successful_insert_id_in_cur_stmt.
+  */
+  ulonglong  insert_id_for_cur_row;
+  /*
+    Variant of the above: gets 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 for
+    prev 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, 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.
+    - when stmt ends, first_successful_insert_id_in_prev_stmt is set to
+    first_successful_insert_id_in_cur_stmt.
+    More insert_id variables are further below.
   */
-  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;
   ulonglong  limit_found_rows;
   ulonglong  options;           /* Bitmap of states */
   longlong   row_count_func;	/* For the ROW_COUNT() function */
@@ -1142,7 +1200,25 @@
   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;
+  /*
+    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.
+    More insert_id methods further below.
+  */
+  bool	     clear_next_insert_id;
   bool	     in_lock_tables;
   bool       query_error, bootstrap, cleanup_done;
   bool	     tmp_table_used;
@@ -1273,19 +1349,39 @@
   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)
+  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;
+    insert_id_for_cur_row= id_arg;
+  }
+  inline void	record_first_successful_insert_id_in_cur_stmt()
   {
-    last_insert_id= id_arg;
-    insert_id_used=1;
+    if (first_successful_insert_id_in_cur_stmt == 0)
+      first_successful_insert_id_in_cur_stmt= insert_id_for_cur_row;
   }
-  inline ulonglong insert_id(void)
+  inline ulonglong read_first_successful_insert_id_in_prev_stmt(void)
   {
-    if (!last_insert_id_used)
+    if (!stmt_depends_on_first_successful_insert_id_in_prev_stmt)
     {
-      last_insert_id_used=1;
-      current_insert_id=last_insert_id;
+      /* 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 last_insert_id;
+    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;
   }
   inline ulonglong found_rows(void)
   {
@@ -1557,7 +1653,6 @@
   TABLE_LIST *table_list;
   TABLE *table;
   List<Item> *fields;
-  ulonglong last_insert_id;
   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-13 00:04:20 +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,19 @@
     }
   }
   thd->proc_info="end";
+  /*
+    If an autoinc column, and we didn't successfully insert an autogenerated
+    value, we'll report the id of the last inserted row.
+  */
+  id= (table->next_number_field && info.copied &&
+       (thd->first_successful_insert_id_in_cur_stmt == 0)) ?
+    table->next_number_field->val_int() :
+    thd->first_successful_insert_id_in_cur_stmt;
   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 +948,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 +959,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 +984,7 @@
       if (info->handle_duplicates == DUP_REPLACE &&
           table->next_number_field &&
           key_nr == table->s->next_number_index &&
-	  table->file->auto_increment_column_changed)
+	  (thd->insert_id_for_cur_row > 0))
 	goto err;
       if (table->file->table_flags() & HA_DUPP_POS)
       {
@@ -1049,12 +1043,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,7 +1050,25 @@
           goto err;
 	}
         info->updated++;
-
+        /*
+          Some users are asking that if ON DUP KEY UPDATE updated a row
+          instead of inserting one, then a SELECT LAST_INSERT_ID() returns the
+          id of the updated row. See BUG#19243.
+          It is not so stupid. If we wanted to do that, that would be:
+        */
+#ifdef FOR_DISCUSSION_IN_MYSQL
+        if (table->next_number_field)
+        {
+          /*
+            We don't call thd->record_insert_id_for_cur_row() to not touch
+            first_insert_id_in_cur_stmt_for_binlog, in this case:
+            INSERT VALUES (null,dup_value),(null,not_dup_value);
+            then it is the value of NULL which we want to store in binlog.
+          */
+          thd->insert_id_for_cur_row= table->next_number_field->val_int();
+          thd->record_first_successful_insert_id_in_cur_stmt();
+        }
+#endif
         trg_error= (table->triggers &&
                     table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
                                                       TRG_ACTION_AFTER, TRUE));
@@ -1088,16 +1094,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 +1133,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 +1159,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 +1226,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 +1620,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 +1999,8 @@
 
     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_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 +2164,6 @@
                              enum_duplicates duplic,
                              bool ignore_check_option_errors)
   :table_list(table_list_par), table(table_par), fields(fields_par),
-   last_insert_id(0),
    insert_into_view(table_list_par && table_list_par->view != 0)
 {
   bzero((char*) &info,sizeof(info));
@@ -2374,8 +2381,6 @@
         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 +2445,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,
@@ -2484,8 +2487,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 +2516,8 @@
     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);
+  ::send_ok(thd, (ulong) thd->row_count_func,
+            thd->first_successful_insert_id_in_cur_stmt, 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-13 00:04:20 +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-13 00:04:20 +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-13 00:04:21 +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-13 00:04:21 +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-13 00:04:21 +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-13 00:04:21 +02:00
@@ -693,7 +693,7 @@
     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);
+	    thd->first_successful_insert_id_in_cur_stmt, buff);
     DBUG_PRINT("info",("%d records updated",updated));
   }
   thd->count_cuted_fields= CHECK_FIELD_IGNORE;		/* calc cuted fields */
@@ -1676,6 +1676,6 @@
   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);
+            thd->first_successful_insert_id_in_cur_stmt, buff);
   return FALSE;
 }

--- 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-13 00:04:19 +02:00
@@ -144,6 +144,23 @@
 # This should be exactly one greater than in the previous call.
 select last_insert_id();
 
+# BUG#20339 - store 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.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-13 00:04:20 +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.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-13 00:04:19 +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()
+2
+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()
+2
+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-13 00:04:20 +02:00
@@ -286,3 +286,73 @@
 --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;
+
+# see what we return in the scenario of BUG#19243
+# "wrong LAST_INSERT_ID() after ON DUPLICATE KEY UPDATE"
+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-13 00:04:19 +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-13 00:04:20 +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,10 @@
 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;
+  /* QQ: why this sys_var_tmp thing ? */
+  thd->sys_var_tmp.long_value= (long)
+    thd->read_first_successful_insert_id_in_prev_stmt();
+  return (byte*) &thd->first_successful_insert_id_in_prev_stmt;
 }
 
 
@@ -2577,7 +2580,7 @@
 byte *sys_var_insert_id::value_ptr(THD *thd, enum_var_type type,
 				   LEX_STRING *base)
 {
-  return (byte*) &thd->current_insert_id;
+  return (byte*) &thd->next_insert_id;
 }
 
 

--- 1.53/sql/ha_partition.cc	2006-06-02 22:22:50 +02:00
+++ 1.54/sql/ha_partition.cc	2006-06-13 00:04:20 +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-13 00:04:20 +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-13 00:04:20 +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-13 00:04:20 +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-13 00:04:19 +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.190/tests/mysql_client_test.c	2006-05-24 14:33:38 +02:00
+++ 1.191/tests/mysql_client_test.c	2006-06-13 00:04:21 +02:00
@@ -14947,7 +14947,149 @@
 
   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 == 0); /* behaviour change, was 300 */
+  rc= mysql_query(mysql, "drop table t1");
+  /* table with auto_increment column */
+  rc= mysql_query(mysql, "create table t1 (f1 int not null primary key auto_increment, f2 varchar(255))");
+  myquery(rc);
+  rc= mysql_query(mysql, "insert into t1 values (1,'a')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 1);
+  rc= mysql_query(mysql, "insert into t1 values (null,'b')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 2);
+  rc= mysql_query(mysql, "insert into t1 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
+  */
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "insert into t1 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 t1 values (10,'a'),(11,'b')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 11);
+  rc= mysql_query(mysql, "insert into t1 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
+  */
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "insert into t1 values (null,'a'),(null,'b')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 14);
+  rc= mysql_query(mysql, "insert into t1 select null,'a' union select null,'b'");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 16);
+  rc= mysql_query(mysql, "insert into t1 select 12,'a' union select 13,'b'");
+  myquery_r(rc);
+  rc= mysql_query(mysql, "insert ignore into t1 select 12,'a' union select 13,'b'");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "insert into t1 values (12,'a'),(13,'b')");
+  myquery_r(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "insert ignore into t1 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 t1 values (null,'e'),(12,'a'),(13,'b')");
+  myquery_r(rc);
+  rc= mysql_query(mysql, "insert into t1 values (null,'e'),(12,'a'),(13,'b'),(25,'g')");
+  myquery_r(rc);
+  rc= mysql_query(mysql, "insert into t1 values (null,last_insert_id(300))");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  /* according to manual, this might be 300... ? */
+  DIE_UNLESS(res == 20);
+  /* If first autogenerated number fails and 2nd works: */
+  rc= mysql_query(mysql, "drop table t1");
+  myquery(rc);
+  rc= mysql_query(mysql, "create table t1 (f1 int not null primary key "
+                  "auto_increment, f2 varchar(255), unique (f2))");
+  myquery(rc);
+  rc= mysql_query(mysql, "insert into t1 values (null,'e')");
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 1);
+  rc= mysql_query(mysql, "insert ignore into t1 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 t1 values (null,'e'),(12,'c'),(null,'d')");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 13); /* behaviour change, was 3 */
+  /*
+    Old code of UPDATE statements (mysql_update()) tested insert_id_used in
+    send_ok(), how could this variable ever be true there, we test that in
+    fact send_ok() always returns an id of 0 for UPDATE.
+  */
+  rc= mysql_query(mysql, "update t1 set f1=14 where f1=12");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "update t1 set f1=NULL where f1=14");
+  myquery(rc);
+  res= mysql_insert_id(mysql);
+  DIE_UNLESS(res == 0);
+  rc= mysql_query(mysql, "drop table t1");
+  myquery(rc);
+}
+
+/*
   Read and parse arguments and MySQL options from my.cnf
 */
 
@@ -15015,6 +15157,7 @@
 
 
 static struct my_tests_st my_tests[]= {
+  { "test_mysql_insert_id", test_mysql_insert_id },
   { "test_view_sp_list_fields", test_view_sp_list_fields},
   { "client_query", client_query },
   { "test_prepare_insert_update", test_prepare_insert_update},
Thread
bk commit into 5.1 tree (guilhem:1.2178) BUG#20188guilhem13 Jun