Edit File: flush_table.result
drop table if exists t1,t2; create table t1 (a int not null auto_increment primary key); insert into t1 values(0); lock table t1 read; flush table t1; ERROR HY000: Table 't1' was locked with a READ lock and can't be updated unlock tables; lock table t1 write; flush table t1; check table t1; Table Op Msg_type Msg_text test.t1 check status OK unlock tables; lock table t1 write; lock table t1 read; flush table t1; select * from t1; a 1 unlock tables; unlock tables; lock table t1 write; lock table t1 write; flush table t1; select * from t1; a 1 unlock tables; unlock tables; select * from t1; a 1 drop table t1; create table t1(table_id char(20) primary key); create table t2(table_id char(20) primary key); insert into t1 values ('test.t1'); insert into t1 values (''); insert into t2 values ('test.t2'); insert into t2 values (''); handler t1 open as a1; handler t1 open as a2; handler t2 open; handler a1 read first limit 9; table_id test.t1 handler a2 read first limit 9; table_id test.t1 handler t2 read first limit 9; table_id test.t2 flush tables; handler a1 read first limit 9; table_id test.t1 handler a2 read first limit 9; table_id test.t1 handler t2 read first limit 9; table_id test.t2 handler t1 open as a1; ERROR 42000: Not unique table/alias: 'a1' handler t1 open as a2; ERROR 42000: Not unique table/alias: 'a2' handler t2 open; ERROR 42000: Not unique table/alias: 't2' handler a1 read first limit 9; table_id test.t1 handler a2 read first limit 9; table_id test.t1 handler t2 read first limit 9; table_id test.t2 flush table t1; handler a1 read first limit 9; table_id test.t1 handler a2 read first limit 9; table_id test.t1 handler t2 read first limit 9; table_id test.t2 flush table t2; handler t2 close; drop table t1; drop table t2; create table t1(table_id char(20) primary key); insert into t1 values ('Record-01'); insert into t1 values ('Record-02'); insert into t1 values ('Record-03'); insert into t1 values ('Record-04'); insert into t1 values ('Record-05'); handler t1 open; handler t1 read first limit 1; table_id Record-01 handler t1 read next limit 1; table_id Record-02 handler t1 read next limit 1; table_id Record-03 flush table t1; handler t1 read next limit 1; table_id Record-01 handler t1 read next limit 1; table_id Record-02 handler t1 close; drop table t1; FLUSH TABLES WITH READ LOCK ; FLUSH TABLES WITH READ LOCK ; UNLOCK TABLES; # # 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 MySQL 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 MySQL 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= MyISAM; FLUSH TABLE t1 FOR EXPORT; ERROR HY000: Table storage engine for 't1' doesn't have this option DROP TABLE t1; # Connection con1 # Connection defalt # Test 7: Check privileges required. # CREATE DATABASE db1; CREATE TABLE db1.t1 (a INT) engine= InnoDB; GRANT RELOAD, SELECT, LOCK TABLES ON *.* TO user1@localhost; GRANT CREATE, DROP ON *.* TO user2@localhost; GRANT RELOAD, SELECT ON *.* TO user3@localhost; GRANT SELECT, LOCK TABLES ON *.* TO user4@localhost; GRANT RELOAD, LOCK TABLES ON *.* TO user5@localhost; # Connection con1 as user1 FLUSH TABLE db1.t1 FOR EXPORT; UNLOCK TABLES; # Connection default # Connection con1 as user2 FLUSH TABLE db1.t1 FOR EXPORT; ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation # Connection default # Connection con1 as user3 FLUSH TABLE db1.t1 FOR EXPORT; ERROR 42000: Access denied for user 'user3'@'localhost' to database 'db1' # Connection default # Connection con1 as user4 FLUSH TABLE db1.t1 FOR EXPORT; ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation # Connection default # Connection con1 as user5 FLUSH TABLE db1.t1 FOR EXPORT; ERROR 42000: SELECT command denied to user 'user5'@'localhost' for table 't1' # Connection default DROP USER user1@localhost, user2@localhost, user3@localhost, user4@localhost, user5@localhost; DROP TABLE db1.t1; DROP DATABASE db1; # 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; # 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