diff options
Diffstat (limited to 'mysql-test/suite/rpl')
4 files changed, 345 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl/r/rpl_autogen_query_multi_byte_char.result b/mysql-test/suite/rpl/r/rpl_autogen_query_multi_byte_char.result new file mode 100644 index 00000000000..b03c0057a69 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_autogen_query_multi_byte_char.result @@ -0,0 +1,29 @@ +include/master-slave.inc +[connection master] +Test case 1:- table name with one character latin name. +SET @s:=CONCAT("CREATE TABLE `",REPEAT(CHAR(131),1),"` (a INT)"); +PREPARE STMT FROM @s; +EXECUTE stmt; +SET @s:=CONCAT("INSERT INTO `",REPEAT(CHAR(131),1),"` VALUES (1)"); +PREPARE STMT FROM @s; +EXECUTE stmt; +SET @s:=CONCAT("DROP TABLE `",REPEAT(CHAR(131),1), "`"); +PREPARE STMT FROM @s; +EXECUTE stmt; +Test case 2:- table name and database names with one character latin name. +SET @s:=CONCAT("CREATE DATABASE `",REPEAT(CHAR(131),1),"`"); +PREPARE STMT FROM @s; +EXECUTE stmt; +SET @s:=CONCAT("CREATE TABLE `",REPEAT(CHAR(131),1),"`.`",REPEAT(CHAR(131),1),"` (a INT)"); +PREPARE STMT FROM @s; +EXECUTE stmt; +SET @s:=CONCAT("INSERT INTO `",REPEAT(CHAR(131),1),"`.`",REPEAT(CHAR(131),1),"` VALUES (1)"); +PREPARE STMT FROM @s; +EXECUTE stmt; +SET @s:=CONCAT("DROP TABLE `",REPEAT(CHAR(131),1),"`.`",REPEAT(CHAR(131),1), "`"); +PREPARE STMT FROM @s; +EXECUTE stmt; +SET @s:=CONCAT("DROP DATABASE `",REPEAT(CHAR(131),1),"`"); +PREPARE STMT FROM @s; +EXECUTE stmt; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_unsafe_statements.result b/mysql-test/suite/rpl/r/rpl_unsafe_statements.result new file mode 100644 index 00000000000..2efb3eba2b1 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_unsafe_statements.result @@ -0,0 +1,53 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; +CREATE TABLE t2(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; +CREATE TRIGGER trig1 AFTER INSERT ON t1 +FOR EACH ROW +INSERT INTO t2(i) VALUES(new.i); +START TRANSACTION; +INSERT INTO t2(i) VALUES (1); +ROLLBACK; +INSERT INTO t1(i) VALUES(2); +START TRANSACTION; +LOCK TABLES t1 WRITE, t2 WRITE; +INSERT INTO t1(i) VALUES(3); +UNLOCK TABLES; +COMMIT; +include/diff_tables.inc [master:t1, slave:t1] +include/diff_tables.inc [master:t2, slave:t2] +DROP TABLE t1,t2; +CREATE TABLE t1(i INT) ENGINE=INNODB; +CREATE TABLE t2(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; +INSERT INTO t1 values (1), (2), (3); +START TRANSACTION; +INSERT INTO t2(i) VALUES (1); +ROLLBACK; +INSERT INTO t2(i) SELECT i FROM t1; +START TRANSACTION; +LOCK TABLES t2 WRITE, t1 READ; +INSERT INTO t2(i) SELECT i FROM t1; +UNLOCK TABLES; +COMMIT; +include/diff_tables.inc [master:t1, slave:t1] +include/diff_tables.inc [master:t2, slave:t2] +DROP TABLE t1,t2; +CREATE TABLE t1(i int, id INT AUTO_INCREMENT, PRIMARY KEY (i, id)) ENGINE=MYISAM; +INSERT INTO t1 (i) values (1); +START TRANSACTION; +LOCK TABLES t1 WRITE; +INSERT INTO t1 (i) values (2); +UNLOCK TABLES; +COMMIT; +include/diff_tables.inc [master:t1, slave:t1] +DROP TABLE t1; +CREATE TABLE t1(i INT, j INT, UNIQUE KEY(i), UNIQUE KEY(j)) ENGINE=INNODB; +INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1; +START TRANSACTION; +LOCK TABLES t1 WRITE; +INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1; +UNLOCK TABLES; +COMMIT; +include/diff_tables.inc [master:t1, slave:t1] +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_autogen_query_multi_byte_char.test b/mysql-test/suite/rpl/t/rpl_autogen_query_multi_byte_char.test new file mode 100644 index 00000000000..a93fcbac82f --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_autogen_query_multi_byte_char.test @@ -0,0 +1,87 @@ +############################################################################### +# Bug#21205695 DROP TABLE MAY CAUSE SLAVES TO BREAK +# +# Problem: +# ======== +# 1) Drop table queries are re-generated by server +# before writing the events(queries) into binlog +# for various reasons. If table name/db name contains +# a non regular characters (like latin characters), +# the generated query is wrong. Hence it breaks the +# replication. +# 2) In the edge case, when table name contains +# 64 latin characters (latin takes 2 bytes), server is +# throwing an assert (M_TBLLEN < 128) +# +# 3) In the edge case, when db name contains 64 latin +# characters, binlog contents are interpreted wrongly +# which is leading to replication issues. +# +############################################################################### + +--source include/not_windows.inc +--source include/master-slave.inc + +--let iter=1 +# Change iteration to 4 after fixing Bug #22280214 +while ($iter <= 2) +{ + --connection master + if ($iter == 1) + { + --echo Test case 1:- table name with one character latin name. + --let $tblname= REPEAT(CHAR(131),1) + } + if ($iter == 2) + { + --echo Test case 2:- table name and database names with one character latin name. + --let $tblname= REPEAT(CHAR(131),1),"`.`",REPEAT(CHAR(131),1) + --eval SET @s:=CONCAT("CREATE DATABASE `",REPEAT(CHAR(131),1),"`") + PREPARE STMT FROM @s; EXECUTE stmt; + } + # After fixing Bug #22280214 DATADIR LOCATION IS LIMITING + # IDENTIFIER MAX LENGTH, the following two tests (iter 3 and 4) can be + # uncommented. + #if ($iter == 3) + #{ + # --echo Test case 3:- table name and database names with 64 latin characters name. + # --let $tblname= REPEAT(CHAR(131),64),"`.`", REPEAT(CHAR(131),64) + # --eval SET @s:=CONCAT("CREATE DATABASE `",REPEAT(CHAR(131),64),"`") + # PREPARE STMT FROM @s; EXECUTE stmt; + #} + #if ($iter == 4) + #{ + # --echo Test case 4:- table name and database names with 64 Euro(€) characters. + # --let $tblname= REPEAT(CHAR(226,130,172),64),"`.`", REPEAT(CHAR(226,130,172),64) + # --eval SET @s:=CONCAT("CREATE DATABASE `",REPEAT(CHAR(226,130,172),64),"`") + # PREPARE STMT FROM @s; EXECUTE stmt; + #} + --eval SET @s:=CONCAT("CREATE TABLE `",$tblname,"` (a INT)") + PREPARE STMT FROM @s; EXECUTE stmt; + --eval SET @s:=CONCAT("INSERT INTO `",$tblname,"` VALUES (1)") + PREPARE STMT FROM @s; EXECUTE stmt; + --eval SET @s:=CONCAT("DROP TABLE `",$tblname, "`") + PREPARE STMT FROM @s; EXECUTE stmt; + if ($iter == 2) + { + --eval SET @s:=CONCAT("DROP DATABASE `",REPEAT(CHAR(131),1),"`") + PREPARE STMT FROM @s; EXECUTE stmt; + } + # After fixing Bug #22280214 DATADIR LOCATION IS LIMITING + # IDENTIFIER MAX LENGTH, the following two tests (iter 3 and 4) can be + # uncommented. + #if ($iter == 3) + #{ + # --eval SET @s:=CONCAT("DROP DATABASE `",REPEAT(CHAR(131),64),"`") + # PREPARE STMT FROM @s; EXECUTE stmt; + #} + #if ($iter == 4) + #{ + # --eval SET @s:=CONCAT("DROP DATABASE `",REPEAT(CHAR(226,130,172),64),"`") + # PREPARE STMT FROM @s; EXECUTE stmt; + #} + --sync_slave_with_master + --inc $iter +} + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_unsafe_statements.test b/mysql-test/suite/rpl/t/rpl_unsafe_statements.test new file mode 100644 index 00000000000..cbb4b54a220 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_unsafe_statements.test @@ -0,0 +1,176 @@ +################################################################################ +# Bug#17047208 REPLICATION DIFFERENCE FOR MULTIPLE TRIGGERS +# Problem: If DML invokes a trigger or a stored function that inserts into an +# AUTO_INCREMENT column, that DML has to be marked as 'unsafe' statement. If the +# tables are locked in the transaction prior to DML statement (using LOCK +# TABLES), then the DML statement is not marked as 'unsafe' statement. + +# Steps to reproduce the reported test case (BINLOG_STMT_UNSAFE_AUTOINC_COLUMNS) +# Case-1: +# > Create a trigger on a table and do a insert in the trigger that updates +# auto increment column +# > A DML that executes the trigger in step.1 and check that DML is marked +# as unsafe and DML is written into binlog using row format (in MBR) +# > Execute the step 2 by locking the required tables prior to DML and check +# that DML is marked as unsafe and DML is written into binlog using row +# format (in MBR) +# +# This test script also adds test cases to cover few other unsafe statements. +# Case-2: BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT +# Case-3: BINLOG_STMT_UNSAFE_AUTOINC_NOT_FIRST +# Case-4: BINLOG_STMT_UNSAFE_INSERT_TWO_KEYS +################################################################################ + +--source include/have_innodb.inc +--source include/have_binlog_format_mixed.inc +--source include/master-slave.inc + +# Case-1: BINLOG_STMT_UNSAFE_AUTOINC_COLUMNS +# Statement is unsafe because it invokes a trigger or a +# stored function that inserts into an AUTO_INCREMENT column. + +# Step-1.1: Create two tables, one with AUTO_INCREMENT column. +CREATE TABLE t1(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; +CREATE TABLE t2(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; + +# Step-1.2: Create a trigger that inserts into an AUTO_INCREMENT column. +CREATE TRIGGER trig1 AFTER INSERT ON t1 +FOR EACH ROW + INSERT INTO t2(i) VALUES(new.i); + +# Step-1.3: Create some gap in auto increment value on master's t2 table +# but not on slave (by doing rollback). Just in case if the unsafe statements +# are written in statement format, diff tables will fail. +START TRANSACTION; +INSERT INTO t2(i) VALUES (1); +ROLLBACK; + +# Step-1.4: Insert a tuple into table t1 that triggers trig1 which inserts +# into an AUTO_INCREMENT column. +INSERT INTO t1(i) VALUES(2); + +# Step-1.5: Repeat step 1.4 but using 'LOCK TABLES' logic. +START TRANSACTION; +LOCK TABLES t1 WRITE, t2 WRITE; +INSERT INTO t1(i) VALUES(3); +UNLOCK TABLES; +COMMIT; + +# Step-1.6: Sync slave with master +--sync_slave_with_master + +# Step-1.7: Diff master-slave tables to make sure everything is in sync. +--let $diff_tables=master:t1, slave:t1 +--source include/diff_tables.inc + +--let $diff_tables=master:t2, slave:t2 +--source include/diff_tables.inc + +# Step-1.8: Cleanup +--connection master +DROP TABLE t1,t2; + +# Case-2: BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT +# Statements writing to a table with an auto-increment column after selecting +# from another table are unsafe because the order in which rows are retrieved +# determines what (if any) rows will be written. This order cannot be +# predicted and may differ on master and the slave. + +# Step-2.1: Create two tables, one with AUTO_INCREMENT column. +CREATE TABLE t1(i INT) ENGINE=INNODB; +CREATE TABLE t2(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; + +# Step-2.2: Create some tuples in table t1. +INSERT INTO t1 values (1), (2), (3); + +# Step-2.3: Create some gap in auto increment value on master's t2 table +# but not on slave (by doing rollback). Just in case if the unsafe statements +# are written in statement format, diff tables will fail. +START TRANSACTION; +INSERT INTO t2(i) VALUES (1); +ROLLBACK; + +# Step-2.4: Insert into t2 (table with an auto-increment) by selecting tuples +# from table t1. +INSERT INTO t2(i) SELECT i FROM t1; + +# Step-2.5: Repeat step 2.4 but now with 'LOCK TABLES' logic. +START TRANSACTION; +LOCK TABLES t2 WRITE, t1 READ; +INSERT INTO t2(i) SELECT i FROM t1; +UNLOCK TABLES; +COMMIT; + +# Step-2.6: Sync slave with master +--sync_slave_with_master + +# Step-2.7: Diff master-slave tables to make sure everything is in sync. +--let $diff_tables=master:t1, slave:t1 +--source include/diff_tables.inc + +--let $diff_tables=master:t2, slave:t2 +--source include/diff_tables.inc + +# Step-2.8: Cleanup +--connection master +DROP TABLE t1,t2; + +# Case-3: BINLOG_STMT_UNSAFE_AUTOINC_NOT_FIRST +# INSERT into autoincrement field which is not the first part in the +# composed primary key is unsafe +# +# Step-3.1: Create a table with auto increment column and a composed primary key +# (second column is auto increment column). Such a definition is allowed only +# with 'myisam' engine. +CREATE TABLE t1(i int, id INT AUTO_INCREMENT, PRIMARY KEY (i, id)) ENGINE=MYISAM; + +# Step-3.2: Inserting into such a table is unsafe. +INSERT INTO t1 (i) values (1); + +# Step-3.3: Repeat step 3.2, now with 'LOCK TABLES' logic. +START TRANSACTION; +LOCK TABLES t1 WRITE; +INSERT INTO t1 (i) values (2); +UNLOCK TABLES; +COMMIT; + +# Step-3.4: Sync slave with master +--sync_slave_with_master + +# Step-3.5: Diff master-slave tables to make sure everything is in sync. +--let $diff_tables=master:t1, slave:t1 +--source include/diff_tables.inc + +# Step-3.6: Cleanup +--connection master +DROP TABLE t1; + +# Case-4: BINLOG_STMT_UNSAFE_INSERT_TWO_KEYS +# INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY +# is unsafe Statement + +# Step-4.1: Create a table with two unique keys +CREATE TABLE t1(i INT, j INT, UNIQUE KEY(i), UNIQUE KEY(j)) ENGINE=INNODB; + +# Step-4.2: Inserting into such a table is unsafe. +INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1; + +# Step-4.3: Repeat step 3.2, now with 'LOCK TABLES' logic. +START TRANSACTION; +LOCK TABLES t1 WRITE; +INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1; +UNLOCK TABLES; +COMMIT; + +# Step-4.4: Sync slave with master +--sync_slave_with_master + +# Step-4.5: Diff master-slave tables to make sure everything is in sync. +--let $diff_tables=master:t1, slave:t1 +--source include/diff_tables.inc + +# Step-4.6: Cleanup +--connection master +DROP TABLE t1; + +--source include/rpl_end.inc |