1set @start_read_only= @@global.read_only; 2DROP TABLE IF EXISTS t1,t2,t3; 3create user test@localhost; 4grant CREATE, SELECT, DROP on *.* to test@localhost; 5connect con1,localhost,test,,test; 6connection default; 7set global read_only=0; 8connection con1; 9create table t1 (a int); 10create trigger trg1 before insert on t1 for each row set @a:=1; 11insert into t1 values(1); 12create table t2 select * from t1; 13connection default; 14set global read_only=1; 15create table t3 (a int); 16drop table t3; 17connection con1; 18select @@global.read_only; 19@@global.read_only 201 21create table t3 (a int); 22ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 23insert into t1 values(1); 24ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 25drop trigger trg1; 26ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 27update t1 set a=1 where 1=0; 28ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 29update t1,t2 set t1.a=t2.a+1 where t1.a=t2.a; 30ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 31delete t1,t2 from t1,t2 where t1.a=t2.a; 32ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 33create temporary table t3 (a int); 34create temporary table t4 (a int) select * from t3; 35insert into t3 values(1); 36insert into t4 select * from t3; 37create table t3 (a int); 38ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 39update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; 40ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 41update t1,t3 set t3.a=t1.a+1 where t1.a=t3.a; 42update t4,t3 set t4.a=t3.a+1 where t4.a=t3.a; 43delete t1 from t1,t3 where t1.a=t3.a; 44ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 45delete t3 from t1,t3 where t1.a=t3.a; 46delete t4 from t3,t4 where t4.a=t3.a; 47create temporary table t1 (a int); 48insert into t1 values(1); 49update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; 50delete t1 from t1,t3 where t1.a=t3.a; 51drop table t1; 52insert into t1 values(1); 53ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 54drop temporary table if exists t1; 55Warnings: 56Note 1051 Unknown table 'test.t1' 57connection default; 58set global read_only=0; 59lock table t1 write; 60connection con1; 61lock table t2 write; 62connection default; 63set global read_only=1; 64ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 65unlock tables ; 66send set global read_only=1; 67set global read_only=1; 68connection con1; 69select @@global.read_only; 70@@global.read_only 710 72unlock tables ; 73select @@global.read_only; 74@@global.read_only 751 76connection default; 77reap; 78connection default; 79set global read_only=0; 80lock table t1 read; 81connection con1; 82lock table t2 read; 83connection default; 84set global read_only=1; 85ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 86unlock tables ; 87set global read_only=1; 88select @@global.read_only; 89@@global.read_only 901 91connection con1; 92select @@global.read_only; 93@@global.read_only 941 95unlock tables ; 96connection default; 97connection default; 98set global read_only=0; 99BEGIN; 100connection con1; 101BEGIN; 102connection default; 103set global read_only=1; 104ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 105ROLLBACK; 106set global read_only=1; 107connection con1; 108select @@global.read_only; 109@@global.read_only 1101 111ROLLBACK; 112connection default; 113set global read_only=0; 114flush tables with read lock; 115set global read_only=1; 116unlock tables; 117connect root2,localhost,root,,test; 118connection default; 119set global read_only=0; 120flush tables with read lock; 121connection root2; 122set global read_only=1; 123connection default; 124select @@global.read_only; 125@@global.read_only 1261 127unlock tables; 128disconnect root2; 129drop temporary table ttt; 130ERROR 42S02: Unknown table 'test.ttt' 131drop temporary table if exists ttt; 132Warnings: 133Note 1051 Unknown table 'test.ttt' 134connection default; 135set global read_only=0; 136disconnect con1; 137drop table t1,t2; 138drop user test@localhost; 139# 140# Bug#27440 read_only allows create and drop database 141# 142set global read_only= 1; 143drop database if exists mysqltest_db1; 144drop database if exists mysqltest_db2; 145delete from mysql.user where User like 'mysqltest_%'; 146delete from mysql.db where User like 'mysqltest_%'; 147delete from mysql.tables_priv where User like 'mysqltest_%'; 148delete from mysql.columns_priv where User like 'mysqltest_%'; 149flush privileges; 150create user `mysqltest_u1`@`%`; 151grant all on mysqltest_db2.* to `mysqltest_u1`@`%`; 152create database mysqltest_db1; 153grant all on mysqltest_db1.* to `mysqltest_u1`@`%`; 154flush privileges; 155connect con_bug27440,127.0.0.1,mysqltest_u1,,test,$MASTER_MYPORT,; 156connection con_bug27440; 157create database mysqltest_db2; 158ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 159show databases like '%mysqltest_db2%'; 160Database (%mysqltest_db2%) 161drop database mysqltest_db1; 162ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 163disconnect con_bug27440; 164connection default; 165delete from mysql.user where User like 'mysqltest_%'; 166delete from mysql.db where User like 'mysqltest_%'; 167delete from mysql.tables_priv where User like 'mysqltest_%'; 168delete from mysql.columns_priv where User like 'mysqltest_%'; 169flush privileges; 170drop database mysqltest_db1; 171set global read_only= @start_read_only; 172# 173# MDEV-16987 - ALTER DATABASE possible in read-only mode 174# 175CREATE USER user1@localhost; 176GRANT ALTER ON test1.* TO user1@localhost; 177CREATE DATABASE test1; 178SET GLOBAL read_only=1; 179ALTER DATABASE test1 CHARACTER SET utf8; 180ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 181SET GLOBAL read_only=0; 182DROP DATABASE test1; 183DROP USER user1@localhost; 184USE test; 185# End of 5.5 tests 186# 187# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY); 188# 189# 190# Test interaction with read_only system variable. 191CREATE TABLE t1(a INT); 192INSERT INTO t1 VALUES (1), (2); 193CREATE USER user1; 194connect con1, localhost, user1; 195connection default; 196SET GLOBAL read_only= 1; 197# All allowed with super privilege 198START TRANSACTION; 199COMMIT; 200START TRANSACTION READ ONLY; 201COMMIT; 202START TRANSACTION READ WRITE; 203COMMIT; 204# We allow implicit RW transaction without super privilege 205# for compatibility reasons 206connection con1; 207START TRANSACTION; 208# Check that table updates are still disallowed. 209INSERT INTO t1 VALUES (3); 210ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 211UPDATE t1 SET a= 1; 212ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 213DELETE FROM t1; 214ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 215COMMIT; 216START TRANSACTION READ ONLY; 217COMMIT; 218# Explicit RW trans is not allowed without super privilege 219START TRANSACTION READ WRITE; 220ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement 221COMMIT; 222disconnect con1; 223connection default; 224DROP USER user1; 225SET GLOBAL read_only= 0; 226DROP TABLE t1; 227# End of 10.0 tests 228