Debian Codegraph @

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

raw
1 #
2 # This file contains test cases for bugs which involve views, several
3 # concurren connections and manifest themselves as wrong binary log
4 # sequence which results in broken replication. In principle we are
5 # mostly interested in SBR here but this test will also work with RBR.
6 #
7 --source include/not_group_replication_plugin.inc
8 --source include/set_privilege_checks_user_as_system_user.inc
9 --source include/master-slave.inc
10
11 --echo #
12 --echo # Bug #25144 "replication / binlog with view breaks".
13 --echo # Statements that used views didn't ensure that view were not modified
14 --echo # during their execution. Indeed this led to incorrect binary log with
15 --echo # statement based logging and as result to broken replication.
16 --echo #
17 #
18 # Suppress "unsafe" warnings.
19 #
20 disable_query_log;
21 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
22 enable_query_log;
23
24
25 --disable_warnings
26 drop tables if exists t1, t2;
27 drop view if exists v1;
28 --enable_warnings
29 --echo # Syncing slave with master and switching to connection 'slave'
30 --source include/sync_slave_sql_with_master.inc
31
32 connect (master2,127.0.0.1,root,,test,$MASTER_MYPORT,);
33
34 --echo # Switching to connection 'master'
35 connection master;
36 create table t1 (i int);
37 create table t2 (i int);
38 create view v1 as select * from t1;
39
40 --echo # First we try to concurrently execute statement that uses view
41 --echo # and statement that drops it. We use "user" locks as means to
42 --echo # suspend execution of first statement once it opens our view.
43 select get_lock("lock_bg25144", 1);
44
45 --echo # Switching to connection 'master1'
46 connection master1;
47 --send insert into v1 values (get_lock("lock_bg25144", 100))
48
49 --echo # Switching to connection 'master2'
50 connection master2;
51 let $wait_condition=
52 select count(*) = 1 from information_schema.processlist
53 where state = "User lock" and info like "insert into v1 %lock_bg25144%";
54 --source include/wait_condition_or_abort.inc
55 --send drop view v1
56
57 --echo # Switching to connection 'master'
58 connection master;
59 let $wait_condition=
60 select count(*) = 1 from information_schema.processlist
61 where state = "Waiting for table metadata lock" and info = "drop view v1";
62 --source include/wait_condition_or_abort.inc
63
64 select release_lock("lock_bg25144");
65
66 --echo # Switching to connection 'master1'
67 connection master1;
68 --disable_warnings
69 --reap
70 --enable_warnings
71 select release_lock("lock_bg25144");
72
73 --echo # Switching to connection 'master2'
74 connection master2;
75 --reap
76
77 --echo # Switching to connection 'master'
78 connection master;
79 --echo # Check that insertion through view did happen.
80 select * from t1;
81 --echo # Syncing slave with master and switching to connection 'slave'
82 --source include/sync_slave_sql_with_master.inc
83 --echo # Check that slave was able to replicate this sequence
84 --echo # which means that we got correct binlog order.
85 select * from t1;
86
87 --echo # Switching to connection 'master'
88 connection master;
89 --echo # Now we will repeat the test by trying concurrently execute
90 --echo # statement that uses a view and statement that alters it.
91 create view v1 as select * from t1;
92
93 select get_lock("lock_bg25144", 1);
94
95 --echo # Switching to connection 'master1'
96 connection master1;
97 --send insert into v1 values (get_lock("lock_bg25144", 100))
98
99 --echo # Switching to connection 'master2'
100 connection master2;
101 let $wait_condition=
102 select count(*) = 1 from information_schema.processlist
103 where state = "User lock" and info like "insert into v1 %lock_bg25144%";
104 --source include/wait_condition_or_abort.inc
105 --send alter view v1 as select * from t2
106
107 --echo # Switching to connection 'master'
108 connection master;
109 let $wait_condition=
110 select count(*) = 1 from information_schema.processlist
111 where state = "Waiting for table metadata lock" and
112 info = "alter view v1 as select * from t2";
113 --source include/wait_condition_or_abort.inc
114
115 select release_lock("lock_bg25144");
116
117 --echo # Switching to connection 'master1'
118 connection master1;
119 --disable_warnings
120 --reap
121 --enable_warnings
122 select release_lock("lock_bg25144");
123
124 --echo # Switching to connection 'master2'
125 connection master2;
126 --reap
127
128 --echo # Switching to connection 'master'
129 connection master;
130
131 --echo # Second insertion should go to t1 as well.
132 select * from t1;
133 select * from t2;
134
135 --echo # Syncing slave with master and switching to connection 'slave'
136 --source include/sync_slave_sql_with_master.inc
137 --echo # Now let us check that statements were logged in proper order
138 --echo # So we have same result on slave.
139 select * from t1;
140 select * from t2;
141
142 --echo # Switching to connection 'master'
143 connection master;
144 drop table t1, t2;
145 drop view v1;
146 --echo # Syncing slave with master and switching to connection 'slave'
147 --source include/sync_slave_sql_with_master.inc
148 --source include/rpl_end.inc