1# 2# Bug#49132 3# This test verifies if executing DDL statement before trying to manipulate 4# a temporary table causes row-based replication to break with error 'table 5# does not exist' base on myisam engine. 6# 7 8source include/have_binlog_format_row.inc; 9source include/master-slave.inc; 10 11LET $ENGINE_TYPE= MyISAM; 12source include/rpl_tmp_table_and_DDL.test; 13sync_slave_with_master; 14 15# 16# BUG #51839 17# The test makes sure the binlog format is not changed 18# after a execution of DDL with a table locked, so that 19# the following DML statements will not cause the slave 20# to stop. 21# 22 23--connection master 24--disable_abort_on_error 25 26CREATE TABLE t1 (a CHAR(30)); 27CREATE TEMPORARY TABLE t2 (b CHAR(60)); 28 29# CREATE FUNCTION when a table is locked. 30LOCK TABLE t1 WRITE; 31CREATE FUNCTION f1 () RETURNS TINYINT RETURN 13; 32INSERT INTO t2 VALUES ("CREATE FUNCTION f1 with table locked"); 33 34UNLOCK TABLE; 35CREATE FUNCTION f2 () RETURNS TINYINT RETURN 13; 36 37# ALTER FUNCTION when a table is locked. 38LOCK TABLE t1 WRITE; 39ALTER FUNCTION f2 SQL SECURITY INVOKER; 40INSERT INTO t2 VALUES ("ALTER FUNCTION f2 with table locked"); 41 42# DROP FUNCTION when a table is locked. 43LOCK TABLE t1 WRITE; 44DROP FUNCTION f2; 45INSERT INTO t2 VALUES ("DROP FUNCTION f2 with table locked"); 46 47 48# CREATE PROCEDURE when a table is locked. 49CREATE PROCEDURE p1() SELECT 1; 50INSERT INTO t2 VALUES ("CREATE PROCEDURE p1 with table locked"); 51 52UNLOCK TABLE; 53CREATE PROCEDURE p2() SELECT 1; 54 55# ALTER PROCEDURE when a table is locked. 56LOCK TABLE t1 WRITE; 57ALTER PROCEDURE p2 SQL SECURITY INVOKER; 58INSERT INTO t2 VALUES ("ALTER PROCEDURE P2 with table locked"); 59 60# DROP PROCEDURE when a table is locked. 61DROP PROCEDURE p2; 62INSERT INTO t2 VALUES ("DROP PROCEDURE p2 with table locked"); 63 64 65# CREATE EVENT when a table is locked. 66CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; 67INSERT INTO t2 VALUES ("CREATE EVENT e1 with table locked"); 68 69UNLOCK TABLE; 70CREATE EVENT e2 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; 71 72# ALTER EVENT when a table is locked. 73LOCK TABLE t1 WRITE; 74ALTER EVENT e2 ON SCHEDULE EVERY 20 HOUR DO SELECT 1; 75INSERT INTO t2 VALUES ("ALTER EVENT e2 with table locked"); 76 77# DROP EVENT when a table is locked. 78DROP EVENT e2; 79INSERT INTO t2 VALUES ("DROP EVENT e2 with table locked"); 80 81 82# CREATE DATABASE when a table is locked. 83CREATE DATABASE mysqltest1; 84INSERT INTO t2 VALUES ("CREATE DATABASE mysqltest1 with table locked"); 85 86UNLOCK TABLE; 87CREATE DATABASE mysqltest2; 88 89# DROP DATABASE when a table is locked. 90LOCK TABLE t1 WRITE; 91DROP DATABASE mysqltest2; 92INSERT INTO t2 VALUES ("DROP DATABASE mysqltest2 with table locked"); 93 94UNLOCK TABLE; 95DROP DATABASE mysqltest2; 96 97# CREATE USER when a table is locked. 98LOCK TABLE t1 WRITE; 99CREATE USER test_1@localhost; 100INSERT INTO t2 VALUES ("CREATE USER test_1@localhost with table locked"); 101 102UNLOCK TABLE; 103CREATE USER test_2@localhost; 104 105# GRANT select on table to user when a table is locked. 106LOCK TABLE t1 WRITE; 107GRANT SELECT ON t1 TO test_2@localhost; 108INSERT INTO t2 VALUES ("GRANT select on table to user with table locked"); 109 110# GRANT all on function to user when a table is locked. 111GRANT ALL ON f2 TO test_2@localhost; 112INSERT INTO t2 VALUES ("GRANT ALL ON f2 TO test_2 with table locked"); 113 114# GRANT all on procdure to user when a table is locked. 115GRANT ALL ON p2 TO test_2@localhost; 116INSERT INTO t2 VALUES ("GRANT ALL ON p2 TO test_2 with table locked"); 117 118# GRANT USAGE ON *.* to user when a table is locked. 119GRANT USAGE ON *.* TO test_2@localhost; 120INSERT INTO t2 VALUES ("GRANT USAGE ON *.* TO test_2 with table locked"); 121 122 123# REVOKE ALL PRIVILEGES on function to user when a table is locked. 124REVOKE ALL PRIVILEGES ON f2 FROM test_2@localhost; 125INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on function to user with table locked"); 126 127# REVOKE ALL PRIVILEGES on procedure to user when a table is locked. 128REVOKE ALL PRIVILEGES ON p2 FROM test_2@localhost; 129INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on procedure to user with table locked"); 130 131# REVOKE ALL PRIVILEGES on table to user when a table is locked. 132REVOKE ALL PRIVILEGES ON t1 FROM test_2@localhost; 133INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on table to user with table locked"); 134 135# REVOKE USAGE ON *.* to user when a table is locked. 136REVOKE USAGE ON *.* FROM test_2@localhost; 137INSERT INTO t2 VALUES ("REVOKE USAGE ON *.* TO test_2 with table locked"); 138 139 140# RENAME USER when a table is locked. 141RENAME USER test_2@localhost TO test_3@localhost; 142INSERT INTO t2 VALUES ("RENAME USER test_2 TO test_3 with table locked"); 143 144UNLOCK TABLE; 145RENAME USER test_2@localhost TO test_3@localhost; 146 147# DROP USER when a table is locked. 148LOCK TABLE t1 WRITE; 149DROP USER test_3@localhost; 150INSERT INTO t2 VALUES ("DROP USER test_3@localhost with table locked"); 151 152UNLOCK TABLE; 153 154# Bug #20439913 CREATE TABLE DB.TABLE LIKE TMPTABLE IS 155# BINLOGGED INCORRECTLY - BREAKS A SLAVE 156CREATE DATABASE db; 157CREATE TABLE db.t1 LIKE t2; 158CREATE TABLE t3 LIKE t2; 159DROP TABLE t3; 160DROP DATABASE db; 161# end of Bug #20439913 test 162 163DROP USER test_3@localhost; 164DROP FUNCTION f2; 165DROP PROCEDURE p2; 166DROP EVENT e2; 167DROP TABLE t1, t2; 168 169--source include/rpl_end.inc 170