mirror of
https://github.com/MariaDB/server.git
synced 2025-10-08 08:49:13 +02:00
eeb00ceffd
Follow-up patch with adjustments of test files and updates of result files for tests. Some of tests were rewritten slighlty. Everywhere where common pattern used: ----- CREATE USER userA; --connect con1 ... userA ... <sql statements...> --disconnect con1 DROP USER userA; ----- the DROP USER statement has been eclosed into the directive --disable_warnings --enable_warnings This change is caused by the race conddition between --disconnect and DROP USER since a number of currently running sessions established on behalf the user being dropped is counted by holding the rw_lock THD_list_iterator::lock that is not acquired on execution the DROP USER statement but the lock is taken as the last step on handling disconnection (when the client is already sending the next statement). Therefore, for the cases where the command --disconnect precedes the DROP USER statement we hide the possible warnings about presence of active sessions for the user being deleted to make tests deterministic.
273 lines
6.2 KiB
Text
273 lines
6.2 KiB
Text
DROP TABLE IF EXISTS table_11733 ;
|
|
create user test@localhost;
|
|
grant CREATE, SELECT, DROP, INSERT on *.* to test@localhost;
|
|
connect con1,localhost,test,,test;
|
|
connection default;
|
|
set global read_only=0;
|
|
create table table_11733 (a int) engine=InnoDb;
|
|
connection con1;
|
|
BEGIN;
|
|
insert into table_11733 values(11733);
|
|
connection default;
|
|
set global read_only=1;
|
|
connection con1;
|
|
select @@global.read_only;
|
|
@@global.read_only
|
|
ON
|
|
select * from table_11733 ;
|
|
a
|
|
11733
|
|
COMMIT;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
connection default;
|
|
set global read_only=0;
|
|
drop table table_11733 ;
|
|
drop user test@localhost;
|
|
Warnings:
|
|
Note 4226 Dropped users ['test'@'localhost'] have active connections. Use KILL CONNECTION if they should not be used anymore.
|
|
disconnect con1;
|
|
create user test@localhost;
|
|
GRANT CREATE, SELECT, DROP, LOCK TABLES ON *.* TO test@localhost;
|
|
connect con1, localhost, test, , test;
|
|
connection default;
|
|
CREATE TABLE t1(a INT) ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES (0), (1);
|
|
SET GLOBAL read_only=1;
|
|
connection con1;
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
1
|
|
BEGIN;
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
1
|
|
COMMIT;
|
|
connection default;
|
|
SET GLOBAL read_only=0;
|
|
FLUSH TABLES WITH READ LOCK;
|
|
connection con1;
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
1
|
|
BEGIN;
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
1
|
|
COMMIT;
|
|
connection default;
|
|
UNLOCK TABLES;
|
|
connection con1;
|
|
lock table t1 read;
|
|
connection default;
|
|
set global read_only=1;
|
|
connection con1;
|
|
unlock tables;
|
|
connection default;
|
|
SET GLOBAL read_only=0;
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
DROP USER test@localhost;
|
|
Warnings:
|
|
Note 4226 Dropped users ['test'@'localhost'] have active connections. Use KILL CONNECTION if they should not be used anymore.
|
|
disconnect con1;
|
|
# End of 5.1 tests
|
|
#
|
|
# Bug#33669: Transactional temporary tables do not work under --read-only
|
|
#
|
|
DROP DATABASE IF EXISTS db1;
|
|
# Setup user and tables
|
|
CREATE USER bug33669@localhost;
|
|
CREATE DATABASE db1;
|
|
CREATE TABLE db1.t1 (a INT) ENGINE=INNODB;
|
|
CREATE TABLE db1.t2 (a INT) ENGINE=INNODB;
|
|
INSERT INTO db1.t1 VALUES (1);
|
|
INSERT INTO db1.t2 VALUES (2);
|
|
GRANT CREATE TEMPORARY TABLES, DROP, INSERT, DELETE, UPDATE,
|
|
SELECT, LOCK TABLES ON db1.* TO bug33669@localhost;
|
|
SET GLOBAL READ_ONLY = ON;
|
|
connect con1,localhost,bug33669,,db1;
|
|
|
|
# Create, insert and drop temporary table:
|
|
|
|
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
|
|
INSERT INTO temp VALUES (1);
|
|
DROP TABLE temp;
|
|
|
|
# Lock base tables and use temporary table:
|
|
|
|
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
|
|
LOCK TABLES t1 READ, t2 READ;
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
INSERT INTO temp values (1);
|
|
SELECT * FROM t2;
|
|
a
|
|
2
|
|
UNLOCK TABLES;
|
|
DROP TABLE temp;
|
|
|
|
# Transaction
|
|
|
|
BEGIN;
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES (1);
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
INSERT INTO temp VALUES (1);
|
|
SELECT * FROM t2;
|
|
a
|
|
2
|
|
ROLLBACK;
|
|
SELECT * FROM temp;
|
|
a
|
|
DROP TABLE temp;
|
|
|
|
# Lock base table as READ and temporary table as WRITE:
|
|
|
|
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
|
|
LOCK TABLES t1 READ, temp WRITE;
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
SELECT * FROM temp;
|
|
a
|
|
INSERT INTO t1 VALUES (1);
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
INSERT INTO temp VALUES (1);
|
|
DROP TABLE temp;
|
|
UNLOCK TABLES;
|
|
|
|
# Lock temporary table that shadows a base table:
|
|
|
|
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB;
|
|
LOCK TABLES t1 WRITE;
|
|
DROP TABLE t1;
|
|
SELECT * FROM t1;
|
|
ERROR HY000: Table 't1' was not locked with LOCK TABLES
|
|
|
|
# INSERT SELECT from base table into temporary table:
|
|
|
|
CREATE TEMPORARY TABLE temp1 (a INT) ENGINE=INNODB;
|
|
CREATE TEMPORARY TABLE temp2 LIKE temp1;
|
|
BEGIN;
|
|
INSERT INTO temp1 VALUES (10);
|
|
INSERT INTO temp2 VALUES (10);
|
|
INSERT INTO temp1 SELECT * FROM t1;
|
|
INSERT INTO temp2 SELECT * FROM t2;
|
|
SELECT * FROM temp1 ORDER BY a;
|
|
a
|
|
1
|
|
10
|
|
SELECT * FROM temp2 ORDER BY a;
|
|
a
|
|
2
|
|
10
|
|
ROLLBACK;
|
|
SELECT * FROM temp1,temp2;
|
|
a a
|
|
LOCK TABLES t1 READ, t2 READ;
|
|
INSERT INTO temp1 VALUES (10);
|
|
INSERT INTO temp2 VALUES (10);
|
|
INSERT INTO temp1 SELECT * FROM t1;
|
|
INSERT INTO temp2 SELECT * FROM t2;
|
|
SELECT * FROM temp1 ORDER BY a;
|
|
a
|
|
1
|
|
10
|
|
SELECT * FROM temp2 ORDER BY a;
|
|
a
|
|
2
|
|
10
|
|
UNLOCK TABLES;
|
|
DELETE temp1, temp2 FROM temp1, temp2;
|
|
INSERT INTO temp1 VALUES (10);
|
|
INSERT INTO temp2 VALUES (10);
|
|
INSERT INTO temp1 SELECT * FROM t1;
|
|
INSERT INTO temp2 SELECT * FROM t2;
|
|
SELECT * FROM temp1 ORDER BY a;
|
|
a
|
|
1
|
|
10
|
|
SELECT * FROM temp2 ORDER BY a;
|
|
a
|
|
2
|
|
10
|
|
DROP TABLE temp1, temp2;
|
|
|
|
# INSERT and INSERT SELECT that uses subqueries:
|
|
CREATE TEMPORARY TABLE temp1 (a INT) ENGINE=INNODB;
|
|
CREATE TEMPORARY TABLE temp2 LIKE temp1;
|
|
INSERT INTO temp1 (a) VALUES ((SELECT MAX(a) FROM t1));
|
|
LOCK TABLES t2 READ;
|
|
INSERT INTO temp2 (a) VALUES ((SELECT MAX(a) FROM t2));
|
|
UNLOCK TABLES;
|
|
LOCK TABLES t1 READ, t2 READ;
|
|
INSERT INTO temp1 SELECT * FROM t1 WHERE a < (SELECT MAX(a) FROM t2);
|
|
INSERT INTO temp2 SELECT * FROM t2 WHERE a > (SELECT MAX(a) FROM t1);
|
|
UNLOCK TABLES;
|
|
INSERT INTO temp1 SELECT * FROM t1 WHERE a < (SELECT MAX(a) FROM t2);
|
|
INSERT INTO temp2 SELECT * FROM t2 WHERE a > (SELECT MAX(a) FROM t1);
|
|
SELECT * FROM temp1 ORDER BY a;
|
|
a
|
|
1
|
|
1
|
|
1
|
|
SELECT * FROM temp2 ORDER BY a;
|
|
a
|
|
2
|
|
2
|
|
2
|
|
DROP TABLE temp1, temp2;
|
|
|
|
# Multiple table update:
|
|
|
|
CREATE TEMPORARY TABLE temp1 (a INT) ENGINE=INNODB;
|
|
CREATE TEMPORARY TABLE temp2 LIKE temp1;
|
|
INSERT INTO temp1 VALUES (1),(2);
|
|
INSERT INTO temp2 VALUES (3),(4);
|
|
UPDATE temp1,temp2 SET temp1.a = 5, temp2.a = 10;
|
|
SELECT * FROM temp1, temp2;
|
|
a a
|
|
5 10
|
|
5 10
|
|
5 10
|
|
5 10
|
|
DROP TABLE temp1, temp2;
|
|
|
|
# Disconnect and cleanup
|
|
|
|
disconnect con1;
|
|
connection default;
|
|
SET GLOBAL READ_ONLY = OFF;
|
|
DROP USER bug33669@localhost;
|
|
DROP DATABASE db1;
|
|
# End of 5.5 tests
|
|
#
|
|
# MDEV-33889 Read only server throws error when running a create temporary table as select statement
|
|
#
|
|
create table t1(a int) engine=innodb;
|
|
create user u1@localhost;
|
|
grant insert, select, update, delete, create temporary tables on test.* to u1@localhost;
|
|
insert into t1 values (1);
|
|
set global read_only=1;
|
|
connect u1,localhost,u1;
|
|
set default_tmp_storage_engine=innodb;
|
|
create temporary table tt1 (a int);
|
|
create temporary table tt2 like t1;
|
|
create temporary table tt3 as select * from t1;
|
|
select * from tt3;
|
|
a
|
|
1
|
|
disconnect u1;
|
|
connection default;
|
|
drop table t1;
|
|
drop user u1@localhost;
|
|
set global read_only=0;
|
|
# End of 10.5 tests
|