From 25a436bdc4a043bc7adb842c009d30d2130c13d2 Mon Sep 17 00:00:00 2001 From: Date: Fri, 22 Jan 2010 17:38:21 +0800 Subject: Bug #49132 Replication failure on temporary table + DDL In RBR, DDL statement will change binlog format to non row-based format before it is binlogged, but the binlog format was not be restored, and then manipulating a temporary table can not reset binlog format to row-based format rightly. So that the manipulated statement is binlogged with statement-based format. To fix the problem, restore the state of binlog format after the DDL statement is binlogged. --- .../extra/rpl_tests/rpl_tmp_table_and_DDL.test | 159 +++++++++++++++++++++ .../binlog/r/binlog_row_mix_innodb_myisam.result | 64 ++++++--- .../suite/ndb/r/ndb_tmp_table_and_DDL.result | 90 ++++++++++++ mysql-test/suite/ndb/t/ndb_tmp_table_and_DDL.test | 11 ++ .../suite/rpl/r/rpl_tmp_table_and_DDL.result | 96 +++++++++++++ mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test | 13 ++ 6 files changed, 411 insertions(+), 22 deletions(-) create mode 100644 mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL.test create mode 100644 mysql-test/suite/ndb/r/ndb_tmp_table_and_DDL.result create mode 100644 mysql-test/suite/ndb/t/ndb_tmp_table_and_DDL.test create mode 100644 mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result create mode 100644 mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test (limited to 'mysql-test') diff --git a/mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL.test b/mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL.test new file mode 100644 index 00000000000..9cf287281a0 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_tmp_table_and_DDL.test @@ -0,0 +1,159 @@ +# +# This test verify if executing DDL statement before trying to manipulate +# a temporary table causes row-based replication to break with error 'table +# does not exist'. +# + +# CREATE TABLE when a temporary table is open. +CREATE TEMPORARY TABLE t1 (a INT); +EVAL CREATE TABLE t2 (a INT, b INT) ENGINE= $ENGINE_TYPE; +INSERT INTO t1 VALUES (1); + +# CREATE EVENT when a temporary table is open. +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; +INSERT INTO t1 VALUES (1); + +# ALTER EVENT when a temporary table is open. +ALTER EVENT e1 ON SCHEDULE EVERY 20 HOUR DO SELECT 1; +INSERT INTO t1 VALUES (1); + +# DROP EVENT when a temporary table is open. +DROP EVENT IF EXISTS e1; +INSERT INTO t1 VALUES (1); + +# CREATE PROCEDURE when a temporary table is open. +CREATE PROCEDURE p1() SELECT 1; +INSERT INTO t1 VALUES (1); + +# Alter PROCEDURE when a temporary table is open. +ALTER PROCEDURE p1 SQL SECURITY INVOKER; +INSERT INTO t1 VALUES (1); + +# CREATE FUNCTION when a temporary table is open. +CREATE FUNCTION f1() RETURNS INT RETURN 123; +INSERT INTO t1 VALUES (1); + +# ALTER FUNCTION when a temporary table is open. +ALTER FUNCTION f1 SQL SECURITY INVOKER; +INSERT INTO t1 VALUES (1); + +# CREATE DATABASE when a temporary table is open. +CREATE DATABASE mysqltest1; +INSERT INTO t1 VALUES (1); + +# DROP DATABASE when a temporary table is open. +DROP DATABASE mysqltest1; +INSERT INTO t1 VALUES (1); + +# CREATE USER when a temporary table is open. +CREATE USER test_1@localhost; +INSERT INTO t1 VALUES (1); + +# GRANT select on table to user when a temporary table is open. +GRANT SELECT ON t2 TO test_1@localhost; +INSERT INTO t1 VALUES (1); + +# GRANT all on function to user when a temporary table is open. +GRANT ALL ON f1 TO test_1@localhost; +INSERT INTO t1 VALUES (1); + +# GRANT all on procedure to user when a temporary table is open. +GRANT ALL ON p1 TO test_1@localhost; +INSERT INTO t1 VALUES (1); + +# GRANT usage on *.* to user when a temporary table is open. +GRANT USAGE ON *.* TO test_1@localhost; +INSERT INTO t1 VALUES (1); + +# REVOKE ALL PRIVILEGES on function to user when a temporary table is open. +REVOKE ALL PRIVILEGES ON f1 FROM test_1@localhost; +INSERT INTO t1 VALUES (1); + +# REVOKE ALL PRIVILEGES on procedure to user when a temporary table is open. +REVOKE ALL PRIVILEGES ON p1 FROM test_1@localhost; +INSERT INTO t1 VALUES (1); + +# REVOKE ALL PRIVILEGES on table to user when a temporary table is open. +REVOKE ALL PRIVILEGES ON t2 FROM test_1@localhost; +INSERT INTO t1 VALUES (1); + +# REVOKE usage on *.* from user when a temporary table is open. +REVOKE USAGE ON *.* FROM test_1@localhost; +INSERT INTO t1 VALUES (1); + +# RENAME USER when a temporary table is open. +RENAME USER test_1@localhost TO test_2@localhost; +INSERT INTO t1 VALUES (1); + +# DROP USER when a temporary table is open. +DROP USER test_2@localhost; +INSERT INTO t1 VALUES (1); + +# Test ACL statement in sub statement +DELIMITER |; +CREATE PROCEDURE p2() +BEGIN + # CREATE USER when a temporary table is open. + CREATE TEMPORARY TABLE t3 (a INT); + CREATE USER test_2@localhost; + INSERT INTO t1 VALUES (1); + + # GRANT select on table to user when a temporary table is open. + GRANT SELECT ON t2 TO test_2@localhost; + INSERT INTO t1 VALUES (1); + + # GRANT all on function to user when a temporary table is open. + GRANT ALL ON f1 TO test_2@localhost; + INSERT INTO t1 VALUES (1); + + # GRANT all on procedure to user when a temporary table is open. + GRANT ALL ON p1 TO test_2@localhost; + INSERT INTO t1 VALUES (1); + + # GRANT usage on *.* to user when a temporary table is open. + GRANT USAGE ON *.* TO test_2@localhost; + INSERT INTO t1 VALUES (1); + + # REVOKE ALL PRIVILEGES on function to user when a temporary table is open. + REVOKE ALL PRIVILEGES ON f1 FROM test_2@localhost; + INSERT INTO t1 VALUES (1); + + # REVOKE ALL PRIVILEGES on procedure to user when a temporary table is open. + REVOKE ALL PRIVILEGES ON p1 FROM test_2@localhost; + INSERT INTO t1 VALUES (1); + + # REVOKE ALL PRIVILEGES on table to user when a temporary table is open. + REVOKE ALL PRIVILEGES ON t2 FROM test_2@localhost; + INSERT INTO t1 VALUES (1); + + # REVOKE usage on *.* from user when a temporary table is open. + REVOKE USAGE ON *.* FROM test_2@localhost; + INSERT INTO t1 VALUES (1); + + # RENAME USER when a temporary table is open. + RENAME USER test_2@localhost TO test_3@localhost; + INSERT INTO t1 VALUES (1); + + # DROP USER when a temporary table is open. + DROP USER test_3@localhost; + INSERT INTO t1 VALUES (1); + DROP TEMPORARY TABLE t3; +END | +DELIMITER ;| + +# DROP PROCEDURE when a temporary table is open. +DROP PROCEDURE p1; +INSERT INTO t1 VALUES (1); +DROP PROCEDURE p2; +INSERT INTO t1 VALUES (1); + +# DROP FUNCTION when a temporary table is open. +DROP FUNCTION f1; +INSERT INTO t1 VALUES (1); + +# DROP TABLE when a temporary table is open. +DROP TABLE t2; +INSERT INTO t1 VALUES (1); + +DROP TEMPORARY TABLE t1; + diff --git a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result index ef98275041c..87e9b7f8685 100644 --- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result @@ -772,8 +772,11 @@ insert into t2 values (bug27417(2)); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show binlog events from ; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Intvar # # INSERT_ID=3 -master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(2)) +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 3 */; count(*) 3 @@ -787,8 +790,11 @@ count(*) 2 show binlog events from ; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Intvar # # INSERT_ID=4 -master-bin.000001 # Query # # use `test`; delete from t2 where a=bug27417(3) +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT select count(*) from t1 /* must be 5 */; count(*) 5 @@ -810,8 +816,9 @@ ERROR 23000: Duplicate entry '1' for key 'PRIMARY' show binlog events from ; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN -master-bin.000001 # Intvar # # INSERT_ID=1 -master-bin.000001 # Query # # use `test`; insert into t2 values (bug27417(1)) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 1 */; count(*) @@ -825,8 +832,10 @@ ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show binlog events from ; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN -master-bin.000001 # Intvar # # INSERT_ID=2 -master-bin.000001 # Query # # use `test`; insert into t2 select bug27417(1) union select bug27417(2) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 2 */; count(*) @@ -838,8 +847,13 @@ update t3 set b=b+bug27417(1); ERROR 23000: Duplicate entry '4' for key 'b' show binlog events from ; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Intvar # # INSERT_ID=4 -master-bin.000001 # Query # # use `test`; update t3 set b=b+bug27417(1) +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 2 */; count(*) 2 @@ -853,8 +867,9 @@ ERROR 23000: Duplicate entry '2' for key 'PRIMARY' show binlog events from ; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN -master-bin.000001 # Intvar # # INSERT_ID=6 -master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */ +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 4 */; count(*) @@ -869,7 +884,7 @@ UPDATE t3,t4 SET t3.a=t4.a + bug27417(1); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' select count(*) from t1 /* must be 1 */; count(*) -1 +2 drop table t4; delete from t1; delete from t2; @@ -884,8 +899,10 @@ ERROR 23000: Duplicate entry '1' for key 'PRIMARY' show binlog events from ; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN -master-bin.000001 # Intvar # # INSERT_ID=9 -master-bin.000001 # Query # # use `test`; delete from t2 +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 1 */; count(*) @@ -904,7 +921,11 @@ ERROR 23000: Duplicate entry '1' for key 'PRIMARY' show binlog events from ; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN -master-bin.000001 # Query # # use `test`; delete t2.* from t2,t5 where t2.a=t5.a + 1 +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # ROLLBACK select count(*) from t1 /* must be 1 */; count(*) @@ -924,12 +945,11 @@ count(*) show binlog events from ; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN -master-bin.000001 # Intvar # # INSERT_ID=10 -master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci -master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# -master-bin.000001 # Intvar # # INSERT_ID=10 -master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci -master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @b) SET `b`=((@b) + `bug27417`(2)) ;file_id=# +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # ROLLBACK drop trigger trg_del_t2; drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/suite/ndb/r/ndb_tmp_table_and_DDL.result b/mysql-test/suite/ndb/r/ndb_tmp_table_and_DDL.result new file mode 100644 index 00000000000..1b0f718ad65 --- /dev/null +++ b/mysql-test/suite/ndb/r/ndb_tmp_table_and_DDL.result @@ -0,0 +1,90 @@ +CREATE TEMPORARY TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT) ENGINE= NDB; +INSERT INTO t1 VALUES (1); +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; +INSERT INTO t1 VALUES (1); +ALTER EVENT e1 ON SCHEDULE EVERY 20 HOUR DO SELECT 1; +INSERT INTO t1 VALUES (1); +DROP EVENT IF EXISTS e1; +INSERT INTO t1 VALUES (1); +CREATE PROCEDURE p1() SELECT 1; +INSERT INTO t1 VALUES (1); +ALTER PROCEDURE p1 SQL SECURITY INVOKER; +INSERT INTO t1 VALUES (1); +CREATE FUNCTION f1() RETURNS INT RETURN 123; +INSERT INTO t1 VALUES (1); +ALTER FUNCTION f1 SQL SECURITY INVOKER; +INSERT INTO t1 VALUES (1); +CREATE DATABASE mysqltest1; +INSERT INTO t1 VALUES (1); +DROP DATABASE mysqltest1; +INSERT INTO t1 VALUES (1); +CREATE USER test_1@localhost; +INSERT INTO t1 VALUES (1); +GRANT SELECT ON t2 TO test_1@localhost; +INSERT INTO t1 VALUES (1); +GRANT ALL ON f1 TO test_1@localhost; +INSERT INTO t1 VALUES (1); +GRANT ALL ON p1 TO test_1@localhost; +INSERT INTO t1 VALUES (1); +GRANT USAGE ON *.* TO test_1@localhost; +INSERT INTO t1 VALUES (1); +REVOKE ALL PRIVILEGES ON f1 FROM test_1@localhost; +INSERT INTO t1 VALUES (1); +REVOKE ALL PRIVILEGES ON p1 FROM test_1@localhost; +INSERT INTO t1 VALUES (1); +REVOKE ALL PRIVILEGES ON t2 FROM test_1@localhost; +INSERT INTO t1 VALUES (1); +REVOKE USAGE ON *.* FROM test_1@localhost; +INSERT INTO t1 VALUES (1); +RENAME USER test_1@localhost TO test_2@localhost; +INSERT INTO t1 VALUES (1); +DROP USER test_2@localhost; +INSERT INTO t1 VALUES (1); +CREATE PROCEDURE p2() +BEGIN +# CREATE USER when a temporary table is open. +CREATE TEMPORARY TABLE t3 (a INT); +CREATE USER test_2@localhost; +INSERT INTO t1 VALUES (1); +# GRANT select on table to user when a temporary table is open. +GRANT SELECT ON t2 TO test_2@localhost; +INSERT INTO t1 VALUES (1); +# GRANT all on function to user when a temporary table is open. +GRANT ALL ON f1 TO test_2@localhost; +INSERT INTO t1 VALUES (1); +# GRANT all on procedure to user when a temporary table is open. +GRANT ALL ON p1 TO test_2@localhost; +INSERT INTO t1 VALUES (1); +# GRANT usage on *.* to user when a temporary table is open. +GRANT USAGE ON *.* TO test_2@localhost; +INSERT INTO t1 VALUES (1); +# REVOKE ALL PRIVILEGES on function to user when a temporary table is open. +REVOKE ALL PRIVILEGES ON f1 FROM test_2@localhost; +INSERT INTO t1 VALUES (1); +# REVOKE ALL PRIVILEGES on procedure to user when a temporary table is open. +REVOKE ALL PRIVILEGES ON p1 FROM test_2@localhost; +INSERT INTO t1 VALUES (1); +# REVOKE ALL PRIVILEGES on table to user when a temporary table is open. +REVOKE ALL PRIVILEGES ON t2 FROM test_2@localhost; +INSERT INTO t1 VALUES (1); +# REVOKE usage on *.* from user when a temporary table is open. +REVOKE USAGE ON *.* FROM test_2@localhost; +INSERT INTO t1 VALUES (1); +# RENAME USER when a temporary table is open. +RENAME USER test_2@localhost TO test_3@localhost; +INSERT INTO t1 VALUES (1); +# DROP USER when a temporary table is open. +DROP USER test_3@localhost; +INSERT INTO t1 VALUES (1); +DROP TEMPORARY TABLE t3; +END | +DROP PROCEDURE p1; +INSERT INTO t1 VALUES (1); +DROP PROCEDURE p2; +INSERT INTO t1 VALUES (1); +DROP FUNCTION f1; +INSERT INTO t1 VALUES (1); +DROP TABLE t2; +INSERT INTO t1 VALUES (1); +DROP TEMPORARY TABLE t1; diff --git a/mysql-test/suite/ndb/t/ndb_tmp_table_and_DDL.test b/mysql-test/suite/ndb/t/ndb_tmp_table_and_DDL.test new file mode 100644 index 00000000000..748c0ac28e4 --- /dev/null +++ b/mysql-test/suite/ndb/t/ndb_tmp_table_and_DDL.test @@ -0,0 +1,11 @@ +# +# Bug#49132 +# This test verifies if executing DDL statement before trying to manipulate +# a temporary table causes row-based replication to break with error 'table +# does not exist' base on ndb engine. +# + +source include/have_ndb.inc; + +LET $ENGINE_TYPE= NDB; +source extra/rpl_tests/rpl_tmp_table_and_DDL.test; diff --git a/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result b/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result new file mode 100644 index 00000000000..5729faa9659 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result @@ -0,0 +1,96 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +CREATE TEMPORARY TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT) ENGINE= MyISAM; +INSERT INTO t1 VALUES (1); +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; +INSERT INTO t1 VALUES (1); +ALTER EVENT e1 ON SCHEDULE EVERY 20 HOUR DO SELECT 1; +INSERT INTO t1 VALUES (1); +DROP EVENT IF EXISTS e1; +INSERT INTO t1 VALUES (1); +CREATE PROCEDURE p1() SELECT 1; +INSERT INTO t1 VALUES (1); +ALTER PROCEDURE p1 SQL SECURITY INVOKER; +INSERT INTO t1 VALUES (1); +CREATE FUNCTION f1() RETURNS INT RETURN 123; +INSERT INTO t1 VALUES (1); +ALTER FUNCTION f1 SQL SECURITY INVOKER; +INSERT INTO t1 VALUES (1); +CREATE DATABASE mysqltest1; +INSERT INTO t1 VALUES (1); +DROP DATABASE mysqltest1; +INSERT INTO t1 VALUES (1); +CREATE USER test_1@localhost; +INSERT INTO t1 VALUES (1); +GRANT SELECT ON t2 TO test_1@localhost; +INSERT INTO t1 VALUES (1); +GRANT ALL ON f1 TO test_1@localhost; +INSERT INTO t1 VALUES (1); +GRANT ALL ON p1 TO test_1@localhost; +INSERT INTO t1 VALUES (1); +GRANT USAGE ON *.* TO test_1@localhost; +INSERT INTO t1 VALUES (1); +REVOKE ALL PRIVILEGES ON f1 FROM test_1@localhost; +INSERT INTO t1 VALUES (1); +REVOKE ALL PRIVILEGES ON p1 FROM test_1@localhost; +INSERT INTO t1 VALUES (1); +REVOKE ALL PRIVILEGES ON t2 FROM test_1@localhost; +INSERT INTO t1 VALUES (1); +REVOKE USAGE ON *.* FROM test_1@localhost; +INSERT INTO t1 VALUES (1); +RENAME USER test_1@localhost TO test_2@localhost; +INSERT INTO t1 VALUES (1); +DROP USER test_2@localhost; +INSERT INTO t1 VALUES (1); +CREATE PROCEDURE p2() +BEGIN +# CREATE USER when a temporary table is open. +CREATE TEMPORARY TABLE t3 (a INT); +CREATE USER test_2@localhost; +INSERT INTO t1 VALUES (1); +# GRANT select on table to user when a temporary table is open. +GRANT SELECT ON t2 TO test_2@localhost; +INSERT INTO t1 VALUES (1); +# GRANT all on function to user when a temporary table is open. +GRANT ALL ON f1 TO test_2@localhost; +INSERT INTO t1 VALUES (1); +# GRANT all on procedure to user when a temporary table is open. +GRANT ALL ON p1 TO test_2@localhost; +INSERT INTO t1 VALUES (1); +# GRANT usage on *.* to user when a temporary table is open. +GRANT USAGE ON *.* TO test_2@localhost; +INSERT INTO t1 VALUES (1); +# REVOKE ALL PRIVILEGES on function to user when a temporary table is open. +REVOKE ALL PRIVILEGES ON f1 FROM test_2@localhost; +INSERT INTO t1 VALUES (1); +# REVOKE ALL PRIVILEGES on procedure to user when a temporary table is open. +REVOKE ALL PRIVILEGES ON p1 FROM test_2@localhost; +INSERT INTO t1 VALUES (1); +# REVOKE ALL PRIVILEGES on table to user when a temporary table is open. +REVOKE ALL PRIVILEGES ON t2 FROM test_2@localhost; +INSERT INTO t1 VALUES (1); +# REVOKE usage on *.* from user when a temporary table is open. +REVOKE USAGE ON *.* FROM test_2@localhost; +INSERT INTO t1 VALUES (1); +# RENAME USER when a temporary table is open. +RENAME USER test_2@localhost TO test_3@localhost; +INSERT INTO t1 VALUES (1); +# DROP USER when a temporary table is open. +DROP USER test_3@localhost; +INSERT INTO t1 VALUES (1); +DROP TEMPORARY TABLE t3; +END | +DROP PROCEDURE p1; +INSERT INTO t1 VALUES (1); +DROP PROCEDURE p2; +INSERT INTO t1 VALUES (1); +DROP FUNCTION f1; +INSERT INTO t1 VALUES (1); +DROP TABLE t2; +INSERT INTO t1 VALUES (1); +DROP TEMPORARY TABLE t1; diff --git a/mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test b/mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test new file mode 100644 index 00000000000..56924a2efe9 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test @@ -0,0 +1,13 @@ +# +# Bug#49132 +# This test verifies if executing DDL statement before trying to manipulate +# a temporary table causes row-based replication to break with error 'table +# does not exist' base on myisam engine. +# + +source include/master-slave.inc; +source include/have_binlog_format_row.inc; + +LET $ENGINE_TYPE= MyISAM; +source extra/rpl_tests/rpl_tmp_table_and_DDL.test; + -- cgit v1.2.1