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