Debian Codegraph @

mysql-test/suite/rpl/r/rpl_row_create_table.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection slave;
4 connection master;
5 connection slave;
6 include/wait_for_slave_to_stop.inc
7 include/wait_for_slave_to_start.inc
8 include/rpl_reset.inc
9 connection master;
10 CREATE TABLE t1 (a INT);
11 CREATE OR REPLACE TABLE t1 (a INT, b INT);
12 CREATE TABLE t2 (a INT, b INT) ENGINE=Merge;
13 CREATE TABLE t3 (a INT, b INT) CHARSET=utf8;
14 CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8;
15 include/show_binlog_events.inc
16 Log_name Pos Event_type Server_id End_log_pos Info
17 master-bin.000001 # Gtid # # GTID #-#-#
18 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT)
19 master-bin.000001 # Gtid # # GTID #-#-#
20 master-bin.000001 # Query # # use `test`; CREATE OR REPLACE TABLE t1 (a INT, b INT)
21 master-bin.000001 # Gtid # # GTID #-#-#
22 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a INT, b INT) ENGINE=Merge
23 master-bin.000001 # Gtid # # GTID #-#-#
24 master-bin.000001 # Query # # use `test`; CREATE TABLE t3 (a INT, b INT) CHARSET=utf8
25 master-bin.000001 # Gtid # # GTID #-#-#
26 master-bin.000001 # Query # # use `test`; CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8
27 SHOW CREATE TABLE t1;
28 Table t1
29 Create Table CREATE TABLE `t1` (
30 `a` int(11) DEFAULT NULL,
31 `b` int(11) DEFAULT NULL
32 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
33 SHOW CREATE TABLE t2;
34 Table t2
35 Create Table CREATE TABLE `t2` (
36 `a` int(11) DEFAULT NULL,
37 `b` int(11) DEFAULT NULL
38 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
39 SHOW CREATE TABLE t3;
40 Table t3
41 Create Table CREATE TABLE `t3` (
42 `a` int(11) DEFAULT NULL,
43 `b` int(11) DEFAULT NULL
44 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
45 connection slave;
46 SHOW CREATE TABLE t1;
47 Table t1
48 Create Table CREATE TABLE `t1` (
49 `a` int(11) DEFAULT NULL,
50 `b` int(11) DEFAULT NULL
51 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
52 SHOW CREATE TABLE t2;
53 Table t2
54 Create Table CREATE TABLE `t2` (
55 `a` int(11) DEFAULT NULL,
56 `b` int(11) DEFAULT NULL
57 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
58 SHOW CREATE TABLE t3;
59 Table t3
60 Create Table CREATE TABLE `t3` (
61 `a` int(11) DEFAULT NULL,
62 `b` int(11) DEFAULT NULL
63 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
64 connection master;
65 CREATE TABLE t5 (b INT, c INT) SELECT * FROM t3;
66 CREATE TEMPORARY TABLE tt3 (a INT, b INT);
67 INSERT INTO tt3 VALUES (1,2), (2,4), (3,6), (4,2), (5,10), (6,12);
68 CREATE TABLE t6 (b INT, c INT) SELECT * FROM tt3;
69 SHOW CREATE TABLE t5;
70 Table t5
71 Create Table CREATE TABLE `t5` (
72 `c` int(11) DEFAULT NULL,
73 `a` int(11) DEFAULT NULL,
74 `b` int(11) DEFAULT NULL
75 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
76 SELECT * FROM t5 ORDER BY a,b,c;
77 c a b
78 SHOW CREATE TABLE t6;
79 Table t6
80 Create Table CREATE TABLE `t6` (
81 `c` int(11) DEFAULT NULL,
82 `a` int(11) DEFAULT NULL,
83 `b` int(11) DEFAULT NULL
84 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
85 SELECT * FROM t6 ORDER BY a,b,c;
86 c a b
87 NULL 1 2
88 NULL 2 4
89 NULL 3 6
90 NULL 4 2
91 NULL 5 10
92 NULL 6 12
93 connection slave;
94 SHOW CREATE TABLE t5;
95 Table t5
96 Create Table CREATE TABLE `t5` (
97 `c` int(11) DEFAULT NULL,
98 `a` int(11) DEFAULT NULL,
99 `b` int(11) DEFAULT NULL
100 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
101 SELECT * FROM t5 ORDER BY a,b,c;
102 c a b
103 SHOW CREATE TABLE t6;
104 Table t6
105 Create Table CREATE TABLE `t6` (
106 `c` int(11) DEFAULT NULL,
107 `a` int(11) DEFAULT NULL,
108 `b` int(11) DEFAULT NULL
109 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
110 SELECT * FROM t6 ORDER BY a,b,c;
111 c a b
112 NULL 1 2
113 NULL 2 4
114 NULL 3 6
115 NULL 4 2
116 NULL 5 10
117 NULL 6 12
118 include/rpl_reset.inc
119 connection master;
120 CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3;
121 ERROR 23000: Duplicate entry '2' for key 'b'
122 include/show_binlog_events.inc
123 CREATE TABLE t7 (a INT, b INT UNIQUE);
124 INSERT INTO t7 SELECT a,b FROM tt3;
125 ERROR 23000: Duplicate entry '2' for key 'b'
126 SELECT * FROM t7 ORDER BY a,b;
127 a b
128 1 2
129 2 4
130 3 6
131 include/show_binlog_events.inc
132 Log_name Pos Event_type Server_id End_log_pos Info
133 master-bin.000001 # Gtid # # GTID #-#-#
134 master-bin.000001 # Query # # use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE)
135 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
136 master-bin.000001 # Annotate_rows # # INSERT INTO t7 SELECT a,b FROM tt3
137 master-bin.000001 # Table_map # # table_id: # (test.t7)
138 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
139 master-bin.000001 # Query # # COMMIT
140 connection slave;
141 SELECT * FROM t7 ORDER BY a,b;
142 a b
143 1 2
144 2 4
145 3 6
146 include/rpl_reset.inc
147 connection master;
148 CREATE TEMPORARY TABLE tt4 (a INT, b INT);
149 INSERT INTO tt4 VALUES (4,8), (5,10), (6,12);
150 BEGIN;
151 INSERT INTO t7 SELECT a,b FROM tt4;
152 ROLLBACK;
153 Warnings:
154 Warning 1196 Some non-transactional changed tables couldn't be rolled back
155 include/show_binlog_events.inc
156 Log_name Pos Event_type Server_id End_log_pos Info
157 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
158 master-bin.000001 # Annotate_rows # # INSERT INTO t7 SELECT a,b FROM tt4
159 master-bin.000001 # Table_map # # table_id: # (test.t7)
160 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
161 master-bin.000001 # Query # # COMMIT
162 SELECT * FROM t7 ORDER BY a,b;
163 a b
164 1 2
165 2 4
166 3 6
167 4 8
168 5 10
169 6 12
170 connection slave;
171 SELECT * FROM t7 ORDER BY a,b;
172 a b
173 1 2
174 2 4
175 3 6
176 4 8
177 5 10
178 6 12
179 include/rpl_reset.inc
180 connection master;
181 CREATE TABLE t8 LIKE t4;
182 CREATE TABLE t9 LIKE tt4;
183 CREATE TEMPORARY TABLE tt5 LIKE t4;
184 CREATE TEMPORARY TABLE tt6 LIKE tt4;
185 CREATE TEMPORARY TABLE tt7 SELECT 1;
186 SHOW CREATE TABLE t8;
187 Table t8
188 Create Table CREATE TABLE `t8` (
189 `a` int(11) DEFAULT NULL,
190 `b` int(11) DEFAULT NULL
191 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
192 SHOW CREATE TABLE t9;
193 Table t9
194 Create Table CREATE TABLE `t9` (
195 `a` int(11) DEFAULT NULL,
196 `b` int(11) DEFAULT NULL
197 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
198 include/show_binlog_events.inc
199 Log_name Pos Event_type Server_id End_log_pos Info
200 master-bin.000001 # Gtid # # GTID #-#-#
201 master-bin.000001 # Query # # use `test`; CREATE TABLE t8 LIKE t4
202 master-bin.000001 # Gtid # # GTID #-#-#
203 master-bin.000001 # Query # # use `test`; CREATE TABLE `t9` (
204 `a` int(11) DEFAULT NULL,
205 `b` int(11) DEFAULT NULL
206 ) ENGINE=MyISAM
207 connection slave;
208 SHOW CREATE TABLE t8;
209 Table t8
210 Create Table CREATE TABLE `t8` (
211 `a` int(11) DEFAULT NULL,
212 `b` int(11) DEFAULT NULL
213 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
214 SHOW CREATE TABLE t9;
215 Table t9
216 Create Table CREATE TABLE `t9` (
217 `a` int(11) DEFAULT NULL,
218 `b` int(11) DEFAULT NULL
219 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
220 connection master;
221 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9;
222 connection slave;
223 STOP SLAVE;
224 include/wait_for_slave_to_stop.inc
225 SET GLOBAL default_storage_engine=@@default_storage_engine;
226 START SLAVE;
227 include/wait_for_slave_to_start.inc
228 ================ BUG#22864 ================
229 include/rpl_reset.inc
230 connection master;
231 SET AUTOCOMMIT=0;
232 CREATE TABLE t1 (a INT);
233 INSERT INTO t1 VALUES (1),(2),(3);
234 CREATE TABLE t2 ENGINE=INNODB SELECT * FROM t1;
235 ROLLBACK;
236 CREATE TABLE t3 ENGINE=INNODB SELECT * FROM t1;
237 INSERT INTO t3 VALUES (4),(5),(6);
238 ROLLBACK;
239 CREATE TABLE t4 ENGINE=INNODB SELECT * FROM t1;
240 INSERT INTO t1 VALUES (4),(5),(6);
241 ROLLBACK;
242 Warnings:
243 Warning 1196 Some non-transactional changed tables couldn't be rolled back
244 SHOW TABLES;
245 Tables_in_test
246 tt7
247 tt6
248 tt5
249 tt4
250 tt3
251 t1
252 t2
253 t3
254 t4
255 SELECT * FROM t1 ORDER BY a;
256 a
257 1
258 2
259 3
260 4
261 5
262 6
263 SELECT * FROM t2 ORDER BY a;
264 a
265 1
266 2
267 3
268 SELECT * FROM t3 ORDER BY a;
269 a
270 1
271 2
272 3
273 SELECT * FROM t4 ORDER BY a;
274 a
275 1
276 2
277 3
278 include/show_binlog_events.inc
279 Log_name Pos Event_type Server_id End_log_pos Info
280 master-bin.000001 # Gtid # # GTID #-#-#
281 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT)
282 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
283 master-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (1),(2),(3)
284 master-bin.000001 # Table_map # # table_id: # (test.t1)
285 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
286 master-bin.000001 # Query # # COMMIT
287 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
288 master-bin.000001 # Query # # use `test`; CREATE TABLE `t2` (
289 `a` int(11) DEFAULT NULL
290 ) ENGINE=InnoDB
291 master-bin.000001 # Annotate_rows # # CREATE TABLE t2 ENGINE=INNODB SELECT * FROM t1
292 master-bin.000001 # Table_map # # table_id: # (test.t2)
293 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
294 master-bin.000001 # Xid # # COMMIT /* XID */
295 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
296 master-bin.000001 # Query # # use `test`; CREATE TABLE `t3` (
297 `a` int(11) DEFAULT NULL
298 ) ENGINE=InnoDB
299 master-bin.000001 # Annotate_rows # # CREATE TABLE t3 ENGINE=INNODB SELECT * FROM t1
300 master-bin.000001 # Table_map # # table_id: # (test.t3)
301 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
302 master-bin.000001 # Xid # # COMMIT /* XID */
303 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
304 master-bin.000001 # Query # # use `test`; CREATE TABLE `t4` (
305 `a` int(11) DEFAULT NULL
306 ) ENGINE=InnoDB
307 master-bin.000001 # Annotate_rows # # CREATE TABLE t4 ENGINE=INNODB SELECT * FROM t1
308 master-bin.000001 # Table_map # # table_id: # (test.t4)
309 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
310 master-bin.000001 # Xid # # COMMIT /* XID */
311 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
312 master-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (4),(5),(6)
313 master-bin.000001 # Table_map # # table_id: # (test.t1)
314 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
315 master-bin.000001 # Query # # COMMIT
316 connection slave;
317 SHOW TABLES;
318 Tables_in_test
319 t1
320 t2
321 t3
322 t4
323 SELECT * FROM t1 ORDER BY a;
324 a
325 1
326 2
327 3
328 4
329 5
330 6
331 SELECT * FROM t2 ORDER BY a;
332 a
333 1
334 2
335 3
336 SELECT * FROM t3 ORDER BY a;
337 a
338 1
339 2
340 3
341 SELECT * FROM t4 ORDER BY a;
342 a
343 1
344 2
345 3
346 connection master;
347 DROP TABLE IF EXISTS t1,t2,t3,t4;
348 SET AUTOCOMMIT=1;
349 connection slave;
350 include/rpl_reset.inc
351 connection master;
352 CREATE TABLE t1 (a INT);
353 INSERT INTO t1 VALUES (1),(2),(3);
354 CREATE TABLE t2 (a INT) ENGINE=INNODB;
355 BEGIN;
356 INSERT INTO t2 SELECT a*a FROM t1;
357 CREATE TEMPORARY TABLE tt1
358 SELECT a+1 AS a
359 FROM t1
360 WHERE a MOD 2 = 1;
361 INSERT INTO t2 SELECT a+2 FROM tt1;
362 COMMIT;
363 SELECT * FROM t2 ORDER BY a;
364 a
365 1
366 4
367 4
368 6
369 9
370 include/show_binlog_events.inc
371 Log_name Pos Event_type Server_id End_log_pos Info
372 master-bin.000001 # Gtid # # GTID #-#-#
373 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT)
374 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
375 master-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (1),(2),(3)
376 master-bin.000001 # Table_map # # table_id: # (test.t1)
377 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
378 master-bin.000001 # Query # # COMMIT
379 master-bin.000001 # Gtid # # GTID #-#-#
380 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a INT) ENGINE=INNODB
381 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
382 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT a*a FROM t1
383 master-bin.000001 # Table_map # # table_id: # (test.t2)
384 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
385 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT a+2 FROM tt1
386 master-bin.000001 # Table_map # # table_id: # (test.t2)
387 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
388 master-bin.000001 # Xid # # COMMIT /* XID */
389 connection slave;
390 SELECT * FROM t2 ORDER BY a;
391 a
392 1
393 4
394 4
395 6
396 9
397 connection master;
398 TRUNCATE TABLE t2;
399 connection slave;
400 include/rpl_reset.inc
401 connection master;
402 BEGIN;
403 INSERT INTO t2 SELECT a*a FROM t1;
404 CREATE TEMPORARY TABLE tt2
405 SELECT a+1 AS a
406 FROM t1
407 WHERE a MOD 2 = 1;
408 INSERT INTO t2 SELECT a+2 FROM tt2;
409 ROLLBACK;
410 Warnings:
411 Warning 1196 Some non-transactional changed tables couldn't be rolled back
412 SELECT * FROM t2 ORDER BY a;
413 a
414 include/show_binlog_events.inc
415 Log_name Pos Event_type Server_id End_log_pos Info
416 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
417 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT a*a FROM t1
418 master-bin.000001 # Table_map # # table_id: # (test.t2)
419 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
420 master-bin.000001 # Annotate_rows # # INSERT INTO t2 SELECT a+2 FROM tt2
421 master-bin.000001 # Table_map # # table_id: # (test.t2)
422 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
423 master-bin.000001 # Query # # ROLLBACK
424 connection slave;
425 SELECT * FROM t2 ORDER BY a;
426 a
427 connection master;
428 DROP TABLE t1,t2;
429 connection slave;
430 connection master;
431 CREATE TABLE t1 (a INT);
432 INSERT INTO t1 VALUES (1),(1);
433 CREATE TABLE t2 (a INT UNIQUE) ENGINE=INNODB SELECT * FROM t1;
434 ERROR 23000: Duplicate entry '1' for key 'a'
435 INSERT INTO t1 VALUES (2);
436 connection slave;
437 *** the proof of the fix:
438 select must show that the last insert performed on the slave ***
439 SELECT * FROM t1;
440 a
441 1
442 1
443 2
444 connection master;
445 DROP TABLE t1;
446 connection slave;
447 include/rpl_reset.inc
448 connection master;
449 DROP DATABASE IF EXISTS mysqltest1;
450 CREATE DATABASE mysqltest1;
451 CREATE TABLE mysqltest1.without_select (f1 BIGINT);
452 CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1;
453 include/show_binlog_events.inc
454 Log_name Pos Event_type Server_id End_log_pos Info
455 master-bin.000001 # Gtid # # GTID #-#-#
456 master-bin.000001 # Query # # DROP DATABASE IF EXISTS mysqltest1
457 master-bin.000001 # Gtid # # GTID #-#-#
458 master-bin.000001 # Query # # CREATE DATABASE mysqltest1
459 master-bin.000001 # Gtid # # GTID #-#-#
460 master-bin.000001 # Query # # use `test`; CREATE TABLE mysqltest1.without_select (f1 BIGINT)
461 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
462 master-bin.000001 # Query # # use `test`; CREATE TABLE `mysqltest1`.`with_select` (
463 `f1` int(1) NOT NULL
464 )
465 master-bin.000001 # Annotate_rows # # CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1
466 master-bin.000001 # Table_map # # table_id: # (mysqltest1.with_select)
467 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
468 master-bin.000001 # Query # # COMMIT
469 connection slave;
470 connection master;
471 DROP DATABASE mysqltest1;
472 connection slave;
473 include/rpl_reset.inc
474 connection master;
475 CREATE TEMPORARY TABLE t7(c1 INT);
476 CREATE TABLE t5(c1 INT);
477 CREATE TABLE t4(c1 INT);
478 CREATE VIEW bug48506_t1 AS SELECT 1;
479 CREATE VIEW bug48506_t2 AS SELECT * FROM t4;
480 CREATE VIEW bug48506_t3 AS SELECT t5.c1 AS A, t4.c1 AS B FROM t5, t4;
481 CREATE TABLE bug48506_t4(c1 INT);
482 connection slave;
483 DROP VIEW bug48506_t1, bug48506_t2, bug48506_t3;
484 DROP TABLE bug48506_t4;
485 connection master;
486 CREATE TABLE IF NOT EXISTS bug48506_t1 LIKE t7;
487 CREATE TABLE IF NOT EXISTS bug48506_t2 LIKE t7;
488 CREATE TABLE IF NOT EXISTS bug48506_t3 LIKE t7;
489 CREATE TABLE IF NOT EXISTS bug48506_t4 LIKE t7;
490 connection slave;
491 SHOW TABLES LIKE 'bug48506%';
492 Tables_in_test (bug48506%)
493 connection master;
494 DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3;
495 DROP TEMPORARY TABLES t7;
496 DROP TABLES t4, t5;
497 DROP TABLES IF EXISTS bug48506_t4;
498 *** MDEV-31794: Preserved unsupported table flags break replication
499 CREATE TEMPORARY TABLE t1 (a INT) ENGINE=Aria TRANSACTIONAL=1;
500 ALTER TABLE t1 ENGINE=MyISAM;
501 CREATE TABLE t2 LIKE t1;
502 connection slave;
503 SHOW CREATE TABLE t2;
504 Table Create Table
505 t2 CREATE TABLE `t2` (
506 `a` int(11) DEFAULT NULL
507 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci /* TRANSACTIONAL=1 */
508 connection master;
509 DROP TEMPORARY TABLE t1;
510 DROP TABLE t2;
511 include/rpl_end.inc
512 end of the tests