Debian Codegraph @

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

raw
1 # Testing table creations for row-based replication.
2
3 --source include/have_binlog_format_row.inc
4 --source include/have_innodb.inc
5 --source include/master-slave.inc
6 connection slave;
7 --source include/have_innodb.inc
8 connection master;
9
10 # Set the default storage engine to different values on master and
11 # slave. We need to stop the slave for the server variable to take
12 # effect, since the variable is only read on start-up.
13 sync_slave_with_master;
14 --disable_query_log
15 set @@default_storage_engine = @@global.default_storage_engine;
16 STOP SLAVE;
17 --source include/wait_for_slave_to_stop.inc
18 SET GLOBAL default_storage_engine=memory;
19 START SLAVE;
20 --source include/wait_for_slave_to_start.inc
21 --enable_query_log
22
23 --source include/rpl_reset.inc
24
25 connection master;
26 CREATE TABLE t1 (a INT);
27 CREATE OR REPLACE TABLE t1 (a INT, b INT);
28 CREATE TABLE t2 (a INT, b INT) ENGINE=Merge;
29 CREATE TABLE t3 (a INT, b INT) CHARSET=utf8;
30 CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8;
31 --source include/show_binlog_events.inc
32 --query_vertical SHOW CREATE TABLE t1
33 --query_vertical SHOW CREATE TABLE t2
34 --query_vertical SHOW CREATE TABLE t3
35 sync_slave_with_master;
36 --query_vertical SHOW CREATE TABLE t1
37 --query_vertical SHOW CREATE TABLE t2
38 --query_vertical SHOW CREATE TABLE t3
39
40 connection master;
41 CREATE TABLE t5 (b INT, c INT) SELECT * FROM t3;
42
43 CREATE TEMPORARY TABLE tt3 (a INT, b INT);
44 INSERT INTO tt3 VALUES (1,2), (2,4), (3,6), (4,2), (5,10), (6,12);
45 CREATE TABLE t6 (b INT, c INT) SELECT * FROM tt3;
46 --query_vertical SHOW CREATE TABLE t5
47 SELECT * FROM t5 ORDER BY a,b,c;
48 --query_vertical SHOW CREATE TABLE t6
49 SELECT * FROM t6 ORDER BY a,b,c;
50 sync_slave_with_master;
51 --query_vertical SHOW CREATE TABLE t5
52 SELECT * FROM t5 ORDER BY a,b,c;
53 --query_vertical SHOW CREATE TABLE t6
54 SELECT * FROM t6 ORDER BY a,b,c;
55
56 --source include/rpl_reset.inc
57
58 connection master;
59 # Test for erroneous constructions
60 --error ER_DUP_ENTRY
61 CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3;
62 # Shouldn't be written to the binary log
63 --source include/show_binlog_events.inc
64
65 # Test that INSERT-SELECT works the same way as for SBR.
66 CREATE TABLE t7 (a INT, b INT UNIQUE);
67 --error ER_DUP_ENTRY
68 INSERT INTO t7 SELECT a,b FROM tt3;
69 SELECT * FROM t7 ORDER BY a,b;
70 # Should be written to the binary log
71 --source include/show_binlog_events.inc
72 sync_slave_with_master;
73 SELECT * FROM t7 ORDER BY a,b;
74
75 --source include/rpl_reset.inc
76
77 connection master;
78 CREATE TEMPORARY TABLE tt4 (a INT, b INT);
79 INSERT INTO tt4 VALUES (4,8), (5,10), (6,12);
80 BEGIN;
81 INSERT INTO t7 SELECT a,b FROM tt4;
82 ROLLBACK;
83 --source include/show_binlog_events.inc
84 SELECT * FROM t7 ORDER BY a,b;
85 sync_slave_with_master;
86 SELECT * FROM t7 ORDER BY a,b;
87
88 --source include/rpl_reset.inc
89
90 connection master;
91 CREATE TABLE t8 LIKE t4;
92 CREATE TABLE t9 LIKE tt4;
93 CREATE TEMPORARY TABLE tt5 LIKE t4;
94 CREATE TEMPORARY TABLE tt6 LIKE tt4;
95 CREATE TEMPORARY TABLE tt7 SELECT 1;
96 --query_vertical SHOW CREATE TABLE t8
97 --query_vertical SHOW CREATE TABLE t9
98 --source include/show_binlog_events.inc
99 sync_slave_with_master;
100 --query_vertical SHOW CREATE TABLE t8
101 --query_vertical SHOW CREATE TABLE t9
102
103 connection master;
104 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9;
105 sync_slave_with_master;
106 # Here we reset the value of the default storage engine
107 STOP SLAVE;
108 --source include/wait_for_slave_to_stop.inc
109 SET GLOBAL default_storage_engine=@@default_storage_engine;
110 START SLAVE;
111 --source include/wait_for_slave_to_start.inc
112 --enable_ps_protocol
113
114 # BUG#22864 (Rollback following CREATE ... SELECT discards 'CREATE
115 # table' from log):
116 --echo ================ BUG#22864 ================
117
118 --source include/rpl_reset.inc
119
120 connection master;
121 SET AUTOCOMMIT=0;
122 CREATE TABLE t1 (a INT);
123 INSERT INTO t1 VALUES (1),(2),(3);
124
125 CREATE TABLE t2 ENGINE=INNODB SELECT * FROM t1;
126 ROLLBACK;
127
128 CREATE TABLE t3 ENGINE=INNODB SELECT * FROM t1;
129 INSERT INTO t3 VALUES (4),(5),(6);
130 ROLLBACK;
131
132 CREATE TABLE t4 ENGINE=INNODB SELECT * FROM t1;
133 INSERT INTO t1 VALUES (4),(5),(6);
134 ROLLBACK;
135
136 SHOW TABLES;
137 SELECT * FROM t1 ORDER BY a;
138 SELECT * FROM t2 ORDER BY a;
139 SELECT * FROM t3 ORDER BY a;
140 SELECT * FROM t4 ORDER BY a;
141 --source include/show_binlog_events.inc
142 sync_slave_with_master;
143 SHOW TABLES;
144 SELECT * FROM t1 ORDER BY a;
145 SELECT * FROM t2 ORDER BY a;
146 SELECT * FROM t3 ORDER BY a;
147 SELECT * FROM t4 ORDER BY a;
148
149 connection master;
150 DROP TABLE IF EXISTS t1,t2,t3,t4;
151 SET AUTOCOMMIT=1;
152 sync_slave_with_master;
153
154 # Some tests with temporary tables
155 --source include/rpl_reset.inc
156
157 connection master;
158 CREATE TABLE t1 (a INT);
159 INSERT INTO t1 VALUES (1),(2),(3);
160
161 CREATE TABLE t2 (a INT) ENGINE=INNODB;
162
163 BEGIN;
164 INSERT INTO t2 SELECT a*a FROM t1;
165 CREATE TEMPORARY TABLE tt1
166 SELECT a+1 AS a
167 FROM t1
168 WHERE a MOD 2 = 1;
169 INSERT INTO t2 SELECT a+2 FROM tt1;
170 COMMIT;
171
172 SELECT * FROM t2 ORDER BY a;
173 --source include/show_binlog_events.inc
174 sync_slave_with_master;
175 SELECT * FROM t2 ORDER BY a;
176
177 connection master;
178 TRUNCATE TABLE t2;
179 sync_slave_with_master;
180
181 --source include/rpl_reset.inc
182
183 connection master;
184 BEGIN;
185 INSERT INTO t2 SELECT a*a FROM t1;
186 CREATE TEMPORARY TABLE tt2
187 SELECT a+1 AS a
188 FROM t1
189 WHERE a MOD 2 = 1;
190 INSERT INTO t2 SELECT a+2 FROM tt2;
191 ROLLBACK;
192
193 SELECT * FROM t2 ORDER BY a;
194 source include/show_binlog_events.inc;
195 sync_slave_with_master;
196 SELECT * FROM t2 ORDER BY a;
197
198 connection master;
199 DROP TABLE t1,t2;
200 sync_slave_with_master;
201
202 #
203 # bug#35762 Failing CREATE-SELECT produces bad binlog in row mode
204 #
205
206 connection master;
207
208 CREATE TABLE t1 (a INT);
209
210 INSERT INTO t1 VALUES (1),(1);
211 --error ER_DUP_ENTRY
212 CREATE TABLE t2 (a INT UNIQUE) ENGINE=INNODB SELECT * FROM t1;
213 INSERT INTO t1 VALUES (2);
214
215 sync_slave_with_master;
216 # connection slave;
217
218 --echo *** the proof of the fix:
219 --echo select must show that the last insert performed on the slave ***
220 SELECT * FROM t1;
221
222 connection master;
223 DROP TABLE t1;
224 sync_slave_with_master;
225
226 #
227 # BUG#34707: Row based replication: slave creates table within wrong database
228 #
229
230 --source include/rpl_reset.inc
231
232 connection master;
233 --disable_warnings
234 DROP DATABASE IF EXISTS mysqltest1;
235 --enable_warnings
236 CREATE DATABASE mysqltest1;
237
238 CREATE TABLE mysqltest1.without_select (f1 BIGINT);
239 CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1;
240 source include/show_binlog_events.inc;
241 sync_slave_with_master;
242
243 connection master;
244 DROP DATABASE mysqltest1;
245 sync_slave_with_master;
246
247 #
248 # BUG#48506: crash in CREATE TABLE <existing_view> IF NOT EXISTS LIKE
249 # <tmp_tbl> with RBL
250 #
251
252 --source include/rpl_reset.inc
253
254 connection master;
255 CREATE TEMPORARY TABLE t7(c1 INT);
256 CREATE TABLE t5(c1 INT);
257 CREATE TABLE t4(c1 INT);
258 CREATE VIEW bug48506_t1 AS SELECT 1;
259 CREATE VIEW bug48506_t2 AS SELECT * FROM t4;
260 CREATE VIEW bug48506_t3 AS SELECT t5.c1 AS A, t4.c1 AS B FROM t5, t4;
261 CREATE TABLE bug48506_t4(c1 INT);
262 --disable_warnings
263 sync_slave_with_master;
264 DROP VIEW bug48506_t1, bug48506_t2, bug48506_t3;
265 DROP TABLE bug48506_t4;
266
267 connection master;
268 CREATE TABLE IF NOT EXISTS bug48506_t1 LIKE t7;
269 CREATE TABLE IF NOT EXISTS bug48506_t2 LIKE t7;
270 CREATE TABLE IF NOT EXISTS bug48506_t3 LIKE t7;
271 CREATE TABLE IF NOT EXISTS bug48506_t4 LIKE t7;
272 --enable_warnings
273 sync_slave_with_master;
274
275 SHOW TABLES LIKE 'bug48506%';
276
277 connection master;
278 DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3;
279 DROP TEMPORARY TABLES t7;
280 DROP TABLES t4, t5;
281 DROP TABLES IF EXISTS bug48506_t4;
282
283
284 --echo *** MDEV-31794: Preserved unsupported table flags break replication
285 CREATE TEMPORARY TABLE t1 (a INT) ENGINE=Aria TRANSACTIONAL=1;
286 # After ALTER to MyISAM, TRANSACTIONAL=1 is a left-over option normally
287 # invalid for MyISAM.
288 ALTER TABLE t1 ENGINE=MyISAM;
289 # Since row-based binlogging is used, the temporary table t1 is not binlogged,
290 # so this CREATE TABLE LIKE is replicated as a plain CREATE TABLE which
291 # specifies invalid TRANSACTIONAL=1 for a MyISAM table.
292 # Test that the slave will still allow the create table.
293 CREATE TABLE t2 LIKE t1;
294 --sync_slave_with_master
295 SHOW CREATE TABLE t2;
296
297 --connection master
298 DROP TEMPORARY TABLE t1;
299 DROP TABLE t2;
300
301
302 --source include/rpl_end.inc
303
304 --echo end of the tests