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.
8
source include/master-slave.inc;
9
source include/have_binlog_format_row.inc;
11
LET $ENGINE_TYPE= MyISAM;
12
source extra/rpl_tests/rpl_tmp_table_and_DDL.test;
13
sync_slave_with_master;
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
24
--disable_abort_on_error
26
CREATE TABLE t1 (a CHAR(30));
27
CREATE TEMPORARY TABLE t2 (b CHAR(60));
29
# CREATE FUNCTION when a table is locked.
31
CREATE FUNCTION f1 () RETURNS TINYINT RETURN 13;
32
INSERT INTO t2 VALUES ("CREATE FUNCTION f1 with table locked");
35
CREATE FUNCTION f2 () RETURNS TINYINT RETURN 13;
37
# ALTER FUNCTION when a table is locked.
39
ALTER FUNCTION f2 SQL SECURITY INVOKER;
40
INSERT INTO t2 VALUES ("ALTER FUNCTION f2 with table locked");
42
# DROP FUNCTION when a table is locked.
45
INSERT INTO t2 VALUES ("DROP FUNCTION f2 with table locked");
48
# CREATE PROCEDURE when a table is locked.
49
CREATE PROCEDURE p1() SELECT 1;
50
INSERT INTO t2 VALUES ("CREATE PROCEDURE p1 with table locked");
53
CREATE PROCEDURE p2() SELECT 1;
55
# ALTER PROCEDURE when a table is locked.
57
ALTER PROCEDURE p2 SQL SECURITY INVOKER;
58
INSERT INTO t2 VALUES ("ALTER PROCEDURE P2 with table locked");
60
# DROP PROCEDURE when a table is locked.
62
INSERT INTO t2 VALUES ("DROP PROCEDURE p2 with table locked");
65
# CREATE EVENT when a table is locked.
66
CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1;
67
INSERT INTO t2 VALUES ("CREATE EVENT e1 with table locked");
70
CREATE EVENT e2 ON SCHEDULE EVERY 10 HOUR DO SELECT 1;
72
# ALTER EVENT when a table is locked.
74
ALTER EVENT e2 ON SCHEDULE EVERY 20 HOUR DO SELECT 1;
75
INSERT INTO t2 VALUES ("ALTER EVENT e2 with table locked");
77
# DROP EVENT when a table is locked.
79
INSERT INTO t2 VALUES ("DROP EVENT e2 with table locked");
82
# CREATE DATABASE when a table is locked.
83
CREATE DATABASE mysqltest1;
84
INSERT INTO t2 VALUES ("CREATE DATABASE mysqltest1 with table locked");
87
CREATE DATABASE mysqltest2;
89
# DROP DATABASE when a table is locked.
91
DROP DATABASE mysqltest2;
92
INSERT INTO t2 VALUES ("DROP DATABASE mysqltest2 with table locked");
95
DROP DATABASE mysqltest2;
97
# CREATE USER when a table is locked.
99
CREATE USER test_1@localhost;
100
INSERT INTO t2 VALUES ("CREATE USER test_1@localhost with table locked");
103
CREATE USER test_2@localhost;
105
# GRANT select on table to user when a table is locked.
107
GRANT SELECT ON t1 TO test_2@localhost;
108
INSERT INTO t2 VALUES ("GRANT select on table to user with table locked");
110
# GRANT all on function to user when a table is locked.
111
GRANT ALL ON f2 TO test_2@localhost;
112
INSERT INTO t2 VALUES ("GRANT ALL ON f2 TO test_2 with table locked");
114
# GRANT all on procdure to user when a table is locked.
115
GRANT ALL ON p2 TO test_2@localhost;
116
INSERT INTO t2 VALUES ("GRANT ALL ON p2 TO test_2 with table locked");
118
# GRANT USAGE ON *.* to user when a table is locked.
119
GRANT USAGE ON *.* TO test_2@localhost;
120
INSERT INTO t2 VALUES ("GRANT USAGE ON *.* TO test_2 with table locked");
123
# REVOKE ALL PRIVILEGES on function to user when a table is locked.
124
REVOKE ALL PRIVILEGES ON f2 FROM test_2@localhost;
125
INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on function to user with table locked");
127
# REVOKE ALL PRIVILEGES on procedure to user when a table is locked.
128
REVOKE ALL PRIVILEGES ON p2 FROM test_2@localhost;
129
INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on procedure to user with table locked");
131
# REVOKE ALL PRIVILEGES on table to user when a table is locked.
132
REVOKE ALL PRIVILEGES ON t1 FROM test_2@localhost;
133
INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on table to user with table locked");
135
# REVOKE USAGE ON *.* to user when a table is locked.
136
REVOKE USAGE ON *.* FROM test_2@localhost;
137
INSERT INTO t2 VALUES ("REVOKE USAGE ON *.* TO test_2 with table locked");
140
# RENAME USER when a table is locked.
141
RENAME USER test_2@localhost TO test_3@localhost;
142
INSERT INTO t2 VALUES ("RENAME USER test_2 TO test_3 with table locked");
145
RENAME USER test_2@localhost TO test_3@localhost;
147
# DROP USER when a table is locked.
149
DROP USER test_3@localhost;
150
INSERT INTO t2 VALUES ("DROP USER test_3@localhost with table locked");
153
DROP USER test_3@localhost;
159
--source include/rpl_end.inc