2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
7
CREATE TABLE t1 (a INT, b INT);
8
CREATE TABLE t2 (a INT, b INT) ENGINE=Merge;
9
CREATE TABLE t3 (a INT, b INT) CHARSET=utf8;
10
CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8;
11
SHOW BINLOG EVENTS FROM 217;
17
Info use `test`; CREATE TABLE t1 (a INT, b INT)
23
Info use `test`; CREATE TABLE t2 (a INT, b INT) ENGINE=Merge
29
Info use `test`; CREATE TABLE t3 (a INT, b INT) CHARSET=utf8
35
Info use `test`; CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8
39
Create Table CREATE TABLE `t1` (
40
`a` int(11) DEFAULT NULL,
41
`b` int(11) DEFAULT NULL
42
) ENGINE=MyISAM DEFAULT CHARSET=latin1
45
Create Table CREATE TABLE `t2` (
46
`a` int(11) DEFAULT NULL,
47
`b` int(11) DEFAULT NULL
48
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
51
Create Table CREATE TABLE `t3` (
52
`a` int(11) DEFAULT NULL,
53
`b` int(11) DEFAULT NULL
54
) ENGINE=MyISAM DEFAULT CHARSET=utf8
58
Create Table CREATE TABLE `t1` (
59
`a` int(11) DEFAULT NULL,
60
`b` int(11) DEFAULT NULL
61
) ENGINE=MEMORY DEFAULT CHARSET=latin1
64
Create Table CREATE TABLE `t2` (
65
`a` int(11) DEFAULT NULL,
66
`b` int(11) DEFAULT NULL
67
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
70
Create Table CREATE TABLE `t3` (
71
`a` int(11) DEFAULT NULL,
72
`b` int(11) DEFAULT NULL
73
) ENGINE=MEMORY DEFAULT CHARSET=utf8
74
CREATE TABLE t5 (b INT, c INT) SELECT * FROM t3;
75
CREATE TEMPORARY TABLE tt3 (a INT, b INT);
76
INSERT INTO tt3 VALUES (1,2), (2,4), (3,6), (4,2), (5,10), (6,12);
77
CREATE TABLE t6 (b INT, c INT) SELECT * FROM tt3;
81
Create Table CREATE TABLE `t5` (
82
`c` int(11) DEFAULT NULL,
83
`a` int(11) DEFAULT NULL,
84
`b` int(11) DEFAULT NULL
85
) ENGINE=MyISAM DEFAULT CHARSET=latin1
86
SELECT * FROM t5 ORDER BY a,b,c;
90
Create Table CREATE TABLE `t6` (
91
`c` int(11) DEFAULT NULL,
92
`a` int(11) DEFAULT NULL,
93
`b` int(11) DEFAULT NULL
94
) ENGINE=MyISAM DEFAULT CHARSET=latin1
95
SELECT * FROM t6 ORDER BY a,b,c;
104
SHOW CREATE TABLE t5;
106
Create Table CREATE TABLE `t5` (
107
`c` int(11) DEFAULT NULL,
108
`a` int(11) DEFAULT NULL,
109
`b` int(11) DEFAULT NULL
110
) ENGINE=MEMORY DEFAULT CHARSET=latin1
111
SELECT * FROM t5 ORDER BY a,b,c;
113
SHOW CREATE TABLE t6;
115
Create Table CREATE TABLE `t6` (
116
`c` int(11) DEFAULT NULL,
117
`a` int(11) DEFAULT NULL,
118
`b` int(11) DEFAULT NULL
119
) ENGINE=MEMORY DEFAULT CHARSET=latin1
120
SELECT * FROM t6 ORDER BY a,b,c;
128
CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3;
129
ERROR 23000: Duplicate entry '2' for key 'b'
130
SHOW BINLOG EVENTS FROM 1375;
131
Log_name Pos Event_type Server_id End_log_pos Info
132
CREATE TABLE t7 (a INT, b INT UNIQUE);
133
INSERT INTO t7 SELECT a,b FROM tt3;
134
ERROR 23000: Duplicate entry '2' for key 'b'
135
SELECT * FROM t7 ORDER BY a,b;
140
SHOW BINLOG EVENTS FROM 1375;
141
Log_name Pos Event_type Server_id End_log_pos Info
142
# 1375 Query # 1475 use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE)
143
# 1475 Query # 1543 use `test`; BEGIN
144
# 1543 Table_map # 1585 table_id: # (test.t7)
145
# 1585 Write_rows # 1641 table_id: # flags: STMT_END_F
146
# 1641 Query # 1712 use `test`; ROLLBACK
147
SELECT * FROM t7 ORDER BY a,b;
152
CREATE TEMPORARY TABLE tt4 (a INT, b INT);
153
INSERT INTO tt4 VALUES (4,8), (5,10), (6,12);
155
INSERT INTO t7 SELECT a,b FROM tt4;
158
Warning 1196 Some non-transactional changed tables couldn't be rolled back
159
SHOW BINLOG EVENTS FROM 1712;
160
Log_name Pos Event_type Server_id End_log_pos Info
161
# 1712 Query # 1780 use `test`; BEGIN
162
# 1780 Table_map # 1822 table_id: # (test.t7)
163
# 1822 Write_rows # 1878 table_id: # flags: STMT_END_F
164
# 1878 Query # 1947 use `test`; COMMIT
165
SELECT * FROM t7 ORDER BY a,b;
173
SELECT * FROM t7 ORDER BY a,b;
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;
187
SHOW CREATE TABLE t8;
189
Create Table CREATE TABLE `t8` (
190
`a` int(11) DEFAULT NULL,
191
`b` int(11) DEFAULT NULL
192
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8
193
SHOW CREATE TABLE t9;
195
Create Table CREATE TABLE `t9` (
196
`a` int(11) DEFAULT NULL,
197
`b` int(11) DEFAULT NULL
198
) ENGINE=MyISAM DEFAULT CHARSET=latin1
199
SHOW BINLOG EVENTS FROM 1947;
200
Log_name Pos Event_type Server_id End_log_pos Info
201
# 1947 Query # 2033 use `test`; CREATE TABLE t8 LIKE t4
202
# 2033 Query # 2172 use `test`; CREATE TABLE `t9` (
203
`a` int(11) DEFAULT NULL,
204
`b` int(11) DEFAULT NULL
207
SHOW CREATE TABLE t8;
209
Create Table CREATE TABLE `t8` (
210
`a` int(11) DEFAULT NULL,
211
`b` int(11) DEFAULT NULL
212
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8
213
SHOW CREATE TABLE t9;
215
Create Table CREATE TABLE `t9` (
216
`a` int(11) DEFAULT NULL,
217
`b` int(11) DEFAULT NULL
218
) ENGINE=MEMORY DEFAULT CHARSET=latin1
219
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9;
221
SET GLOBAL storage_engine=@storage_engine;
223
================ BUG#22864 ================
229
CREATE TABLE t1 (a INT);
230
INSERT INTO t1 VALUES (1),(2),(3);
231
CREATE TABLE t2 ENGINE=INNODB SELECT * FROM t1;
233
CREATE TABLE t3 ENGINE=INNODB SELECT * FROM t1;
234
INSERT INTO t3 VALUES (4),(5),(6);
236
CREATE TABLE t4 ENGINE=INNODB SELECT * FROM t1;
237
INSERT INTO t1 VALUES (4),(5),(6);
240
Warning 1196 Some non-transactional changed tables couldn't be rolled back
247
SELECT TABLE_NAME,ENGINE
248
FROM INFORMATION_SCHEMA.TABLES
249
WHERE TABLE_NAME LIKE 't_'
256
SELECT * FROM t1 ORDER BY a;
264
SELECT * FROM t2 ORDER BY a;
269
SELECT * FROM t3 ORDER BY a;
274
SELECT * FROM t4 ORDER BY a;
280
Log_name Pos Event_type Server_id End_log_pos Info
281
# 4 Format_desc # 107 Server ver: #, Binlog ver: #
282
# 107 Query # 193 use `test`; CREATE TABLE t1 (a INT)
283
# 193 Query # 261 use `test`; BEGIN
284
# 261 Table_map # 302 table_id: # (test.t1)
285
# 302 Write_rows # 346 table_id: # flags: STMT_END_F
286
# 346 Query # 415 use `test`; COMMIT
287
# 415 Query # 483 use `test`; BEGIN
288
# 483 Query # 608 use `test`; CREATE TABLE `t2` (
289
`a` int(11) DEFAULT NULL
291
# 608 Table_map # 649 table_id: # (test.t2)
292
# 649 Write_rows # 693 table_id: # flags: STMT_END_F
293
# 693 Xid # 720 COMMIT /* XID */
294
# 720 Query # 788 use `test`; BEGIN
295
# 788 Query # 913 use `test`; CREATE TABLE `t3` (
296
`a` int(11) DEFAULT NULL
298
# 913 Table_map # 954 table_id: # (test.t3)
299
# 954 Write_rows # 998 table_id: # flags: STMT_END_F
300
# 998 Xid # 1025 COMMIT /* XID */
301
# 1025 Query # 1093 use `test`; BEGIN
302
# 1093 Query # 1218 use `test`; CREATE TABLE `t4` (
303
`a` int(11) DEFAULT NULL
305
# 1218 Table_map # 1259 table_id: # (test.t4)
306
# 1259 Write_rows # 1303 table_id: # flags: STMT_END_F
307
# 1303 Xid # 1330 COMMIT /* XID */
308
# 1330 Query # 1398 use `test`; BEGIN
309
# 1398 Table_map # 1439 table_id: # (test.t1)
310
# 1439 Write_rows # 1483 table_id: # flags: STMT_END_F
311
# 1483 Query # 1552 use `test`; COMMIT
318
SELECT TABLE_NAME,ENGINE
319
FROM INFORMATION_SCHEMA.TABLES
320
WHERE TABLE_NAME LIKE 't_'
327
SELECT * FROM t1 ORDER BY a;
335
SELECT * FROM t2 ORDER BY a;
340
SELECT * FROM t3 ORDER BY a;
345
SELECT * FROM t4 ORDER BY a;
350
DROP TABLE IF EXISTS t1,t2,t3,t4;
356
CREATE TABLE t1 (a INT);
357
INSERT INTO t1 VALUES (1),(2),(3);
358
CREATE TABLE t2 (a INT) ENGINE=INNODB;
360
INSERT INTO t2 SELECT a*a FROM t1;
361
CREATE TEMPORARY TABLE tt1
365
INSERT INTO t2 SELECT a+2 FROM tt1;
367
SELECT * FROM t2 ORDER BY a;
375
Log_name Pos Event_type Server_id End_log_pos Info
376
# 4 Format_desc # 107 Server ver: #, Binlog ver: #
377
# 107 Query # 193 use `test`; CREATE TABLE t1 (a INT)
378
# 193 Query # 261 use `test`; BEGIN
379
# 261 Table_map # 302 table_id: # (test.t1)
380
# 302 Write_rows # 346 table_id: # flags: STMT_END_F
381
# 346 Query # 415 use `test`; COMMIT
382
# 415 Query # 515 use `test`; CREATE TABLE t2 (a INT) ENGINE=INNODB
383
# 515 Query # 583 use `test`; BEGIN
384
# 583 Table_map # 624 table_id: # (test.t2)
385
# 624 Write_rows # 668 table_id: # flags: STMT_END_F
386
# 668 Table_map # 709 table_id: # (test.t2)
387
# 709 Write_rows # 748 table_id: # flags: STMT_END_F
388
# 748 Xid # 775 COMMIT /* XID */
389
SELECT * FROM t2 ORDER BY a;
398
INSERT INTO t2 SELECT a*a FROM t1;
399
CREATE TEMPORARY TABLE tt2
403
INSERT INTO t2 SELECT a+2 FROM tt2;
406
Warning 1196 Some non-transactional changed tables couldn't be rolled back
407
SELECT * FROM t2 ORDER BY a;
409
SHOW BINLOG EVENTS FROM 950;
410
Log_name Pos Event_type Server_id End_log_pos Info
411
# 950 Query # 1018 use `test`; BEGIN
412
# 1018 Table_map # 1059 table_id: # (test.t2)
413
# 1059 Write_rows # 1103 table_id: # flags: STMT_END_F
414
# 1103 Table_map # 1144 table_id: # (test.t2)
415
# 1144 Write_rows # 1183 table_id: # flags: STMT_END_F
416
# 1183 Query # 1254 use `test`; ROLLBACK
417
SELECT * FROM t2 ORDER BY a;