mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-10-31 10:56:12 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/read_only.result
Monty ce8a74f235 MDEV-36425 Extend read_only to also block share locks and super user 
The main purpose of this allow one to use the --read-only
option to ensure that no one can issue a query that can
block replication.

The --read-only option can now take 4 different values:
0  No read only (as before).
1  Blocks changes for users without the 'READ ONLY ADMIN'
   privilege (as before).
2  Blocks in addition LOCK TABLES and SELECT IN SHARE MODE
   for not 'READ ONLY ADMIN' users.
3  Blocks in addition 'READ_ONLY_ADMIN' users for all the
   previous statements.

read_only is changed to an enum and one can use the following
names for the lock levels:
OFF, ON, NO_LOCK, NO_LOCK_NO_ADMIN

Too keep things compatible with older versions config files, one can
still use values FALSE and TRUE, which are mapped to OFF and ON.

The main visible changes are:
- 'show variables like "read_only"' now returns a string
   instead of a number.
- Error messages related to read_only violations now contains
  the current value off readonly.

Other things:
- is_read_only_ctx() renamed to check_read_only_with_error()
- Moved TL_READ_SKIP_LOCKED to it's logical place

Reviewed by: Sergei Golubchik <serg@mariadb.org>
2025-04-28 12:59:39 +03:00

340 lines
11 KiB
Text
Raw Permalink Blame History

set @start_read_only= @@global.read_only;
DROP TABLE IF EXISTS t1,t2,t3;
create user test@localhost;
grant CREATE, SELECT, DROP on *.* to test@localhost;
grant ALL on test.* to test@localhost;
connect con1,localhost,test,,test;
connection default;
set global read_only=0;
connection con1;
create table t1 (a int);
create trigger trg1 before insert on t1 for each row set @a:=1;
insert into t1 values(1);
create table t2 select * from t1;
connection default;
set global read_only=1;
create table t3 (a int);
drop table t3;
connection con1;
select @@global.read_only;
@@global.read_only
ON
create table t3 (a int);
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
insert into t1 values(1);
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
drop trigger trg1;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
update t1 set a=1 where 1=0;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
update t1,t2 set t1.a=t2.a+1 where t1.a=t2.a;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
delete t1,t2 from t1,t2 where t1.a=t2.a;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
create temporary table t3 (a int);
create temporary table t4 (a int) select * from t3;
insert into t3 values(1);
insert into t4 select * from t3;
create table t3 (a int);
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
update t1,t3 set t3.a=t1.a+1 where t1.a=t3.a;
update t4,t3 set t4.a=t3.a+1 where t4.a=t3.a;
delete t1 from t1,t3 where t1.a=t3.a;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
delete t3 from t1,t3 where t1.a=t3.a;
delete t4 from t3,t4 where t4.a=t3.a;
create temporary table t1 (a int);
insert into t1 values(1);
update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a;
delete t1 from t1,t3 where t1.a=t3.a;
drop table t1;
insert into t1 values(1);
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
drop temporary table if exists t1;
Warnings:
Note 1051 Unknown table 'test.t1'
connection default;
set global read_only=0;
lock table t1 write;
connection con1;
lock table t2 write;
connection default;
set global read_only=1;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
unlock tables ;
send set global read_only=1;
set global read_only=1;
connection con1;
select @@global.read_only;
@@global.read_only
OFF
unlock tables ;
select @@global.read_only;
@@global.read_only
ON
connection default;
reap;
connection default;
set global read_only=0;
lock table t1 read;
connection con1;
lock table t2 read;
connection default;
set global read_only=1;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
unlock tables ;
set global read_only=1;
select @@global.read_only;
@@global.read_only
ON
connection con1;
select @@global.read_only;
@@global.read_only
ON
unlock tables ;
connection default;
connection default;
set global read_only=0;
BEGIN;
connection con1;
BEGIN;
connection default;
set global read_only=1;
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
ROLLBACK;
set global read_only=1;
connection con1;
select @@global.read_only;
@@global.read_only
ON
ROLLBACK;
connection default;
set global read_only=0;
flush tables with read lock;
set global read_only=1;
unlock tables;
connect root2,localhost,root,,test;
connection default;
set global read_only=0;
flush tables with read lock;
connection root2;
set global read_only=1;
connection default;
select @@global.read_only;
@@global.read_only
ON
unlock tables;
disconnect root2;
drop temporary table ttt;
ERROR 42S02: Unknown table 'test.ttt'
drop temporary table if exists ttt;
Warnings:
Note 1051 Unknown table 'test.ttt'
connection default;
set global read_only=0;
disconnect con1;
drop table t1,t2;
drop user test@localhost;
#
# Bug#27440 read_only allows create and drop database
#
set global read_only= 1;
drop database if exists mysqltest_db1;
drop database if exists mysqltest_db2;
delete from mysql.user where User like 'mysqltest_%';
delete from mysql.db where User like 'mysqltest_%';
delete from mysql.tables_priv where User like 'mysqltest_%';
delete from mysql.columns_priv where User like 'mysqltest_%';
flush privileges;
create user `mysqltest_u1`@`%`;
grant all on mysqltest_db2.* to `mysqltest_u1`@`%`;
create database mysqltest_db1;
grant all on mysqltest_db1.* to `mysqltest_u1`@`%`;
grant select on test.* to `mysqltest_u1`@`%`;
flush privileges;
connect con_bug27440,127.0.0.1,mysqltest_u1,,test,$MASTER_MYPORT,;
connection con_bug27440;
create database mysqltest_db2;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
show databases like '%mysqltest_db2%';
Database (%mysqltest_db2%)
drop database mysqltest_db1;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
disconnect con_bug27440;
connection default;
delete from mysql.user where User like 'mysqltest_%';
delete from mysql.db where User like 'mysqltest_%';
delete from mysql.tables_priv where User like 'mysqltest_%';
delete from mysql.columns_priv where User like 'mysqltest_%';
flush privileges;
drop database mysqltest_db1;
set global read_only= @start_read_only;
#
# MDEV-16987 - ALTER DATABASE possible in read-only mode
#
CREATE USER user1@localhost;
GRANT ALTER ON test1.* TO user1@localhost;
CREATE DATABASE test1;
SET GLOBAL read_only=1;
ALTER DATABASE test1 CHARACTER SET utf8;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
SET GLOBAL read_only=0;
DROP DATABASE test1;
DROP USER user1@localhost;
USE test;
# End of 5.5 tests
#
# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY);
#
#
# Test interaction with read_only system variable.
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1), (2);
CREATE USER user1;
GRANT ALL on test.* to user1;
connect con1, localhost, user1;
connection default;
SET GLOBAL read_only= 1;
# All allowed with super privilege
START TRANSACTION;
COMMIT;
START TRANSACTION READ ONLY;
COMMIT;
START TRANSACTION READ WRITE;
COMMIT;
# We allow implicit RW transaction without super privilege
# for compatibility reasons
connection con1;
START TRANSACTION;
# Check that table updates are still disallowed.
INSERT INTO t1 VALUES (3);
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
UPDATE t1 SET a= 1;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
DELETE FROM t1;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
COMMIT;
START TRANSACTION READ ONLY;
COMMIT;
# Explicit RW trans is not allowed without super privilege
START TRANSACTION READ WRITE;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
COMMIT;
disconnect con1;
connection default;
DROP USER user1;
SET GLOBAL read_only= 0;
DROP TABLE t1;
# End of 10.0 tests
#
# MDEV-36425 Extend read_only to also block share locks and super user
#
CREATE USER user1;
GRANT ALL on test.* to user1;
connect con1, localhost, user1;
connection default;
CREATE TABLE t1 (a int primary key auto_increment);
insert into t1 values (1),(2);
show variables like "read_only";
Variable_name Value
read_only OFF
SET GLOBAL read_only=1;
show variables like "read_only";
Variable_name Value
read_only ON
# admin
insert into t1 values ();
lock tables t1 read;
unlock tables;
lock tables t1 write;
unlock tables;
begin;
select count(*) from t1 LOCK IN SHARE MODE;
count(*)
3
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
count(*)
9
commit;
# user
connection con1;
insert into t1 values ();
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
lock tables t1 read;
unlock tables;
lock tables t1 write;
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
unlock tables;
begin;
select count(*) from t1 LOCK IN SHARE MODE;
count(*)
3
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
count(*)
9
commit;
connection default;
SET GLOBAL read_only=2;
show variables like "read_only";
Variable_name Value
read_only NO_LOCK
# admin
insert into t1 values ();
lock tables t1 read;
unlock tables;
lock tables t1 write;
unlock tables;
begin;
select count(*) from t1 LOCK IN SHARE MODE;
count(*)
4
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
count(*)
16
commit;
#user
connection con1;
insert into t1 values ();
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK option so it cannot execute this statement
lock tables t1 read;
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK option so it cannot execute this statement
unlock tables;
lock tables t1 write;
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK option so it cannot execute this statement
unlock tables;
begin;
select count(*) from t1 LOCK IN SHARE MODE;
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK option so it cannot execute this statement
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK option so it cannot execute this statement
commit;
connection default;
SET GLOBAL read_only=3;
show variables like "read_only";
Variable_name Value
read_only NO_LOCK_NO_ADMIN
# admin
insert into t1 values ();
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK_NO_ADMIN option so it cannot execute this statement
lock tables t1 read;
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK_NO_ADMIN option so it cannot execute this statement
unlock tables;
lock tables t1 write;
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK_NO_ADMIN option so it cannot execute this statement
unlock tables;
begin;
select count(*) from t1 LOCK IN SHARE MODE;
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK_NO_ADMIN option so it cannot execute this statement
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK_NO_ADMIN option so it cannot execute this statement
commit;
SET GLOBAL read_only=0;
select count(*) from t1;
count(*)
4
drop table t1;
drop user user1;
disconnect con1;
# End of 11.8 tests
Reference in a new issue View git blame Copy permalink