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
|
include/master-slave.inc
[connection master]
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
connection master;
create user test;
connection 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;
@@read_only
1
select * from t1;
a
1001
select * from t2;
a
2001
connection slave;
select @@read_only;
@@read_only
0
select * from t1;
a
1001
select * from t2;
a
2001
connection master;
set global read_only=0;
connection master1;
BEGIN;
connection master2;
BEGIN;
connection master;
select @@read_only;
@@read_only
0
set global read_only=1;
connection master1;
*** On SUPER USER connection ***
insert into t1 values(1002);
insert into t2 values(2002);
connection master2;
*** On regular USER connection ***
insert into t1 values(1003);
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
insert into t2 values(2003);
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
connection master1;
*** SUPER USER COMMIT (must succeed) ***
COMMIT;
connection master2;
*** regular USER COMMIT (must succeed - nothing to commit) ***
COMMIT;
connection master;
select @@read_only;
@@read_only
1
set global read_only=0;
connection master1;
insert into t1 values(1004);
insert into t2 values(2004);
select * from t1;
a
1001
1002
1004
select * from t2;
a
2001
2002
2004
connection slave;
select * from t1;
a
1001
1002
1004
select * from t2;
a
2001
2002
2004
connection slave1;
set global read_only=1;
connection slave;
select @@read_only;
@@read_only
1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
connection master;
insert into t1 values(1005);
insert into t2 values(2005);
select * from t1;
a
1001
1002
1004
1005
select * from t2;
a
2001
2002
2004
2005
connection slave;
connection slave;
select * from t1;
a
1001
1002
1004
1005
select * from t2;
a
2001
2002
2004
2005
connection slave2;
insert into t1 values(1006);
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
insert into t2 values(2006);
ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement
connection master;
drop user test;
drop table t1;
drop table t2;
connection slave;
set global read_only=0;
include/rpl_end.inc
|
