Debian Codegraph @

mysql-test/suite/rpl/t/rpl_unsafe_statements.test

raw
1 ################################################################################
2 # Bug#17047208 REPLICATION DIFFERENCE FOR MULTIPLE TRIGGERS
3 # Problem: If DML invokes a trigger or a stored function that inserts into an
4 # AUTO_INCREMENT column, that DML has to be marked as 'unsafe' statement. If the
5 # tables are locked in the transaction prior to DML statement (using LOCK
6 # TABLES), then the DML statement is not marked as 'unsafe' statement.
7
8 # Steps to reproduce the reported test case (BINLOG_STMT_UNSAFE_AUTOINC_COLUMNS)
9 # Case-1:
10 # > Create a trigger on a table and do a insert in the trigger that updates
11 # auto increment column
12 # > A DML that executes the trigger in step.1 and check that DML is marked
13 # as unsafe and DML is written into binlog using row format (in MBR)
14 # > Execute the step 2 by locking the required tables prior to DML and check
15 # that DML is marked as unsafe and DML is written into binlog using row
16 # format (in MBR)
17 #
18 # This test script also adds test cases to cover few other unsafe statements.
19 # Case-2: BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT
20 # Case-3: BINLOG_STMT_UNSAFE_AUTOINC_NOT_FIRST
21 # Case-4: BINLOG_STMT_UNSAFE_INSERT_TWO_KEYS
22 ################################################################################
23
24 --source include/have_binlog_format_mixed.inc
25 --source include/set_privilege_checks_user_as_system_user.inc
26 --source include/master-slave.inc
27
28 # Case-1: BINLOG_STMT_UNSAFE_AUTOINC_COLUMNS
29 # Statement is unsafe because it invokes a trigger or a
30 # stored function that inserts into an AUTO_INCREMENT column.
31
32 # Step-1.1: Create two tables, one with AUTO_INCREMENT column.
33 CREATE TABLE t1(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB;
34 CREATE TABLE t2(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB;
35
36 # Step-1.2: Create a trigger that inserts into an AUTO_INCREMENT column.
37 CREATE TRIGGER trig1 AFTER INSERT ON t1
38 FOR EACH ROW
39 INSERT INTO t2(i) VALUES(new.i);
40
41 # Step-1.3: Create some gap in auto increment value on master's t2 table
42 # but not on slave (by doing rollback). Just in case if the unsafe statements
43 # are written in statement format, diff tables will fail.
44 START TRANSACTION;
45 INSERT INTO t2(i) VALUES (1);
46 ROLLBACK;
47
48 # Step-1.4: Insert a tuple into table t1 that triggers trig1 which inserts
49 # into an AUTO_INCREMENT column.
50 INSERT INTO t1(i) VALUES(2);
51
52 # Step-1.5: Repeat step 1.4 but using 'LOCK TABLES' logic.
53 START TRANSACTION;
54 LOCK TABLES t1 WRITE, t2 WRITE;
55 INSERT INTO t1(i) VALUES(3);
56 UNLOCK TABLES;
57 COMMIT;
58
59 # Step-1.6: Sync slave with master
60 --sync_slave_with_master
61
62 # Step-1.7: Diff master-slave tables to make sure everything is in sync.
63 --let $diff_tables=master:t1, slave:t1
64 --source include/diff_tables.inc
65
66 --let $diff_tables=master:t2, slave:t2
67 --source include/diff_tables.inc
68
69 # Step-1.8: Cleanup
70 --connection master
71 DROP TABLE t1,t2;
72
73 # Case-2: BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT
74 # Statements writing to a table with an auto-increment column after selecting
75 # from another table are unsafe because the order in which rows are retrieved
76 # determines what (if any) rows will be written. This order cannot be
77 # predicted and may differ on master and the slave.
78
79 # Step-2.1: Create two tables, one with AUTO_INCREMENT column.
80 CREATE TABLE t1(i INT) ENGINE=INNODB;
81 CREATE TABLE t2(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB;
82
83 # Step-2.2: Create some tuples in table t1.
84 INSERT INTO t1 values (1), (2), (3);
85
86 # Step-2.3: Create some gap in auto increment value on master's t2 table
87 # but not on slave (by doing rollback). Just in case if the unsafe statements
88 # are written in statement format, diff tables will fail.
89 START TRANSACTION;
90 INSERT INTO t2(i) VALUES (1);
91 ROLLBACK;
92
93 # Step-2.4: Insert into t2 (table with an auto-increment) by selecting tuples
94 # from table t1.
95 INSERT INTO t2(i) SELECT i FROM t1;
96
97 # Step-2.5: Repeat step 2.4 but now with 'LOCK TABLES' logic.
98 START TRANSACTION;
99 LOCK TABLES t2 WRITE, t1 READ;
100 INSERT INTO t2(i) SELECT i FROM t1;
101 UNLOCK TABLES;
102 COMMIT;
103
104 # Step-2.6: Sync slave with master
105 --sync_slave_with_master
106
107 # Step-2.7: Diff master-slave tables to make sure everything is in sync.
108 --let $diff_tables=master:t1, slave:t1
109 --source include/diff_tables.inc
110
111 --let $diff_tables=master:t2, slave:t2
112 --source include/diff_tables.inc
113
114 # Step-2.8: Cleanup
115 --connection master
116 DROP TABLE t1,t2;
117
118 # Case-4: BINLOG_STMT_UNSAFE_INSERT_TWO_KEYS
119 # INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY
120 # is unsafe Statement
121
122 # Step-4.1: Create a table with two unique keys
123 CREATE TABLE t1(i INT, j INT, UNIQUE KEY(i), UNIQUE KEY(j)) ENGINE=INNODB;
124
125 # Step-4.2: Inserting into such a table is unsafe.
126 INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1;
127
128 # Step-4.3: Repeat step 3.2, now with 'LOCK TABLES' logic.
129 START TRANSACTION;
130 LOCK TABLES t1 WRITE;
131 INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1;
132 UNLOCK TABLES;
133 COMMIT;
134
135 # Step-4.4: Sync slave with master
136 --sync_slave_with_master
137
138 # Step-4.5: Diff master-slave tables to make sure everything is in sync.
139 --let $diff_tables=master:t1, slave:t1
140 --source include/diff_tables.inc
141
142 # Step-4.6: Cleanup
143 --connection master
144 DROP TABLE t1;
145
146 --source include/rpl_end.inc