Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_binlog_format_errors.test

raw
1 # ==== Purpose ====
2 # The purpose of this script is to test that binary logging returns an error
3 # when its format is incompatible with the statement to be logged and to verify
4 # the correctness of the error message.
5 #
6 # ==== Requirements ====
7 # For the conditions 1 to 7 listed in the comments section of method
8 # decide_logging_format() verify that binary logging returns the proper error or
9 # warning.
10 #
11 # R1. When both row-incapable and statement-incapable storage engines are
12 # involved then binary logging returns error
13 # `ER_BINLOG_ROW_ENGINE_AND_STMT_ENGINE`.
14 # R2. When binlog_format = ROW and the SQL statement contains a table stored in
15 # a engine limited to statement-logging then binary logging returns error
16 # `ER_BINLOG_ROW_MODE_AND_STMT_ENGINE`.
17 # R3. When binlog_format = ROW and a row is injected in a engine limited to
18 # statement-logging then binary_log returns error
19 # `ER_BINLOG_ROW_INJECTION_AND_STMT_ENGINE`.
20 # R4. When binlog_format = MIXED, the SQL statement is unsafe and the storage
21 # engine is limited to statement-logging then binary logs returns error
22 # `ER_BINLOG_UNSAFE_AND_STMT_ENGINE`.
23 # R5. When binlog_format = STATEMENT and the SQL statement contains a table
24 # stored in a engine limited to row-logging then binary log returns error
25 # `ER_BINLOG_STMT_MODE_AND_ROW_ENGINE`.
26 # R6. When binlog_format = STATEMENT and a row is injected then binary logging
27 # returns error `ER_BINLOG_ROW_INJECTION_AND_STMT_MODE`.
28 # R7. When binlog_format = STATEMENT and the SQL statement is unsafe then a
29 # warning is returned.
30 #
31 # ==== Implementation ====
32 #
33 # TC1. Verify that when both row-incapable and statement-incapable storage
34 # engines are involved in a SQL statement then binary logging returns the
35 # error `ER_BINLOG_ROW_ENGINE_AND_STMT_ENGINE`.
36 # ------------------------------------------------------------------
37 # With binlog_format = ROW :
38 # 1) Create a trigger which inserts data on a row-only table upon insertion of
39 # data in statement-only table.
40 # 2) Try to insert data in the statement-only table.
41 # 3) Verify that error `ER_BINLOG_ROW_ENGINE_AND_STMT_ENGINE` is returned.
42 # 4) Verify that no data was inserted in the statement-only table.
43 # 5) Verify that no data was inserted in the row-only table.
44 #
45 # TC2. Verify that when binlog_format = ROW and the SQL statement contains a
46 # table stored in a engine limited to statement-logging then binary logging
47 # returns the error `ER_BINLOG_ROW_MODE_AND_STMT_ENGINE`.
48 # ------------------------------------------------------------------
49 # With binlog_format = ROW :
50 #
51 # 1) Try to insert data in a statement-only table.
52 # 2) Verify that `ER_BINLOG_ROW_MODE_AND_STMT_ENGINE` is returned.
53 # 3) Verify that no data was inserted in the statement-only table.
54 #
55 # TC3. Verify that when binlog_format = ROW and the SQL statement modifies data
56 # of a table stored on the slave server in a engine limited to
57 # statement-logging then binary logging returns the error
58 # `ER_BINLOG_ROW_INJECTION_AND_STMT_ENGINE`.
59 # ------------------------------------------------------------------
60 # With binlog_format = ROW :
61 # 1) On the master server insert data in a table which is stored in the slave as
62 # statement-only.
63 # 2) On the slave server verify that error
64 # `ER_BINLOG_ROW_INJECTION_AND_STMT_ENGINE` is returned.
65 # 3) On the slave server verify that no data was inserted in the
66 # statement-only table.
67 #
68 # TC4. Verify that when binlog_format = ROW and a row is injected in a table
69 # stored in a engine limited to statement-logging then binary logging
70 # returns the error
71 # `ER_BINLOG_ROW_INJECTION_AND_STMT_ENGINE`.
72 # ------------------------------------------------------------------
73 # With binlog_format = ROW :
74 # 1) Execute a BINLOG statement which inserts data in a
75 # statement-only table.
76 # 2) Verify that error `ER_BINLOG_ROW_INJECTION_AND_STMT_ENGINE` is returned.
77 # 3) Verify that no data was inserted in the statement-only
78 # table.
79 #
80 # TC5. Verify that when binlog_format = MIXED, the SQL statement is unsafe and
81 # the storage engine is limited to statement-logging then binary logging
82 # returns the error `ER_BINLOG_UNSAFE_AND_STMT_ENGINE`.
83 # ------------------------------------------------------------------
84 # With binlog_format = MIXED :
85 # 1) Try to execute an unsafe statement on a statement-only table.
86 # 2) Verify that error `ER_BINLOG_UNSAFE_AND_STMT_ENGINE` is returned.
87 # 3) Verify that no data was inserted in the statement-only table.
88 #
89 # TC6. Verify that when binlog_format = MIXED and a multi-unsafe statement is
90 # executed in a table stored in a engine limited to statement-logging then
91 # binary logging returns the error `ER_BINLOG_UNSAFE_AND_STMT_ENGINE`.
92 # ------------------------------------------------------------------
93 # With binlog_format = MIXED :
94 # 1) Try to execute a multi-unsafe statement in a statement-only table.
95 # 2) Verify that error `ER_BINLOG_UNSAFE_AND_STMT_ENGINE` is returned.
96 # 3) Verify that no data was inserted in the statement-only table.
97 #
98 # TC7. Verify that when binlog_format = STATEMENT and the SQL statement modifies
99 # a table stored in INNODB engine as row-only then binary logging returns
100 # the error `ER_BINLOG_STMT_MODE_AND_ROW_ENGINE`.
101 # ------------------------------------------------------------------
102 # With binlog_format = STATEMENT :
103 # 1) Try to insert data on table stored in INNODB as row-only.
104 # 2) Verify that error `ER_BINLOG_STMT_MODE_AND_ROW_ENGINE` is returned.
105 # 3) Verify that no data was inserted in the table.
106 #
107 # TC8. Verify that when binlog_format = STATEMENT and the default database is
108 # ignored by binary logging no error is returned if the SQL statement
109 # modifies a table stored in INNODB as row-only.
110 # ------------------------------------------------------------------
111 # With binlog_format = STATEMENT :
112 # 1) Set the default database to a database which is ignored by binary logging.
113 # 2) Insert data in the table stored in INNODB as row-only.
114 # 3) Verify that data was inserted.
115 #
116 # TC9. Verify that when binlog_format = STATEMENT and the SQL statement modifies
117 # a table stored in a row-only engine then binary logging returns the error
118 # `ER_BINLOG_STMT_MODE_AND_ROW_ENGINE`.
119 # ------------------------------------------------------------------
120 # With binlog_format = STATEMENT:
121 # 1) Try to insert data in a table stored in a row-only engine.
122 # 2) Verify that error `ER_BINLOG_STMT_MODE_AND_ROW_ENGINE` is returned.
123 # 3) Verify that no data was inserted in the row-only table.
124 #
125 # TC10. Verify that when binlog_format = STATEMENT and the default database is
126 # ignored by binary logging no error is returned if the SQL statement
127 # modifies a table stored in a row-only engine.
128 # ------------------------------------------------------------------
129 # With binlog_format = STATEMENT :
130 # 1) Set the default database to a database which is ignored by binary logging.
131 # 2) Insert data in a table stored in the row-only engine.
132 # 3) Verify that data was inserted.
133 #
134 # TC11. Verify that when binlog_format = STATEMENT and a row is injected then
135 # binary logging returns the error `ER_BINLOG_ROW_INJECTION_AND_STMT_MODE`.
136 # ------------------------------------------------------------------
137 # With binlog_format = STATEMENT :
138 # 1) Execute a BINLOG statement which inserts data.
139 # 2) Verify that error `ER_BINLOG_ROW_INJECTION_AND_STMT_MODE` is returned.
140 # 3) Verify that no data was inserted.
141 #
142 # TC12. Verify that when binlog_format = STATEMENT and the default database is
143 # ignored by binary logging no error is returned for row injection.
144 # ------------------------------------------------------------------
145 # With binlog_format = STATEMENT :
146 # 1) Set the default database to a database which is ignored by binary logging.
147 # 2) Execute a BINLOG statement which inserts data.
148 # 3) Verify that data was inserted.
149 #
150 # TC13. Verify that when binlog_format = STATEMENT and the SQL statement is
151 # unsafe then a warning is returned.
152 # ------------------------------------------------------------------
153 # With binlog_format = STATEMENT :
154 # 1) Execute a unsafe SQL statement.
155 # 2) Verify that warning is returned.
156 # 3) Verify that data was inserted.
157 #
158 # TC14. Verify that when binlog_format = STATEMENT and the default database is
159 # ignored by binary logging no warning is returned for an SQL unsafe
160 # statement.
161 # ------------------------------------------------------------------
162 # With binlog_format = STATEMENT :
163 # 1) Set the default database to a database which is ignored by binary logging.
164 # 2) Execute a unsafe statement.
165 # 3) Verify that no warning is returned.
166 # 4) Verify that data was inserted.
167 #
168 # TC15. Verify that when binlog_format = STATEMENT, a row is injected in the
169 # master server having binlog_format = MIXED and the slave server has
170 # binlog_format = STATEMENT then binary returns the error
171 # `ER_BINLOG_ROW_INJECTION_AND_STMT_MODE`.
172 # ------------------------------------------------------------------
173 # With binlog_format = MIXED for the master server and binlog_format = STATEMENT
174 # for the slave server
175 # 1) On the master server execute a BINLOG statement which injects a row.
176 # 2) Verify that on the slave server the error
177 # `ER_BINLOG_ROW_INJECTION_AND_STMT_MODE` is returned.
178 # 3) Verify that no data was inserted in the slave server.
179 #
180 # ==== References ====
181 # BUG#39934: Slave stops for engine that only support row-based logging
182 # BUG#42829: binlogging enabled for all schemas regardless of binlog-do-db /
183 # binlog-ignore-db
184 #
185 # ==== Related test cases ====
186 #
187 # binlog.binlog_unsafe verifies more thoroughly that a warning is
188 # given for the case when an unsafe statement is executed and
189 # binlog_format = STATEMENT.
190 #
191 # ndb_rpl.ndb_rpl_binlog_format_errors verifies that a binlog error is issued
192 # if more than one engine is involved in a statement and at least one is doing
193 # it's own logging (is *self-logging*), which is the case for NDB engine.
194
195 # Need debug so that 'SET @@session.debug' works.
196 --source include/have_debug.inc
197 # Need example plugin because it is the only statement-only engine.
198 # Dynamic loading of Example does not work on Windows currently.
199 --source include/not_windows.inc
200 --source include/have_example_plugin.inc
201 # The test changes binlog_format, so there is no reason to run it
202 # under more than one binlog format.
203 --source include/have_binlog_format_row.inc
204 # Disable multi threaded slave since an error in a worker slave leads to an
205 # upper-level error in the coordinator
206 --source include/not_mts_replica_parallel_workers.inc
207 # The test does not support group replication plugin since binlog_format is
208 # not equal to 'ROW' for some test cases
209 --source include/not_group_replication_plugin.inc
210 # The test requires MyISAM engine
211 --source include/have_myisam.inc
212
213 --source include/master-slave.inc
214
215 --disable_query_log
216 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since binlog_format = STATEMENT");
217 CALL mtr.add_suppression("Cannot execute statement: impossible to write to binary log");
218 --enable_query_log
219
220 --echo ==== Initialize ====
221
222 --echo [on slave]
223 --source include/rpl_connection_slave.inc
224
225 SET @old_binlog_format= @@global.binlog_format;
226 INSTALL PLUGIN example SONAME 'ha_example.so';
227
228 --echo [on master]
229 --source include/rpl_connection_master.inc
230
231 SET @old_binlog_format= @@global.binlog_format;
232 INSTALL PLUGIN example SONAME 'ha_example.so';
233
234 CREATE TABLE t (a VARCHAR(100)) ENGINE = MYISAM;
235 CREATE TABLE t_row (a VARCHAR(100)) ENGINE = INNODB;
236 CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE;
237 CREATE TABLE t_slave_stmt (a VARCHAR(100)) ENGINE = MYISAM;
238 CREATE TABLE t_autoinc (a INT KEY AUTO_INCREMENT) ENGINE = MYISAM;
239 CREATE TABLE t_double_autoinc (a INT KEY AUTO_INCREMENT) ENGINE = MYISAM;
240
241 --eval CREATE TRIGGER trig_autoinc BEFORE INSERT ON t_autoinc FOR EACH ROW BEGIN INSERT INTO t_stmt VALUES ('x'); END
242 --eval CREATE TRIGGER trig_double_autoinc BEFORE INSERT ON t_double_autoinc FOR EACH ROW BEGIN INSERT INTO t_autoinc VALUES (NULL); END
243
244 CREATE DATABASE other;
245
246 # This makes the innodb table row-only
247 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
248
249 --echo [on slave]
250 --source include/sync_slave_sql_with_master.inc
251
252 DROP TABLE t_slave_stmt;
253 CREATE TABLE t_slave_stmt (a INT) ENGINE = EXAMPLE;
254
255 --echo [on master]
256 --source include/rpl_connection_master.inc
257
258 # This is a format description event. It is needed because any BINLOG
259 # statement containing a row event must be preceded by a BINLOG
260 # statement containing a format description event.
261 BINLOG '
262 1gRVSg8BAAAAZgAAAGoAAAABAAQANS4xLjM2LWRlYnVnLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAA
263 AAAAAAAAAAAAAAAAAADWBFVKEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
264 ';
265
266
267 --echo ==== Test ====
268
269 --echo ---- binlog_format='row' ----
270
271
272 --echo * TC1: Modify both row-only and stmt-only table
273 --eval CREATE TRIGGER trig_2 AFTER INSERT ON t_stmt FOR EACH ROW BEGIN INSERT INTO t_row VALUES(1); END
274 --error ER_BINLOG_ROW_ENGINE_AND_STMT_ENGINE
275 INSERT INTO t_stmt VALUES (1);
276 SELECT * FROM t_stmt /* should be empty */;
277 SELECT * FROM t_row /* should be empty */;
278 DROP TRIGGER trig_2;
279
280 --echo * TC2: Stmt-only table and binlog_format='row'
281 --error ER_BINLOG_ROW_MODE_AND_STMT_ENGINE
282 INSERT INTO t_stmt VALUES (1);
283 SELECT * FROM t_stmt /* should be empty */;
284
285 --echo * TC3: Row injection and stmt-only table: in slave sql thread
286 INSERT INTO t_slave_stmt VALUES (1);
287 --echo [on slave]
288 --source include/rpl_connection_slave.inc
289 # 1664 = ER_BINLOG_ROW_INJECTION_AND_STMT_ENGINE
290 --let $slave_sql_errno= 1664
291 --let $show_slave_sql_error= 1
292 --source include/wait_for_slave_sql_error_and_skip.inc
293 --source include/rpl_connection_slave.inc
294 SELECT * FROM t_slave_stmt /* should be empty */;
295
296 --echo [on master]
297 --source include/rpl_connection_master.inc
298 --echo * TC4: Row injection and stmt-only table: use BINLOG statement
299 # This is a Table_map_event and a Write_rows_event. Together, they are
300 # equivalent to 'INSERT INTO t_stmt VALUES (1)'
301 --error ER_BINLOG_ROW_INJECTION_AND_STMT_ENGINE
302 BINLOG '
303 1gRVShMBAAAALwAAAEABAAAAABcAAAAAAAAABHRlc3QABnRfc3RtdAABDwJkAAE=
304 1gRVShcBAAAAIAAAAGABAAAQABcAAAAAAAEAAf/+ATE=
305 ';
306 SELECT * FROM t_stmt /* should be empty */;
307
308
309 --echo ---- binlog_format=mixed ----
310
311 --echo [on slave]
312 --source include/sync_slave_sql_with_master.inc
313 --source include/stop_slave.inc
314 SET @@global.binlog_format = MIXED;
315 --source include/start_slave.inc
316 --echo [on master]
317 --source include/rpl_connection_master.inc
318 SET @@global.binlog_format = MIXED;
319 SET @@session.binlog_format = MIXED;
320
321 --echo * TC5: Unsafe statement and stmt-only engine
322 --error ER_BINLOG_UNSAFE_AND_STMT_ENGINE
323 INSERT INTO t_stmt VALUES (UUID());
324 SELECT * FROM t_stmt; /* should be empty */;
325
326 # Concatenate two unsafe values, and then concatenate NULL to
327 # that so that the result is NULL and we instead use autoinc.
328 --echo * TC6: Multi-unsafe statement and stmt-only engine
329 --error ER_BINLOG_UNSAFE_AND_STMT_ENGINE
330 INSERT INTO t_double_autoinc SELECT CONCAT(UUID(), @@hostname, NULL) FROM mysql.general_log LIMIT 1;
331 SELECT * FROM t_double_autoinc; /* should be empty */;
332
333
334 --echo ---- binlog_format=statement ----
335
336 --echo [on slave]
337 --source include/sync_slave_sql_with_master.inc
338 --source include/stop_slave.inc
339 SET @@global.binlog_format = STATEMENT;
340 --source include/start_slave.inc
341 --echo [on master]
342 --source include/rpl_connection_master.inc
343 SET @@global.binlog_format = STATEMENT;
344 SET @@session.binlog_format = STATEMENT;
345
346 --echo * TC7: Row-only engine and binlog_format=statement: innodb-specific message
347 --error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
348 INSERT INTO t_row VALUES (1);
349 SELECT * FROM t_row /* should be empty */;
350
351 --echo * TC8: Same statement, but db filtered out - no error
352 USE other;
353 INSERT INTO test.t_row VALUES (1);
354 USE test;
355 SELECT * FROM t_row /* should contain the value 1 */;
356 USE other;
357 DELETE FROM test.t_row;
358 USE test;
359
360 --echo * TC9: Row-only engine and binlog_format=statement: generic message
361 SET @@session.debug= '+d,no_innodb_binlog_errors';
362 --error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE
363 INSERT INTO t_row VALUES (1);
364 SELECT * FROM t_row /* should be empty */;
365
366 --echo * TC10: Same statement, but db filtered out - no error
367 USE other;
368 INSERT INTO test.t_row VALUES (1);
369 USE test;
370 SET @@session.debug= '';
371 SELECT * FROM t_row /* should contain the value 1 */;
372
373 --echo * TC11: Row injection and binlog_format=statement: BINLOG statement
374 # This is a Table_map_event and a Write_rows_event. Together, they are
375 # equivalent to 'INSERT INTO t VALUES (1)'.
376 --error ER_BINLOG_ROW_INJECTION_AND_STMT_MODE
377 BINLOG '
378 cNpVShMBAAAAKgAAADYBAAAAABcAAAAAAAAABHRlc3QAAXQAAQ8CZAAB
379 cNpVShcBAAAAIAAAAFYBAAAQABcAAAAAAAEAAf/+ATE=
380 ';
381 SELECT * FROM t /* should be empty */;
382
383 --echo * TC12: Same statement, but db filtered out - no error
384 # This is a Table_map_event and a Write_rows_event. Together, they are
385 # equivalent to 'INSERT INTO t VALUES (1)'.
386 USE other;
387 BINLOG '
388 cNpVShMBAAAAKgAAADYBAAAAABcAAAAAAAAABHRlc3QAAXQAAQ8CZAAB
389 cNpVShcBAAAAIAAAAFYBAAAQABcAAAAAAAEAAf/+ATE=
390 ';
391 USE test;
392 SELECT * FROM t /* should contain the value 1 */;
393 DELETE FROM t;
394
395 --echo * TC13: Unsafe statement and binlog_format=statement
396 # This will give a warning.
397 INSERT INTO t VALUES (COALESCE(1, UUID()));
398 SELECT * FROM t /* should contain the value 1 */;
399 DELETE FROM t;
400
401 --echo * TC14: Same statement, but db filtered out - no message
402 USE other;
403 INSERT INTO test.t VALUES (COALESCE(1, UUID()));
404 USE test;
405 SELECT * FROM t /* should contain the value 1 */;
406 DELETE FROM t;
407
408
409 --echo ---- master: binlog_format=mixed, slave: binlog_format=statement ----
410
411 SET @@global.binlog_format = MIXED;
412 SET @@session.binlog_format = MIXED;
413
414 --echo * TC15: Row injection and binlog_format=statement: in slave sql thread
415 INSERT INTO t VALUES (COALESCE(1, UUID()));
416 --echo [on slave]
417 --source include/rpl_connection_slave.inc
418 # 1666 = ER_BINLOG_ROW_INJECTION_AND_STMT_MODE
419 --let $slave_sql_errno= 1666
420 --let $show_sql_error= 1
421 --source include/wait_for_slave_sql_error_and_skip.inc
422 --source include/rpl_connection_slave.inc
423 SELECT * FROM t /* should be empty */;
424 --echo [on master]
425 --source include/rpl_connection_master.inc
426
427
428 --echo ==== Clean up ====
429
430 DROP TRIGGER trig_autoinc;
431 DROP TRIGGER trig_double_autoinc;
432 DROP TABLE t, t_row, t_stmt, t_slave_stmt, t_autoinc, t_double_autoinc;
433 DROP DATABASE other;
434 SET @@global.binlog_format = @old_binlog_format;
435 SET @@session.binlog_format = @old_binlog_format;
436 UNINSTALL PLUGIN example;
437 --echo [on slave]
438 --source include/sync_slave_sql_with_master.inc
439 --source include/stop_slave.inc
440 SET @@global.binlog_format = @old_binlog_format;
441 SET @@session.binlog_format = @old_binlog_format;
442 --source include/start_slave.inc
443 UNINSTALL PLUGIN example;
444
445 --source include/rpl_end.inc