rpl_view_multi.test 4.39 KB
Newer Older
Konstantin Osipov's avatar
Konstantin Osipov committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14
#
# This file contains test cases for bugs which involve views, several
# concurren connections and manifest themselves as wrong binary log
# sequence which results in broken replication. In principle we are
# mostly interested in SBR here but this test will also work with RBR.
#
--source include/master-slave.inc

--echo #
--echo # Bug #25144 "replication / binlog with view breaks".
--echo # Statements that used views didn't ensure that view were not modified
--echo # during their execution. Indeed this led to incorrect binary log with
--echo # statement based logging and as result to broken replication.
--echo #
15 16 17 18 19 20 21
#
# Suppress "unsafe" warnings. 
#
disable_query_log;
call mtr.add_suppression("Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT");
enable_query_log;

Konstantin Osipov's avatar
Konstantin Osipov committed
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

--disable_warnings
drop tables if exists t1, t2;
drop view if exists v1;
--enable_warnings
--echo # Syncing slave with master and switching to connection 'slave'
--sync_slave_with_master

connect (master2,127.0.0.1,root,,test,$MASTER_MYPORT,);

--echo # Switching to connection 'master'
connection master;
create table t1 (i int);
create table t2 (i int);
create view v1 as select * from t1;

--echo # First we try to concurrently execute statement that uses view
--echo # and statement that drops it. We use "user" locks as means to
--echo # suspend execution of first statement once it opens our view.
select get_lock("lock_bg25144", 1);

--echo # Switching to connection 'master1'
connection master1;
--send insert into v1 values (get_lock("lock_bg25144", 100))

--echo # Switching to connection 'master2'
connection master2;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "User lock" and info like "insert into v1 %lock_bg25144%";
--source include/wait_condition.inc
--send drop view v1

--echo # Switching to connection 'master'
connection master;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table" and info = "drop view v1";
--source include/wait_condition.inc

select release_lock("lock_bg25144");

--echo # Switching to connection 'master1'
connection master1;
66
--disable_warnings
Konstantin Osipov's avatar
Konstantin Osipov committed
67
--reap
68
--enable_warnings
Konstantin Osipov's avatar
Konstantin Osipov committed
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
select release_lock("lock_bg25144");

--echo # Switching to connection 'master2'
connection master2;
--reap

--echo # Switching to connection 'master'
connection master;
--echo # Check that insertion through view did happen.
select * from t1;
--echo # Syncing slave with master and switching to connection 'slave'
--sync_slave_with_master
--echo # Check that slave was able to replicate this sequence
--echo # which means that we got correct binlog order.
select * from t1;

--echo # Switching to connection 'master'
connection master;
--echo # Now we will repeat the test by trying concurrently execute
--echo # statement that uses a view and statement that alters it.
create view v1 as select * from t1;

select get_lock("lock_bg25144", 1);

--echo # Switching to connection 'master1'
connection master1;
--send insert into v1 values (get_lock("lock_bg25144", 100))

--echo # Switching to connection 'master2'
connection master2;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "User lock" and info like "insert into v1 %lock_bg25144%";
--source include/wait_condition.inc
--send alter view v1 as select * from t2

--echo # Switching to connection 'master'
connection master;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table" and
  info = "alter view v1 as select * from t2";
--source include/wait_condition.inc

select release_lock("lock_bg25144");

--echo # Switching to connection 'master1'
connection master1;
117
--disable_warnings
Konstantin Osipov's avatar
Konstantin Osipov committed
118
--reap
119
--enable_warnings
Konstantin Osipov's avatar
Konstantin Osipov committed
120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145
select release_lock("lock_bg25144");

--echo # Switching to connection 'master2'
connection master2;
--reap

--echo # Switching to connection 'master'
connection master;

--echo # Second insertion should go to t1 as well.
select * from t1;
select * from t2;

--echo # Syncing slave with master and switching to connection 'slave'
--sync_slave_with_master
--echo # Now let us check that statements were logged in proper order
--echo # So we have same result on slave.
select * from t1;
select * from t2;

--echo # Switching to connection 'master'
connection master;
drop table t1, t2;
drop view v1;
--echo # Syncing slave with master and switching to connection 'slave'
--sync_slave_with_master