EVOLUTION-MANAGER

Edit File: flush-innodb.result

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