diff options
| -rw-r--r-- | mysql-test/extra/rpl_tests/rpl_insert_duplicate.test | 64 | ||||
| -rw-r--r-- | mysql-test/extra/rpl_tests/rpl_insert_ignore.test | 80 | ||||
| -rw-r--r-- | mysql-test/include/commit.inc | 8 | ||||
| -rw-r--r-- | mysql-test/r/commit_1innodb.result | 8 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_insert_duplicate.result | 29 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_insert_ignore.result | 64 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/t/rpl_insert_duplicate.test | 14 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/t/rpl_insert_ignore.test | 8 | ||||
| -rw-r--r-- | sql/sql_insert.cc | 2 |
9 files changed, 194 insertions, 83 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test b/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test new file mode 100644 index 00000000000..77140174f4b --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test @@ -0,0 +1,64 @@ +# 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 a before and after +# the update and comparing the binlog positions. The sum should be the same at 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) +--let $statement_file=INSERT INTO t1 SELECT t2.a FROM t2 ORDER BY t2.a ON DUPLICATE KEY UPDATE t1.a= t1.a +--eval $statement_file + +--let $assert_cond= SUM(a) = 1 FROM t1 +--let $assert_text= Sum of elements in t1 should be 1. +--source include/assert.inc + +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= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +if (`SELECT @@BINLOG_FORMAT != 'ROW' && UPPER('$engine_type') = UPPER('Innodb')`) +{ + --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 1, 1\', Info, 1]\' LIKE \'%$statement_file\' + --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +if (`SELECT @@BINLOG_FORMAT != 'ROW' && UPPER('$engine_type') = UPPER('MyIsam')`) +{ + --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 0, 1\', Info, 1]\' LIKE \'%$statement_file\' + --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +--source include/assert.inc + +# Compare master and slave +--sync_slave_with_master +--let $diff_tables= master:test.t1 , slave:test.t1 +--source include/diff_tables.inc + +# Clean up +--connection master +drop table t1, t2; +--sync_slave_with_master diff --git a/mysql-test/extra/rpl_tests/rpl_insert_ignore.test b/mysql-test/extra/rpl_tests/rpl_insert_ignore.test index 43d45ef6c60..1eb66358423 100644 --- a/mysql-test/extra/rpl_tests/rpl_insert_ignore.test +++ b/mysql-test/extra/rpl_tests/rpl_insert_ignore.test @@ -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,49 @@ 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); - -INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a; - -SELECT * FROM t1 ORDER BY a; - -sync_slave_with_master; -SELECT * FROM t1 ORDER BY a; - -connection master; +--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 +--sync_slave_with_master +--let $diff_tables= master:test.t1 , slave:test.t1 +--source include/diff_tables.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 occurrences in t1 before and after +# the insert and comparing the binlog positions. 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) +--let $statement_file=INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a +--eval $statement_file + +--let $assert_cond= COUNT(*) = 6 FROM t1 +--let $assert_text= Count of elements in t1 should be 6. +--source include/assert.inc + +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= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +if (`SELECT @@BINLOG_FORMAT != 'ROW' && UPPER('$engine_type') = UPPER('Innodb')`) +{ + --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 2, 1\', Info, 1]\' LIKE \'%$statement_file\' + --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +if (`SELECT @@BINLOG_FORMAT != 'ROW' && UPPER('$engine_type') = UPPER('MyIsam')`) +{ + --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 1, 1\', Info, 1]\' LIKE \'%$statement_file\' + --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +--source include/assert.inc + +# Clean up +--connection master drop table t1, t2; -sync_slave_with_master; - -# End of 4.1 tests +--sync_slave_with_master diff --git a/mysql-test/include/commit.inc b/mysql-test/include/commit.inc index d412eae8364..7924f9bc96f 100644 --- a/mysql-test/include/commit.inc +++ b/mysql-test/include/commit.inc @@ -502,16 +502,16 @@ call p_verify_status_increment(2, 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 # diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 1f0b2c8019b..d21af5ef555 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -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. diff --git a/mysql-test/suite/rpl/r/rpl_insert_duplicate.result b/mysql-test/suite/rpl/r/rpl_insert_duplicate.result new file mode 100644 index 00000000000..61ebbaaa5a9 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_insert_duplicate.result @@ -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/assert.inc [In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.] +include/diff_tables.inc [master:test.t1 , slave:test.t1] +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/assert.inc [In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.] +include/diff_tables.inc [master:test.t1 , slave:test.t1] +drop table t1, t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_insert_ignore.result b/mysql-test/suite/rpl/r/rpl_insert_ignore.result index 6937c3d0987..5d9c4aec9f7 100644 --- a/mysql-test/suite/rpl/r/rpl_insert_ignore.result +++ b/mysql-test/suite/rpl/r/rpl_insert_ignore.result @@ -20,48 +20,36 @@ 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/diff_tables.inc [master:test.t1 , slave:test.t1] +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/assert.inc [In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.] +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/diff_tables.inc [master:test.t1 , slave:test.t1] 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.] +include/assert.inc [In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.] drop table t1, t2; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_insert_duplicate.test b/mysql-test/suite/rpl/t/rpl_insert_duplicate.test new file mode 100644 index 00000000000..7dd38a696ae --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_insert_duplicate.test @@ -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 diff --git a/mysql-test/suite/rpl/t/rpl_insert_ignore.test b/mysql-test/suite/rpl/t/rpl_insert_ignore.test index 1d6c8e7168e..265a52fb51d 100644 --- a/mysql-test/suite/rpl/t/rpl_insert_ignore.test +++ b/mysql-test/suite/rpl/t/rpl_insert_ignore.test @@ -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 diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index f0735a9e093..e2f93ee4de5 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -881,7 +881,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, */ query_cache_invalidate3(thd, table_list, 1); } - if ((changed && error <= 0) || + if (error <= 0 || thd->transaction.stmt.modified_non_trans_table || was_insert_delayed) { |