1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
66
67
68
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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
#
# 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 #
#
# Suppress "unsafe" warnings.
#
disable_query_log;
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
enable_query_log;
--disable_warnings
drop tables if exists t1, t2;
drop view if exists v1;
--enable_warnings
--echo # Syncing slave with master
--sync_slave_with_master
--disable_ps2_protocol
connect (master2,127.0.0.1,root,,test,$MASTER_MYPORT,);
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);
connection master1;
--send insert into v1 values (get_lock("lock_bg25144", 100))
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
connection master;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for table metadata lock" and info = "drop view v1";
--source include/wait_condition.inc
select release_lock("lock_bg25144");
connection master1;
--disable_warnings
--reap
--enable_warnings
select release_lock("lock_bg25144");
connection master2;
--reap
connection master;
--echo # Check that insertion through view did happen.
select * from t1;
--echo # Syncing slave with master
--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;
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);
connection master1;
--send insert into v1 values (get_lock("lock_bg25144", 100))
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
connection master;
let $wait_condition=
select count(*) = 1 from information_schema.processlist
where state = "Waiting for table metadata lock" and
info = "alter view v1 as select * from t2";
--source include/wait_condition.inc
select release_lock("lock_bg25144");
connection master1;
--disable_warnings
--reap
--enable_warnings
select release_lock("lock_bg25144");
connection master2;
--reap
connection master;
--echo # Second insertion should go to t1 as well.
select * from t1;
select * from t2;
--echo # Syncing slave with master
--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;
--enable_ps2_protocol
connection master;
drop table t1, t2;
drop view v1;
--echo # Syncing slave with master
--sync_slave_with_master
--source include/rpl_end.inc
|
