Debian Codegraph @

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

raw
1 # Test case for BUG #11733
2 -- source include/have_innodb.inc
3 -- source include/master-slave.inc
4
5 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
6
7 # Create a test and replicate it to slave
8 connection master;
9 create user test;
10 grant all on test.* to test;
11 sync_slave_with_master;
12
13 # Setting the master readonly :
14 # - the variable @@readonly is not replicated on the slave
15
16 connect (master2,127.0.0.1,test,,test,$MASTER_MYPORT,);
17 connect (slave2,127.0.0.1,test,,test,$SLAVE_MYPORT,);
18
19 connection master1;
20
21 create table t1(a int) engine=InnoDB;
22 create table t2(a int) engine=MyISAM;
23 insert into t1 values(1001);
24 insert into t2 values(2001);
25
26 connection master;
27 set global read_only=1;
28
29 connection master1;
30 select @@read_only;
31 select * from t1;
32 select * from t2;
33
34 sync_slave_with_master;
35 select @@read_only;
36 select * from t1;
37 select * from t2;
38
39 # - replication of transactions
40 connection master;
41 set global read_only=0;
42
43 connection master1;
44 BEGIN;
45
46 connection master2;
47 BEGIN;
48
49 connection master;
50 select @@read_only;
51 set global read_only=1;
52
53 connection master1;
54 -- echo *** On SUPER USER connection ***
55 insert into t1 values(1002);
56 --disable_warnings
57 insert into t2 values(2002);
58 --enable_warnings
59
60 connection master2;
61 -- echo *** On regular USER connection ***
62 --error ER_OPTION_PREVENTS_STATEMENT
63 insert into t1 values(1003);
64 --error ER_OPTION_PREVENTS_STATEMENT
65 insert into t2 values(2003);
66
67 connection master1;
68 ## works even with read_only=1, because master1 is root
69 -- echo *** SUPER USER COMMIT (must succeed) ***
70 COMMIT;
71
72 connection master2;
73 -- echo *** regular USER COMMIT (must succeed - nothing to commit) ***
74 COMMIT;
75
76 connection master;
77 select @@read_only;
78 set global read_only=0;
79
80 connection master1;
81 insert into t1 values(1004);
82 insert into t2 values(2004);
83
84 select * from t1;
85 select * from t2;
86
87 sync_slave_with_master;
88 select * from t1;
89 select * from t2;
90
91 # Setting the slave readonly : replication will pass
92 #
93 connection slave1;
94 set global read_only=1;
95
96 connection slave;
97 select @@read_only;
98 # Make sure the replicated table is also transactional
99 show create table t1;
100 # Make sure the replicated table is not transactional
101 show create table t2;
102
103 connection master;
104 insert into t1 values(1005);
105 insert into t2 values(2005);
106 select * from t1;
107 select * from t2;
108
109 sync_slave_with_master;
110 connection slave;
111 select * from t1;
112 select * from t2;
113
114 # Non root user can not write on the slave
115 connection slave2;
116 --error ER_OPTION_PREVENTS_STATEMENT
117 insert into t1 values(1006);
118 --error ER_OPTION_PREVENTS_STATEMENT
119 insert into t2 values(2006);
120
121
122 --echo #
123 --echo # MDEV-30978: On slave XA COMMIT/XA ROLLBACK fail to return an error in read-only mode
124 --echo #
125 --echo # Where a read-only server permits writes through replication, it
126 --echo # should not permit user connections to commit/rollback XA transactions
127 --echo # prepared via replication. This test ensure this behavior is prohibited
128 --echo #
129
130 # Note: slave's read_only=1 is set prior to this test case
131
132 connection master;
133 xa start '1';
134 insert into t1 values (1007);
135 xa end '1';
136 xa prepare '1';
137 sync_slave_with_master;
138
139 connection slave2;
140 --error ER_OPTION_PREVENTS_STATEMENT
141 xa commit '1';
142 --error ER_OPTION_PREVENTS_STATEMENT
143 xa rollback '1';
144
145 connection master;
146 xa rollback '1';
147
148 ## Cleanup
149 connection master;
150 drop user test;
151 drop table t1;
152 drop table t2;
153 sync_slave_with_master;
154 set global read_only=0;
155
156
157 --source include/rpl_end.inc