[tomato.git] / release / src / router / mysql / mysql-test / suite / rpl / r / rpl_row_create_table.result
| blob | c6aa05f9e9046203b31cf019e46d886f9376b877 |
1 stop slave;
2 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
3 reset master;
4 reset slave;
5 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
6 start slave;
7 **** Resetting master and slave ****
8 include/stop_slave.inc
9 RESET SLAVE;
10 RESET MASTER;
11 include/start_slave.inc
12 CREATE TABLE t1 (a INT, b INT);
13 CREATE TABLE t2 (a INT, b INT) ENGINE=Merge;
14 CREATE TABLE t3 (a INT, b INT) CHARSET=utf8;
15 CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8;
16 show binlog events from <binlog_start>;
17 Log_name Pos Event_type Server_id End_log_pos Info
18 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT)
19 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a INT, b INT) ENGINE=Merge
20 master-bin.000001 # Query # # use `test`; CREATE TABLE t3 (a INT, b INT) CHARSET=utf8
21 master-bin.000001 # Query # # use `test`; CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8
22 **** On Master ****
23 SHOW CREATE TABLE t1;
24 Table t1
25 Create Table CREATE TABLE `t1` (
26 `a` int(11) DEFAULT NULL,
27 `b` int(11) DEFAULT NULL
28 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
29 SHOW CREATE TABLE t2;
30 Table t2
31 Create Table CREATE TABLE `t2` (
32 `a` int(11) DEFAULT NULL,
33 `b` int(11) DEFAULT NULL
34 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
35 SHOW CREATE TABLE t3;
36 Table t3
37 Create Table CREATE TABLE `t3` (
38 `a` int(11) DEFAULT NULL,
39 `b` int(11) DEFAULT NULL
40 ) ENGINE=MyISAM DEFAULT CHARSET=utf8
41 **** On Slave ****
42 SHOW CREATE TABLE t1;
43 Table t1
44 Create Table CREATE TABLE `t1` (
45 `a` int(11) DEFAULT NULL,
46 `b` int(11) DEFAULT NULL
47 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
48 SHOW CREATE TABLE t2;
49 Table t2
50 Create Table CREATE TABLE `t2` (
51 `a` int(11) DEFAULT NULL,
52 `b` int(11) DEFAULT NULL
53 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
54 SHOW CREATE TABLE t3;
55 Table t3
56 Create Table CREATE TABLE `t3` (
57 `a` int(11) DEFAULT NULL,
58 `b` int(11) DEFAULT NULL
59 ) ENGINE=MEMORY DEFAULT CHARSET=utf8
60 CREATE TABLE t5 (b INT, c INT) SELECT * FROM t3;
61 CREATE TEMPORARY TABLE tt3 (a INT, b INT);
62 INSERT INTO tt3 VALUES (1,2), (2,4), (3,6), (4,2), (5,10), (6,12);
63 CREATE TABLE t6 (b INT, c INT) SELECT * FROM tt3;
64 **** On Master ****
65 SHOW CREATE TABLE t5;
66 Table t5
67 Create Table CREATE TABLE `t5` (
68 `c` int(11) DEFAULT NULL,
69 `a` int(11) DEFAULT NULL,
70 `b` int(11) DEFAULT NULL
71 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
72 SELECT * FROM t5 ORDER BY a,b,c;
73 c a b
74 SHOW CREATE TABLE t6;
75 Table t6
76 Create Table CREATE TABLE `t6` (
77 `c` int(11) DEFAULT NULL,
78 `a` int(11) DEFAULT NULL,
79 `b` int(11) DEFAULT NULL
80 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
81 SELECT * FROM t6 ORDER BY a,b,c;
82 c a b
83 NULL 1 2
84 NULL 2 4
85 NULL 3 6
86 NULL 4 2
87 NULL 5 10
88 NULL 6 12
89 **** On Slave ****
90 SHOW CREATE TABLE t5;
91 Table t5
92 Create Table CREATE TABLE `t5` (
93 `c` int(11) DEFAULT NULL,
94 `a` int(11) DEFAULT NULL,
95 `b` int(11) DEFAULT NULL
96 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
97 SELECT * FROM t5 ORDER BY a,b,c;
98 c a b
99 SHOW CREATE TABLE t6;
100 Table t6
101 Create Table CREATE TABLE `t6` (
102 `c` int(11) DEFAULT NULL,
103 `a` int(11) DEFAULT NULL,
104 `b` int(11) DEFAULT NULL
105 ) ENGINE=MEMORY DEFAULT CHARSET=latin1
106 SELECT * FROM t6 ORDER BY a,b,c;
107 c a b
108 NULL 1 2
109 NULL 2 4
110 NULL 3 6
111 NULL 4 2
112 NULL 5 10
113 NULL 6 12
114 **** Resetting master and slave ****
115 include/stop_slave.inc
116 RESET SLAVE;
117 RESET MASTER;
118 include/start_slave.inc
119 CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3;
120 ERROR 23000: Duplicate entry '2' for key 'b'
121 show binlog events from <binlog_start>;
122 Log_name Pos Event_type Server_id End_log_pos Info
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 show binlog events from <binlog_start>;
132 Log_name Pos Event_type Server_id End_log_pos Info
133 master-bin.000001 # Query # # use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE)
134 master-bin.000001 # Query # # BEGIN
135 master-bin.000001 # Table_map # # table_id: # (test.t7)
136 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
137 master-bin.000001 # Query # # ROLLBACK
138 SELECT * FROM t7 ORDER BY a,b;
139 a b
140 1 2
141 2 4
142 3 6
143 **** Resetting master and slave ****
144 include/stop_slave.inc
145 RESET SLAVE;
146 RESET MASTER;
147 include/start_slave.inc
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 show binlog events from <binlog_start>;
156 Log_name Pos Event_type Server_id End_log_pos Info
157 master-bin.000001 # Query # # BEGIN
158 master-bin.000001 # Table_map # # table_id: # (test.t7)
159 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
160 master-bin.000001 # Query # # COMMIT
161 SELECT * FROM t7 ORDER BY a,b;
162 a b
163 1 2
164 2 4
165 3 6
166 4 8
167 5 10
168 6 12
169 SELECT * FROM t7 ORDER BY a,b;
170 a b
171 1 2
172 2 4
173 3 6
174 4 8
175 5 10
176 6 12
177 **** Resetting master and slave ****
178 include/stop_slave.inc
179 RESET SLAVE;
180 RESET MASTER;
181 include/start_slave.inc
182 CREATE TABLE t8 LIKE t4;
183 CREATE TABLE t9 LIKE tt4;
184 CREATE TEMPORARY TABLE tt5 LIKE t4;
185 CREATE TEMPORARY TABLE tt6 LIKE tt4;
186 CREATE TEMPORARY TABLE tt7 SELECT 1;
187 **** On Master ****
188 SHOW CREATE TABLE t8;
189 Table t8
190 Create Table CREATE TABLE `t8` (
191 `a` int(11) DEFAULT NULL,
192 `b` int(11) DEFAULT NULL
193 ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8
194 SHOW CREATE TABLE t9;
195 Table t9
196 Create Table CREATE TABLE `t9` (
197 `a` int(11) DEFAULT NULL,
198 `b` int(11) DEFAULT NULL
199 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
200 show binlog events from <binlog_start>;
201 Log_name Pos Event_type Server_id End_log_pos Info
202 master-bin.000001 # Query # # use `test`; CREATE TABLE t8 LIKE t4
203 master-bin.000001 # Query # # use `test`; CREATE TABLE `t9` (
204 `a` int(11) DEFAULT NULL,
205 `b` int(11) DEFAULT NULL
206 )
207 **** On 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=utf8
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=MEMORY DEFAULT CHARSET=latin1
220 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9;
221 STOP SLAVE;
222 SET GLOBAL storage_engine=@storage_engine;
223 START SLAVE;
224 ================ BUG#22864 ================
225 stop slave;
226 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
227 reset master;
228 reset slave;
229 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
230 start slave;
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 t1
247 t2
248 t3
249 t4
250 SELECT * FROM t1 ORDER BY a;
251 a
252 1
253 2
254 3
255 4
256 5
257 6
258 SELECT * FROM t2 ORDER BY a;
259 a
260 1
261 2
262 3
263 SELECT * FROM t3 ORDER BY a;
264 a
265 1
266 2
267 3
268 SELECT * FROM t4 ORDER BY a;
269 a
270 1
271 2
272 3
273 show binlog events from <binlog_start>;
274 Log_name Pos Event_type Server_id End_log_pos Info
275 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT)
276 master-bin.000001 # Query # # BEGIN
277 master-bin.000001 # Table_map # # table_id: # (test.t1)
278 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
279 master-bin.000001 # Query # # COMMIT
280 master-bin.000001 # Query # # BEGIN
281 master-bin.000001 # Query # # use `test`; CREATE TABLE `t2` (
282 `a` int(11) DEFAULT NULL
283 ) ENGINE=InnoDB
284 master-bin.000001 # Table_map # # table_id: # (test.t2)
285 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
286 master-bin.000001 # Xid # # COMMIT /* XID */
287 master-bin.000001 # Query # # BEGIN
288 master-bin.000001 # Query # # use `test`; CREATE TABLE `t3` (
289 `a` int(11) DEFAULT NULL
290 ) ENGINE=InnoDB
291 master-bin.000001 # Table_map # # table_id: # (test.t3)
292 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
293 master-bin.000001 # Xid # # COMMIT /* XID */
294 master-bin.000001 # Query # # BEGIN
295 master-bin.000001 # Query # # use `test`; CREATE TABLE `t4` (
296 `a` int(11) DEFAULT NULL
297 ) ENGINE=InnoDB
298 master-bin.000001 # Table_map # # table_id: # (test.t4)
299 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
300 master-bin.000001 # Xid # # COMMIT /* XID */
301 master-bin.000001 # Query # # BEGIN
302 master-bin.000001 # Table_map # # table_id: # (test.t1)
303 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
304 master-bin.000001 # Query # # COMMIT
305 SHOW TABLES;
306 Tables_in_test
307 t1
308 t2
309 t3
310 t4
311 SELECT * FROM t1 ORDER BY a;
312 a
313 1
314 2
315 3
316 4
317 5
318 6
319 SELECT * FROM t2 ORDER BY a;
320 a
321 1
322 2
323 3
324 SELECT * FROM t3 ORDER BY a;
325 a
326 1
327 2
328 3
329 SELECT * FROM t4 ORDER BY a;
330 a
331 1
332 2
333 3
334 DROP TABLE IF EXISTS t1,t2,t3,t4;
335 SET AUTOCOMMIT=1;
336 STOP SLAVE;
337 RESET SLAVE;
338 RESET MASTER;
339 START SLAVE;
340 CREATE TABLE t1 (a INT);
341 INSERT INTO t1 VALUES (1),(2),(3);
342 CREATE TABLE t2 (a INT) ENGINE=INNODB;
343 BEGIN;
344 INSERT INTO t2 SELECT a*a FROM t1;
345 CREATE TEMPORARY TABLE tt1
346 SELECT a+1 AS a
347 FROM t1
348 WHERE a MOD 2 = 1;
349 INSERT INTO t2 SELECT a+2 FROM tt1;
350 COMMIT;
351 SELECT * FROM t2 ORDER BY a;
352 a
353 1
354 4
355 4
356 6
357 9
358 show binlog events from <binlog_start>;
359 Log_name Pos Event_type Server_id End_log_pos Info
360 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT)
361 master-bin.000001 # Query # # BEGIN
362 master-bin.000001 # Table_map # # table_id: # (test.t1)
363 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
364 master-bin.000001 # Query # # COMMIT
365 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a INT) ENGINE=INNODB
366 master-bin.000001 # Query # # BEGIN
367 master-bin.000001 # Table_map # # table_id: # (test.t2)
368 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
369 master-bin.000001 # Table_map # # table_id: # (test.t2)
370 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
371 master-bin.000001 # Xid # # COMMIT /* XID */
372 SELECT * FROM t2 ORDER BY a;
373 a
374 1
375 4
376 4
377 6
378 9
379 TRUNCATE TABLE t2;
380 **** Resetting master and slave ****
381 include/stop_slave.inc
382 RESET SLAVE;
383 RESET MASTER;
384 include/start_slave.inc
385 BEGIN;
386 INSERT INTO t2 SELECT a*a FROM t1;
387 CREATE TEMPORARY TABLE tt2
388 SELECT a+1 AS a
389 FROM t1
390 WHERE a MOD 2 = 1;
391 INSERT INTO t2 SELECT a+2 FROM tt2;
392 ROLLBACK;
393 Warnings:
394 Warning 1196 Some non-transactional changed tables couldn't be rolled back
395 SELECT * FROM t2 ORDER BY a;
396 a
397 show binlog events from <binlog_start>;
398 Log_name Pos Event_type Server_id End_log_pos Info
399 master-bin.000001 # Query # # BEGIN
400 master-bin.000001 # Table_map # # table_id: # (test.t2)
401 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
402 master-bin.000001 # Table_map # # table_id: # (test.t2)
403 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
404 master-bin.000001 # Query # # ROLLBACK
405 SELECT * FROM t2 ORDER BY a;
406 a
407 DROP TABLE t1,t2;
408 CREATE TABLE t1 (a INT);
409 INSERT INTO t1 VALUES (1),(1);
410 CREATE TABLE t2 (a INT UNIQUE) ENGINE=INNODB SELECT * FROM t1;
411 ERROR 23000: Duplicate entry '1' for key 'a'
412 INSERT INTO t1 VALUES (2);
413 *** the proof of the fix:
414 select must show that the last insert performed on the slave ***
415 SELECT * FROM t1;
416 a
417 1
418 1
419 2
420 DROP TABLE t1;
421 stop slave;
422 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
423 reset master;
424 reset slave;
425 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
426 start slave;
427 DROP DATABASE IF EXISTS mysqltest1;
428 CREATE DATABASE mysqltest1;
429 CREATE TABLE mysqltest1.without_select (f1 BIGINT);
430 CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1;
431 show binlog events from <binlog_start>;
432 Log_name Pos Event_type Server_id End_log_pos Info
433 master-bin.000001 # Query # # DROP DATABASE IF EXISTS mysqltest1
434 master-bin.000001 # Query # # CREATE DATABASE mysqltest1
435 master-bin.000001 # Query # # use `test`; CREATE TABLE mysqltest1.without_select (f1 BIGINT)
436 master-bin.000001 # Query # # BEGIN
437 master-bin.000001 # Query # # use `test`; CREATE TABLE `mysqltest1`.`with_select` (
438 `f1` int(1) NOT NULL DEFAULT '0'
439 )
440 master-bin.000001 # Table_map # # table_id: # (mysqltest1.with_select)
441 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
442 master-bin.000001 # Query # # COMMIT
443 DROP DATABASE mysqltest1;
444 stop slave;
445 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
446 reset master;
447 reset slave;
448 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
449 start slave;
450 CREATE TEMPORARY TABLE t7(c1 INT);
451 CREATE TABLE t5(c1 INT);
452 CREATE TABLE t4(c1 INT);
453 CREATE VIEW bug48506_t1 AS SELECT 1;
454 CREATE VIEW bug48506_t2 AS SELECT * FROM t4;
455 CREATE VIEW bug48506_t3 AS SELECT t5.c1 AS A, t4.c1 AS B FROM t5, t4;
456 CREATE TABLE bug48506_t4(c1 INT);
457 DROP VIEW bug48506_t1, bug48506_t2, bug48506_t3;
458 DROP TABLE bug48506_t4;
459 CREATE TABLE IF NOT EXISTS bug48506_t1 LIKE t7;
460 CREATE TABLE IF NOT EXISTS bug48506_t2 LIKE t7;
461 CREATE TABLE IF NOT EXISTS bug48506_t3 LIKE t7;
462 CREATE TABLE IF NOT EXISTS bug48506_t4 LIKE t7;
463 SHOW TABLES LIKE 'bug48506%';
464 Tables_in_test (bug48506%)
465 bug48506_t4
466 DROP VIEW IF EXISTS bug48506_t1, bug48506_t2, bug48506_t3;
467 DROP TEMPORARY TABLES t7;
468 DROP TABLES t4, t5;
469 DROP TABLES IF EXISTS bug48506_t4;
470 CREATE TABLE t1 SELECT 1;
471 CREATE TABLE IF NOT EXISTS t1 SELECT 1;
472 Warnings:
473 Note 1050 Table 't1' already exists
474 Comparing tables master:test.t1 and slave:test.t1
475 DROP TABLE t1;
476 end of the tests