diff options
Diffstat (limited to 'mysql-test/main/flush.test')
| -rw-r--r-- | mysql-test/main/flush.test | 791 |
1 files changed, 791 insertions, 0 deletions
diff --git a/mysql-test/main/flush.test b/mysql-test/main/flush.test new file mode 100644 index 00000000..887c48ef --- /dev/null +++ b/mysql-test/main/flush.test @@ -0,0 +1,791 @@ +--disable_service_connection + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +connection con1; + +--disable_warnings +drop table if exists t1,t2; +drop database if exists mysqltest; +--enable_warnings + +create temporary table t1(n int not null primary key); +create table t2(n int); +insert into t2 values(3); +let $1=100; +disable_query_log; +while ($1) +{ + connection con1; + send replace into t1 select n from t2; + connection con2; + send flush tables; + connection con1; + reap; + connection con2; + reap; + dec $1; +} +enable_query_log; +connection con1; +select * from t1; +connection con2; +flush tables with read lock and disable checkpoint; +--error 1223 +drop table t2; +connection con1; +send drop table t2; +connection con2; +unlock tables; +connection con1; +reap; + +#test if drop database will wait until we release the global read lock +connection con1; +create database mysqltest; +create table mysqltest.t1(n int); +insert into mysqltest.t1 values (23); +flush tables with read lock; +connection con2; +send drop database mysqltest; +connection con1; +select * from mysqltest.t1; +unlock tables; +connection con2; +reap; + +# test if dirty close releases global read lock +connection con1; +create table t1 (n int); +flush tables with read lock; +dirty_close con1; +connection con2; +insert into t1 values (345); +select * from t1; +drop table t1; + +# +# Bug#9459 - deadlock with flush with lock, and lock table write +# +create table t1 (c1 int); +lock table t1 write; +# Cannot get the global read lock with write locked tables. +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables with read lock; +lock table t1 read; +# Cannot get the global read lock with read locked tables. +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables with read lock; +unlock tables; +flush tables with read lock; +--error 1223 +lock table t1 write; +lock table t1 read; +--error 1223 +lock table t1 write; +# Release all table locks and the global read lock. +unlock tables; +create table t2 (c1 int); +create table t3 (c1 int); +lock table t1 read, t2 read, t3 write; +# Cannot get the global read lock with write locked tables. +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables with read lock; +lock table t1 read, t2 read, t3 read; +# Cannot get the global read lock with read locked tables. +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables with read lock; +unlock tables; +drop table t1, t2, t3; + +# End of 4.1 tests + +# +# Test of deadlock problem when doing FLUSH TABLE with read lock +# (Bug was in NTPL threads in Linux when using different mutex while +# waiting for a condtion variable) + +create table t1 (c1 int); +create table t2 (c1 int); + +connect (con1,localhost,root,,); +connect (con3,localhost,root,,); + +connection con1; +lock table t1 write; + +connection con2; +send flush tables with read lock; +--sleep 1 + +connection con3; +send insert into t2 values(1); +--sleep 1 + +connection con1; +unlock tables; +disconnect con1; + +connection con2; +reap; +disconnect con2; + +connection con3; +# It hangs here (insert into t2 does not end). +reap; +disconnect con3; + +connection default; +drop table t1, t2; + +# +# Bug#32528 Global read lock with a low priority write lock causes a server crash +# + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +set session low_priority_updates=1; + +create table t1 (a int); +create table t2 (b int); + +lock tables t1 write; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables with read lock; +unlock tables; + +lock tables t1 read, t2 write; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables with read lock; +unlock tables; + +lock tables t1 read; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables with read lock; +unlock tables; + +drop table t1, t2; + +set session low_priority_updates=default; + +# +# Bug #33334 mysqltest_embedded crashes when disconnecting before reap +# + +connect (con1,localhost,root,,); +send select benchmark(200, (select sin(1))) > 1000; +disconnect con1; +--source include/wait_until_disconnected.inc +connection default; + +--echo End of 5.0 tests + +# +# Bug #26380: LOCK TABLES + FLUSH LOGS causes deadlock +# +set @old_general_log= @@general_log; +set @old_read_only= @@read_only; +set global general_log= on; + +flush tables with read lock; +flush logs; +unlock tables; + +set global read_only=1; +flush logs; +unlock tables; + +flush tables with read lock; +flush logs; +unlock tables; + +set global general_log= @old_general_log; +set global read_only= @old_read_only; + +--echo End of 5.1 tests + + +--echo # +--echo # Additional test for bug #51136 "Crash in pthread_rwlock_rdlock +--echo # on TEMPORARY + HANDLER + LOCK + SP". +--echo # Also see the main test for this bug in include/handler.inc. +--echo # +--disable_warnings +drop tables if exists t1, t2; +--enable_warnings +create table t1 (i int); +create temporary table t2 (j int); +flush tables with read lock; +lock table t2 read; +--echo # This commit should not release any MDL locks. +commit; +--echo # The below statement crashed before the bug fix as it +--echo # has attempted to release global shared metadata lock +--echo # which was already released by commit. +unlock tables; +drop tables t1, t2; + + + +--echo # +--echo # Tests for WL#5000 FLUSH TABLES|TABLE table_list WITH READ LOCK +--echo # +--echo # I. Check the incompatible changes in the grammar. +--echo # +--error ER_PARSE_ERROR +flush tables with read lock, hosts; +--error ER_PARSE_ERROR +flush privileges, tables; +--error ER_PARSE_ERROR +flush privileges, tables with read lock; +--error ER_PARSE_ERROR +flush privileges, tables; +--error ER_PARSE_ERROR +flush tables with read lock, tables; +show tables; +--echo # +--echo # II. Check the allowed syntax. +--echo # +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings +create table t1 (a int); +create table t2 (a int); +create table t3 (a int); +lock table t1 read, t2 read; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables with read lock; +unlock tables; +flush tables with read lock; +flush tables t1, t2 with read lock; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables t1, t2 with read lock; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +flush tables with read lock; +select * from t1; +select * from t2; +--error ER_TABLE_NOT_LOCKED +select * from t3; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +insert into t1 (a) values (1); +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +insert into t2 (a) values (1); +--error ER_TABLE_NOT_LOCKED +insert into t3 (a) values (1); +--error ER_NO_SUCH_TABLE +lock table no_such_table read; +--echo # +--echo # We implicitly left the locked tables +--echo # mode but still have the read lock. +--echo # +--error ER_CANT_UPDATE_WITH_READLOCK +insert into t2 (a) values (1); +unlock tables; +insert into t1 (a) values (1); +insert into t2 (a) values (1); +flush table t1, t2 with read lock; +select * from t1; +select * from t2; +--error ER_TABLE_NOT_LOCKED +select * from t3; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +insert into t1 (a) values (2); +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +insert into t2 (a) values (2); +--error ER_TABLE_NOT_LOCKED +insert into t3 (a) values (2); +--error ER_NO_SUCH_TABLE +lock table no_such_table read; +insert into t3 (a) values (2); + + +--echo # +--echo # III. Concurrent tests. +--echo # +connect (con1,localhost,root,,); +--echo # +--echo # Check that flush tables <list> with read lock +--echo # does not affect non-locked tables. +connection default; +--echo # +flush tables t1 with read lock; +connection con1; +select * from t1; +select * from t2; +insert into t2 (a) values (3); +connection default; +unlock tables; +--echo # +--echo # Check that "FLUSH TABLES <list> WITH READ LOCK" is +--echo # compatible with active "FLUSH TABLES WITH READ LOCK". +--echo # Vice versa it is not true, since tables read-locked by +--echo # "FLUSH TABLES <list> WITH READ LOCK" can't be flushed. +flush tables with read lock; +connection con1; +flush table t1 with read lock; +select * from t1; +unlock tables; +connection default; +unlock tables; +--echo # +--echo # Check that FLUSH TABLES t1 WITH READ LOCK +--echo # does not conflict with an existing FLUSH TABLES t2 +--echo # WITH READ LOCK. +--echo # +flush table t1 with read lock; +connection con1; +flush table t2 with read lock; +unlock tables; +connection default; +unlock tables; +--echo # +--echo # Check that FLUSH TABLES t1 WITH READ LOCK +--echo # does not conflict with SET GLOBAL read_only=1. +--echo # +set global read_only=1; +connection con1; +flush table t1 with read lock; +unlock tables; +connection default; +set global read_only=0; +--echo # +--echo # Check that it's possible to read-lock +--echo # tables locked with FLUSH TABLE <list> WITH READ LOCK. +--echo # +flush tables t1, t2 with read lock; +connection con1; +lock table t1 read, t2 read; +unlock tables; +connection default; +unlock tables; + +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +connection default; +drop table t1, t2, t3; + +--echo # +--echo # Bug#51710 FLUSH TABLES <view> WITH READ LOCK kills the server +--echo # +--disable_warnings +drop view if exists v1, v2, v3; +drop table if exists t1, v1; +--enable_warnings +create table t1 (a int); +create view v1 as select 1; +create view v2 as select * from t1; +create view v3 as select * from v2; + +flush table v1, v2, v3 with read lock; +unlock tables; +flush table v1 with read lock; +unlock tables; +flush table v2 with read lock; +unlock tables; +flush table v3 with read lock; +unlock tables; +create temporary table v1 (a int); +flush table v1 with read lock; +unlock tables; +drop view v1; +create table v1 (a int); +flush table v1 with read lock; +drop temporary table v1; +unlock tables; +drop view v2, v3; +drop table t1, v1; + + +--echo # +--echo # FLUSH TABLES <list> WITH READ LOCK and HANDLER +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (a int, key a (a)); +insert into t1 (a) values (1), (2), (3); +handler t1 open; +handler t1 read a next; +handler t1 read a next; +flush tables t1 with read lock; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +handler t1 read a next; +unlock tables; +handler t1 read a next; +handler t1 close; +drop table t1; + +--echo # +--echo # Bug#52117 Pending FLUSH TALBES <list> aborts +--echo # transactions unnecessarily. +--echo # +--disable_view_protocol +--disable_warnings +drop table if exists t1; +--enable_warnings +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +connection default; + +create table t1 (a int); +begin; +select * from t1; +connection con1; +--echo # +--echo # Issue a LOCK TABLE t1 READ. We could use HANDLER t1 OPEN +--echo # or a long-running select -- anything that +--echo # prevents FLUSH TABLE t1 from immediate completion would do. +--echo # +lock table t1 read; +connection con2; +--echo # +--echo # FLUSH TABLE expels the table definition from the cache. +--echo # Sending 'flush table t1'... +send flush table t1; +connection default; +--echo # Let flush table sync in. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" + and info = "flush table t1"; +--source include/wait_condition.inc +select * from t1; +connection con1; +select * from t1; +unlock tables; +connection default; +select count(*) from information_schema.processlist where state = "Waiting for table metadata lock"; +commit; +connection con2; +--echo # Reaping 'flush table t1'... +reap; +connection default; + +--echo # +--echo # Repeat the same test but with FLUSH TABLES +--echo # + +begin; +select * from t1; +connection con1; +--echo # +--echo # Issue a LOCK TABLE t1 READ. +--echo # +lock table t1 read; +connection con2; +send flush tables; +connection default; +select * from t1; +connection con1; +select * from t1; +unlock tables; +connection con2; +--echo # Reaping 'flush tables'... +reap; +connection default; +commit; + +--echo # Cleanup + +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc +connection default; +drop table t1; +--enable_view_protocol + +--echo # +--echo # Test for bug #55273 "FLUSH TABLE tm WITH READ LOCK for Merge table +--echo # causes assert failure". +--echo # +--disable_warnings +drop table if exists t1, t2, tm; +--enable_warnings +create table t1 (i int); +create table t2 (i int); +create table tm (i int) engine=merge union=(t1, t2); +insert into t1 values (1), (2); +insert into t2 values (3), (4); +--echo # The below statement should succeed and lock merge +--echo # table for read. Only merge table gets flushed and +--echo # not underlying tables. +flush tables tm with read lock; +select * from tm; +--echo # Check that underlying tables are locked. +select * from t1; +select * from t2; +unlock tables; +--echo # This statement should succeed as well and flush +--echo # all tables in the list. +flush tables tm, t1, t2 with read lock; +select * from tm; +--echo # Naturally, underlying tables should be locked in this case too. +select * from t1; +select * from t2; +unlock tables; +drop tables tm, t1, t2; + + +--echo # +--echo # Test for bug #57006 "Deadlock between HANDLER and +--echo # FLUSH TABLES WITH READ LOCK". +--echo # +--disable_view_protocol +--disable_warnings +drop table if exists t1, t2; +--enable_warnings +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +connection default; +create table t1 (i int); +create table t2 (i int); +handler t1 open; + +connection con1; +flush tables with read lock; +connection default; +--echo # The below statement should not cause deadlock. +--echo # Sending: +--send insert into t2 values (1) + +connection con2; +--echo # Wait until INSERT starts to wait for FTWRL to go away. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for backup lock" + and info = "insert into t2 values (1)"; +--source include/wait_condition.inc + +connection con1; +unlock tables; + +connection default; +--echo # Reap INSERT. +--reap +handler t1 close; + +--echo # Cleanup. +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc +connection default; +drop tables t1, t2; +--enable_view_protocol + +--echo # +--echo # Bug#57649 FLUSH TABLES under FLUSH TABLES <list> WITH READ LOCK leads +--echo # to assert failure. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT); +FLUSH TABLES t1 WITH READ LOCK; + +# All these triggered the assertion +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +FLUSH TABLES; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a= 1; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +ALTER TABLE t1 COMMENT 'test'; + +UNLOCK TABLES; +DROP TABLE t1; + + +--echo # +--echo # Test for bug #12641342 - "61401: UPDATE PERFORMANCE DEGRADES +--echo # GRADUALLY IF A TRIGGER EXISTS". +--echo # +--echo # One of side-effects of this bug was that a transaction which +--echo # involved DML statements requiring prelocking blocked concurrent +--echo # FLUSH TABLES WITH READ LOCK for the whole its duration, while +--echo # correct behavior in this case is to block FTWRL only for duration +--echo # of individual DML statements. +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TABLE t1 (id INT PRIMARY KEY, value INT); +INSERT INTO t1 VALUES (1, 1); +CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW SET @var = "a"; +BEGIN; +UPDATE t1 SET value= value + 1 WHERE id = 1; + +connect(con1, localhost, root); +--echo # The below FLUSH TABLES WITH READ LOCK should succeed and +--echo # should not be blocked by the transaction in default connection. +FLUSH TABLES WITH READ LOCK; +UNLOCK TABLES; +disconnect con1; +--source include/wait_until_disconnected.inc + +connection default; +COMMIT; +DROP TABLE t1; + +--echo # +--echo # Test flushing slave or relay logs twice +--echo # +--error ER_WRONG_USAGE +flush relay logs,relay logs; +--error ER_WRONG_USAGE +flush slave,slave; + +--echo # +--echo # MDEV-15890 Strange error message if you try to +--echo # FLUSH TABLES <view> after LOCK TABLES <view>. +--echo # + +CREATE TABLE t1 (qty INT, price INT); +CREATE VIEW v1 AS SELECT qty, price, qty*price AS value FROM t1; + +LOCK TABLES v1 READ; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +FLUSH TABLES v1; +UNLOCK TABLES; + +LOCK TABLES v1 WRITE; +FLUSH TABLES v1; +UNLOCK TABLES; + +LOCK TABLES v1 READ; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +FLUSH TABLES t1; +UNLOCK TABLES; + +LOCK TABLES t1 READ; +--error ER_TABLE_NOT_LOCKED +FLUSH TABLES v1; +UNLOCK TABLES; + +DROP VIEW v1; +DROP TABLE t1; + +--echo # +--echo # MDEV-15888 Implement FLUSH TABLES tbl_name [, tbl_name] ... WITH READ LOCK for views. +--echo # + +create table t1 (a int); +insert into t1 values (1), (2), (3); +create view v1 as select * from t1; +create view v2 as select * from v1; + +flush table v1 with read lock; +connect(con1, localhost, root); +--send insert into v1 values (4) +--sleep 1 +connection default; +--echo # Wait until INSERT starts to wait for FTWRL to go away. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" + and info = "insert into v1 values (4)"; +--source include/wait_condition.inc +select * from t1; +unlock tables; + +connection con1; +reap; + +connection default; +select * from t1; + +flush table v2 with read lock; +connection con1; +--send insert into t1 values (5) +--sleep 1 +connection default; +--echo # Wait until INSERT starts to wait for FTWRL to go away. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" + and info = "insert into t1 values (5)"; +--source include/wait_condition.inc +select * from t1; +unlock tables; + +connection con1; +reap; + +connection default; +select * from t1; + +drop view v1, v2; +drop table t1; +disconnect con1; + +--echo # +--echo # MDEV-25837 Assertion `thd->locked_tables_mode == LTM_NONE' failed in Locked_tables_list::init_locked_tables. +--echo # + +CREATE FUNCTION f() RETURNS INTEGER RETURN 1; +CREATE TABLE t (a INT); +CREATE VIEW v AS SELECT 2 FROM t WHERE f() < 3; +FLUSH TABLE v WITH READ LOCK; + +UNLOCK TABLES; +DROP VIEW v; +DROP FUNCTION f; +DROP TABLE t; + +--echo # +--echo # Test FLUSH THREADS +--echo # + +set @save_thread_cache_size=@@global.thread_cache_size; +set @@global.thread_cache_size=0; +flush threads; +show status like "Threads_cached"; +set @@global.thread_cache_size=@save_thread_cache_size; + +--echo # +--echo # MDEV-25906: SIGSEGV in flush_tables_with_read_lock on FTWRL or FTFE | SIGSEGV in ha_maria::extra +--echo # +CREATE VIEW v0 AS SELECT 1; +CREATE VIEW v1 AS SELECT 1 FROM (SELECT 1) AS d; +CREATE VIEW v2 AS SELECT * FROM v1; + +FLUSH TABLE v0 WITH READ LOCK; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +DROP VIEW v0; +UNLOCK TABLES; +FLUSH TABLE v1 WITH READ LOCK; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +DROP VIEW v1; +UNLOCK TABLES; +FLUSH TABLE v2 WITH READ LOCK; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +DROP VIEW v2; +UNLOCK TABLES; + +FLUSH TABLE v0 FOR EXPORT; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +DROP VIEW v0; +UNLOCK TABLES; +FLUSH TABLE v1 FOR EXPORT; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +DROP VIEW v1; +UNLOCK TABLES; +FLUSH TABLE v2 FOR EXPORT; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +DROP VIEW v2; +UNLOCK TABLES; +DROP VIEW v2, v1, v0; + +--echo # +--echo # End of 10.6 tests +--echo # + +--enable_service_connection |