Debian Codegraph @

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

raw
1 #
2 # rpl_switch_stm_row_mixed tests covers
3 #
4 # - Master is switching explicitly between STATEMENT, ROW, and MIXED
5 # binlog format showing when it is possible and when not.
6 # - Master switching from MIXED to RBR implicitly listing all use
7 # cases, e.g a query invokes UUID(), thereafter to serve as the
8 # definition of MIXED binlog format
9 # - correctness of execution
10
11 # Thread stack overrun in debug mode on sparc
12 --source include/not_sparc_debug.inc
13 --source include/not_ndb_default.inc
14 --source include/not_group_replication_plugin.inc
15 --source include/set_privilege_checks_user_as_system_user.inc
16 --source include/master-slave.inc
17
18 # Since this test generates row-based events in the binary log, the
19 # slave SQL thread cannot be in STATEMENT mode to execute this test,
20 # so we only execute it for MIXED and ROW as default value of
21 # BINLOG_FORMAT.
22
23 connection slave;
24 -- source include/have_binlog_format_mixed_or_row.inc
25
26 connection master;
27 --disable_warnings
28 drop database if exists mysqltest1;
29 create database mysqltest1;
30 --enable_warnings
31 use mysqltest1;
32
33 # Save binlog format
34 set @my_binlog_format= @@global.binlog_format;
35
36 # play with switching
37 set session binlog_format='mixed';
38 show session variables like "binlog_format%";
39 set session binlog_format='statement';
40 show session variables like "binlog_format%";
41 set session binlog_format='row';
42 show session variables like "binlog_format%";
43
44 set global binlog_format=DEFAULT;
45 show global variables like "binlog_format%";
46 set global binlog_format=MIXED;
47 show global variables like "binlog_format%";
48 set global binlog_format=STATEMENT;
49 show global variables like "binlog_format%";
50 set global binlog_format='ROW';
51 show global variables like "binlog_format%";
52 show session variables like "binlog_format%";
53 select @@global.binlog_format, @@session.binlog_format;
54
55 CREATE TABLE t1 (a varchar(100));
56
57 prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
58 set @string="emergency_1_";
59 insert into t1 values("work_2_");
60 execute stmt1 using @string;
61 deallocate prepare stmt1;
62
63 prepare stmt1 from 'insert into t1 select ?';
64 insert into t1 values(concat(UUID(),"work_3_"));
65 execute stmt1 using @string;
66 deallocate prepare stmt1;
67
68 insert into t1 values(concat("for_4_",UUID()));
69 insert into t1 select "yesterday_5_";
70
71 # verify that temp tables prevent a switch to SBR
72 create temporary table tmp(a char(100));
73 insert into tmp values("see_6_");
74 --error ER_TEMP_TABLE_PREVENTS_SWITCH_SESSION_BINLOG_FORMAT
75 set binlog_format=statement;
76 insert into t1 select * from tmp;
77 drop temporary table tmp;
78
79 # Now we go to SBR
80 set binlog_format=statement;
81 show global variables like "binlog_format%";
82 show session variables like "binlog_format%";
83 select @@global.binlog_format, @@session.binlog_format;
84 set global binlog_format=statement;
85 show global variables like "binlog_format%";
86 show session variables like "binlog_format%";
87 select @@global.binlog_format, @@session.binlog_format;
88
89 prepare stmt1 from 'insert into t1 select ?';
90 set @string="emergency_7_";
91 insert into t1 values("work_8_");
92 execute stmt1 using @string;
93 deallocate prepare stmt1;
94
95 prepare stmt1 from 'insert into t1 select ?';
96 insert into t1 values("work_9_");
97 execute stmt1 using @string;
98 deallocate prepare stmt1;
99
100 insert into t1 values("for_10_");
101 insert into t1 select "yesterday_11_";
102
103 # test statement (is not default after wl#3368)
104 set binlog_format=statement;
105 select @@global.binlog_format, @@session.binlog_format;
106 set global binlog_format=statement;
107 select @@global.binlog_format, @@session.binlog_format;
108
109 prepare stmt1 from 'insert into t1 select ?';
110 set @string="emergency_12_";
111 insert into t1 values("work_13_");
112 execute stmt1 using @string;
113 deallocate prepare stmt1;
114
115 prepare stmt1 from 'insert into t1 select ?';
116 insert into t1 values("work_14_");
117 execute stmt1 using @string;
118 deallocate prepare stmt1;
119
120 insert into t1 values("for_15_");
121 insert into t1 select "yesterday_16_";
122
123 # and now the mixed mode
124
125 set global binlog_format=mixed;
126 select @@global.binlog_format, @@session.binlog_format;
127 set binlog_format=default;
128 select @@global.binlog_format, @@session.binlog_format;
129
130 prepare stmt1 from 'insert into t1 select concat(UUID(),?)';
131 set @string="emergency_17_";
132 insert into t1 values("work_18_");
133 execute stmt1 using @string;
134 deallocate prepare stmt1;
135
136 prepare stmt1 from 'insert into t1 select ?';
137 insert into t1 values(concat(UUID(),"work_19_"));
138 execute stmt1 using @string;
139 deallocate prepare stmt1;
140
141 insert into t1 values(concat("for_20_",UUID()));
142 insert into t1 select "yesterday_21_";
143
144 prepare stmt1 from 'insert into t1 select ?';
145 insert into t1 values(concat(UUID(),"work_22_"));
146 execute stmt1 using @string;
147 deallocate prepare stmt1;
148
149 insert into t1 values(concat("for_23_",UUID()));
150 insert into t1 select "yesterday_24_";
151
152 # Test of CREATE TABLE SELECT
153
154 create table t2 select rpad(UUID(),100,' ');
155 create table t3 select 1 union select UUID();
156 # UUID cannot be converted to DOUBLE
157 # create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3);
158 create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
159 # what if UUID() is first:
160 --disable_warnings
161 insert ignore into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3);
162 --enable_warnings
163
164 # inside a stored procedure
165
166 delimiter |;
167 create procedure foo()
168 begin
169 insert into t1 values("work_25_");
170 insert into t1 values(concat("for_26_",UUID()));
171 insert into t1 select "yesterday_27_";
172 end|
173 create procedure foo2()
174 begin
175 insert into t1 values(concat("emergency_28_",UUID()));
176 insert into t1 values("work_29_");
177 insert into t1 values(concat("for_30_",UUID()));
178 set session binlog_format='row'; # accepted for stored procs
179 insert into t1 values("more work_31_");
180 set session binlog_format=mixed;
181 end|
182 create function foo3() returns bigint unsigned
183 begin
184 set session binlog_format='row'; # rejected for stored funcs
185 insert into t1 values("alarm");
186 return 100;
187 end|
188 create procedure foo4(x varchar(100))
189 begin
190 insert into t1 values(concat("work_250_",x));
191 insert into t1 select "yesterday_270_";
192 end|
193 delimiter ;|
194 call foo();
195 call foo2();
196 call foo4("hello");
197 call foo4(UUID());
198 call foo4("world");
199
200 # test that can't SET in a stored function
201 --error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT
202 select foo3();
203 select * from t1 where a="alarm";
204
205 # Tests of stored functions/triggers/views for BUG#20930 "Mixed
206 # binlogging mode does not work with stored functions, triggers,
207 # views"
208
209 # Function which calls procedure
210 drop function foo3;
211 delimiter |;
212 create function foo3() returns bigint unsigned
213 begin
214 insert into t1 values("foo3_32_");
215 call foo();
216 return 100;
217 end|
218 delimiter ;|
219 insert into t2 select foo3();
220
221 prepare stmt1 from 'insert into t2 select foo3()';
222 execute stmt1;
223 execute stmt1;
224 deallocate prepare stmt1;
225
226 # Test if stored function calls stored function which calls procedure
227 # which requires row-based.
228
229 delimiter |;
230 create function foo4() returns bigint unsigned
231 begin
232 insert into t2 select foo3();
233 return 100;
234 end|
235 delimiter ;|
236 select foo4();
237
238 prepare stmt1 from 'select foo4()';
239 execute stmt1;
240 execute stmt1;
241 deallocate prepare stmt1;
242
243 # A simple stored function
244 delimiter |;
245 create function foo5() returns bigint unsigned
246 begin
247 insert into t2 select UUID();
248 return 100;
249 end|
250 delimiter ;|
251 select foo5();
252
253 prepare stmt1 from 'select foo5()';
254 execute stmt1;
255 execute stmt1;
256 deallocate prepare stmt1;
257
258 # A simple stored function where UUID() is in the argument
259 delimiter |;
260 create function foo6(x varchar(100)) returns bigint unsigned
261 begin
262 insert into t2 select x;
263 return 100;
264 end|
265 delimiter ;|
266 select foo6("foo6_1_");
267 select foo6(concat("foo6_2_",UUID()));
268
269 prepare stmt1 from 'select foo6(concat("foo6_3_",UUID()))';
270 execute stmt1;
271 execute stmt1;
272 deallocate prepare stmt1;
273
274
275 # Test of views using UUID()
276
277 create view v1 as select uuid();
278 create table t11 (data varchar(255));
279 insert into t11 select * from v1;
280 # Test of querying INFORMATION_SCHEMA which parses the view's body,
281 # to verify that it binlogs statement-based (is not polluted by
282 # the parsing of the view's body).
283 insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11');
284 prepare stmt1 from "insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11')";
285 execute stmt1;
286 execute stmt1;
287 deallocate prepare stmt1;
288
289 # Test of triggers with UUID()
290 delimiter |;
291 create trigger t11_bi before insert on t11 for each row
292 begin
293 set NEW.data = concat(NEW.data,UUID());
294 end|
295 delimiter ;|
296 insert into t11 values("try_560_");
297
298 # Test that INSERT DELAYED works in mixed mode (BUG#20649)
299 insert delayed into t2 values("delay_1_");
300 insert delayed into t2 values(concat("delay_2_",UUID()));
301 insert delayed into t2 values("delay_6_");
302
303 # Test for BUG#20633 (INSERT DELAYED RAND()/user_variable does not
304 # replicate fine in statement-based ; we test that in mixed mode it
305 # works).
306 insert delayed into t2 values(rand());
307 set @a=2.345;
308 insert delayed into t2 values(@a);
309
310 # With INSERT DELAYED, rows are written to the binlog after they are
311 # written to the table. Therefore, it is not enough to wait until the
312 # rows make it to t2 on the master (the rows may not be in the binlog
313 # at that time, and may still not be in the binlog when
314 # sync_slave_with_master is later called). Instead, we wait until the
315 # rows make it to t2 on the slave. We first call
316 # sync_slave_with_master, so that we are sure that t2 has been created
317 # on the slave.
318 --source include/sync_slave_sql_with_master.inc
319 let $wait_condition= SELECT COUNT(*) = 19 FROM mysqltest1.t2;
320 --source include/wait_condition.inc
321 connection master;
322
323 # If you want to do manual testing of the mixed mode regarding UDFs (not
324 # testable automatically as quite platform- and compiler-dependent),
325 # you just need to set the variable below to 1, and to
326 # "make udf_example.so" in sql/, and to copy sql/udf_example.so to
327 # MYSQL_TEST_DIR/lib/mysql.
328 let $you_want_to_test_UDF=0;
329 if ($you_want_to_test_UDF)
330 {
331 CREATE FUNCTION metaphon RETURNS STRING SONAME '$UDF_EXAMPLE_LIB';
332 prepare stmt1 from 'insert into t1 select metaphon(?)';
333 set @string="emergency_133_";
334 insert into t1 values("work_134_");
335 execute stmt1 using @string;
336 deallocate prepare stmt1;
337 prepare stmt1 from 'insert into t1 select ?';
338 insert into t1 values(metaphon("work_135_"));
339 execute stmt1 using @string;
340 deallocate prepare stmt1;
341 insert into t1 values(metaphon("for_136_"));
342 insert into t1 select "yesterday_137_";
343 create table t6 select metaphon("for_138_");
344 create table t7 select 1 union select metaphon("for_139_");
345 create table t8 select * from t1 where 3 in (select 1 union select 2 union select metaphon("for_140_") union select 3);
346 create table t9 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3);
347 }
348
349 create table t20 select * from t1; # save for comparing later
350 create table t21 select * from t2;
351 create table t22 select * from t3;
352 drop table t1,t2,t3;
353
354 # This tests the fix to
355 # BUG#19630 stored function inserting into two auto_increment breaks statement-based binlog
356 # We verify that under the mixed binlog mode, a stored function
357 # modifying at least two tables having an auto_increment column,
358 # is binlogged row-based. Indeed in statement-based binlogging,
359 # only the auto_increment value generated for the first table
360 # is recorded in the binlog, the value generated for the 2nd table
361 # lacking.
362
363 create table t1 (a int primary key auto_increment, b varchar(100));
364 create table t2 (a int primary key auto_increment, b varchar(100));
365 create table t3 (b varchar(100));
366 delimiter |;
367 create function f (x varchar(100)) returns int deterministic
368 begin
369 insert into t1 values(null,x);
370 insert into t2 values(null,x);
371 return 1;
372 end|
373 delimiter ;|
374 select f("try_41_");
375 # Two operations which compensate each other except that their net
376 # effect is that they advance the auto_increment counter of t2 on slave:
377 --source include/sync_slave_sql_with_master.inc
378 use mysqltest1;
379 insert into t2 values(2,null),(3,null),(4,null);
380 delete from t2 where a>=2;
381
382 connection master;
383 # this is the call which didn't replicate well
384 select f("try_42_");
385 --source include/sync_slave_sql_with_master.inc
386
387 # now use prepared statement and test again, just to see that the RBB
388 # mode isn't set at PREPARE but at EXECUTE.
389
390 insert into t2 values(3,null),(4,null);
391 delete from t2 where a>=3;
392
393 connection master;
394 prepare stmt1 from 'select f(?)';
395 set @string="try_43_";
396 insert into t1 values(null,"try_44_"); # should be SBB
397 execute stmt1 using @string; # should be RBB
398 deallocate prepare stmt1;
399 --source include/sync_slave_sql_with_master.inc
400
401 # verify that if only one table has auto_inc, it does not trigger RBB
402 # (we'll check in binlog further below)
403
404 connection master;
405 create table t12 select * from t1; # save for comparing later
406 drop table t1;
407 create table t1 (a int, b varchar(100), key(a));
408 select f("try_45_");
409
410 # restore table's key
411 create table t13 select * from t1;
412 drop table t1;
413 create table t1 (a int primary key auto_increment, b varchar(100));
414
415 # now test if it's two functions, each of them inserts in one table
416
417 drop function f;
418 # we need a unique key to have sorting of rows by mysqldump
419 create table t14 (unique (a)) select * from t2;
420 truncate table t2;
421 delimiter |;
422 create function f1 (x varchar(100)) returns int deterministic
423 begin
424 insert into t1 values(null,x);
425 return 1;
426 end|
427 create function f2 (x varchar(100)) returns int deterministic
428 begin
429 insert into t2 values(null,x);
430 return 1;
431 end|
432 delimiter ;|
433 select f1("try_46_"),f2("try_47_");
434
435 --source include/sync_slave_sql_with_master.inc
436 insert into t2 values(2,null),(3,null),(4,null);
437 delete from t2 where a>=2;
438
439 connection master;
440 # Test with SELECT and INSERT
441 select f1("try_48_"),f2("try_49_");
442 insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_")));
443 --source include/sync_slave_sql_with_master.inc
444
445 # verify that if f2 does only read on an auto_inc table, this does not
446 # switch to RBB
447 connection master;
448 drop function f2;
449 delimiter |;
450 create function f2 (x varchar(100)) returns int deterministic
451 begin
452 declare y int;
453 insert into t1 values(null,x);
454 set y = (select count(*) from t2);
455 return y;
456 end|
457 delimiter ;|
458 select f1("try_53_"),f2("try_54_");
459 --source include/sync_slave_sql_with_master.inc
460
461 # And now, a normal statement with a trigger (no stored functions)
462
463 connection master;
464 drop function f2;
465 delimiter |;
466 create trigger t1_bi before insert on t1 for each row
467 begin
468 insert into t2 values(null,"try_55_");
469 end|
470 delimiter ;|
471 insert into t1 values(null,"try_56_");
472 # and now remove one auto_increment and verify SBB
473 alter table t1 modify a int, drop primary key;
474 insert into t1 values(null,"try_57_");
475 --source include/sync_slave_sql_with_master.inc
476
477 # Test for BUG#20499 "mixed mode with temporary table breaks binlog"
478 # Slave used to have only 2 rows instead of 3.
479 connection master;
480 CREATE TEMPORARY TABLE t15 SELECT UUID();
481 create table t16 like t15;
482 INSERT INTO t16 SELECT * FROM t15;
483 # we'll verify that this one is done RBB
484 insert into t16 values("try_65_");
485 drop table t15;
486 # we'll verify that this one is done SBB
487 insert into t16 values("try_66_");
488 --source include/sync_slave_sql_with_master.inc
489
490 # and now compare:
491
492 connection master;
493
494 # first check that data on master is sensible
495 select count(*) from t1;
496 select count(*) from t2;
497 select count(*) from t3;
498 # select count(*) from t4;
499 select count(*) from t5;
500 select count(*) from t11;
501 select count(*) from t20;
502 select count(*) from t21;
503 select count(*) from t22;
504 select count(*) from t12;
505 select count(*) from t13;
506 select count(*) from t14;
507 select count(*) from t16;
508 if ($you_want_to_test_UDF)
509 {
510 select count(*) from t6;
511 select count(*) from t7;
512 select count(*) from t8;
513 select count(*) from t9;
514 }
515
516 --source include/sync_slave_sql_with_master.inc
517
518 #
519 # Bug#20863 If binlog format is changed between update and unlock of
520 # tables, wrong binlog
521 #
522
523 connection master;
524 DROP TABLE IF EXISTS t11;
525 SET SESSION BINLOG_FORMAT=STATEMENT;
526 CREATE TABLE t11 (song VARCHAR(255));
527 LOCK TABLES t11 WRITE;
528 SET SESSION BINLOG_FORMAT='ROW';
529 INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict');
530 SET SESSION BINLOG_FORMAT=STATEMENT;
531 INSERT INTO t11 VALUES('Careful With That Axe, Eugene');
532 UNLOCK TABLES;
533
534 --query_vertical SELECT * FROM t11
535 --source include/sync_slave_sql_with_master.inc
536 USE mysqltest1;
537 --query_vertical SELECT * FROM t11
538
539 connection master;
540 DROP TABLE IF EXISTS t12;
541 SET SESSION BINLOG_FORMAT=MIXED;
542 CREATE TABLE t12 (data LONG);
543 LOCK TABLES t12 WRITE;
544 INSERT INTO t12 VALUES(UUID());
545 UNLOCK TABLES;
546 --source include/sync_slave_sql_with_master.inc
547
548 #
549 # BUG#28086: SBR of USER() becomes corrupted on slave
550 #
551
552 connection master;
553
554 # Just to get something that is non-trivial, albeit still simple, we
555 # stuff the result of USER() and CURRENT_USER() into a variable.
556 --delimiter $$
557 CREATE FUNCTION my_user()
558 RETURNS CHAR(64)
559 BEGIN
560 DECLARE user CHAR(64);
561 SELECT USER() INTO user;
562 RETURN user;
563 END $$
564 --delimiter ;
565
566 --delimiter $$
567 CREATE FUNCTION my_current_user()
568 RETURNS CHAR(64)
569 BEGIN
570 DECLARE user CHAR(64);
571 SELECT CURRENT_USER() INTO user;
572 RETURN user;
573 END $$
574 --delimiter ;
575
576 DROP TABLE IF EXISTS t13;
577 CREATE TABLE t13 (data CHAR(64));
578 INSERT INTO t13 VALUES (USER());
579 INSERT INTO t13 VALUES (my_user());
580 INSERT INTO t13 VALUES (CURRENT_USER());
581 INSERT INTO t13 VALUES (my_current_user());
582
583 --source include/sync_slave_sql_with_master.inc
584
585 # as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID
586 --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
587 --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql
588
589 # Let's compare. Note: If they match test will pass, if they do not match
590 # the test will show that the diff statement failed and not reject file
591 # will be created. You will need to go to the mysql-test dir and diff
592 # the files your self to see what is not matching
593
594 diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql;
595
596 connection master;
597
598 # Now test that mysqlbinlog works fine on a binlog generated by the
599 # mixed mode
600
601 # BUG#11312 "DELIMITER is not written to the binary log that causes
602 # syntax error" makes that mysqlbinlog will fail if we pass it the
603 # text of queries; this forces us to use --base64-output here.
604
605 # BUG#20929 "BINLOG command causes invalid free plus assertion
606 # failure" makes mysqld segfault when receiving --base64-output
607
608 # So I can't enable this piece of test
609 # SIGH
610
611 if ($enable_when_11312_or_20929_fixed)
612 {
613 --exec $MYSQL_BINLOG --base64-output $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_mixed.sql
614 drop database mysqltest1;
615 --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/mysqlbinlog_mixed.sql
616 --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
617 # the old mysqldump output on slave is the same as what it was on
618 # master before restoring on master.
619 diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql;
620 }
621
622 drop database mysqltest1;
623 --source include/sync_slave_sql_with_master.inc
624
625 connection master;
626 # Restore binlog format setting
627 set global binlog_format =@my_binlog_format;
628
629 # Cleanup
630 --remove_file $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql
631 --remove_file $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
632 --source include/rpl_end.inc
633