#At file:///home/acorreia/workspace.oracle/repository.mysql/bzrwork/bug-59338/mysql-5.1/ based on revid:john.embretsen@stripped
3555 Alfranio Correia 2011-01-27
BUG#59338 Inconsistency in binlog for statements that don't change any rows STATEMENT SBR
In SBR, if a statement does not fail, it is always written to the binary
log, regardless if rows are changed or not. If there is a failure, a
statement is only written to the binary log if a non-transactional (.e.g.
MyIsam) engine is updated.
INSERT ON DUPLICATE KEY UPDATE and INSERT IGNORE were not following the
rule above and were not written to the binary log, if then engine was
Innodb.
@ mysql-test/extra/rpl_tests/rpl_insert_duplicate.test
Added test case.
@ mysql-test/extra/rpl_tests/rpl_insert_ignore.test
Updated test case.
@ mysql-test/include/commit.inc
Updated test case as the calls to the binary log have changed
for INSERT ON DUPLICATE and INSERT IGNORE.
@ mysql-test/r/commit_1innodb.result
Updated result file.
@ mysql-test/suite/rpl/r/rpl_insert_duplicate.result
Added test case.
@ mysql-test/suite/rpl/r/rpl_insert_ignore.result
Updated result file.
@ mysql-test/suite/rpl/t/rpl_insert_duplicate.test
Added test case.
@ mysql-test/suite/rpl/t/rpl_insert_ignore.test
Improved test case.
added:
mysql-test/extra/rpl_tests/rpl_insert_duplicate.test
mysql-test/suite/rpl/r/rpl_insert_duplicate.result
mysql-test/suite/rpl/t/rpl_insert_duplicate.test
modified:
mysql-test/extra/rpl_tests/rpl_insert_ignore.test
mysql-test/include/commit.inc
mysql-test/r/commit_1innodb.result
mysql-test/suite/rpl/r/rpl_insert_ignore.result
mysql-test/suite/rpl/t/rpl_insert_ignore.test
sql/sql_insert.cc
=== added file 'mysql-test/extra/rpl_tests/rpl_insert_duplicate.test'
--- a/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test 2011-01-27 15:32:47 +0000
@@ -0,0 +1,51 @@
+# BUG#59338 Inconsistency in binlog for statements that don't change any rows STATEMENT SBR
+# In SBR, if a statement does not fail, it is always written to the binary log,
+# regardless if rows are changed or not. If there is a failure, a statement is
+# only written to the binary log if a non-transactional (.e.g. MyIsam) engine
+# is updated. INSERT ON DUPLICATE KEY UPDATE was not following the rule above
+# and was not written to the binary log, if then engine was Innodb.
+#
+# In this test case, we check if INSERT ON DUPLICATE KEY UPDATE that does not
+# change anything is still written to the binary log.
+
+# Prepare environment
+--connection master
+
+eval CREATE TABLE t1 (
+ a INT UNSIGNED NOT NULL PRIMARY KEY
+) ENGINE=$engine_type;
+
+eval CREATE TABLE t2 (
+ a INT UNSIGNED
+) ENGINE=$engine_type;
+
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1);
+
+# An insert duplicate that does not update anything must be written to the binary
+# log in SBR and MIXED modes. We check this property by summing b before and after
+# the update on b and printing out the contents of the binary log. The sum should
+# be the same in both points and the statement should be in the binary log.
+--let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1)
+--let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1)
+INSERT INTO t1 SELECT t2.a FROM t2 ORDER BY t2.a ON DUPLICATE
+KEY UPDATE t1.a= t1.a;
+--let $assert_cond= SUM(a) = 1 FROM t1
+--let $assert_text= Sum of elements in t1 should be 1.
+--source include/assert.inc
+--let $binlog_position_cmp= <
+if (`SELECT @@BINLOG_FORMAT = 'ROW'`)
+{
+ --let $binlog_position_cmp= =
+}
+--let $assert_cond= [SHOW MASTER STATUS, Position, 1] $binlog_position_cmp $binlog_start
+--let $assert_text= Binlog position should increase if binlog_format=statement or mixed, or stay unchanged if binlog_format=row.
+
+# Compare master and slave
+--let $rpl_diff_statement= SELECT * FROM t1 order by t1.a
+--source include/rpl_diff.inc
+
+# Clean up
+connection master;
+drop table t1, t2;
+sync_slave_with_master;
=== modified file 'mysql-test/extra/rpl_tests/rpl_insert_ignore.test'
--- a/mysql-test/extra/rpl_tests/rpl_insert_ignore.test 2007-06-18 13:36:10 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_insert_ignore.test 2011-01-27 15:32:47 +0000
@@ -5,6 +5,7 @@
# Slave needs to be started with --innodb to store table in InnoDB.
# Same test for MyISAM (which had no bug).
+--connection master
eval CREATE TABLE t1 (
a int unsigned not null auto_increment primary key,
b int unsigned,
@@ -32,38 +33,35 @@ INSERT INTO t2 VALUES (5, 4);
INSERT INTO t2 VALUES (6, 6);
INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
-
-# Compare results
-
-SELECT * FROM t1 ORDER BY a;
-
-sync_slave_with_master;
-SELECT * FROM t1 ORDER BY a;
-
-# Now do the same for MyISAM
-
-connection master;
-drop table t1;
-eval CREATE TABLE t1 (
- a int unsigned not null auto_increment primary key,
- b int unsigned,
- unique (b)
-) ENGINE=$engine_type2;
-
-INSERT INTO t1 VALUES (1, 1);
-INSERT INTO t1 VALUES (2, 2);
-INSERT INTO t1 VALUES (3, 3);
-INSERT INTO t1 VALUES (4, 4);
-
+--let $assert_cond= COUNT(*) = 6 FROM t1
+--let $assert_text= Count of elements in t1 should be 6.
+--source include/assert.inc
+
+# Compare master and slave
+--let $rpl_diff_statement= SELECT * FROM t1 order by t1.a
+--source include/rpl_diff.inc
+
+# BUG#59338 Inconsistency in binlog for statements that don't change any rows STATEMENT SBR
+# An insert ignore that does not update anything must be written to the binary log in SBR
+# and MIXED modes. We check this property by counting occurrence in t1 before and after
+# the insert and printing out the contents of the binary log. The count should be the same
+#in both points and the statement should be in the binary log.
+--connection master
+--let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1)
+--let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1)
INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
+--let $assert_cond= COUNT(*) = 6 FROM t1
+--let $assert_text= Count of elements in t1 should be 6.
+--source include/assert.inc
+--let $binlog_position_cmp= <
+if (`SELECT @@BINLOG_FORMAT = 'ROW'`)
+{
+ --let $binlog_position_cmp= =
+}
+--let $assert_cond= [SHOW MASTER STATUS, Position, 1] $binlog_position_cmp $binlog_start
+--let $assert_text= Binlog position should increase if binlog_format=statement or mixed, or stay unchanged if binlog_format=row.
-SELECT * FROM t1 ORDER BY a;
-
-sync_slave_with_master;
-SELECT * FROM t1 ORDER BY a;
-
+# Clean up
connection master;
drop table t1, t2;
sync_slave_with_master;
-
-# End of 4.1 tests
=== modified file 'mysql-test/include/commit.inc'
--- a/mysql-test/include/commit.inc 2010-07-20 17:36:15 +0000
+++ b/mysql-test/include/commit.inc 2011-01-27 15:32:47 +0000
@@ -502,16 +502,16 @@ call p_verify_status_increment(2, 2, 2,
--echo # 12. Read-write statement: IODKU, change 0 rows.
--echo #
insert t1 set a=2 on duplicate key update a=2;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 2, 1, 0);
commit;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 2, 1, 0);
--echo # 13. Read-write statement: INSERT IGNORE, change 0 rows.
--echo #
insert ignore t1 set a=2;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 2, 1, 0);
commit;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 2, 1, 0);
--echo # 14. Read-write statement: INSERT IGNORE, change 1 row.
--echo #
=== modified file 'mysql-test/r/commit_1innodb.result'
--- a/mysql-test/r/commit_1innodb.result 2010-07-20 17:36:15 +0000
+++ b/mysql-test/r/commit_1innodb.result 2011-01-27 15:32:47 +0000
@@ -518,21 +518,21 @@ SUCCESS
# 12. Read-write statement: IODKU, change 0 rows.
#
insert t1 set a=2 on duplicate key update a=2;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 2, 1, 0);
SUCCESS
commit;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 2, 1, 0);
SUCCESS
# 13. Read-write statement: INSERT IGNORE, change 0 rows.
#
insert ignore t1 set a=2;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 2, 1, 0);
SUCCESS
commit;
-call p_verify_status_increment(1, 0, 1, 0);
+call p_verify_status_increment(2, 2, 1, 0);
SUCCESS
# 14. Read-write statement: INSERT IGNORE, change 1 row.
=== added file 'mysql-test/suite/rpl/r/rpl_insert_duplicate.result'
--- a/mysql-test/suite/rpl/r/rpl_insert_duplicate.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_insert_duplicate.result 2011-01-27 15:32:47 +0000
@@ -0,0 +1,29 @@
+include/master-slave.inc
+[connection master]
+CREATE TABLE t1 (
+a INT UNSIGNED NOT NULL PRIMARY KEY
+) ENGINE=innodb;
+CREATE TABLE t2 (
+a INT UNSIGNED
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1);
+INSERT INTO t1 SELECT t2.a FROM t2 ORDER BY t2.a ON DUPLICATE
+KEY UPDATE t1.a= t1.a;
+include/assert.inc [Sum of elements in t1 should be 1.]
+include/rpl_diff.inc
+drop table t1, t2;
+CREATE TABLE t1 (
+a INT UNSIGNED NOT NULL PRIMARY KEY
+) ENGINE=myisam;
+CREATE TABLE t2 (
+a INT UNSIGNED
+) ENGINE=myisam;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1);
+INSERT INTO t1 SELECT t2.a FROM t2 ORDER BY t2.a ON DUPLICATE
+KEY UPDATE t1.a= t1.a;
+include/assert.inc [Sum of elements in t1 should be 1.]
+include/rpl_diff.inc
+drop table t1, t2;
+include/rpl_end.inc
=== modified file 'mysql-test/suite/rpl/r/rpl_insert_ignore.result'
--- a/mysql-test/suite/rpl/r/rpl_insert_ignore.result 2010-12-19 17:07:28 +0000
+++ b/mysql-test/suite/rpl/r/rpl_insert_ignore.result 2011-01-27 15:32:47 +0000
@@ -20,48 +20,34 @@ INSERT INTO t2 VALUES (4, 3);
INSERT INTO t2 VALUES (5, 4);
INSERT INTO t2 VALUES (6, 6);
INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
-SELECT * FROM t1 ORDER BY a;
-a b
-1 1
-2 2
-3 3
-4 4
-5 5
-6 6
-SELECT * FROM t1 ORDER BY a;
-a b
-1 1
-2 2
-3 3
-4 4
-5 5
-6 6
-drop table t1;
+include/assert.inc [Count of elements in t1 should be 6.]
+include/rpl_diff.inc
+INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
+include/assert.inc [Count of elements in t1 should be 6.]
+drop table t1, t2;
CREATE TABLE t1 (
a int unsigned not null auto_increment primary key,
b int unsigned,
unique (b)
-) ENGINE=myisam;
-INSERT INTO t1 VALUES (1, 1);
-INSERT INTO t1 VALUES (2, 2);
-INSERT INTO t1 VALUES (3, 3);
-INSERT INTO t1 VALUES (4, 4);
+) ENGINE=innodb;
+CREATE TABLE t2 (
+a int unsigned, # to force INSERT SELECT to have a certain order
+b int unsigned
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (NULL, 1);
+INSERT INTO t1 VALUES (NULL, 2);
+INSERT INTO t1 VALUES (NULL, 3);
+INSERT INTO t1 VALUES (NULL, 4);
+INSERT INTO t2 VALUES (1, 1);
+INSERT INTO t2 VALUES (2, 2);
+INSERT INTO t2 VALUES (3, 5);
+INSERT INTO t2 VALUES (4, 3);
+INSERT INTO t2 VALUES (5, 4);
+INSERT INTO t2 VALUES (6, 6);
+INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
+include/assert.inc [Count of elements in t1 should be 6.]
+include/rpl_diff.inc
INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a;
-SELECT * FROM t1 ORDER BY a;
-a b
-1 1
-2 2
-3 3
-4 4
-5 5
-6 6
-SELECT * FROM t1 ORDER BY a;
-a b
-1 1
-2 2
-3 3
-4 4
-5 5
-6 6
+include/assert.inc [Count of elements in t1 should be 6.]
drop table t1, t2;
include/rpl_end.inc
=== added file 'mysql-test/suite/rpl/t/rpl_insert_duplicate.test'
--- a/mysql-test/suite/rpl/t/rpl_insert_duplicate.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_insert_duplicate.test 2011-01-27 15:32:47 +0000
@@ -0,0 +1,14 @@
+#########################################
+# Wrapper for rpl_insert_duplicate.test #
+#########################################
+-- source include/master-slave.inc
+-- source include/have_innodb.inc
+#-- source include/have_binlog_format_mixed_or_statement.inc
+
+let $engine_type=innodb;
+-- source extra/rpl_tests/rpl_insert_duplicate.test
+
+let $engine_type=myisam;
+-- source extra/rpl_tests/rpl_insert_duplicate.test
+
+--source include/rpl_end.inc
=== modified file 'mysql-test/suite/rpl/t/rpl_insert_ignore.test'
--- a/mysql-test/suite/rpl/t/rpl_insert_ignore.test 2010-12-19 17:07:28 +0000
+++ b/mysql-test/suite/rpl/t/rpl_insert_ignore.test 2011-01-27 15:32:47 +0000
@@ -4,7 +4,11 @@
-- source include/not_ndb_default.inc
-- source include/have_innodb.inc
-- source include/master-slave.inc
-let $engine_type=innodb;
-let $engine_type2=myisam;
+
+-- let $engine_type=innodb
-- source extra/rpl_tests/rpl_insert_ignore.test
+
+-- let $engine_type=innodb
+-- source extra/rpl_tests/rpl_insert_ignore.test
+
--source include/rpl_end.inc
=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc 2010-10-07 08:13:11 +0000
+++ b/sql/sql_insert.cc 2011-01-27 15:32:47 +0000
@@ -881,7 +881,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
*/
query_cache_invalidate3(thd, table_list, 1);
}
- if ((changed && error <= 0) ||
+ if (error <= 0 ||
thd->transaction.stmt.modified_non_trans_table ||
was_insert_delayed)
{
Attachment: [text/bzr-bundle] bzr/alfranio.correia@oracle.com-20110127153247-8coppkuzwd62oq1v.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1 branch (alfranio.correia:3555) Bug#59338 | Alfranio Correia | 27 Jan |
