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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
|
# Test case for BUG #11733
-- source include/have_innodb.inc
-- source include/master-slave.inc
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
# Create a test and replicate it to slave
connection master;
create user test;
grant all on test.* to test;
sync_slave_with_master;
# Setting the master readonly :
# - the variable @@readonly is not replicated on the slave
connect (master2,127.0.0.1,test,,test,$MASTER_MYPORT,);
connect (slave2,127.0.0.1,test,,test,$SLAVE_MYPORT,);
connection master1;
create table t1(a int) engine=InnoDB;
create table t2(a int) engine=MyISAM;
insert into t1 values(1001);
insert into t2 values(2001);
connection master;
set global read_only=1;
connection master1;
select @@read_only;
select * from t1;
select * from t2;
sync_slave_with_master;
select @@read_only;
select * from t1;
select * from t2;
# - replication of transactions
connection master;
set global read_only=0;
connection master1;
BEGIN;
connection master2;
BEGIN;
connection master;
select @@read_only;
set global read_only=1;
connection master1;
-- echo *** On SUPER USER connection ***
insert into t1 values(1002);
--disable_warnings
insert into t2 values(2002);
--enable_warnings
connection master2;
-- echo *** On regular USER connection ***
--error ER_OPTION_PREVENTS_STATEMENT
insert into t1 values(1003);
--error ER_OPTION_PREVENTS_STATEMENT
insert into t2 values(2003);
connection master1;
## works even with read_only=1, because master1 is root
-- echo *** SUPER USER COMMIT (must succeed) ***
COMMIT;
connection master2;
-- echo *** regular USER COMMIT (must succeed - nothing to commit) ***
COMMIT;
connection master;
select @@read_only;
set global read_only=0;
connection master1;
insert into t1 values(1004);
insert into t2 values(2004);
select * from t1;
select * from t2;
sync_slave_with_master;
select * from t1;
select * from t2;
# Setting the slave readonly : replication will pass
#
connection slave1;
set global read_only=1;
connection slave;
select @@read_only;
# Make sure the replicated table is also transactional
show create table t1;
# Make sure the replicated table is not transactional
show create table t2;
connection master;
insert into t1 values(1005);
insert into t2 values(2005);
select * from t1;
select * from t2;
sync_slave_with_master;
connection slave;
select * from t1;
select * from t2;
# Non root user can not write on the slave
connection slave2;
--error ER_OPTION_PREVENTS_STATEMENT
insert into t1 values(1006);
--error ER_OPTION_PREVENTS_STATEMENT
insert into t2 values(2006);
--echo #
--echo # MDEV-30978: On slave XA COMMIT/XA ROLLBACK fail to return an error in read-only mode
--echo #
--echo # Where a read-only server permits writes through replication, it
--echo # should not permit user connections to commit/rollback XA transactions
--echo # prepared via replication. This test ensure this behavior is prohibited
--echo #
# Note: slave's read_only=1 is set prior to this test case
connection master;
xa start '1';
insert into t1 values (1007);
xa end '1';
xa prepare '1';
sync_slave_with_master;
connection slave2;
--error ER_OPTION_PREVENTS_STATEMENT
xa commit '1';
--error ER_OPTION_PREVENTS_STATEMENT
xa rollback '1';
connection master;
xa rollback '1';
## Cleanup
connection master;
drop user test;
drop table t1;
drop table t2;
sync_slave_with_master;
set global read_only=0;
--source include/rpl_end.inc
|
