Debian Codegraph @

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

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