1#
2# Setup
3#
4# Save original value
5set @start_read_only= @@global.read_only;
6set @start_super_read_only= @@global.super_read_only;
7grant CREATE, SELECT, DROP on *.* to test@localhost;
8# connect (con1,localhost,test,,test);
9# connection default;
10#
11# Test combinations of settings and changes
12#
13# ro,sro: 0,0: set ro->0: 0,0
14set global read_only=0;
15set global super_read_only=0;
16select @@global.read_only;
17@@global.read_only
180
19select @@global.super_read_only;
20@@global.super_read_only
210
22set global read_only=0;
23select @@global.read_only;
24@@global.read_only
250
26select @@global.super_read_only;
27@@global.super_read_only
280
29# ro,sro: 0,0: set ro->1: 1,0
30set global read_only=0;
31set global super_read_only=0;
32select @@global.read_only;
33@@global.read_only
340
35select @@global.super_read_only;
36@@global.super_read_only
370
38set global read_only=1;
39select @@global.read_only;
40@@global.read_only
411
42select @@global.super_read_only;
43@@global.super_read_only
440
45# ro,sro: 0,0: set sro->0: 0,0
46set global read_only=0;
47set global super_read_only=0;
48select @@global.read_only;
49@@global.read_only
500
51select @@global.super_read_only;
52@@global.super_read_only
530
54set global super_read_only=0;
55select @@global.read_only;
56@@global.read_only
570
58select @@global.super_read_only;
59@@global.super_read_only
600
61# ro,sro: 0,0: set sro->1: 1,1
62set global read_only=0;
63set global super_read_only=0;
64select @@global.read_only;
65@@global.read_only
660
67select @@global.super_read_only;
68@@global.super_read_only
690
70set global super_read_only=1;
71select @@global.read_only;
72@@global.read_only
731
74select @@global.super_read_only;
75@@global.super_read_only
761
77# ro,sro: 1,0: set ro->0: 0,0
78set global read_only=1;
79set global super_read_only=0;
80select @@global.read_only;
81@@global.read_only
821
83select @@global.super_read_only;
84@@global.super_read_only
850
86set global read_only=0;
87select @@global.read_only;
88@@global.read_only
890
90select @@global.super_read_only;
91@@global.super_read_only
920
93# ro,sro: 1,0: set ro->1: 1,0
94set global read_only=1;
95set global super_read_only=0;
96select @@global.read_only;
97@@global.read_only
981
99select @@global.super_read_only;
100@@global.super_read_only
1010
102set global read_only=1;
103select @@global.read_only;
104@@global.read_only
1051
106select @@global.super_read_only;
107@@global.super_read_only
1080
109# ro,sro: 1,0: set sro->0: 1,0
110set global read_only=1;
111set global super_read_only=0;
112select @@global.read_only;
113@@global.read_only
1141
115select @@global.super_read_only;
116@@global.super_read_only
1170
118set global super_read_only=0;
119select @@global.read_only;
120@@global.read_only
1211
122select @@global.super_read_only;
123@@global.super_read_only
1240
125# ro,sro: 1,0: set sro->1: 1,1
126set global read_only=1;
127set global super_read_only=0;
128select @@global.read_only;
129@@global.read_only
1301
131select @@global.super_read_only;
132@@global.super_read_only
1330
134set global super_read_only=1;
135select @@global.read_only;
136@@global.read_only
1371
138select @@global.super_read_only;
139@@global.super_read_only
1401
141# ro,sro: 1,1: set ro->0: 0,0
142set global read_only=1;
143set global super_read_only=1;
144select @@global.read_only;
145@@global.read_only
1461
147select @@global.super_read_only;
148@@global.super_read_only
1491
150set global read_only=0;
151select @@global.read_only;
152@@global.read_only
1530
154select @@global.super_read_only;
155@@global.super_read_only
1560
157# ro,sro: 1,1: set ro->1: 1,1
158set global read_only=1;
159set global super_read_only=1;
160select @@global.read_only;
161@@global.read_only
1621
163select @@global.super_read_only;
164@@global.super_read_only
1651
166set global read_only=1;
167select @@global.read_only;
168@@global.read_only
1691
170select @@global.super_read_only;
171@@global.super_read_only
1721
173# ro,sro: 1,1: set sro->0: 1,0
174set global read_only=1;
175set global super_read_only=1;
176select @@global.read_only;
177@@global.read_only
1781
179select @@global.super_read_only;
180@@global.super_read_only
1811
182set global super_read_only=0;
183select @@global.read_only;
184@@global.read_only
1851
186select @@global.super_read_only;
187@@global.super_read_only
1880
189# ro,sro: 1,1: set sro->1: 1,1
190set global read_only=1;
191set global super_read_only=1;
192select @@global.read_only;
193@@global.read_only
1941
195select @@global.super_read_only;
196@@global.super_read_only
1971
198set global super_read_only=1;
199select @@global.read_only;
200@@global.read_only
2011
202select @@global.super_read_only;
203@@global.super_read_only
2041
205set global read_only=0;
206set global super_read_only=0;
207DROP TABLE IF EXISTS t1,t2,t3;
208#
209# Create tables/Make sure normal writes work
210#
211set global super_read_only=0;
212connection con1;
213create table t1 (a int);
214insert into t1 values(1);
215create table t2 select * from t1;
216update t1, t2 set t1.a=2, t2.a=2;
217start transaction read write;
218commit;
219create trigger trig before insert on t1 for each row set new.a = new.a;
220connection default;
221#
222# Make sure it blocks SUPER
223#
224set global super_read_only=1;
225create table t3 (a int);
226ERROR HY000: The MySQL server is running with the --read-only (super) option so it cannot execute this statement
227drop table t3;
228ERROR HY000: The MySQL server is running with the --read-only (super) option so it cannot execute this statement
229update t1, t2 set t1.a=3, t2.a=3;
230ERROR HY000: The MySQL server is running with the --read-only (super) option so it cannot execute this statement
231start transaction read write;
232ERROR HY000: The MySQL server is running with the --read-only (super) option so it cannot execute this statement
233drop trigger trig;
234ERROR HY000: The MySQL server is running with the --read-only (super) option so it cannot execute this statement
235#
236# Make sure it still blocks for non-super
237#
238connection con1;
239select @@global.read_only;
240@@global.read_only
2411
242select @@global.super_read_only;
243@@global.super_read_only
2441
245create table t3 (a int);
246ERROR HY000: The MySQL server is running with the --read-only (super) option so it cannot execute this statement
247insert into t1 values(1);
248ERROR HY000: The MySQL server is running with the --read-only (super) option so it cannot execute this statement
249update t1, t2 set t1.a=3, t2.a=3;
250ERROR HY000: The MySQL server is running with the --read-only (super) option so it cannot execute this statement
251start transaction read write;
252ERROR HY000: The MySQL server is running with the --read-only (super) option so it cannot execute this statement
253drop trigger trig;
254ERROR HY000: The MySQL server is running with the --read-only (super) option so it cannot execute this statement
255#
256# Cleanup
257#
258connection default;
259set global super_read_only=0;
260drop table t1,t2;
261drop user test@localhost;
262set global read_only= @start_read_only;
263set global super_read_only= @start_super_read_only;
264