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