FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT; UNLOCK TABLES; CREATE TABLE t1 ( m MEDIUMTEXT ) ENGINE=InnoDB; INSERT INTO t1 VALUES ( REPEAT('i',1048576) ); 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 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; # Connection con1 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 # Connection con2 # 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 # 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 # Connection defalt # Test 7: Check privileges required. # in flush-innodb-notembedded.test # Test 8: FLUSH TABLE 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; # Connection con1 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 # 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 # Connection con1 # 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; # Reset innodb_file_per_table SET GLOBAL innodb_file_per_table= @old_innodb_file_per_table; # End of 5.6 tests