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 # Case-5: BINLOG_STMT_UNSAFE_SKIP_LOCKED
23 ################################################################################
24
25 --source include/have_innodb.inc
26 --source include/have_binlog_format_mixed.inc
27 --source include/master-slave.inc
28
29 # Case-1: BINLOG_STMT_UNSAFE_AUTOINC_COLUMNS
30 # Statement is unsafe because it invokes a trigger or a
31 # stored function that inserts into an AUTO_INCREMENT column.
32
33 # Step-1.1: Create two tables, one with AUTO_INCREMENT column.
34 CREATE TABLE t1(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB;
35 CREATE TABLE t2(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB;
36
37 # Step-1.2: Create a trigger that inserts into an AUTO_INCREMENT column.
38 CREATE TRIGGER trig1 AFTER INSERT ON t1
39 FOR EACH ROW
40 INSERT INTO t2(i) VALUES(new.i);
41
42 # Step-1.3: Create some gap in auto increment value on master's t2 table
43 # but not on slave (by doing rollback). Just in case if the unsafe statements
44 # are written in statement format, diff tables will fail.
45 START TRANSACTION;
46 INSERT INTO t2(i) VALUES (1);
47 ROLLBACK;
48
49 # Step-1.4: Insert a tuple into table t1 that triggers trig1 which inserts
50 # into an AUTO_INCREMENT column.
51 INSERT INTO t1(i) VALUES(2);
52
53 # Step-1.5: Repeat step 1.4 but using 'LOCK TABLES' logic.
54 START TRANSACTION;
55 LOCK TABLES t1 WRITE, t2 WRITE;
56 INSERT INTO t1(i) VALUES(3);
57 UNLOCK TABLES;
58 COMMIT;
59
60 # Step-1.6: Sync slave with master
61 --sync_slave_with_master
62
63 # Step-1.7: Diff master-slave tables to make sure everything is in sync.
64 --let $diff_tables=master:t1, slave:t1
65 --source include/diff_tables.inc
66
67 --let $diff_tables=master:t2, slave:t2
68 --source include/diff_tables.inc
69
70 # Step-1.8: Cleanup
71 --connection master
72 DROP TABLE t1,t2;
73
74 # Case-2: BINLOG_STMT_UNSAFE_WRITE_AUTOINC_SELECT
75 # Statements writing to a table with an auto-increment column after selecting
76 # from another table are unsafe because the order in which rows are retrieved
77 # determines what (if any) rows will be written. This order cannot be
78 # predicted and may differ on master and the slave.
79
80 # Step-2.1: Create two tables, one with AUTO_INCREMENT column.
81 CREATE TABLE t1(i INT) ENGINE=INNODB;
82 CREATE TABLE t2(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB;
83
84 # Step-2.2: Create some tuples in table t1.
85 INSERT INTO t1 values (1), (2), (3);
86
87 # Step-2.3: Create some gap in auto increment value on master's t2 table
88 # but not on slave (by doing rollback). Just in case if the unsafe statements
89 # are written in statement format, diff tables will fail.
90 START TRANSACTION;
91 INSERT INTO t2(i) VALUES (1);
92 ROLLBACK;
93
94 # Step-2.4: Insert into t2 (table with an auto-increment) by selecting tuples
95 # from table t1.
96 INSERT INTO t2(i) SELECT i FROM t1;
97
98 # Step-2.5: Repeat step 2.4 but now with 'LOCK TABLES' logic.
99 START TRANSACTION;
100 LOCK TABLES t2 WRITE, t1 READ;
101 INSERT INTO t2(i) SELECT i FROM t1;
102 UNLOCK TABLES;
103 COMMIT;
104
105 # Step-2.6: Sync slave with master
106 --sync_slave_with_master
107
108 # Step-2.7: Diff master-slave tables to make sure everything is in sync.
109 --let $diff_tables=master:t1, slave:t1
110 --source include/diff_tables.inc
111
112 --let $diff_tables=master:t2, slave:t2
113 --source include/diff_tables.inc
114
115 # Step-2.8: Cleanup
116 --connection master
117 DROP TABLE t1,t2;
118
119 # Case-3: BINLOG_STMT_UNSAFE_AUTOINC_NOT_FIRST
120 # INSERT into autoincrement field which is not the first part in the
121 # composed primary key is unsafe
122 #
123 # Step-3.1: Create a table with auto increment column and a composed primary key
124 # (second column is auto increment column). Such a definition is allowed only
125 # with 'myisam' engine.
126 CREATE TABLE t1(i int, id INT AUTO_INCREMENT, PRIMARY KEY (i, id)) ENGINE=MYISAM;
127
128 # Step-3.2: Inserting into such a table is unsafe.
129 INSERT INTO t1 (i) values (1);
130
131 # Step-3.3: Repeat step 3.2, now with 'LOCK TABLES' logic.
132 START TRANSACTION;
133 LOCK TABLES t1 WRITE;
134 INSERT INTO t1 (i) values (2);
135 UNLOCK TABLES;
136 COMMIT;
137
138 # Step-3.4: Sync slave with master
139 --sync_slave_with_master
140
141 # Step-3.5: Diff master-slave tables to make sure everything is in sync.
142 --let $diff_tables=master:t1, slave:t1
143 --source include/diff_tables.inc
144
145 # Step-3.6: Cleanup
146 --connection master
147 DROP TABLE t1;
148
149 # Case-4: BINLOG_STMT_UNSAFE_INSERT_TWO_KEYS
150 # INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY
151 # is unsafe Statement
152
153 # Step-4.1: Create a table with two unique keys
154 CREATE TABLE t1(i INT, j INT, UNIQUE KEY(i), UNIQUE KEY(j)) ENGINE=INNODB;
155
156 # Step-4.2: Inserting into such a table is unsafe.
157 INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1;
158
159 # Step-4.3: Repeat step 3.2, now with 'LOCK TABLES' logic.
160 START TRANSACTION;
161 LOCK TABLES t1 WRITE;
162 INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1;
163 UNLOCK TABLES;
164 COMMIT;
165
166 # Step-4.4: Sync slave with master
167 --sync_slave_with_master
168
169 # Step-4.5: Diff master-slave tables to make sure everything is in sync.
170 --let $diff_tables=master:t1, slave:t1
171 --source include/diff_tables.inc
172
173 # Step-4.6: Cleanup
174 --connection master
175 DROP TABLE t1;
176
177 # Case-5: BINLOG_STMT_UNSAFE_SKIP_LOCKED
178 # INSERT... ON KEY UPDATE SKIP LOCKED is unsafe Statement
179
180 # Step-5.1: Create a table some index
181 CREATE TABLE t1(i INT,PRIMARY KEY(i)) ENGINE=INNODB;
182 CREATE TABLE t2(i INT,PRIMARY KEY(i)) ENGINE=INNODB;
183
184 # Step-5.2: Inserting some values
185 INSERT INTO t1 (i) VALUES (1),(2),(3),(4),(5);
186
187 # Step-5.3: Lock one of the values
188 connect (con1, localhost, root,);
189 START TRANSACTION;
190 SELECT i FROM t1 WHERE i=3 FOR UPDATE;
191
192 # Step-5.4: Create non-deterministic inserts/tables
193 --connection master
194 INSERT INTO t2 SELECT i FROM t1 LOCK IN SHARE MODE SKIP LOCKED;
195 CREATE TABLE t3 AS SELECT i FROM t1 LOCK IN SHARE MODE SKIP LOCKED;
196 SELECT * FROM t2 ORDER BY i;
197 SELECT * FROM t3 ORDER BY i;
198
199 # Step-5.5: Sync slave with master
200 --sync_slave_with_master
201
202 # Step-5.6: Diff master-replica tables insert statements are in sync
203 --let $diff_tables=master:t2, slave:t2
204 --source include/diff_tables.inc
205
206 # Step-5.7: Diff master-replica tables create select table is in sync
207 --let $diff_tables=master:t3, slave:t3
208 --source include/diff_tables.inc
209
210 # Step-5.8: Cleanup
211 --disconnect con1
212 --connection master
213 DROP TABLE t1, t2, t3;
214
215 --source include/rpl_end.inc