mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
278 lines
6.4 KiB
Text
278 lines
6.4 KiB
Text
# should work with embedded server after mysqltest is fixed
|
|
-- source include/not_embedded.inc
|
|
-- source include/have_innodb.inc
|
|
-- source include/no_view_protocol.inc
|
|
|
|
#
|
|
# BUG#11733: COMMITs should not happen if read-only is set
|
|
#
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS table_11733 ;
|
|
--enable_warnings
|
|
|
|
# READ_ONLY does nothing to SUPER users
|
|
# so we use a non-SUPER one:
|
|
|
|
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;
|
|
|
|
# Any transactional engine will do
|
|
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;
|
|
select * from table_11733 ;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
COMMIT;
|
|
|
|
connection default;
|
|
set global read_only=0;
|
|
drop table table_11733 ;
|
|
drop user test@localhost;
|
|
|
|
disconnect con1;
|
|
|
|
#
|
|
# Bug #35732: read-only blocks SELECT statements in InnoDB
|
|
#
|
|
# Test 1: read only mode
|
|
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;
|
|
BEGIN;
|
|
SELECT * FROM t1;
|
|
COMMIT;
|
|
|
|
connection default;
|
|
SET GLOBAL read_only=0;
|
|
|
|
#
|
|
# Test 2: global read lock
|
|
#
|
|
FLUSH TABLES WITH READ LOCK;
|
|
|
|
connection con1;
|
|
SELECT * FROM t1;
|
|
BEGIN;
|
|
SELECT * FROM t1;
|
|
COMMIT;
|
|
|
|
#
|
|
# Tests that LOCK TABLE doesn't block the SET READ_ONLY=1 for the InnoDB tables
|
|
#
|
|
|
|
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;
|
|
|
|
disconnect con1;
|
|
|
|
--echo # End of 5.1 tests
|
|
|
|
--echo #
|
|
--echo # Bug#33669: Transactional temporary tables do not work under --read-only
|
|
--echo #
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS db1;
|
|
--enable_warnings
|
|
|
|
--echo # 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);
|
|
|
|
--echo
|
|
--echo # Create, insert and drop temporary table:
|
|
--echo
|
|
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
|
|
INSERT INTO temp VALUES (1);
|
|
DROP TABLE temp;
|
|
|
|
--echo
|
|
--echo # Lock base tables and use temporary table:
|
|
--echo
|
|
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
|
|
LOCK TABLES t1 READ, t2 READ;
|
|
SELECT * FROM t1;
|
|
INSERT INTO temp values (1);
|
|
SELECT * FROM t2;
|
|
UNLOCK TABLES;
|
|
DROP TABLE temp;
|
|
|
|
--echo
|
|
--echo # Transaction
|
|
--echo
|
|
BEGIN;
|
|
SELECT * FROM t1;
|
|
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO temp VALUES (1);
|
|
SELECT * FROM t2;
|
|
ROLLBACK;
|
|
SELECT * FROM temp;
|
|
DROP TABLE temp;
|
|
|
|
--echo
|
|
--echo # Lock base table as READ and temporary table as WRITE:
|
|
--echo
|
|
CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB;
|
|
LOCK TABLES t1 READ, temp WRITE;
|
|
SELECT * FROM t1;
|
|
SELECT * FROM temp;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO temp VALUES (1);
|
|
DROP TABLE temp;
|
|
UNLOCK TABLES;
|
|
|
|
--echo
|
|
--echo # Lock temporary table that shadows a base table:
|
|
--echo
|
|
CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB;
|
|
LOCK TABLES t1 WRITE;
|
|
DROP TABLE t1;
|
|
--error ER_TABLE_NOT_LOCKED
|
|
SELECT * FROM t1;
|
|
|
|
--echo
|
|
--echo # INSERT SELECT from base table into temporary table:
|
|
--echo
|
|
|
|
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;
|
|
SELECT * FROM temp2 ORDER BY a;
|
|
ROLLBACK;
|
|
SELECT * FROM temp1,temp2;
|
|
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;
|
|
SELECT * FROM temp2 ORDER BY a;
|
|
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;
|
|
SELECT * FROM temp2 ORDER BY a;
|
|
DROP TABLE temp1, temp2;
|
|
|
|
--echo
|
|
--echo # 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;
|
|
SELECT * FROM temp2 ORDER BY a;
|
|
DROP TABLE temp1, temp2;
|
|
|
|
--echo
|
|
--echo # Multiple table update:
|
|
--echo
|
|
|
|
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;
|
|
DROP TABLE temp1, temp2;
|
|
|
|
--echo
|
|
--echo # Disconnect and cleanup
|
|
--echo
|
|
disconnect con1;
|
|
connection default;
|
|
SET GLOBAL READ_ONLY = OFF;
|
|
DROP USER bug33669@localhost;
|
|
DROP DATABASE db1;
|
|
|
|
--echo # End of 5.5 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-33889 Read only server throws error when running a create temporary table as select statement
|
|
--echo #
|
|
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;
|
|
disconnect u1;
|
|
|
|
connection default;
|
|
drop table t1;
|
|
drop user u1@localhost;
|
|
set global read_only=0;
|
|
|
|
--echo # End of 10.5 tests
|