From: Date: February 14 2007 6:47pm Subject: bk commit into 5.1 tree (guilhem:1.2411) BUG#25507 List-Archive: http://lists.mysql.com/commits/19900 X-Bug: 25507 Message-Id: <200702141747.l1EHllNa014059@gbichot3.local> 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-14 18:47:39+01:00, guilhem@stripped +6 -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. mysql-test/extra/rpl_tests/rpl_insert_delayed.test@stripped, 2007-02-14 18:47:29+01:00, guilhem@stripped +86 -0 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). It is then incorporated into a statement-based and mixed binlogging test, and into a row-based test. mysql-test/extra/rpl_tests/rpl_insert_delayed.test@stripped, 2007-02-14 18:47:29+01:00, guilhem@stripped +0 -0 mysql-test/r/rpl_insert_delayed_row.result@stripped, 2007-02-14 18:47:28+01:00, guilhem@stripped +48 -0 result mysql-test/r/rpl_insert_delayed_row.result@stripped, 2007-02-14 18:47:28+01:00, guilhem@stripped +0 -0 mysql-test/r/rpl_insert_delayed_statement.result@stripped, 2007-02-14 18:47:29+01:00, guilhem@stripped +88 -0 result. Note how "mixed" and "statement" insert different data in the table. mysql-test/r/rpl_insert_delayed_statement.result@stripped, 2007-02-14 18:47:29+01:00, guilhem@stripped +0 -0 mysql-test/t/rpl_insert_delayed_row.test@stripped, 2007-02-14 18:47:28+01:00, guilhem@stripped +14 -0 wrapper to test INSERT DELAYED binlogging in row-based mode mysql-test/t/rpl_insert_delayed_row.test@stripped, 2007-02-14 18:47:28+01:00, guilhem@stripped +0 -0 mysql-test/t/rpl_insert_delayed_statement.test@stripped, 2007-02-14 18:47:28+01:00, guilhem@stripped +20 -0 wrapper to test INSERT DELAYED binlogging in statement-based and mixed mode mysql-test/t/rpl_insert_delayed_statement.test@stripped, 2007-02-14 18:47:28+01:00, guilhem@stripped +0 -0 sql/sql_insert.cc@stripped, 2007-02-14 18:47:27+01:00, guilhem@stripped +30 -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-14 18:47:47 +01:00 +++ 1.245/sql/sql_insert.cc 2007-02-14 18:47:47 +01:00 @@ -341,6 +341,36 @@ 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. + Note that to be fully correct, we should test the "binlog format which + the delayed thread is going to use for this row". But in the common case + where the global binlog format is not changed and the session binlog + format may be changed, that is equal to the global binlog format. + We test it without mutex for speed reasons (condition rarely true), and + in the common case (global not changed) it is as good as without mutex; + if global value is changed, anyway there is uncertainty as the delayed + thread may be old and use the before-the-change value. + */ + lock_type= TL_WRITE; + } table_list->lock_type= lock_type; #ifndef EMBEDDED_LIBRARY --- New file --- +++ mysql-test/extra/rpl_tests/rpl_insert_delayed.test 07/02/14 18:47:29 # The two bugs below (BUG#25507 and BUG#26116) existed only in # statement-based binlogging; we test that now they are fixed; # we also test that mixed and row-based binlogging work too, # for completeness. connection master; --disable_warnings CREATE SCHEMA IF NOT EXISTS mysqlslap; USE mysqlslap; --enable_warnings select @@global.binlog_format; # # BUG#25507 "multi-row insert delayed + auto increment causes # duplicate key entries on slave"; # happened only in statement-based binlogging. # 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; use mysqlslap; 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; # first scenario: duplicate on first row insert delayed into t1 values(10, "my name"); if ($format_stmt) { # 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 (!$format_stmt) { 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 ($format_stmt) { # 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 (!$format_stmt) { 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; USE test; DROP SCHEMA mysqlslap; sync_slave_with_master; connection master; --- New file --- +++ mysql-test/r/rpl_insert_delayed_row.result 07/02/14 18:47:28 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; set @old_global_binlog_format = @@global.binlog_format; set @@global.binlog_format = row; CREATE SCHEMA IF NOT EXISTS mysqlslap; USE mysqlslap; select @@global.binlog_format; @@global.binlog_format ROW CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64)); FLUSH TABLE t1; SELECT COUNT(*) FROM t1; COUNT(*) 5000 use mysqlslap; SELECT COUNT(*) FROM t1; COUNT(*) 5000 truncate table t1; insert delayed into t1 values(10, "my name"); insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); flush table t1; select * from t1; id name 10 my name 20 James Bond select * from t1; id name 10 my name 20 James Bond delete from t1 where id!=10; insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); 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 USE test; DROP SCHEMA mysqlslap; set @@global.binlog_format = @old_global_binlog_format; --- New file --- +++ mysql-test/r/rpl_insert_delayed_statement.result 07/02/14 18:47:29 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; set @old_global_binlog_format = @@global.binlog_format; set @@global.binlog_format = statement; CREATE SCHEMA IF NOT EXISTS mysqlslap; USE mysqlslap; select @@global.binlog_format; @@global.binlog_format STATEMENT CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64)); FLUSH TABLE t1; SELECT COUNT(*) FROM t1; COUNT(*) 5000 use mysqlslap; SELECT COUNT(*) FROM t1; COUNT(*) 5000 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 'PRIMARY' 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 'PRIMARY' 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 USE test; DROP SCHEMA mysqlslap; set @@global.binlog_format = mixed; CREATE SCHEMA IF NOT EXISTS mysqlslap; USE mysqlslap; select @@global.binlog_format; @@global.binlog_format MIXED CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64)); FLUSH TABLE t1; SELECT COUNT(*) FROM t1; COUNT(*) 5000 use mysqlslap; SELECT COUNT(*) FROM t1; COUNT(*) 5000 truncate table t1; insert delayed into t1 values(10, "my name"); insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); flush table t1; select * from t1; id name 10 my name 20 James Bond select * from t1; id name 10 my name 20 James Bond delete from t1 where id!=10; insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); 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 USE test; DROP SCHEMA mysqlslap; set @@global.binlog_format = @old_global_binlog_format; --- New file --- +++ mysql-test/t/rpl_insert_delayed_row.test 07/02/14 18:47:28 --source include/have_binlog_format_row.inc --source include/master-slave.inc --source include/not_embedded.inc --source include/not_windows.inc connection master; set @old_global_binlog_format = @@global.binlog_format; let $format_stmt=0; set @@global.binlog_format = row; --source extra/rpl_tests/rpl_insert_delayed.test connection master; set @@global.binlog_format = @old_global_binlog_format; --- New file --- +++ mysql-test/t/rpl_insert_delayed_statement.test 07/02/14 18:47:28 # we run first in statement-based then in mixed binlogging --source include/have_binlog_format_mixed_or_statement.inc --source include/master-slave.inc --source include/not_embedded.inc --source include/not_windows.inc connection master; set @old_global_binlog_format = @@global.binlog_format; let $format_stmt=1; set @@global.binlog_format = statement; --source extra/rpl_tests/rpl_insert_delayed.test let $format_stmt=0; set @@global.binlog_format = mixed; --source extra/rpl_tests/rpl_insert_delayed.test connection master; set @@global.binlog_format = @old_global_binlog_format;