1
set global innodb_support_xa=default;
2
set session innodb_support_xa=default;
3
SET SESSION STORAGE_ENGINE = InnoDB;
4
drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4;
6
c_id int not null default '0',
7
org_id int default null,
8
unique key contacts$c_id (c_id),
9
key contacts$org_id (org_id)
12
(2,null),(120,null),(141,null),(218,7), (128,1),
13
(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
14
(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
16
slai_id int not null default '0',
17
owner_tbl int default null,
18
owner_id int default null,
19
sla_id int default null,
20
inc_web int default null,
21
inc_email int default null,
22
inc_chat int default null,
23
inc_csr int default null,
24
inc_total int default null,
25
time_billed int default null,
26
activedate timestamp null default null,
27
expiredate timestamp null default null,
28
state int default null,
29
sla_set int default null,
30
unique key t2$slai_id (slai_id),
31
key t2$owner_id (owner_id),
32
key t2$sla_id (sla_id)
34
insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
35
(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
36
(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
39
from t1 c join t2 si on
40
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
41
( si.owner_tbl = 2 and si.owner_id = c.c_id))
43
c.c_id = 218 and expiredate is null;
46
select * from t1 where org_id is null;
52
from t1 c join t2 si on
53
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
54
( si.owner_tbl = 2 and si.owner_id = c.c_id))
56
c.c_id = 218 and expiredate is null;
60
CREATE TABLE t1 (a int, b int, KEY b (b));
61
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b));
62
CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
63
UNIQUE KEY b (b,c), KEY a (a,b,c));
64
INSERT INTO t1 VALUES (1, 1);
65
INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
66
INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
67
INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
68
INSERT INTO t2 SELECT a + 1, b FROM t2;
69
DELETE FROM t2 WHERE a = 1 AND b < 2;
70
INSERT INTO t3 VALUES (1,1,1),(2,1,2);
71
INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3;
72
INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3;
73
SELECT STRAIGHT_JOIN t1.b, t1.a FROM t1, t3, t2 WHERE
74
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
75
ORDER BY t1.b LIMIT 2;
79
SELECT STRAIGHT_JOIN t1.b, t1.a FROM t1, t3, t2 WHERE
80
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
81
ORDER BY t1.b LIMIT 5;
88
DROP TABLE t1, t2, t3;
89
CREATE TABLE `t1` (`id1` INT) ;
90
INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
99
INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
106
SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
110
CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
112
INSERT INTO t1 (c1) VALUES ('1a');
116
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
121
CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
123
INSERT INTO t1 (c1) VALUES ('1a');
127
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
132
CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
134
INSERT INTO t1 (c1) VALUES ('1a');
138
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
144
a1 decimal(10,0) DEFAULT NULL,
146
a3 time DEFAULT NULL,
148
a5 char(175) DEFAULT NULL,
149
a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
151
INDEX idx (a6,a7(239),a5)
153
EXPLAIN SELECT a4 FROM t1 WHERE
155
a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
156
id select_type table type possible_keys key key_len ref rows Extra
157
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
158
EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
159
t.a6=t.a6 AND t1.a6=NULL AND
160
t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
161
id select_type table type possible_keys key key_len ref rows Extra
162
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
164
create table t1m (a int) engine = MEMORY;
165
create table t1i (a int);
166
create table t2m (a int) engine = MEMORY;
167
create table t2i (a int);
168
insert into t2m values (5);
169
insert into t2i values (5);
170
select 1, min(a) from t1i where a=99;
173
select 1, min(a) from t1i where 1=99;
176
select 1, min(1) from t1i where a=99;
179
select 1, min(1) from t1i where 1=99;
182
select 1, max(a) from t1i where a=99;
185
select 1, max(a) from t1i where 1=99;
188
select 1, max(1) from t1i where a=99;
191
select 1, max(1) from t1i where 1=99;
194
explain select count(*), min(7), max(7) from t1m, t1i;
195
id select_type table type possible_keys key key_len ref rows Extra
196
1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
197
1 SIMPLE t1i ALL NULL NULL NULL NULL 1
198
select count(*), min(7), max(7) from t1m, t1i;
199
count(*) min(7) max(7)
201
explain select count(*), min(7), max(7) from t1m, t2i;
202
id select_type table type possible_keys key key_len ref rows Extra
203
1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
204
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
205
select count(*), min(7), max(7) from t1m, t2i;
206
count(*) min(7) max(7)
208
explain select count(*), min(7), max(7) from t2m, t1i;
209
id select_type table type possible_keys key key_len ref rows Extra
210
1 SIMPLE t2m system NULL NULL NULL NULL 1
211
1 SIMPLE t1i ALL NULL NULL NULL NULL 1
212
select count(*), min(7), max(7) from t2m, t1i;
213
count(*) min(7) max(7)
215
drop table t1m, t1i, t2m, t2i;
217
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
219
insert into t1 (a1, a2, b, c, d) values
220
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
221
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
222
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
223
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
224
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
225
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
226
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
227
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
228
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
229
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
230
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
231
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
232
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
233
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
234
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
235
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
236
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
237
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
238
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
239
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
240
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
241
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
242
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
243
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
244
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
245
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
246
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
247
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
248
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
249
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
250
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
251
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
253
pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
255
insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
256
create index idx12672_0 on t4 (a1);
257
create index idx12672_1 on t4 (a1,a2,b,c);
258
create index idx12672_2 on t4 (a1,a2,b);
260
Table Op Msg_type Msg_text
261
test.t4 analyze status OK
262
select distinct a1 from t4 where pk_col not in (1,2,3,4);
269
DROP TABLE IF EXISTS t2, t1;
270
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB;
273
FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION
275
INSERT INTO t1 VALUES (1);
276
INSERT INTO t2 VALUES (1);
277
DELETE IGNORE FROM t1 WHERE i = 1;
279
Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`i`) REFERENCES `t1` (`i`) ON DELETE NO ACTION)
280
SELECT * FROM t1, t2;
286
a varchar(30), b varchar(30), primary key(a), key(b)
288
select distinct a from t1;
291
create table t1(a int, key(a));
292
insert into t1 values(1);
293
select a, count(a) from t1 group by a with rollup;
298
create table t1 (f1 int, f2 char(1), primary key(f1,f2));
299
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
300
alter table t1 drop primary key, add primary key (f2, f1);
301
explain select distinct f1 a, f1 b from t1;
302
id select_type table type possible_keys key key_len ref rows Extra
303
1 SIMPLE t1 index NULL PRIMARY 10 NULL 4 Using index; Using temporary
304
explain select distinct f1, f2 from t1;
305
id select_type table type possible_keys key key_len ref rows Extra
306
1 SIMPLE t1 range NULL PRIMARY 10 NULL 3 Using index for group-by; Using temporary
308
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, name varchar(20),
310
CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, fkey int);
311
ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
312
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
313
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
315
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
316
WHERE t1.name LIKE 'A%';
317
id select_type table type possible_keys key key_len ref rows Extra
318
1 SIMPLE t1 index PRIMARY,name PRIMARY 4 NULL 3 Using where
319
1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
321
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
322
WHERE t1.name LIKE 'A%' OR FALSE;
323
id select_type table type possible_keys key key_len ref rows Extra
324
1 SIMPLE t2 index NULL PRIMARY 4 NULL 5
325
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where
329
name varchar(20) NOT NULL,
330
dept varchar(20) NOT NULL,
335
INSERT INTO t1(id, dept, age, name) VALUES
336
(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
337
(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
338
(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
339
(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
340
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
341
id select_type table type possible_keys key key_len ref rows Extra
342
1 SIMPLE t1 range name name 164 NULL 2 Using where; Using index for group-by
343
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
348
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
349
id select_type table type possible_keys key key_len ref rows Extra
350
1 SIMPLE t1 range name name 164 NULL 2 Using where; Using index for group-by
351
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
354
drop table if exists t1;
355
show variables like 'innodb_rollback_on_timeout';
357
innodb_rollback_on_timeout OFF
358
create table t1 (a int not null primary key) engine = innodb;
359
insert into t1 values (1);
362
insert into t1 values (2);
368
insert into t1 values (5);
373
insert into t1 values (2);
374
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
391
drop table if exists `test`;
393
Note 1051 Unknown table 'test'
394
CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
395
`test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`))
397
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
401
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
402
ON DUPLICATE KEY UPDATE `test2` = '1234';
411
drop table if exists t1;
412
show variables like 'innodb_rollback_on_timeout';
414
innodb_rollback_on_timeout OFF
415
create table t1 (a int not null primary key) engine = innodb;
416
insert into t1 values (1);
419
insert into t1 values (2);
425
insert into t1 values (5);
430
insert into t1 values (2);
431
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
449
id int auto_increment,
451
counter int not null default 1,
455
insert into t1 (id, c) values
458
on duplicate key update id = values(id), counter = counter + 1;
462
insert into t1 (id, c) values
464
on duplicate key update id = values(id), counter = counter + 1;
470
insert into t1 (id, c) values (NULL, 'a');
474
insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
475
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
480
insert into t1 (id, c) values (NULL, 'a')
481
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
487
create table t1(a int) engine=innodb;
488
alter table t1 comment '123';
489
show create table t1;
491
t1 CREATE TABLE `t1` (
493
) ENGINE=InnoDB COMMENT='123'
495
CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
496
INSERT INTO t1 VALUES ('uk'),('bg');
497
SELECT * FROM t1 WHERE a = 'uk';
500
DELETE FROM t1 WHERE a = 'uk';
501
SELECT * FROM t1 WHERE a = 'uk';
503
UPDATE t1 SET a = 'us' WHERE a = 'uk';
504
SELECT * FROM t1 WHERE a = 'uk';
506
CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
507
INSERT INTO t2 VALUES ('uk'),('bg');
508
SELECT * FROM t2 WHERE a = 'uk';
511
DELETE FROM t2 WHERE a = 'uk';
512
SELECT * FROM t2 WHERE a = 'uk';
514
INSERT INTO t2 VALUES ('uk');
515
UPDATE t2 SET a = 'us' WHERE a = 'uk';
516
SELECT * FROM t2 WHERE a = 'uk';
518
CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
519
INSERT INTO t3 VALUES ('uk'),('bg');
520
SELECT * FROM t3 WHERE a = 'uk';
523
DELETE FROM t3 WHERE a = 'uk';
524
SELECT * FROM t3 WHERE a = 'uk';
526
INSERT INTO t3 VALUES ('uk');
527
UPDATE t3 SET a = 'us' WHERE a = 'uk';
528
SELECT * FROM t3 WHERE a = 'uk';
531
create table t1 (a int) engine=innodb;
532
select * from bug29807;
533
ERROR 42S02: Table 'test.bug29807' doesn't exist
536
ERROR 42S02: Unknown table 'bug29807'
537
create table bug29807 (a int);
539
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
540
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
541
switch to connection c1
543
INSERT INTO t2 VALUES (1);
544
switch to connection c2
546
LOCK TABLES t1 READ, t2 READ;
547
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
548
switch to connection c1
550
INSERT INTO t1 VALUES (1);
551
switch to connection default
552
SET AUTOCOMMIT=default;
555
id int NOT NULL auto_increment PRIMARY KEY,
562
b int NOT NULL auto_increment PRIMARY KEY,
565
INSERT INTO t2(c) VALUES ('2007-01-01');
566
INSERT INTO t2(c) SELECT c FROM t2;
567
INSERT INTO t2(c) SELECT c FROM t2;
568
INSERT INTO t2(c) SELECT c FROM t2;
569
INSERT INTO t2(c) SELECT c FROM t2;
570
INSERT INTO t2(c) SELECT c FROM t2;
571
INSERT INTO t2(c) SELECT c FROM t2;
572
INSERT INTO t2(c) SELECT c FROM t2;
573
INSERT INTO t2(c) SELECT c FROM t2;
574
INSERT INTO t2(c) SELECT c FROM t2;
575
INSERT INTO t2(c) SELECT c FROM t2;
576
INSERT INTO t1(b,c) SELECT b,c FROM t2;
577
UPDATE t2 SET c='2007-01-02';
578
INSERT INTO t1(b,c) SELECT b,c FROM t2;
579
UPDATE t2 SET c='2007-01-03';
580
INSERT INTO t1(b,c) SELECT b,c FROM t2;
581
set @@sort_buffer_size=8192;
583
Warning 1292 Truncated incorrect sort_buffer_size value: '8192'
584
SELECT COUNT(*) FROM t1;
588
SELECT COUNT(*) FROM t1
589
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
590
id select_type table type possible_keys key key_len ref rows Extra
591
1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where
592
SELECT COUNT(*) FROM t1
593
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
597
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
598
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
599
id select_type table type possible_keys key key_len ref rows Extra
600
1 SIMPLE t1 index_merge idx_b,idx_c idx_c,idx_b 8,4 NULL # Using sort_union(idx_c,idx_b); Using where
601
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
602
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
605
set @@sort_buffer_size=default;
607
CREATE TABLE t1 (a int, b int);
608
insert into t1 values (1,1),(1,2);
609
CREATE TABLE t2 (primary key (a)) select * from t1;
610
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
611
drop table if exists t2;
613
Note 1051 Unknown table 't2'
614
CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
615
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
616
drop table if exists t2;
618
Note 1051 Unknown table 't2'
619
CREATE TABLE t2 (a int, b int, primary key (a));
621
INSERT INTO t2 values(100,100);
622
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
623
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
632
INSERT INTO t2 select * from t1;
633
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
637
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
639
INSERT INTO t2 values(100,100);
640
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
641
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
647
INSERT INTO t2 values(101,101);
648
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
649
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
659
INSERT INTO t2 select * from t1;
660
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
664
create table t1(f1 varchar(800) not null, key(f1));
666
Warning 1071 Specified key was too long; max key length is 767 bytes
667
insert into t1 values('aaa');
669
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
670
INSERT INTO t1 VALUES ( 1 , 1 , 1);
671
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
672
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
673
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
674
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
675
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
676
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
677
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
678
EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
679
id select_type table type possible_keys key key_len ref rows Extra
680
1 SIMPLE t1 index NULL b 5 NULL 128
681
EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
682
id select_type table type possible_keys key key_len ref rows Extra
683
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
685
drop table if exists t1;
686
show variables like 'innodb_rollback_on_timeout';
688
innodb_rollback_on_timeout OFF
689
create table t1 (a int not null primary key) engine = innodb;
690
insert into t1 values (1);
693
insert into t1 values (2);
699
insert into t1 values (5);
704
insert into t1 values (2);
705
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
722
drop table if exists t1;
723
create table t1 (a int) engine=innodb;
724
alter table t1 alter a set default 1;
727
Bug#24918 drop table and lock / inconsistent between
730
Check transactional tables under LOCK TABLES
732
drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp,
734
create table t24918_access (id int);
735
create table t24918 (id int) engine=myisam;
736
create temporary table t24918_tmp (id int) engine=myisam;
737
create table t24918_trans (id int) engine=innodb;
738
create temporary table t24918_trans_tmp (id int) engine=innodb;
739
lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write;
741
select * from t24918_access;
742
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
743
drop table t24918_trans;
744
select * from t24918_access;
745
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
746
drop table t24918_trans_tmp;
747
select * from t24918_access;
748
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
749
drop table t24918_tmp;
750
select * from t24918_access;
751
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
753
drop table t24918_access;
754
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
755
INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
756
INSERT INTO t1 SELECT a + 8, 2 FROM t1;
757
INSERT INTO t1 SELECT a + 16, 1 FROM t1;
758
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
768
Extra Using where; Using index
769
SELECT * FROM t1 WHERE b=2 ORDER BY a;
787
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
798
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
832
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
842
Extra Using where; Using index
843
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
877
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
879
INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
880
INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
881
INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
882
EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
892
Extra Using where; Using index
893
SELECT * FROM t2 WHERE b=1 ORDER BY a;
911
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
921
Extra Using where; Using index
922
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
940
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
950
Extra Using where; Using index
951
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
969
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
979
Extra Using where; Using index
980
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
999
create table t1(a text) engine=innodb;
1000
insert into t1 values('aaa');
1001
alter table t1 add index(a(1024));
1003
Warning 1071 Specified key was too long; max key length is 767 bytes
1004
Warning 1071 Specified key was too long; max key length is 767 bytes
1005
Warning 1071 Specified key was too long; max key length is 767 bytes
1006
show create table t1;
1008
t1 CREATE TABLE `t1` (
1018
INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
1020
SELECT * FROM t1 WHERE b=20 FOR UPDATE;
1024
SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
1038
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
1039
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
1040
id select_type table type possible_keys key key_len ref rows Extra
1041
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1042
SELECT c,b,d FROM t1 GROUP BY c,b,d;
1047
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1048
id select_type table type possible_keys key key_len ref rows Extra
1049
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
1050
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1055
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
1056
id select_type table type possible_keys key key_len ref rows Extra
1057
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1058
SELECT c,b,d FROM t1 ORDER BY c,b,d;
1063
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
1064
id select_type table type possible_keys key key_len ref rows Extra
1065
1 SIMPLE t1 index NULL c 8 NULL 3
1066
SELECT c,b,d FROM t1 GROUP BY c,b;
1071
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
1072
id select_type table type possible_keys key key_len ref rows Extra
1073
1 SIMPLE t1 index NULL c 8 NULL 3 Using index
1074
SELECT c,b FROM t1 GROUP BY c,b;
1080
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
1081
INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
1082
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1092
Extra Using where; Using index
1093
SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1097
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1107
Extra Using where; Using index
1108
SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1112
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
1123
SELECT * FROM t1 ORDER BY b ASC, a ASC;
1128
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
1139
SELECT * FROM t1 ORDER BY b DESC, a DESC;
1144
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
1154
Extra Using filesort
1155
SELECT * FROM t1 ORDER BY b ASC, a DESC;
1160
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
1170
Extra Using filesort
1171
SELECT * FROM t1 ORDER BY b DESC, a ASC;
1179
# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
1184
DROP TABLE IF EXISTS t1;
1186
CREATE TABLE t1(c INT)
1188
ROW_FORMAT = COMPACT;
1192
SELECT table_schema, table_name, row_format
1193
FROM INFORMATION_SCHEMA.TABLES
1194
WHERE table_schema = DATABASE() AND table_name = 't1';
1195
table_schema table_name row_format
1198
# - change ROW_FORMAT and check;
1200
ALTER TABLE t1 ROW_FORMAT = REDUNDANT;
1202
SELECT table_schema, table_name, row_format
1203
FROM INFORMATION_SCHEMA.TABLES
1204
WHERE table_schema = DATABASE() AND table_name = 't1';
1205
table_schema table_name row_format
1208
# - that's it, cleanup.
1211
create table t1(a char(10) not null, unique key aa(a(1)),
1212
b char(4) not null, unique key bb(b(4))) engine=innodb;
1214
Field Type Null Key Default Extra
1215
a varchar(10) NO UNI NULL
1216
b varchar(4) NO PRI NULL
1217
show create table t1;
1219
t1 CREATE TABLE `t1` (
1220
`a` varchar(10) NOT NULL,
1221
`b` varchar(4) NOT NULL,
1222
UNIQUE KEY `bb` (`b`),
1223
UNIQUE KEY `aa` (`a`())
1226
CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
1227
INSERT INTO t1 VALUES
1228
(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
1229
EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1230
id select_type table type possible_keys key key_len ref rows Extra
1231
1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort
1232
SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1238
set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
1239
set global innodb_autoextend_increment=8;
1240
set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
1241
set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;
1242
set global innodb_commit_concurrency=0;
1243
set global innodb_commit_concurrency=@my_innodb_commit_concurrency;
1246
`k` int NOT NULL auto_increment,
1247
`a` int default NULL,
1248
`c` int default NULL,
1250
UNIQUE KEY `idx_1` (`a`)
1252
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1255
insert into t2 ( a ) values ( 7 ) on duplicate key update c =
1258
select last_insert_id();
1265
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1268
select last_insert_id();
1271
select last_insert_id(0);
1274
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1277
select last_insert_id();
1284
insert ignore into t2 values (null,6,1),(10,8,1);
1285
select last_insert_id();
1288
insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
1289
select last_insert_id();
1299
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1301
0 ) + 1, k=last_insert_id(k);
1302
select last_insert_id();
1313
drop table if exists t1, t2;
1314
create table t1 (i int);
1315
alter table t1 modify i int default 1;
1316
alter table t1 modify i int default 2, rename t2;
1317
lock table t2 write;
1318
alter table t2 modify i int default 3;
1320
lock table t2 write;
1321
alter table t2 modify i int default 4, rename t1;
1324
drop table if exists t1;
1325
create table t1 (i int);
1326
insert into t1 values ();
1327
lock table t1 write;
1328
alter table t1 modify i int default 1;
1329
insert into t1 values ();
1334
alter table t1 change i c char(10) default "Two";
1335
insert into t1 values ();
1348
create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
1349
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
1350
insert into t1(f1) values(1);
1351
select @a:=f2 from t1;
1355
select @b:=f2 from t1;
1358
select if(@a=@b,"ok","wrong");
1359
if(@a=@b,"ok","wrong")
1361
insert into t1(f1) values (1) on duplicate key update f1="1";
1362
select @b:=f2 from t1;
1365
select if(@a=@b,"ok","wrong");
1366
if(@a=@b,"ok","wrong")
1368
insert into t1(f1) select f1 from t1 on duplicate key update f1="1";
1369
select @b:=f2 from t1;
1372
select if(@a=@b,"ok","wrong");
1373
if(@a=@b,"ok","wrong")
1376
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
1377
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1378
CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1379
ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match
1380
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1381
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1382
ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
1383
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1384
CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1385
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1386
ALTER TABLE t2 DROP FOREIGN KEY c2;
1388
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1389
FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1390
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
1391
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1392
FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1393
ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match
1394
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1395
CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1396
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1397
FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1398
FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1399
SHOW CREATE TABLE t2;
1401
t2 CREATE TABLE `t2` (
1404
PRIMARY KEY (`c`,`d`),
1405
CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,
1406
CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
1407
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
1408
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
1412
create table t1 (a int auto_increment primary key) engine=innodb;
1413
alter table t1 order by a;
1414
ERROR HY000: order_st BY ignored because there is a user-defined clustered index in the table 't1'
1417
(vid integer NOT NULL,
1418
tid integer NOT NULL,
1419
idx integer NOT NULL,
1420
name varchar(128) NOT NULL,
1421
type varchar(128) NULL,
1422
PRIMARY KEY(idx, vid, tid),
1423
UNIQUE(vid, tid, name)
1425
INSERT INTO t1 VALUES
1426
(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
1427
(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
1428
(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
1429
(4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
1430
(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
1431
EXPLAIN SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1432
id select_type table type possible_keys key key_len ref rows Extra
1433
1 SIMPLE t1 index vid PRIMARY 12 NULL 16 Using where
1434
SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1435
vid tid idx name type
1441
DROP TABLE IF EXISTS t1;
1442
DROP TABLE IF EXISTS t2;
1443
CREATE TABLE t1(id INT PRIMARY KEY)
1446
t1_id INT PRIMARY KEY,
1447
CONSTRAINT fk1 FOREIGN KEY (t1_id) REFERENCES t1(id))
1450
ALTER TABLE t1 CHANGE id id2 INT;
added
removed
tests/r/innodb_mysql.result
