List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:February 15 2007 2:39pm
Subject:bk commit into 5.0 tree (guilhem:1.2394) BUG#25507
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-15 15:39:03+01:00, guilhem@stripped +5 -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: if (statement-based) binlogging is on, a multi-row
  INSERT DELAYED is silently converted to a non-delayed INSERT.
  Note: it is not possible to test BUG#25507 in 5.0 (requires mysqlslap),
  so it is tested only in the changeset for 5.1. However, BUG#26116
  is tested here, and the fix for BUG#25507 is the same code change.

  mysql-test/r/innodb-replace.result@stripped, 2007-02-15 15:39:01+01:00, guilhem@stripped +2 -2
    result update

  mysql-test/r/rpl_insert_delayed.result@stripped, 2007-02-15 15:39:01+01:00, guilhem@stripped +31 -0
    result. Master and slave match.

  mysql-test/r/rpl_insert_delayed.result@stripped, 2007-02-15 15:39:01+01:00, guilhem@stripped +0 -0

  mysql-test/t/innodb-replace.test@stripped, 2007-02-15 15:39:01+01:00, guilhem@stripped +2 -2
    now that multi-row delayed inserts are converted to normal inserts
    if the statement-based binlog is enabled,
    no error is issued even if this engine does not support INSERT DELAYED,
    as the insert does not go through the INSERT DELAYED code.
    To preserve the goal of this test, we change the statements to single-
    row inserts.

  mysql-test/t/rpl_insert_delayed.test@stripped, 2007-02-15 15:39:01+01:00, guilhem@stripped +67 -0
    Test for BUG#26116 (see if one error at first row on master makes the
    slave's data incorrect, see if one error at second row on master
    makes slave stop).

  mysql-test/t/rpl_insert_delayed.test@stripped, 2007-02-15 15:39:01+01:00, guilhem@stripped +0 -0

  sql/sql_insert.cc@stripped, 2007-02-15 15:39:01+01:00, guilhem@stripped +21 -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.0-rpl-25507

--- 1.214/sql/sql_insert.cc	2007-02-15 15:39:10 +01:00
+++ 1.215/sql/sql_insert.cc	2007-02-15 15:39:10 +01:00
@@ -356,6 +356,27 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
       (duplic == DUP_UPDATE))
     lock_type=TL_WRITE;
 #endif
+  if ((lock_type == TL_WRITE_DELAYED) &&
+      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
--- New file ---
+++ mysql-test/r/rpl_insert_delayed.result	07/02/15 15:39:01
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 (id INT primary key auto_increment, name VARCHAR(64));
truncate table t1;
insert delayed into t1 values(10, "my name");
insert delayed into t1 values(10, "is Bond"), (20, "James Bond");
ERROR 23000: Duplicate entry '10' for key 1
flush table t1;
select * from t1;
id	name
10	my name
select * from t1;
id	name
10	my name
delete from t1 where id!=10;
insert delayed into t1 values(20, "is Bond"), (10, "James Bond");
ERROR 23000: Duplicate entry '10' for key 1
flush table t1;
select * from t1;
id	name
10	my name
20	is Bond
select * from t1;
id	name
10	my name
20	is Bond
drop table t1;

--- New file ---
+++ mysql-test/t/rpl_insert_delayed.test	07/02/15 15:39:01
--source include/master-slave.inc
--source include/not_embedded.inc
--source include/not_windows.inc

connection master;

let $binlog_format_statement=1;

CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64));

sync_slave_with_master;

#
# BUG#26116 "If multi-row INSERT DELAYED has errors,
# statement-based binlogging breaks";
# happened only in statement-based binlogging.
#

connection master;
truncate table t1;
# first scenario: duplicate on first row
insert delayed into t1 values(10, "my name");
if ($binlog_format_statement)
{
  # 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(10, "is Bond"), (20, "James Bond");
}
if (!$binlog_format_statement)
{
  insert delayed into t1 values(10, "is Bond"), (20, "James Bond");
}
flush table t1; # to wait for INSERT DELAYED to be done
select * from t1;
sync_slave_with_master;
# when bug existed in statement-based binlogging, t1 on slave had
# different content from on master
select * from t1;

# second scenario: duplicate on second row
connection master;
delete from t1 where id!=10;
if ($binlog_format_statement)
{
  # statement below will be converted to non-delayed INSERT and so
  # will be binlogged with its ER_DUP_ENTRY error code, guaranteeing
  # replication (slave will hit the same error code and so be fine).
  --error ER_DUP_ENTRY
  insert delayed into t1 values(20, "is Bond"), (10, "James Bond");
}
if (!$binlog_format_statement)
{
  insert delayed into t1 values(20, "is Bond"), (10, "James Bond");
}
flush table t1; # to wait for INSERT DELAYED to be done
select * from t1;
sync_slave_with_master;
# when bug existed in statement-based binlogging, query was binlogged
# with error_code=0 so slave stopped
select * from t1;

# clean up
connection master;
drop table t1;
sync_slave_with_master;
connection master;


--- 1.2/mysql-test/r/innodb-replace.result	2007-02-15 15:39:10 +01:00
+++ 1.3/mysql-test/r/innodb-replace.result	2007-02-15 15:39:10 +01:00
@@ -2,11 +2,11 @@ drop table if exists t1;
 create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb;
 select * from t1;
 c1	c2	stamp
-replace delayed into t1 (c1, c2)  values ( "text1","11"),( "text2","12");
+replace delayed into t1 (c1, c2)  values ( "text1","11");
 ERROR HY000: Table storage engine for 't1' doesn't have this option
 select * from t1;
 c1	c2	stamp
-replace delayed into t1 (c1, c2)  values ( "text1","12"),( "text2","13"),( "text3","14", "a" ),( "text4","15", "b" );
+replace delayed into t1 (c1, c2)  values ( "text1","12");
 ERROR HY000: Table storage engine for 't1' doesn't have this option
 select * from t1;
 c1	c2	stamp

--- 1.3/mysql-test/t/innodb-replace.test	2007-02-15 15:39:10 +01:00
+++ 1.4/mysql-test/t/innodb-replace.test	2007-02-15 15:39:10 +01:00
@@ -12,10 +12,10 @@ drop table if exists t1;
 create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb;
 select * from t1;
 --error 1031
-replace delayed into t1 (c1, c2)  values ( "text1","11"),( "text2","12");
+replace delayed into t1 (c1, c2)  values ( "text1","11");
 select * from t1;
 --error 1031
-replace delayed into t1 (c1, c2)  values ( "text1","12"),( "text2","13"),( "text3","14", "a" ),( "text4","15", "b" );
+replace delayed into t1 (c1, c2)  values ( "text1","12");
 select * from t1;
 drop table t1;
 
Thread
bk commit into 5.0 tree (guilhem:1.2394) BUG#25507Guilhem Bichot15 Feb