| 1 |
# |
| 2 |
# WL#4033 & WL#5404 |
| 3 |
# This test verifies if the query of the rows event |
| 4 |
# is displayed with its row event as comment in RBR |
| 5 |
# by SHOW BINLOG EVENTS and MYSQLBINLOG DUMP. |
| 6 |
# Verifies if the Rows_query log event can be applied |
| 7 |
# and relayed |
| 8 |
# |
| 9 |
|
| 10 |
source include/have_binlog_format_row.inc; |
| 11 |
|
| 12 |
# this test records the output of mysqlbinlog, which |
| 13 |
# will make it output transaction payload events headers |
| 14 |
# if compression is enabled. |
| 15 |
--source include/not_binlog_transaction_compression_on.inc |
| 16 |
|
| 17 |
|
| 18 |
#We will delay the slave start as resetting the debug variable (on the master) |
| 19 |
#while the dump thread is running, may cause sporadic races between user and |
| 20 |
#dump threads. BUG#12765441 |
| 21 |
--let $rpl_skip_start_slave= 1 |
| 22 |
source include/master-slave.inc; |
| 23 |
source include/have_debug.inc; |
| 24 |
source include/force_myisam_default.inc; |
| 25 |
source include/have_myisam.inc; |
| 26 |
|
| 27 |
--echo # Test non-transaction |
| 28 |
let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); |
| 29 |
let $master_binlog= query_get_value(SHOW MASTER STATUS, File, 1); |
| 30 |
create table t1(a int, b int) engine= myisam; |
| 31 |
insert into t1(a,b) values(1,1),(2,1); |
| 32 |
update t1 set a = a + 5 where b = 1 LIMIT 1; |
| 33 |
delete from t1 where a = 6; |
| 34 |
|
| 35 |
-- echo # Test transaction |
| 36 |
create table t2(a int, b int) engine=innodb; |
| 37 |
begin; |
| 38 |
insert into t2(a,b) values(2,1),(3,1); |
| 39 |
update t2 set a = a + 5 where b = 1; |
| 40 |
delete from t2 where a = 7; |
| 41 |
commit; |
| 42 |
|
| 43 |
--echo # Test mixed transaction |
| 44 |
begin; |
| 45 |
insert into t1(a,b) values(1,5); |
| 46 |
insert into t2(a,b) values(2,5); |
| 47 |
insert into t2(a,b) values(2,6); |
| 48 |
insert into t1(a,b) values(1,7); |
| 49 |
commit; |
| 50 |
|
| 51 |
drop table t1, t2; |
| 52 |
let $end_pos= query_get_value("SHOW MASTER STATUS", Position, 1); |
| 53 |
|
| 54 |
create table t3(a int, b int) engine= myisam; |
| 55 |
create table t4(a int, b int) engine= myisam; |
| 56 |
create table t5(a int, b int) engine= myisam; |
| 57 |
insert into t3(a, b) values(1,1); |
| 58 |
--echo # Test the Rows_query log event will be filtered out if slave applys |
| 59 |
--echo # '--replicate-ignore-table' option on the table |
| 60 |
insert into t4(a, b) values(2,1); |
| 61 |
--echo # Test the Rows_query log event will be relayed if slave |
| 62 |
--echo # filters out part Rows event from a rows statement |
| 63 |
update t3,t4 set t3.a=3, t4.a=4 where t3.b=t4.b; |
| 64 |
|
| 65 |
--echo # Test the Rows_query log event will be filtered out if slave applys |
| 66 |
--echo # '--replicate-wild-ignore-table' option on the table |
| 67 |
insert into t5(a, b) values(3,1); |
| 68 |
--echo # Test the Rows_query log event will be filtered out if slave filters |
| 69 |
--echo # out all its related tables by replication filtering rules |
| 70 |
update t4,t5 set t4.a=4, t5.a=5 where t4.b=t5.b; |
| 71 |
|
| 72 |
FLUSH TABLES; |
| 73 |
|
| 74 |
--echo # Test load data infile |
| 75 |
create table t6(a VARCHAR(60)) engine= myisam; |
| 76 |
load data infile '../../std_data/words.dat' into table t6; |
| 77 |
|
| 78 |
drop table t3, t6; |
| 79 |
drop table t4, t5; |
| 80 |
|
| 81 |
--echo # SHOW BINLOG EVENTS ON MASTER before '--exec MYSQL_BINLOG' |
| 82 |
source include/show_binlog_events.inc; |
| 83 |
|
| 84 |
FLUSH LOGS; |
| 85 |
let $MYSQLD_DATADIR= `select @@datadir`; |
| 86 |
--let $prefix=`SELECT UUID()` |
| 87 |
--let $binlog_uuid_filename= $MYSQLTEST_VARDIR/tmp/$prefix-bin.log |
| 88 |
--copy_file $MYSQLD_DATADIR/$master_binlog $binlog_uuid_filename |
| 89 |
RESET MASTER; |
| 90 |
|
| 91 |
--echo # MYSQL_BINLOG output based on a saved copy of master-bin.000001 |
| 92 |
|
| 93 |
--let $mysqlbinlog_parameters= --base64-output=decode-rows -v -v $binlog_uuid_filename |
| 94 |
--source include/mysqlbinlog.inc |
| 95 |
|
| 96 |
--echo # Test the Rows_query log event can be applied |
| 97 |
--exec $MYSQL_BINLOG -v -v --stop-position=$end_pos $binlog_uuid_filename | $MYSQL test 2>&1 |
| 98 |
|
| 99 |
--remove_file $binlog_uuid_filename |
| 100 |
|
| 101 |
--echo # SHOW BINLOG EVENTS ON MASTER after '--exec MYSQL_BINLOG' |
| 102 |
source include/show_binlog_events.inc; |
| 103 |
|
| 104 |
connect (master2,localhost,root,,); |
| 105 |
connection master2; |
| 106 |
--echo # SHOW BINLOG EVENTS ON 2nd CONNECTION TO MASTE |
| 107 |
set @@session.binlog_rows_query_log_events= off; |
| 108 |
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); |
| 109 |
--echo # Test statements from the 2nd connection have no comment event |
| 110 |
--echo # when setting binlog_rows_query_log_events= off |
| 111 |
create table t7(a bigint) engine= myisam; |
| 112 |
insert into t7(a) values(1),(2); |
| 113 |
delete from t7 where a = 1; |
| 114 |
|
| 115 |
set session binlog_format=mixed; |
| 116 |
set @@session.binlog_rows_query_log_events= on; |
| 117 |
--echo # Test the comment event is sent in mixed mode for special |
| 118 |
--echo # functions when setting binlog_rows_query_log_events= on |
| 119 |
insert into t7(a) values(UUID_SHORT()); |
| 120 |
drop table t7; |
| 121 |
source include/show_binlog_events.inc; |
| 122 |
|
| 123 |
--connection slave |
| 124 |
--source include/start_slave.inc |
| 125 |
--connection master |
| 126 |
--source include/sync_slave_sql_with_master.inc |
| 127 |
--let $binlog_start= query_get_value(SHOW BINLOG EVENTS LIMIT 1, End_log_pos, 1) |
| 128 |
--echo # Test The Rows_query log event can be relayed. |
| 129 |
--echo # SHOW BINLOG EVENTS ON SLAVE |
| 130 |
source include/show_binlog_events.inc; |
| 131 |
|
| 132 |
--source include/rpl_end.inc |