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/not_group_replication_plugin.inc
9 source include/have_binlog_format_row.inc;
10 source include/set_privilege_checks_user_as_system_user.inc;
11 source include/master-slave.inc;
12
13 #
14 # BUG #51839
15 # The test makes sure the binlog format is not changed
16 # after a execution of DDL with a table locked, so that
17 # the following DML statements will not cause the slave
18 # to stop.
19 #
20
21 --connection master
22 --disable_abort_on_error
23
24 CREATE TABLE t1 (a CHAR(30));
25 CREATE TEMPORARY TABLE t2 (b CHAR(60));
26
27 # CREATE FUNCTION when a table is locked.
28 LOCK TABLE t1 WRITE;
29 CREATE FUNCTION f1 () RETURNS TINYINT RETURN 13;
30 INSERT INTO t2 VALUES ("CREATE FUNCTION f1 with table locked");
31
32 UNLOCK TABLE;
33 CREATE FUNCTION f2 () RETURNS TINYINT RETURN 13;
34
35 # ALTER FUNCTION when a table is locked.
36 LOCK TABLE t1 WRITE;
37 ALTER FUNCTION f2 SQL SECURITY INVOKER;
38 INSERT INTO t2 VALUES ("ALTER FUNCTION f2 with table locked");
39
40 # DROP FUNCTION when a table is locked.
41 LOCK TABLE t1 WRITE;
42 DROP FUNCTION f2;
43 INSERT INTO t2 VALUES ("DROP FUNCTION f2 with table locked");
44
45
46 # CREATE PROCEDURE when a table is locked.
47 CREATE PROCEDURE p1() SELECT 1;
48 INSERT INTO t2 VALUES ("CREATE PROCEDURE p1 with table locked");
49
50 UNLOCK TABLE;
51 CREATE PROCEDURE p2() SELECT 1;
52
53 # ALTER PROCEDURE when a table is locked.
54 LOCK TABLE t1 WRITE;
55 ALTER PROCEDURE p2 SQL SECURITY INVOKER;
56 INSERT INTO t2 VALUES ("ALTER PROCEDURE P2 with table locked");
57
58 # DROP PROCEDURE when a table is locked.
59 DROP PROCEDURE p2;
60 INSERT INTO t2 VALUES ("DROP PROCEDURE p2 with table locked");
61
62
63 # CREATE EVENT when a table is locked.
64 CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1;
65 INSERT INTO t2 VALUES ("CREATE EVENT e1 with table locked");
66
67 UNLOCK TABLE;
68 CREATE EVENT e2 ON SCHEDULE EVERY 10 HOUR DO SELECT 1;
69
70 # ALTER EVENT when a table is locked.
71 LOCK TABLE t1 WRITE;
72 ALTER EVENT e2 ON SCHEDULE EVERY 20 HOUR DO SELECT 1;
73 INSERT INTO t2 VALUES ("ALTER EVENT e2 with table locked");
74
75 # DROP EVENT when a table is locked.
76 DROP EVENT e2;
77 INSERT INTO t2 VALUES ("DROP EVENT e2 with table locked");
78
79
80 # CREATE DATABASE when a table is locked.
81 CREATE DATABASE mysqltest1;
82 INSERT INTO t2 VALUES ("CREATE DATABASE mysqltest1 with table locked");
83
84 UNLOCK TABLE;
85 CREATE DATABASE mysqltest2;
86
87 # DROP DATABASE when a table is locked.
88 LOCK TABLE t1 WRITE;
89 DROP DATABASE mysqltest2;
90 INSERT INTO t2 VALUES ("DROP DATABASE mysqltest2 with table locked");
91
92 UNLOCK TABLE;
93 DROP DATABASE mysqltest2;
94
95 # CREATE USER when a table is locked.
96 LOCK TABLE t1 WRITE;
97 CREATE USER test_1@localhost;
98 INSERT INTO t2 VALUES ("CREATE USER test_1@localhost with table locked");
99
100 UNLOCK TABLE;
101 CREATE USER test_2@localhost;
102
103 # GRANT select on table to user when a table is locked.
104 LOCK TABLE t1 WRITE;
105 GRANT SELECT ON t1 TO test_2@localhost;
106 INSERT INTO t2 VALUES ("GRANT select on table to user with table locked");
107
108 # GRANT all on function to user when a table is locked.
109 GRANT ALL ON f2 TO test_2@localhost;
110 INSERT INTO t2 VALUES ("GRANT ALL ON f2 TO test_2 with table locked");
111
112 # GRANT all on procdure to user when a table is locked.
113 GRANT ALL ON p2 TO test_2@localhost;
114 INSERT INTO t2 VALUES ("GRANT ALL ON p2 TO test_2 with table locked");
115
116 # GRANT USAGE ON *.* to user when a table is locked.
117 GRANT USAGE ON *.* TO test_2@localhost;
118 INSERT INTO t2 VALUES ("GRANT USAGE ON *.* TO test_2 with table locked");
119
120
121 # REVOKE ALL PRIVILEGES on function to user when a table is locked.
122 REVOKE ALL PRIVILEGES ON f2 FROM test_2@localhost;
123 INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on function to user with table locked");
124
125 # REVOKE ALL PRIVILEGES on procedure to user when a table is locked.
126 REVOKE ALL PRIVILEGES ON p2 FROM test_2@localhost;
127 INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on procedure to user with table locked");
128
129 # REVOKE ALL PRIVILEGES on table to user when a table is locked.
130 REVOKE ALL PRIVILEGES ON t1 FROM test_2@localhost;
131 INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on table to user with table locked");
132
133 # REVOKE USAGE ON *.* to user when a table is locked.
134 REVOKE USAGE ON *.* FROM test_2@localhost;
135 INSERT INTO t2 VALUES ("REVOKE USAGE ON *.* TO test_2 with table locked");
136
137
138 # RENAME USER when a table is locked.
139 RENAME USER test_2@localhost TO test_3@localhost;
140 INSERT INTO t2 VALUES ("RENAME USER test_2 TO test_3 with table locked");
141
142 UNLOCK TABLE;
143 RENAME USER test_2@localhost TO test_3@localhost;
144
145 # DROP USER when a table is locked.
146 LOCK TABLE t1 WRITE;
147 DROP USER test_3@localhost;
148 INSERT INTO t2 VALUES ("DROP USER test_3@localhost with table locked");
149
150 UNLOCK TABLE;
151
152 # Bug #20439913 CREATE TABLE DB.TABLE LIKE TMPTABLE IS
153 # BINLOGGED INCORRECTLY - BREAKS A SLAVE
154 CREATE DATABASE db;
155 CREATE TABLE db.t1 LIKE t2;
156 CREATE TABLE t3 LIKE t2;
157 DROP TABLE t3;
158 DROP DATABASE db;
159 # end of Bug #20439913 test
160
161 DROP USER test_3@localhost;
162 DROP FUNCTION f2;
163 DROP PROCEDURE p2;
164 DROP EVENT e2;
165 DROP TABLE t1, t2;
166
167 --sync_slave_with_master
168 #
169 # BUG#20574550
170 # CREATE TABLE LIKE <TEMP_TABLE> does not preserve original table storage
171 # engine when using row based replication
172 #
173 --connection master
174
175 # Define temp_t1 storage engines
176 --let $engine_temp_t1= InnoDB
177
178 # Create the temporary tables
179 --eval CREATE TEMPORARY TABLE temp_t1 (c1 INT) ENGINE=$engine_temp_t1
180
181 # Create t1 based on temporary tables
182 CREATE TABLE t1 LIKE temp_t1;
183 --sync_slave_with_master
184
185 # On master
186 --connection master
187 # Assert that t1 have the same storage engine as temp_t1
188 --let $engine_t1= query_get_value(SHOW TABLE STATUS WHERE Name='t1', Engine, 1)
189 --let $assert_cond= "$engine_t1" = "$engine_temp_t1"
190 --let $assert_text= "t1 on master and temp_t1 have the same storage engine"
191 --source include/assert.inc
192
193
194 # On slave
195 --connection slave
196 # Assert that t1 have the same storage engine as temp_t1
197 --let $engine_t1= query_get_value(SHOW TABLE STATUS WHERE Name='t1', Engine, 1)
198 --let $assert_cond= "$engine_t1" = "$engine_temp_t1"
199 --let $assert_text= "t1 on slave and temp_t1 have the same storage engine"
200 --source include/assert.inc
201
202 # Cleanup
203 --connection master
204 DROP TEMPORARY TABLE temp_t1;
205 DROP TABLE t1;
206 --source include/rpl_end.inc