Debian Codegraph @

mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test

raw
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
8 source include/have_binlog_format_row.inc;
9 source include/master-slave.inc;
10
11 LET $ENGINE_TYPE= MyISAM;
12 source include/rpl_tmp_table_and_DDL.test;
13 sync_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
26 CREATE TABLE t1 (a CHAR(30));
27 CREATE TEMPORARY TABLE t2 (b CHAR(60));
28
29 # CREATE FUNCTION when a table is locked.
30 LOCK TABLE t1 WRITE;
31 CREATE FUNCTION f1 () RETURNS TINYINT RETURN 13;
32 INSERT INTO t2 VALUES ("CREATE FUNCTION f1 with table locked");
33
34 UNLOCK TABLE;
35 CREATE FUNCTION f2 () RETURNS TINYINT RETURN 13;
36
37 # ALTER FUNCTION when a table is locked.
38 LOCK TABLE t1 WRITE;
39 ALTER FUNCTION f2 SQL SECURITY INVOKER;
40 INSERT INTO t2 VALUES ("ALTER FUNCTION f2 with table locked");
41
42 # DROP FUNCTION when a table is locked.
43 LOCK TABLE t1 WRITE;
44 DROP FUNCTION f2;
45 INSERT INTO t2 VALUES ("DROP FUNCTION f2 with table locked");
46
47
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");
51
52 UNLOCK TABLE;
53 CREATE PROCEDURE p2() SELECT 1;
54
55 # ALTER PROCEDURE when a table is locked.
56 LOCK TABLE t1 WRITE;
57 ALTER PROCEDURE p2 SQL SECURITY INVOKER;
58 INSERT INTO t2 VALUES ("ALTER PROCEDURE P2 with table locked");
59
60 # DROP PROCEDURE when a table is locked.
61 DROP PROCEDURE p2;
62 INSERT INTO t2 VALUES ("DROP PROCEDURE p2 with table locked");
63
64
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");
68
69 UNLOCK TABLE;
70 CREATE EVENT e2 ON SCHEDULE EVERY 10 HOUR DO SELECT 1;
71
72 # ALTER EVENT when a table is locked.
73 LOCK TABLE t1 WRITE;
74 ALTER EVENT e2 ON SCHEDULE EVERY 20 HOUR DO SELECT 1;
75 INSERT INTO t2 VALUES ("ALTER EVENT e2 with table locked");
76
77 # DROP EVENT when a table is locked.
78 DROP EVENT e2;
79 INSERT INTO t2 VALUES ("DROP EVENT e2 with table locked");
80
81
82 # CREATE DATABASE when a table is locked.
83 CREATE DATABASE mysqltest1;
84 INSERT INTO t2 VALUES ("CREATE DATABASE mysqltest1 with table locked");
85
86 UNLOCK TABLE;
87 CREATE DATABASE mysqltest2;
88
89 # DROP DATABASE when a table is locked.
90 LOCK TABLE t1 WRITE;
91 DROP DATABASE mysqltest2;
92 INSERT INTO t2 VALUES ("DROP DATABASE mysqltest2 with table locked");
93
94 UNLOCK TABLE;
95 DROP DATABASE mysqltest2;
96
97 # CREATE USER when a table is locked.
98 LOCK TABLE t1 WRITE;
99 CREATE USER test_1@localhost;
100 INSERT INTO t2 VALUES ("CREATE USER test_1@localhost with table locked");
101
102 UNLOCK TABLE;
103 CREATE USER test_2@localhost;
104
105 # GRANT select on table to user when a table is locked.
106 LOCK TABLE t1 WRITE;
107 GRANT SELECT ON t1 TO test_2@localhost;
108 INSERT 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.
111 GRANT ALL ON f2 TO test_2@localhost;
112 INSERT 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.
115 GRANT ALL ON p2 TO test_2@localhost;
116 INSERT 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.
119 GRANT USAGE ON *.* TO test_2@localhost;
120 INSERT 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.
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");
126
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");
130
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");
134
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");
138
139
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");
143
144 UNLOCK TABLE;
145 RENAME USER test_2@localhost TO test_3@localhost;
146
147 # DROP USER when a table is locked.
148 LOCK TABLE t1 WRITE;
149 DROP USER test_3@localhost;
150 INSERT INTO t2 VALUES ("DROP USER test_3@localhost with table locked");
151
152 UNLOCK TABLE;
153
154 # Bug #20439913 CREATE TABLE DB.TABLE LIKE TMPTABLE IS
155 # BINLOGGED INCORRECTLY - BREAKS A SLAVE
156 CREATE DATABASE db;
157 CREATE TABLE db.t1 LIKE t2;
158 CREATE TABLE t3 LIKE t2;
159 DROP TABLE t3;
160 DROP DATABASE db;
161 # end of Bug #20439913 test
162
163 DROP USER test_3@localhost;
164 DROP FUNCTION f2;
165 DROP PROCEDURE p2;
166 DROP EVENT e2;
167 DROP TABLE t1, t2;
168
169 --source include/rpl_end.inc