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

ChangeSet@stripped, 2007-02-08 11:50:15+01:00, guilhem@stripped +10 -0
  Fix for BUG#24432
  "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values".
  When in an INSERT ON DUPLICATE KEY UPDATE, using
  an autoincrement column, we inserted some autogenerated values and
  also updated some rows, some autogenerated values were not used
  (for example, even if 10 was the largest autoinc value in the table
  at the start of the statement, 12 could be the first autogenerated
  value inserted by the statement, instead of 11). One autogenerated
  value was lost per updated row. Led to exhausting the range of the
  autoincrement column faster.
  Bug introduced by fix of BUG#20188; present since 5.0.24 and 5.1.12.
  This bug breaks replication from a pre-5.0.24 master.
  But the present bugfix, as it makes INSERT ON DUP KEY UPDATE
  behave like pre-5.0.24, breaks replication from a [5.0.24,5.0.34]
  master to a fixed (5.0.36) slave! To warn users against this when
  they upgrade their slave, as agreed with the support team, we add
  code for a fixed slave to detect that it is connected to a buggy
  master in a situation (INSERT ON DUP KEY UPDATE into autoinc column)
  likely to break replication, in which case it cannot replicate so
  stops and prints a message to the slave's error log and to SHOW SLAVE
  STATUS.
  For 5.0.36->[5.0.24,5.0.34] replication we cannot warn as master
  does not know the slave's version (but we always recommended to users
  to have slave at least as new as master).
  As agreed with support, I'll also ask for an alert to be put into
  the MySQL Network Monitoring and Advisory Service.

  mysql-test/r/rpl_insert_id.result@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +58 -0
    results to check the bugfix; without the bugfix, you would see, in
    master and slave:
    "3,2" instead of "2,2" for the INSERT VALUES testcase,
    "11,6,..." instead of "6,6,..." for the INSERT SELECT testcase.

  mysql-test/r/rpl_known_bugs_detection.result@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +133 -0
    see that SHOW SLAVE STATUS prints information that slave found a bug
    in master, and does not execute the dangerous event (table stays
    empty).

  mysql-test/r/rpl_known_bugs_detection.result@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +0 -0

  mysql-test/t/rpl_insert_id.test@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +53 -0
    testing that BUG#24432 is fixed

  mysql-test/t/rpl_known_bugs_detection-master.opt@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +1 -0
    pass debug symbol to make the master pretend it has BUG#24432

  mysql-test/t/rpl_known_bugs_detection-master.opt@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +0 -0

  mysql-test/t/rpl_known_bugs_detection.test@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +90 -0
    new test to see if bug detection by slave works

  mysql-test/t/rpl_known_bugs_detection.test@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +0 -0

  sql/log_event.cc@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +37 -0
    A trick to force the master to pretend it is old and features BUG#24432.
    To do fast lookups in the list of known bugs by version, we compute
    the 3 X.Y.Z numbers from the master's version string and cache that
    into a new member Format_description_log_event::server_version_split.
    We do this computation in the event's two constructors.
    A simple prevention against buffer overrun when reading the master's
    version from a binlog event (assume the event is corrupted on disk,
    and so the version string on disk is longer than ST_SERVER_VER_LEN
    (50), then we would not get a closing 0 at the end of the class member.

  sql/log_event.h@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +2 -0
    new member to hold the "split server version" (3 numbers X.Y.Z),
    and a method to compute this from the version string.

  sql/slave.cc@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +64 -0
    a function which tells, based on master's version (as found
    in the Format_description event in the relay log being executed),
    if master can have a certain bug. This function uses a list of
    bug_id / first_version_with_bug / first_version_with_fix.
    If the test is positive, a short error message is put into SHOW SLAVE
    STATUS, and a verbose message is put into the slave's error log.
    The caller is expected to stop the slave in this case.

  sql/slave.h@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +1 -0
    new function to test if the replication master has a bug

  sql/sql_insert.cc@stripped, 2007-02-08 11:50:12+01:00, guilhem@stripped +19 -1
    Fix for BUG#24432:t he reason was a misplaced restore_auto_increment() 
    (misplaced when fixing BUG#20188). Indeed, when updating the row,
    it is clear that the autogenerated auto_increment value will not be
    used for this row (and if by "chance" the autoinc value present
    in the updated row is >= to the not used autogenerated value,
    adjust_next_insert_id_after_explicit_value() will fix next_insert_id).
    We also add code for a fixed slave to detect that it is connected to
    a buggy master (in which case it cannot replicate so stops).

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

--- 1.223/sql/log_event.cc	2007-02-08 11:50:23 +01:00
+++ 1.224/sql/log_event.cc	2007-02-08 11:50:23 +01:00
@@ -2047,6 +2047,8 @@ Start_log_event_v3::Start_log_event_v3(c
   binlog_version= uint2korr(buf+ST_BINLOG_VER_OFFSET);
   memcpy(server_version, buf+ST_SERVER_VER_OFFSET,
 	 ST_SERVER_VER_LEN);
+  // prevent overrun if log is corrupted on disk
+  server_version[ST_SERVER_VER_LEN-1]= 0;
   created= uint4korr(buf+ST_CREATED_OFFSET);
   /* We use log_pos to mark if this was an artificial event or not */
   artificial_event= (log_pos == 0);
@@ -2170,6 +2172,8 @@ Format_description_log_event(uint8 binlo
   switch (binlog_ver) {
   case 4: /* MySQL 5.0 */
     memcpy(server_version, ::server_version, ST_SERVER_VER_LEN);
+    DBUG_EXECUTE_IF("pretend_version_50034_in_binlog",
+                    strmov(server_version, "5.0.34"););
     common_header_len= LOG_EVENT_HEADER_LEN;
     number_of_event_types= LOG_EVENT_TYPES;
     /* we'll catch my_malloc() error in is_valid() */
@@ -2241,6 +2245,7 @@ Format_description_log_event(uint8 binlo
     post_header_len= 0; /* will make is_valid() fail */
     break;
   }
+  calc_server_version_split();
 }
 
 
@@ -2280,6 +2285,7 @@ Format_description_log_event(const char*
   post_header_len= (uint8*) my_memdup((byte*)buf+ST_COMMON_HEADER_LEN_OFFSET+1,
                                       number_of_event_types*
                                       sizeof(*post_header_len), MYF(0));
+  calc_server_version_split();
   DBUG_VOID_RETURN;
 }
 
@@ -2379,6 +2385,37 @@ int Format_description_log_event::exec_e
   DBUG_RETURN(Start_log_event_v3::exec_event(rli));
 }
 #endif
+
+
+/**
+   Splits the event's 'server_version' string into three numeric pieces stored
+   into 'server_version_split':
+   X.Y.Zabc (X,Y,Z numbers, a not a digit) -> {X,Y,Z}
+   X.Yabc -> {X,Y,0}
+   Xabc -> {X,0,0}
+   'server_version_split' is then used for lookups to find if the server which
+   created this event has some known bug.
+*/
+void Format_description_log_event::calc_server_version_split()
+{
+  char *p= server_version, *r;
+  ulong number;
+  for (uint i= 0; i<=2; i++)
+  {
+    number= strtoul(p, &r, 10);
+    server_version_split[i]= (uchar)number;
+    DBUG_ASSERT(number < 256); // fit in uchar
+    p= r;
+    DBUG_ASSERT(!((i == 0) && (*r != '.'))); // should be true in practice
+    if (*r == '.')
+      p++; // skip the dot
+  }
+  DBUG_PRINT("info",("Format_description_log_event::server_version_split:"
+                     " '%s' %d %d %d", server_version,
+                     server_version_split[0],
+                     server_version_split[1], server_version_split[2]));
+}
+
 
   /**************************************************************************
         Load_log_event methods

--- 1.130/sql/log_event.h	2007-02-08 11:50:23 +01:00
+++ 1.131/sql/log_event.h	2007-02-08 11:50:23 +01:00
@@ -1102,6 +1102,7 @@ public:
   uint8 number_of_event_types;
   /* The list of post-headers' lengthes */
   uint8 *post_header_len;
+  uchar server_version_split[3];
 
   Format_description_log_event(uint8 binlog_ver, const char* server_ver=0);
 
@@ -1133,6 +1134,7 @@ public:
     */
     return FORMAT_DESCRIPTION_HEADER_LEN;
   }
+  void calc_server_version_split();
 };
 
 

--- 1.287/sql/slave.cc	2007-02-08 11:50:23 +01:00
+++ 1.288/sql/slave.cc	2007-02-08 11:50:23 +01:00
@@ -5165,6 +5165,70 @@ end:
 }
 
 
+/**
+   Detects, based on master's version (as found in the relay log), if master
+   has a certain bug.
+   @param rli RELAY_LOG_INFO which tells the master's version
+   @param bug_id Number of the bug as found in bugs.mysql.com
+   @return TRUE if master has the bug, FALSE if it does not.
+*/
+bool rpl_master_has_bug(RELAY_LOG_INFO *rli, uint bug_id)
+{
+  struct st_version_range_for_one_bug {
+    uint        bug_id;
+    const uchar introduced_in[3]; // first version with bug
+    const uchar fixed_in[3];      // first version with fix
+  };
+  static struct st_version_range_for_one_bug versions_for_all_bugs[]=
+  {
+    {24432, { 5, 0, 24 }, { 5, 0, 36 } },
+    {24432, { 5, 1, 12 }, { 5, 1, 15 } }
+  };
+  const uchar *master_ver=
+    rli->relay_log.description_event_for_exec->server_version_split;
+
+  DBUG_ASSERT(sizeof(rli->relay_log.description_event_for_exec->server_version_split) == 3);
+
+  for (uint i= 0;
+       i < sizeof(versions_for_all_bugs)/sizeof(*versions_for_all_bugs);i++)
+  {
+    const uchar *introduced_in= versions_for_all_bugs[i].introduced_in,
+      *fixed_in= versions_for_all_bugs[i].fixed_in;
+    if ((versions_for_all_bugs[i].bug_id == bug_id) &&
+        (memcmp(introduced_in, master_ver, 3) <= 0) &&
+        (memcmp(fixed_in,      master_ver, 3) >  0))
+    {
+      // a verbose message for the error log
+      slave_print_error(rli, ER_UNKNOWN_ERROR,
+                        "According to the master's version ('%s'),"
+                        " it is probable that master suffers from this bug:"
+                        " http://bugs.mysql.com/bug.php?id=%u"
+                        " and thus replicating the current binary log event"
+                        " may make the slave's data become different from the"
+                        " master's data."
+                        " To take no risk, slave refuses to replicate"
+                        " this event and stops."
+                        " We recommend that all updates be stopped on the"
+                        " master and slave, that the data of both be"
+                        " manually synchronized,"
+                        " that master's binary logs be deleted,"
+                        " that master be upgraded to a version at least"
+                        " equal to '%d.%d.%d'. Then replication can be"
+                        " restarted.",
+                        rli->relay_log.description_event_for_exec->server_version,
+                        bug_id,
+                        fixed_in[0], fixed_in[1], fixed_in[2]);
+      // a short message for SHOW SLAVE STATUS (message length constraints)
+      my_printf_error(ER_UNKNOWN_ERROR, "master may suffer from"
+                      " http://bugs.mysql.com/bug.php?id=%u"
+                      " so slave stops; check error log on slave"
+                      " for more info", MYF(0), bug_id);
+      return TRUE;
+    }
+  }
+  return FALSE;
+}
+
 #ifdef HAVE_EXPLICIT_TEMPLATE_INSTANTIATION
 template class I_List_iterator<i_string>;
 template class I_List_iterator<i_string_pair>;

--- 1.213/sql/sql_insert.cc	2007-02-08 11:50:23 +01:00
+++ 1.214/sql/sql_insert.cc	2007-02-08 11:50:23 +01:00
@@ -58,6 +58,7 @@
 #include "sp_head.h"
 #include "sql_trigger.h"
 #include "sql_select.h"
+#include "slave.h"
 
 static int check_null_fields(THD *thd,TABLE *entry);
 #ifndef EMBEDDED_LIBRARY
@@ -468,6 +469,14 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
   thd->cuted_fields = 0L;
   table->next_number_field=table->found_next_number_field;
 
+#ifdef HAVE_REPLICATION
+  if (thd->slave_thread &&
+      (info.handle_duplicates == DUP_UPDATE) &&
+      (table->next_number_field != NULL) &&
+      rpl_master_has_bug(&active_mi->rli, 24432))
+    goto abort;
+#endif
+
   error=0;
   id=0;
   thd->proc_info="update";
@@ -1133,11 +1142,11 @@ int write_record(THD *thd, TABLE *table,
         if (res == VIEW_CHECK_ERROR)
           goto before_trg_err;
 
+        table->file->restore_auto_increment();
         if ((error=table->file->update_row(table->record[1],table->record[0])))
 	{
 	  if ((error == HA_ERR_FOUND_DUPP_KEY) && info->ignore)
           {
-            table->file->restore_auto_increment();
             goto ok_or_after_trg_err;
           }
           goto err;
@@ -2369,6 +2378,15 @@ select_insert::prepare(List<Item> &value
   }
   restore_record(table,s->default_values);		// Get empty record
   table->next_number_field=table->found_next_number_field;
+
+#ifdef HAVE_REPLICATION
+  if (thd->slave_thread &&
+      (info.handle_duplicates == DUP_UPDATE) &&
+      (table->next_number_field != NULL) &&
+      rpl_master_has_bug(&active_mi->rli, 24432))
+    DBUG_RETURN(1);
+#endif
+
   thd->cuted_fields=0;
   if (info.ignore || info.handle_duplicates != DUP_ERROR)
     table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
--- New file ---
+++ mysql-test/r/rpl_known_bugs_detection.result	07/02/08 11:50:12
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
UNIQUE(b));
INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
SELECT * FROM t1;
a	b
1	10
2	2
show slave status;;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	#
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	1105
Last_Error	Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'test'. Query: 'INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10'
Skip_Counter	0
Exec_Master_Log_Pos	238
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
SELECT * FROM t1;
a	b
stop slave;
reset slave;
reset master;
drop table t1;
start slave;
CREATE TABLE t1 (
id bigint(20) unsigned NOT NULL auto_increment,
field_1 int(10) unsigned NOT NULL,
field_2 varchar(255) NOT NULL,
field_3 varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY field_1 (field_1, field_2)
);
CREATE TABLE t2 (
field_a int(10) unsigned NOT NULL,
field_b varchar(255) NOT NULL,
field_c varchar(255) NOT NULL
);
INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
INSERT INTO t1 (field_1, field_2, field_3)
SELECT t2.field_a, t2.field_b, t2.field_c
FROM t2
ON DUPLICATE KEY UPDATE
t1.field_3 = t2.field_c;
INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
INSERT INTO t1 (field_1, field_2, field_3)
SELECT t2.field_a, t2.field_b, t2.field_c
FROM t2
ON DUPLICATE KEY UPDATE
t1.field_3 = t2.field_c;
SELECT * FROM t1;
id	field_1	field_2	field_3
1	1	a	1a
2	2	b	2b
3	3	c	3c
4	4	d	4d
5	5	e	5e
6	6	f	6f
show slave status;;
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	#
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	#
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	1105
Last_Error	Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'test'. Query: 'INSERT INTO t1 (field_1, field_2, field_3)
SELECT t2.field_a, t2.field_b, t2.field_c
FROM t2
ON DUPLICATE KEY UPDATE
t1.field_3 = t2.field_c'
Skip_Counter	0
Exec_Master_Log_Pos	1270
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
SELECT * FROM t1;
id	field_1	field_2	field_3
drop table t1, t2;
drop table t1, t2;

--- New file ---
+++ mysql-test/t/rpl_known_bugs_detection-master.opt	07/02/08 11:50:12
--loose-debug=d,pretend_version_50034_in_binlog

--- New file ---
+++ mysql-test/t/rpl_known_bugs_detection.test	07/02/08 11:50:12
# Test to see if slave can detect certain known bugs present
# on the master, and appropriately decides to stop
# (assuming the bug is fixed in the slave, slave cannot of course
# imitate the bug, so it has to stop).

source include/have_debug.inc;
source include/master-slave.inc;

#
# This is to test that slave properly detects if
# master may suffer from:
# BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values"
# (i.e. on master, INSERT ON DUPLICATE KEY UPDATE is used and manipulates
# an auto_increment column, and is binlogged statement-based).
#

# testcase with INSERT VALUES
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
UNIQUE(b));
sync_slave_with_master;
connection master;
INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
SELECT * FROM t1;
connection slave;
wait_for_slave_to_stop;
# show the error message
--replace_column 1 # 4 # 7 # 8 # 9 # 23 # 33 #
--query_vertical show slave status;
# show that it was not replicated
SELECT * FROM t1;

# restart replication for the next testcase
stop slave;
reset slave;
connection master;
reset master;
drop table t1;
connection slave;
start slave;

# testcase with INSERT SELECT
connection master;
CREATE TABLE t1 (
  id bigint(20) unsigned NOT NULL auto_increment,
  field_1 int(10) unsigned NOT NULL,
  field_2 varchar(255) NOT NULL,
  field_3 varchar(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY field_1 (field_1, field_2)
);
CREATE TABLE t2 (
  field_a int(10) unsigned NOT NULL,
  field_b varchar(255) NOT NULL,
  field_c varchar(255) NOT NULL
);
INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
sync_slave_with_master;
connection master;
# Updating table t1 based on values from table t2
INSERT INTO t1 (field_1, field_2, field_3)
SELECT t2.field_a, t2.field_b, t2.field_c
FROM t2
ON DUPLICATE KEY UPDATE
t1.field_3 = t2.field_c;
# Inserting new record into t2
INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
# Updating t1 again
INSERT INTO t1 (field_1, field_2, field_3)
SELECT t2.field_a, t2.field_b, t2.field_c
FROM t2
ON DUPLICATE KEY UPDATE
t1.field_3 = t2.field_c;
SELECT * FROM t1;
connection slave;
wait_for_slave_to_stop;
# show the error message
--replace_column 1 # 4 # 7 # 8 # 9 # 23 # 33 #
--query_vertical show slave status;
# show that it was not replicated
SELECT * FROM t1;
connection master;
drop table t1, t2;
connection slave;
drop table t1, t2;

# End of 5.0 tests


--- 1.21/mysql-test/r/rpl_insert_id.result	2007-02-08 11:50:23 +01:00
+++ 1.22/mysql-test/r/rpl_insert_id.result	2007-02-08 11:50:23 +01:00
@@ -234,6 +234,64 @@ n	b
 2	100
 3	350
 drop table t1;
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
+UNIQUE(b));
+INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
+SELECT * FROM t1;
+a	b
+1	10
+2	2
+SELECT * FROM t1;
+a	b
+1	10
+2	2
+drop table t1;
+CREATE TABLE t1 (
+id bigint(20) unsigned NOT NULL auto_increment,
+field_1 int(10) unsigned NOT NULL,
+field_2 varchar(255) NOT NULL,
+field_3 varchar(255) NOT NULL,
+PRIMARY KEY (id),
+UNIQUE KEY field_1 (field_1, field_2)
+);
+CREATE TABLE t2 (
+field_a int(10) unsigned NOT NULL,
+field_b varchar(255) NOT NULL,
+field_c varchar(255) NOT NULL
+);
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
+INSERT INTO t1 (field_1, field_2, field_3)
+SELECT t2.field_a, t2.field_b, t2.field_c
+FROM t2
+ON DUPLICATE KEY UPDATE
+t1.field_3 = t2.field_c;
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
+INSERT INTO t1 (field_1, field_2, field_3)
+SELECT t2.field_a, t2.field_b, t2.field_c
+FROM t2
+ON DUPLICATE KEY UPDATE
+t1.field_3 = t2.field_c;
+SELECT * FROM t1;
+id	field_1	field_2	field_3
+1	1	a	1a
+2	2	b	2b
+3	3	c	3c
+4	4	d	4d
+5	5	e	5e
+6	6	f	6f
+SELECT * FROM t1;
+id	field_1	field_2	field_3
+1	1	a	1a
+2	2	b	2b
+3	3	c	3c
+4	4	d	4d
+5	5	e	5e
+6	6	f	6f
+drop table t1, t2;
 DROP PROCEDURE IF EXISTS p1;
 DROP TABLE IF EXISTS t1, t2;
 SELECT LAST_INSERT_ID(0);

--- 1.22/mysql-test/t/rpl_insert_id.test	2007-02-08 11:50:23 +01:00
+++ 1.23/mysql-test/t/rpl_insert_id.test	2007-02-08 11:50:23 +01:00
@@ -246,6 +246,59 @@ connection master;
 drop table t1;
 
 #
+# BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values"
+#
+
+# testcase with INSERT VALUES
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
+UNIQUE(b));
+INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+connection master;
+drop table t1;
+
+# tescase with INSERT SELECT
+CREATE TABLE t1 (
+  id bigint(20) unsigned NOT NULL auto_increment,
+  field_1 int(10) unsigned NOT NULL,
+  field_2 varchar(255) NOT NULL,
+  field_3 varchar(255) NOT NULL,
+  PRIMARY KEY (id),
+  UNIQUE KEY field_1 (field_1, field_2)
+);
+CREATE TABLE t2 (
+  field_a int(10) unsigned NOT NULL,
+  field_b varchar(255) NOT NULL,
+  field_c varchar(255) NOT NULL
+);
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
+# Updating table t1 based on values from table t2
+INSERT INTO t1 (field_1, field_2, field_3)
+SELECT t2.field_a, t2.field_b, t2.field_c
+FROM t2
+ON DUPLICATE KEY UPDATE
+t1.field_3 = t2.field_c;
+# Inserting new record into t2
+INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
+# Updating t1 again
+INSERT INTO t1 (field_1, field_2, field_3)
+SELECT t2.field_a, t2.field_b, t2.field_c
+FROM t2
+ON DUPLICATE KEY UPDATE
+t1.field_3 = t2.field_c;
+SELECT * FROM t1;
+sync_slave_with_master;
+SELECT * FROM t1;
+connection master;
+drop table t1, t2;
+
+#
 # BUG#20339: stored procedure using LAST_INSERT_ID() does not
 # replicate statement-based 
 #

--- 1.99/sql/slave.h	2007-02-08 11:50:23 +01:00
+++ 1.100/sql/slave.h	2007-02-08 11:50:23 +01:00
@@ -533,6 +533,7 @@ void table_rule_ent_hash_to_str(String* 
 void table_rule_ent_dynamic_array_to_str(String* s, DYNAMIC_ARRAY* a);
 bool show_master_info(THD* thd, MASTER_INFO* mi);
 bool show_binlog_info(THD* thd);
+bool rpl_master_has_bug(RELAY_LOG_INFO *rli, uint bug_id);
 
 /* See if the query uses any tables that should not be replicated */
 bool tables_ok(THD* thd, TABLE_LIST* tables);
Thread
bk commit into 5.0 tree (guilhem:1.2392) BUG#24432Guilhem Bichot8 Feb