1# Test of the SUPER_READ_ONLY modification to the READ_ONLY global variable:
2# check that it blocks updates even with SUPER privs
3
4# should work with embedded server after mysqltest is fixed
5--source include/not_embedded.inc
6
7
8--echo #
9--echo # Setup
10--echo #
11
12--echo # Save original value
13set @start_read_only= @@global.read_only;
14set @start_super_read_only= @@global.super_read_only;
15
16grant CREATE, SELECT, DROP on *.* to test@localhost;
17
18--echo # connect (con1,localhost,test,,test);
19connect (con1,localhost,test,,test);
20
21--echo # connection default;
22connection default;
23
24
25--echo #
26--echo # Test combinations of settings and changes
27--echo #
28
29--echo # ro,sro: 0,0: set ro->0: 0,0
30set global read_only=0;
31set global super_read_only=0;
32select @@global.read_only;
33select @@global.super_read_only;
34set global read_only=0;
35select @@global.read_only;
36select @@global.super_read_only;
37
38--echo # ro,sro: 0,0: set ro->1: 1,0
39set global read_only=0;
40set global super_read_only=0;
41select @@global.read_only;
42select @@global.super_read_only;
43set global read_only=1;
44select @@global.read_only;
45select @@global.super_read_only;
46
47--echo # ro,sro: 0,0: set sro->0: 0,0
48set global read_only=0;
49set global super_read_only=0;
50select @@global.read_only;
51select @@global.super_read_only;
52set global super_read_only=0;
53select @@global.read_only;
54select @@global.super_read_only;
55
56--echo # ro,sro: 0,0: set sro->1: 1,1
57set global read_only=0;
58set global super_read_only=0;
59select @@global.read_only;
60select @@global.super_read_only;
61set global super_read_only=1;
62select @@global.read_only;
63select @@global.super_read_only;
64
65# We don't care about 0,1 - since all this ensures it can't happen.
66
67--echo # ro,sro: 1,0: set ro->0: 0,0
68set global read_only=1;
69set global super_read_only=0;
70select @@global.read_only;
71select @@global.super_read_only;
72set global read_only=0;
73select @@global.read_only;
74select @@global.super_read_only;
75
76--echo # ro,sro: 1,0: set ro->1: 1,0
77set global read_only=1;
78set global super_read_only=0;
79select @@global.read_only;
80select @@global.super_read_only;
81set global read_only=1;
82select @@global.read_only;
83select @@global.super_read_only;
84
85--echo # ro,sro: 1,0: set sro->0: 1,0
86set global read_only=1;
87set global super_read_only=0;
88select @@global.read_only;
89select @@global.super_read_only;
90set global super_read_only=0;
91select @@global.read_only;
92select @@global.super_read_only;
93
94--echo # ro,sro: 1,0: set sro->1: 1,1
95set global read_only=1;
96set global super_read_only=0;
97select @@global.read_only;
98select @@global.super_read_only;
99set global super_read_only=1;
100select @@global.read_only;
101select @@global.super_read_only;
102
103
104--echo # ro,sro: 1,1: set ro->0: 0,0
105set global read_only=1;
106set global super_read_only=1;
107select @@global.read_only;
108select @@global.super_read_only;
109set global read_only=0;
110select @@global.read_only;
111select @@global.super_read_only;
112
113--echo # ro,sro: 1,1: set ro->1: 1,1
114set global read_only=1;
115set global super_read_only=1;
116select @@global.read_only;
117select @@global.super_read_only;
118set global read_only=1;
119select @@global.read_only;
120select @@global.super_read_only;
121
122--echo # ro,sro: 1,1: set sro->0: 1,0
123set global read_only=1;
124set global super_read_only=1;
125select @@global.read_only;
126select @@global.super_read_only;
127set global super_read_only=0;
128select @@global.read_only;
129select @@global.super_read_only;
130
131--echo # ro,sro: 1,1: set sro->1: 1,1
132set global read_only=1;
133set global super_read_only=1;
134select @@global.read_only;
135select @@global.super_read_only;
136set global super_read_only=1;
137select @@global.read_only;
138select @@global.super_read_only;
139
140--disable_warnings
141set global read_only=0;
142set global super_read_only=0;
143DROP TABLE IF EXISTS t1,t2,t3;
144--enable_warnings
145
146
147--echo #
148--echo # Create tables/Make sure normal writes work
149--echo #
150
151set global super_read_only=0;
152
153--echo connection con1;
154connection con1;
155
156create table t1 (a int);
157
158insert into t1 values(1);
159
160create table t2 select * from t1;
161
162update t1, t2 set t1.a=2, t2.a=2;
163
164start transaction read write;
165commit;
166
167create trigger trig before insert on t1 for each row set new.a = new.a;
168
169--echo connection default;
170connection default;
171
172
173--echo #
174--echo # Make sure it blocks SUPER
175--echo #
176
177set global super_read_only=1;
178
179--error ER_OPTION_PREVENTS_STATEMENT
180create table t3 (a int);
181
182--error ER_OPTION_PREVENTS_STATEMENT
183drop table t3;
184
185--error ER_OPTION_PREVENTS_STATEMENT
186update t1, t2 set t1.a=3, t2.a=3;
187
188--error ER_OPTION_PREVENTS_STATEMENT
189start transaction read write;
190
191--error ER_OPTION_PREVENTS_STATEMENT
192drop trigger trig;
193
194--echo #
195--echo # Make sure it still blocks for non-super
196--echo #
197
198--echo connection con1;
199connection con1;
200
201select @@global.read_only;
202select @@global.super_read_only;
203
204--error ER_OPTION_PREVENTS_STATEMENT
205create table t3 (a int);
206
207--error ER_OPTION_PREVENTS_STATEMENT
208insert into t1 values(1);
209
210--error ER_OPTION_PREVENTS_STATEMENT
211update t1, t2 set t1.a=3, t2.a=3;
212
213--error ER_OPTION_PREVENTS_STATEMENT
214start transaction read write;
215
216--error ER_OPTION_PREVENTS_STATEMENT
217drop trigger trig;
218
219--echo #
220--echo # Cleanup
221--echo #
222
223--echo connection default;
224connection default;
225set global super_read_only=0;
226disconnect con1;
227drop table t1,t2;
228drop user test@localhost;
229
230# Restore original value
231set global read_only= @start_read_only;
232set global super_read_only= @start_super_read_only;
233