diff options
Diffstat (limited to 'mysql-test/main/flush-innodb.result')
| -rw-r--r-- | mysql-test/main/flush-innodb.result | 305 |
1 files changed, 305 insertions, 0 deletions
diff --git a/mysql-test/main/flush-innodb.result b/mysql-test/main/flush-innodb.result new file mode 100644 index 00000000000..21e5bda7785 --- /dev/null +++ b/mysql-test/main/flush-innodb.result @@ -0,0 +1,305 @@ +FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT; +UNLOCK TABLES; +CREATE TABLE t1 ( m MEDIUMTEXT ) ENGINE=InnoDB; +INSERT INTO t1 VALUES ( REPEAT('i',65535) ); +DROP TABLE t1; + +# +# WL#6168: FLUSH TABLES ... FOR EXPORT -- parser +# + +# Requires innodb_file_per_table +SET @old_innodb_file_per_table= @@GLOBAL.innodb_file_per_table; +SET GLOBAL innodb_file_per_table= 1; +# new "EXPORT" keyword is a valid user variable name: +SET @export = 10; +# new "EXPORT" keyword is a valid SP parameter name: +CREATE PROCEDURE p1(export INT) BEGIN END; +DROP PROCEDURE p1; +# new "EXPORT" keyword is a valid local variable name: +CREATE PROCEDURE p1() +BEGIN +DECLARE export INT; +END| +DROP PROCEDURE p1; +# new "EXPORT" keyword is a valid SP name: +CREATE PROCEDURE export() BEGIN END; +DROP PROCEDURE export; +# new FLUSH TABLES ... FOR EXPORT syntax: +FLUSH TABLES FOR EXPORT; +ERROR 42000: No tables used near 'FOR EXPORT' at line 1 +FLUSH TABLES WITH EXPORT; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXPORT' at line 1 +CREATE TABLE t1 (i INT) engine=InnoDB; +CREATE TABLE t2 LIKE t1; +FLUSH TABLES t1,t2 WITH EXPORT; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXPORT' at line 1 +FLUSH TABLES t1, t2 FOR EXPORT; +UNLOCK TABLES; +# case check +FLUSH TABLES t1, t2 for ExPoRt; +UNLOCK TABLES; +# With LOCAL keyword +FLUSH LOCAL TABLES t1, t2 FOR EXPORT; +UNLOCK TABLES; +# Tables with fully qualified names +FLUSH LOCAL TABLES test.t1, test.t2 for ExPoRt; +UNLOCK TABLES; +DROP TABLES t1, t2; +# new "EXPORT" keyword is a valid table name: +CREATE TABLE export (i INT) engine=InnoDB; +# it's ok to lock the "export" table for export: +FLUSH TABLE export FOR EXPORT; +UNLOCK TABLES; +DROP TABLE export; +# +# WL#6169 FLUSH TABLES ... FOR EXPORT -- runtime +# +# Test 1: Views, temporary tables, non-existent tables +# +CREATE VIEW v1 AS SELECT 1; +CREATE TEMPORARY TABLE t1 (a INT); +FLUSH TABLES v1 FOR EXPORT; +ERROR HY000: 'test.v1' is not of type 'BASE TABLE' +FLUSH TABLES t1 FOR EXPORT; +ERROR 42S02: Table 'test.t1' doesn't exist +FLUSH TABLES non_existent FOR EXPORT; +ERROR 42S02: Table 'test.non_existent' doesn't exist +DROP TEMPORARY TABLE t1; +DROP VIEW v1; +# Test 2: Blocked by update transactions, blocks updates. +# +CREATE TABLE t1 (a INT PRIMARY KEY, b INT) engine= InnoDB; +CREATE TABLE t2 (a INT) engine= InnoDB; +connect con1, localhost, root; +START TRANSACTION; +INSERT INTO t1 VALUES (1, 1); +connection default; +# Should be blocked +# Sending: +FLUSH TABLES t1 FOR EXPORT; +connection con1; +COMMIT; +connection default; +# Reaping: FLUSH TABLES t1 FOR EXPORT +connection con1; +# Should not be blocked +INSERT INTO t2 VALUES (1); +# Should be blocked +# Sending: +INSERT INTO t1 VALUES (2, 2); +connection default; +UNLOCK TABLES; +connection con1; +# Reaping: INSERT INTO t1 VALUES (2, 2); +# Test 3: Read operations should not be affected. +# +START TRANSACTION; +SELECT * FROM t1; +a b +1 1 +2 2 +connection default; +# Should not be blocked +FLUSH TABLES t1 FOR EXPORT; +connection con1; +COMMIT; +# Should not be blocked +SELECT * FROM t1; +a b +1 1 +2 2 +connection default; +UNLOCK TABLES; +# Test 4: Blocked by DDL, blocks DDL. +# +START TRANSACTION; +SELECT * FROM t1; +a b +1 1 +2 2 +connect con2, localhost, root; +# Sending: +ALTER TABLE t1 ADD INDEX i1(b); +connection con1; +# Should be blocked +FLUSH TABLE t1 FOR EXPORT; +connection default; +COMMIT; +connection con2; +# Reaping ALTER TABLE ... +connection con1; +# Reaping FLUSH TABLE t1 FOR EXPORT +UNLOCK TABLES; +connection default; +FLUSH TABLE t1 FOR EXPORT; +connection con2; +# Should be blocked +DROP TABLE t1; +connection default; +UNLOCK TABLES; +connection con2; +# Reaping DROP TABLE t1 +disconnect con2; +connection default; +DROP TABLE t2; +# Test 5: Compatibilty with FLUSH TABLES WITH READ LOCK +# +CREATE TABLE t1(a INT) engine= InnoDB; +FLUSH TABLES WITH READ LOCK; +connection con1; +# This should not block +FLUSH TABLE t1 FOR EXPORT; +UNLOCK TABLES; +connection default; +UNLOCK TABLES; +DROP TABLE t1; +# Test 6: Unsupported storage engines. +# +CREATE TABLE t1(a INT) engine= MEMORY; +FLUSH TABLE t1 FOR EXPORT; +ERROR HY000: Storage engine MEMORY of the table `test`.`t1` doesn't have this option +DROP TABLE t1; +connection con1; +disconnect con1; +connection default; +# Test 7: Check privileges required. +# in flush-innodb-notembedded.test +# Test 8: FLUSH TABLE <table_list> FOR EXPORT is incompatible +# with itself (to avoid race conditions in metadata +# file handling). +# +CREATE TABLE t1 (a INT) engine= InnoDB; +CREATE TABLE t2 (a INT) engine= InnoDB; +connect con1, localhost, root; +FLUSH TABLE t1 FOR EXPORT; +connection default; +# This should not block +FLUSH TABLE t2 FOR EXPORT; +UNLOCK TABLES; +# This should block +# Sending: +FLUSH TABLE t1 FOR EXPORT; +connection con1; +UNLOCK TABLES; +connection default; +# Reaping: FLUSH TABLE t1 FOR EXPORT +UNLOCK TABLES; +# Test 9: LOCK TABLES ... READ is not affected +# +LOCK TABLE t1 READ; +connection con1; +# Should not block +FLUSH TABLE t1 FOR EXPORT; +UNLOCK TABLES; +connection default; +UNLOCK TABLES; +FLUSH TABLE t1 FOR EXPORT; +connection con1; +# Should not block +LOCK TABLE t1 READ; +UNLOCK TABLES; +connection default; +UNLOCK TABLES; +connection con1; +disconnect con1; +connection default; +DROP TABLE t1, t2; +# Test 10: Lock is released if transaction is started after doing +# 'flush table..' in same session +CREATE TABLE t1 ( i INT ) ENGINE = Innodb; +FLUSH TABLE t1 FOR EXPORT; +# error as active locks already exist +FLUSH TABLE t1 FOR EXPORT; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# active locks will be released due to start transaction +START TRANSACTION; +# passes as start transaction released ealier locks +FLUSH TABLE t1 FOR EXPORT; +UNLOCK TABLES; +DROP TABLE t1; +# Test 11: Test 'flush table with fully qualified table names +# and with syntax local/NO_WRITE_TO_BINLOG +connect con1, localhost, root; +connection default; +CREATE TABLE t1 ( i INT ) ENGINE = Innodb; +INSERT INTO t1 VALUES (100),(200); +FLUSH LOCAL TABLES test.t1 FOR EXPORT; +connection con1; +# Should be blocked +# Sending: +FLUSH LOCAL TABLES t1 FOR EXPORT; +connection default; +UNLOCK TABLE; +connection con1; +# Reaping: FLUSH LOCAL TABLES t1 FOR EXPORT +SELECT * FROM t1 ORDER BY i; +i +100 +200 +connection default; +# Should be blocked +# Sending: +FLUSH NO_WRITE_TO_BINLOG TABLES test.t1 FOR EXPORT; +connection con1; +UNLOCK TABLES; +connection default; +# Reaping: FLUSH NO_WRITE_TO_BINLOG TABLES test.t1 FOR EXPORT +SELECT * FROM t1 ORDER BY i; +i +100 +200 +UNLOCK TABLE; +DROP TABLE t1; +# Test 12: Active transaction get committed if user execute +# "FLUSH TABLE ... FOR EXPORT" or "LOCK TABLE.." +connection default; +CREATE TABLE t1 ( i INT ) ENGINE = Innodb; +INSERT INTO t1 VALUES (100),(200); +START TRANSACTION; +INSERT INTO t1 VALUES (300); +# 'flush table..' commit active transaction from same session +FLUSH LOCAL TABLES test.t1 FOR EXPORT; +ROLLBACK; +SELECT * FROM t1 ORDER BY i; +i +100 +200 +300 +START TRANSACTION; +INSERT INTO t1 VALUES (400); +# 'lock table ..' commit active transaction from same session +LOCK TABLES test.t1 READ; +ROLLBACK; +SELECT * FROM t1 ORDER BY i; +i +100 +200 +300 +400 +UNLOCK TABLES; +DROP TABLE t1; +# Test 13: Verify "FLUSH TABLE ... FOR EXPORT" and "LOCK TABLE.." +# in same session +connection default; +CREATE TABLE t1 ( i INT ) ENGINE = Innodb; +# Lock table +LOCK TABLES test.t1 WRITE; +# 'lock table ..' completes even if table lock is acquired +# in same session using 'lock table'. Previous locks are released. +LOCK TABLES test.t1 READ; +# 'flush table ..' gives error if table lock is acquired +# in same session using 'lock table ..' +FLUSH TABLES test.t1 FOR EXPORT; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +# 'lock table ..' completes even if table lock is acquired +# in same session using 'flush table'. Previous locks are released. +LOCK TABLES test.t1 WRITE; +UNLOCK TABLES; +DROP TABLE t1; +connection con1; +disconnect con1; +connection default; +# Reset innodb_file_per_table +SET GLOBAL innodb_file_per_table= @old_innodb_file_per_table; +# End of 5.6 tests |