MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:February 9 2007 3:48pm
Subject:bk commit into 5.1 tree (guilhem:1.2411) BUG#25507
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@stripped, 2007-02-09 16:48:07+01:00, guilhem@stripped +3 -0
  Fix for BUG#25507 "multi-row insert delayed + auto increment causes
  duplicate key entries on slave" (two concurrrent connections doing
  multi-row INSERT DELAYED to insert into an auto_increment column,
  caused replication slave to stop with "duplicate key error" (and
  binlog was wrong), and BUG#26116 "If multi-row INSERT
  DELAYED has errors, statement-based binlogging breaks" (the binlog
  was not accounting for all rows inserted, or slave could stop).
  The fix is that: in statement-based binlogging, a multi-row INSERT
  DELAYED is silently converted to a non-delayed INSERT.
  This is supposed to not affect many 5.1 users as in 5.1, the default
  binlog format is "mixed", which does not have the bug (the bug is
  only with binlog_format=STATEMENT).
  Bug will be fixed in 5.0 but I first submit a patch for 5.1 because:
  - 5.1 has the test framework (mysqlslap) needed to repeat the bug,
  and 5.0 does not (so, 5.0's patch will have no testcase)
  - the code fix of 5.1 is the more complex (5.0's code fix is the same
  after simplification because in 5.0 we only have statement-based
  binlogging).
  We should document how the system delayed_insert thread decides of
  its binlog format (which is not modified by this patch):
  this decision is taken when the thread is created
  and holds until it is terminated (is not affected by any later change
  via SET GLOBAL BINLOG_FORMAT). It is also not affected by the binlog
  format of the connection which issues INSERT DELAYED (this binlog
  format does not affect how the row will be binlogged).
  If one wants to change the binlog format of its server with SET
  GLOBAL BINLOG_FORMAT, it should do FLUSH TABLES to be sure all
  delayed_insert threads terminate and thus new threads are created,
  taking into account the new format.
  Because of this, this patch allows itself to test the global
  binlog_format to decide if INSERT DELAYED should be non-delayed,
  and to test it without its mutex LOCK_global_system_variables
  (which is interesting, as the condition is rarely true in 5.1 so
  better avoid the mutex)

  mysql-test/r/rpl_insert.result@stripped, 2007-02-09 16:48:02+01:00, guilhem@stripped +27 -2
    result update. Data on master and slave match.

  mysql-test/t/rpl_insert.test@stripped, 2007-02-09 16:48:02+01:00, guilhem@stripped +47 -3
    tests for BUG#25507 (lauch many concurrent INSERT DELAYED into an auto_inc
    column and see if they cause duplicates) and BUG#26116 (see if one error
    at first row on master makes the slave's data incorrect).

  sql/sql_insert.cc@stripped, 2007-02-09 16:48:02+01:00, guilhem@stripped +22 -0
    A multi-row INSERT DELAYED cannot be recorded to a statement-based
    binlog in a way that describes the insertions actually done;
    in that case we fallback to a non-delayed INSERT.

# 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-rpl-25507

--- 1.244/sql/sql_insert.cc	2007-02-09 16:48:15 +01:00
+++ 1.245/sql/sql_insert.cc	2007-02-09 16:48:15 +01:00
@@ -341,6 +341,28 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
       (duplic == DUP_UPDATE))
     lock_type=TL_WRITE;
 #endif
+  if ((lock_type == TL_WRITE_DELAYED) &&
+      (global_system_variables.binlog_format == BINLOG_FORMAT_STMT) &&
+      log_on && mysql_bin_log.is_open() &&
+      (values_list.elements > 1))
+  {
+    /*
+      Statement-based binary logging does not work in this case, because:
+      a) two concurrent statements may have their rows intermixed in the
+      queue, leading to autoincrement replication problems on slave (because
+      the values generated used for one statement don't depend only on the
+      value generated for the first row of this statement, so are not
+      replicable)
+      b) if first row of the statement has an error the full statement is
+      not binlogged, while next rows of the statement may be inserted.
+      c) if first row succeeds, statement is binlogged immediately with a
+      zero error code (i.e. "no error"), if then second row fails, query
+      will fail on slave too and slave will stop (wrongly believing that the
+      master got no error).
+      So we fallback to non-delayed INSERT.
+    */
+    lock_type= TL_WRITE;
+  }
   table_list->lock_type= lock_type;
 
 #ifndef EMBEDDED_LIBRARY

--- 1.3/mysql-test/r/rpl_insert.result	2007-02-09 16:48:15 +01:00
+++ 1.4/mysql-test/r/rpl_insert.result	2007-02-09 16:48:15 +01:00
@@ -10,12 +10,37 @@ start slave;
 CREATE SCHEMA IF NOT EXISTS mysqlslap;
 USE mysqlslap;
 CREATE TABLE t1 (id INT, name VARCHAR(64));
-SELECT COUNT(*) FROM mysqlslap.t1;
+SELECT COUNT(*) FROM t1;
 COUNT(*)
 5000
-SELECT COUNT(*) FROM mysqlslap.t1;
+use mysqlslap;
+SELECT COUNT(*) FROM t1;
 COUNT(*)
 5000
+set @old_global_binlog_format = @@global.binlog_format;
+set @@global.binlog_format = statement;
+drop table t1;
+CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64));
+FLUSH TABLE t1;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+5000
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+5000
+truncate table t1;
+insert delayed into t1 values(1, "my name");
+insert delayed into t1 values(1, "is Bond"), (100000, "James Bond");
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+flush table t1;
+select * from t1;
+id	name
+1	my name
+select * from t1;
+id	name
+1	my name
+drop table t1;
+set @@global.binlog_format = @old_global_binlog_format;
 #
 # Cleanup
 #

--- 1.6/mysql-test/t/rpl_insert.test	2007-02-09 16:48:15 +01:00
+++ 1.7/mysql-test/t/rpl_insert.test	2007-02-09 16:48:15 +01:00
@@ -16,10 +16,11 @@ CREATE TABLE t1 (id INT, name VARCHAR(64
 let $query = "INSERT DELAYED INTO t1 VALUES (1, 'Dr. No'), (2, 'From Russia With Love'), (3, 'Goldfinger'), (4, 'Thunderball'), (5, 'You Only Live Twice')";
 --exec $MYSQL_SLAP --silent --concurrency=5 --iterations=200 --query=$query --delimiter=";"
 
+# INSERT DELAYED may take time to actually insert;
 # Wait until all the 5000 inserts has been inserted into the table
 --disable_query_log
 let $counter= 300; # Max 30 seconds wait
-while (`select count(*)!=5000 from mysqlslap.t1`)
+while (`select count(*)!=5000 from t1`)
 {
   sleep 0.1;
   dec $counter;
@@ -30,9 +31,52 @@ while (`select count(*)!=5000 from mysql
 }
 --enable_query_log
 
-SELECT COUNT(*) FROM mysqlslap.t1;
+SELECT COUNT(*) FROM t1;
 sync_slave_with_master;
-SELECT COUNT(*) FROM mysqlslap.t1;
+use mysqlslap;
+SELECT COUNT(*) FROM t1;
+
+#
+# BUG#25507 "multi-row insert delayed + auto increment causes
+# duplicate key entries on slave";
+# happened only in statement-based binlogging.
+#
+
+connection master;
+set @old_global_binlog_format = @@global.binlog_format;
+set @@global.binlog_format = statement;
+drop table t1;
+CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64));
+let $query = "INSERT DELAYED INTO t1 VALUES (null, 'Dr. No'), (null, 'From Russia With Love'), (null, 'Goldfinger'), (null, 'Thunderball'), (null, 'You Only Live Twice')";
+--exec $MYSQL_SLAP --silent --concurrency=5 --iterations=200 --query=$query --delimiter=";"
+
+FLUSH TABLE t1; # another way to be sure INSERT DELAYED has inserted
+SELECT COUNT(*) FROM t1;
+# when bug existed slave failed below ("duplicate key" error at random INSERT)
+sync_slave_with_master;
+SELECT COUNT(*) FROM t1;
+
+#
+# BUG#26116 "If multi-row INSERT DELAYED has errors,
+# statement-based binlogging breaks";
+# happened only in statement-based binlogging.
+#
+
+connection master;
+truncate table t1;
+insert delayed into t1 values(1, "my name");
+# statement below will be converted to non-delayed INSERT and so
+# will stop at first error, guaranteeing replication.
+--error ER_DUP_ENTRY
+insert delayed into t1 values(1, "is Bond"), (100000, "James Bond");
+flush table t1; # to wait for INSERT DELAYED to be done
+select * from t1;
+sync_slave_with_master;
+# when bug existed, t1 on slave had different content from on master
+select * from t1;
+connection master;
+drop table t1;
+set @@global.binlog_format = @old_global_binlog_format;
 
 --echo #
 --echo # Cleanup
Thread
bk commit into 5.1 tree (guilhem:1.2411) BUG#25507Guilhem Bichot9 Feb