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,
147
a5 char(175) DEFAULT NULL,
148
a6 timestamp NOT NULL DEFAULT NOW(),
150
INDEX idx (a6,a7(239),a5)
152
EXPLAIN SELECT a4 FROM t1 WHERE
154
a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
155
id select_type table type possible_keys key key_len ref rows Extra
156
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
157
EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
158
t.a6=t.a6 AND t1.a6=NULL AND
159
t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
160
id select_type table type possible_keys key key_len ref rows Extra
161
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
163
create temporary table t1m (a int) engine = MEMORY;
164
create table t1i (a int);
165
create temporary table t2m (a int) engine = MEMORY;
166
create table t2i (a int);
167
insert into t2m values (5);
168
insert into t2i values (5);
169
select 1, min(a) from t1i where a=99;
172
select 1, min(a) from t1i where 1=99;
175
select 1, min(1) from t1i where a=99;
178
select 1, min(1) from t1i where 1=99;
181
select 1, max(a) from t1i where a=99;
184
select 1, max(a) from t1i where 1=99;
187
select 1, max(1) from t1i where a=99;
190
select 1, max(1) from t1i where 1=99;
193
explain select count(*), min(7), max(7) from t1m, t1i;
194
id select_type table type possible_keys key key_len ref rows Extra
195
1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
196
1 SIMPLE t1i ALL NULL NULL NULL NULL 1
197
select count(*), min(7), max(7) from t1m, t1i;
198
count(*) min(7) max(7)
200
explain select count(*), min(7), max(7) from t1m, t2i;
201
id select_type table type possible_keys key key_len ref rows Extra
202
1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
203
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
204
select count(*), min(7), max(7) from t1m, t2i;
205
count(*) min(7) max(7)
207
explain select count(*), min(7), max(7) from t2m, t1i;
208
id select_type table type possible_keys key key_len ref rows Extra
209
1 SIMPLE t2m system NULL NULL NULL NULL 1
210
1 SIMPLE t1i ALL NULL NULL NULL NULL 1
211
select count(*), min(7), max(7) from t2m, t1i;
212
count(*) min(7) max(7)
214
drop table t1m, t1i, t2m, t2i;
215
create TEMPORARY table t1 (
216
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
218
insert into t1 (a1, a2, b, c, d) values
219
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
220
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
221
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
222
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
223
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
224
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
225
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
226
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
227
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
228
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
229
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
230
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
231
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
232
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
233
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
234
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
235
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
236
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
237
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
238
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
239
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
240
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
241
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
242
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
243
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
244
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
245
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
246
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
247
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
248
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
249
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
250
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
252
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 ' '
254
insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
255
create index idx12672_0 on t4 (a1);
256
create index idx12672_1 on t4 (a1,a2,b,c);
257
create index idx12672_2 on t4 (a1,a2,b);
259
Table Op Msg_type Msg_text
260
test.t4 analyze status OK
261
select distinct a1 from t4 where pk_col not in (1,2,3,4);
268
DROP TABLE IF EXISTS t2, t1;
269
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB;
272
FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION
274
INSERT INTO t1 VALUES (1);
275
INSERT INTO t2 VALUES (1);
276
DELETE IGNORE FROM t1 WHERE i = 1;
278
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)
279
SELECT * FROM t1, t2;
285
a varchar(30), b varchar(30), primary key(a), key(b)
287
select distinct a from t1;
290
create table t1(a int, key(a));
291
insert into t1 values(1);
292
select a, count(a) from t1 group by a with rollup;
297
create table t1 (f1 int, f2 char(1), primary key(f1,f2));
298
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
299
alter table t1 drop primary key, add primary key (f2, f1);
300
explain select distinct f1 a, f1 b from t1;
301
id select_type table type possible_keys key key_len ref rows Extra
302
1 SIMPLE t1 index NULL PRIMARY 10 NULL 4 Using index; Using temporary
303
explain select distinct f1, f2 from t1;
304
id select_type table type possible_keys key key_len ref rows Extra
305
1 SIMPLE t1 range NULL PRIMARY 10 NULL 3 Using index for group-by; Using temporary
307
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, name varchar(20),
309
CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, fkey int);
310
ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
311
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
312
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
314
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
315
WHERE t1.name LIKE 'A%';
316
id select_type table type possible_keys key key_len ref rows Extra
317
1 SIMPLE t1 index PRIMARY,name PRIMARY 4 NULL 3 Using where
318
1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
320
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
321
WHERE t1.name LIKE 'A%' OR FALSE;
322
id select_type table type possible_keys key key_len ref rows Extra
323
1 SIMPLE t2 index NULL PRIMARY 4 NULL 5
324
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where
328
name varchar(20) NOT NULL,
329
dept varchar(20) NOT NULL,
334
INSERT INTO t1(id, dept, age, name) VALUES
335
(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
336
(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
337
(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
338
(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
339
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
340
id select_type table type possible_keys key key_len ref rows Extra
341
1 SIMPLE t1 range name name 164 NULL 2 Using where; Using index for group-by
342
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
347
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
348
id select_type table type possible_keys key key_len ref rows Extra
349
1 SIMPLE t1 range name name 164 NULL 2 Using where; Using index for group-by
350
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
353
drop table if exists t1;
354
show variables like 'innodb_rollback_on_timeout';
356
innodb_rollback_on_timeout OFF
357
create table t1 (a int not null primary key) engine = innodb;
358
insert into t1 values (1);
361
insert into t1 values (2);
367
insert into t1 values (5);
372
insert into t1 values (2);
373
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
390
drop table if exists `test`;
392
Note 1051 Unknown table 'test'
393
CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
394
`test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`))
396
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
400
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
401
ON DUPLICATE KEY UPDATE `test2` = '1234';
410
drop table if exists t1;
411
show variables like 'innodb_rollback_on_timeout';
413
innodb_rollback_on_timeout OFF
414
create table t1 (a int not null primary key) engine = innodb;
415
insert into t1 values (1);
418
insert into t1 values (2);
424
insert into t1 values (5);
429
insert into t1 values (2);
430
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
448
id int auto_increment,
450
counter int not null default 1,
454
insert into t1 (id, c) values
457
on duplicate key update id = values(id), counter = counter + 1;
461
insert into t1 (id, c) values
463
on duplicate key update id = values(id), counter = counter + 1;
469
insert into t1 (id, c) values (NULL, 'a');
473
insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
474
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
479
insert into t1 (id, c) values (NULL, 'a')
480
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
486
create table t1(a int) engine=innodb;
487
alter table t1 comment '123';
488
show create table t1;
490
t1 CREATE TABLE `t1` (
492
) ENGINE=InnoDB COMMENT='123'
494
CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
495
INSERT INTO t1 VALUES ('uk'),('bg');
496
SELECT * FROM t1 WHERE a = 'uk';
499
DELETE FROM t1 WHERE a = 'uk';
500
SELECT * FROM t1 WHERE a = 'uk';
502
UPDATE t1 SET a = 'us' WHERE a = 'uk';
503
SELECT * FROM t1 WHERE a = 'uk';
505
CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
506
INSERT INTO t2 VALUES ('uk'),('bg');
507
SELECT * FROM t2 WHERE a = 'uk';
510
DELETE FROM t2 WHERE a = 'uk';
511
SELECT * FROM t2 WHERE a = 'uk';
513
INSERT INTO t2 VALUES ('uk');
514
UPDATE t2 SET a = 'us' WHERE a = 'uk';
515
SELECT * FROM t2 WHERE a = 'uk';
517
CREATE TEMPORARY TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
518
INSERT INTO t3 VALUES ('uk'),('bg');
519
SELECT * FROM t3 WHERE a = 'uk';
522
DELETE FROM t3 WHERE a = 'uk';
523
SELECT * FROM t3 WHERE a = 'uk';
525
INSERT INTO t3 VALUES ('uk');
526
UPDATE t3 SET a = 'us' WHERE a = 'uk';
527
SELECT * FROM t3 WHERE a = 'uk';
531
id int NOT NULL auto_increment PRIMARY KEY,
538
b int NOT NULL auto_increment PRIMARY KEY,
541
INSERT INTO t2(c) VALUES ('2007-01-01');
542
INSERT INTO t2(c) SELECT c FROM t2;
543
INSERT INTO t2(c) SELECT c FROM t2;
544
INSERT INTO t2(c) SELECT c FROM t2;
545
INSERT INTO t2(c) SELECT c FROM t2;
546
INSERT INTO t2(c) SELECT c FROM t2;
547
INSERT INTO t2(c) SELECT c FROM t2;
548
INSERT INTO t2(c) SELECT c FROM t2;
549
INSERT INTO t2(c) SELECT c FROM t2;
550
INSERT INTO t2(c) SELECT c FROM t2;
551
INSERT INTO t2(c) SELECT c FROM t2;
552
INSERT INTO t1(b,c) SELECT b,c FROM t2;
553
UPDATE t2 SET c='2007-01-02';
554
INSERT INTO t1(b,c) SELECT b,c FROM t2;
555
UPDATE t2 SET c='2007-01-03';
556
INSERT INTO t1(b,c) SELECT b,c FROM t2;
557
set @@sort_buffer_size=8192;
559
Error 1292 Truncated incorrect sort_buffer_size value: '8192'
560
SELECT COUNT(*) FROM t1;
564
SELECT COUNT(*) FROM t1
565
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
566
id select_type table type possible_keys key key_len ref rows Extra
567
1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where
568
SELECT COUNT(*) FROM t1
569
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
573
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
574
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
575
id select_type table type possible_keys key key_len ref rows Extra
576
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
577
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
578
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
581
set @@sort_buffer_size=default;
583
CREATE TABLE t1 (a int, b int);
584
insert into t1 values (1,1),(1,2);
585
CREATE TABLE t2 (primary key (a)) select * from t1;
586
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
587
drop table if exists t2;
589
Note 1051 Unknown table 't2'
590
CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
591
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
592
drop table if exists t2;
594
Note 1051 Unknown table 't2'
595
CREATE TABLE t2 (a int, b int, primary key (a));
597
INSERT INTO t2 values(100,100);
598
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
599
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
608
INSERT INTO t2 select * from t1;
609
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
613
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
615
INSERT INTO t2 values(100,100);
616
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
617
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
623
INSERT INTO t2 values(101,101);
624
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
625
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
635
INSERT INTO t2 select * from t1;
636
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
640
create table t1(f1 varchar(800) not null, key(f1));
642
Warning 1071 Specified key was too long; max key length is 767 bytes
643
insert into t1 values('aaa');
645
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
646
INSERT INTO t1 VALUES ( 1 , 1 , 1);
647
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
648
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
649
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
650
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
651
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
652
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
653
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
654
EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
655
id select_type table type possible_keys key key_len ref rows Extra
656
1 SIMPLE t1 index NULL b 5 NULL 128
657
EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
658
id select_type table type possible_keys key key_len ref rows Extra
659
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
661
drop table if exists t1;
662
show variables like 'innodb_rollback_on_timeout';
664
innodb_rollback_on_timeout OFF
665
create table t1 (a int not null primary key) engine = innodb;
666
insert into t1 values (1);
669
insert into t1 values (2);
675
insert into t1 values (5);
680
insert into t1 values (2);
681
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
698
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
699
INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
700
INSERT INTO t1 SELECT a + 8, 2 FROM t1;
701
INSERT INTO t1 SELECT a + 16, 1 FROM t1;
702
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
712
Extra Using where; Using index
713
SELECT * FROM t1 WHERE b=2 ORDER BY a;
731
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
742
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
776
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
786
Extra Using where; Using index
787
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
821
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
823
INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
824
INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
825
INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
826
EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
836
Extra Using where; Using index
837
SELECT * FROM t2 WHERE b=1 ORDER BY a;
855
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
865
Extra Using where; Using index
866
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
884
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
894
Extra Using where; Using index
895
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
913
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
923
Extra Using where; Using index
924
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
943
create table t1(a text) engine=innodb;
944
insert into t1 values('aaa');
945
alter table t1 add index(a(1024));
947
Warning 1071 Specified key was too long; max key length is 767 bytes
948
show create table t1;
950
t1 CREATE TABLE `t1` (
960
INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
962
SELECT * FROM t1 WHERE b=20 FOR UPDATE;
966
SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
980
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
981
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
982
id select_type table type possible_keys key key_len ref rows Extra
983
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
984
SELECT c,b,d FROM t1 GROUP BY c,b,d;
989
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
990
id select_type table type possible_keys key key_len ref rows Extra
991
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
992
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
997
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
998
id select_type table type possible_keys key key_len ref rows Extra
999
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1000
SELECT c,b,d FROM t1 ORDER BY c,b,d;
1005
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
1006
id select_type table type possible_keys key key_len ref rows Extra
1007
1 SIMPLE t1 index NULL c 8 NULL 3
1008
SELECT c,b,d FROM t1 GROUP BY c,b;
1013
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
1014
id select_type table type possible_keys key key_len ref rows Extra
1015
1 SIMPLE t1 index NULL c 8 NULL 3 Using index
1016
SELECT c,b FROM t1 GROUP BY c,b;
1022
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
1023
INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
1024
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1034
Extra Using where; Using index
1035
SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1039
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1049
Extra Using where; Using index
1050
SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1054
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
1065
SELECT * FROM t1 ORDER BY b ASC, a ASC;
1070
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
1081
SELECT * FROM t1 ORDER BY b DESC, a DESC;
1086
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
1096
Extra Using filesort
1097
SELECT * FROM t1 ORDER BY b ASC, a DESC;
1102
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
1112
Extra Using filesort
1113
SELECT * FROM t1 ORDER BY b DESC, a ASC;
1121
# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
1126
DROP TABLE IF EXISTS t1;
1128
CREATE TABLE t1(c INT)
1130
ROW_FORMAT = COMPACT;
1134
SELECT table_schema, table_name, row_format
1135
FROM data_dictionary.TABLES
1136
WHERE table_schema = DATABASE() AND table_name = 't1';
1137
table_schema table_name row_format
1140
# - change ROW_FORMAT and check;
1142
ALTER TABLE t1 ROW_FORMAT = REDUNDANT;
1144
SELECT table_schema, table_name, row_format
1145
FROM data_dictionary.TABLES
1146
WHERE table_schema = DATABASE() AND table_name = 't1';
1147
table_schema table_name row_format
1150
# - that's it, cleanup.
1153
create table t1(a char(10) not null, unique key aa(a(1)),
1154
b char(4) not null, unique key bb(b(4))) engine=innodb;
1156
Field Type Null Default Default_is_NULL On_Update
1157
a VARCHAR FALSE FALSE
1158
b VARCHAR FALSE FALSE
1159
show create table t1;
1161
t1 CREATE TABLE `t1` (
1162
`a` varchar(10) NOT NULL,
1163
`b` varchar(4) NOT NULL,
1164
UNIQUE KEY `bb` (`b`),
1165
UNIQUE KEY `aa` (`a`(1))
1168
CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
1169
INSERT INTO t1 VALUES
1170
(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
1171
EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1172
id select_type table type possible_keys key key_len ref rows Extra
1173
1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort
1174
SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1180
set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
1181
set global innodb_autoextend_increment=8;
1182
set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
1183
set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;
1184
set global innodb_commit_concurrency=0;
1185
set global innodb_commit_concurrency=@my_innodb_commit_concurrency;
1188
`k` int NOT NULL auto_increment,
1189
`a` int default NULL,
1190
`c` int default NULL,
1192
UNIQUE KEY `idx_1` (`a`)
1194
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1197
insert into t2 ( a ) values ( 7 ) on duplicate key update c =
1200
select last_insert_id();
1207
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1210
select last_insert_id();
1213
select last_insert_id(0);
1216
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1219
select last_insert_id();
1226
insert ignore into t2 values (null,6,1),(10,8,1);
1227
select last_insert_id();
1230
insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
1231
select last_insert_id();
1241
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1243
0 ) + 1, k=last_insert_id(k);
1244
select last_insert_id();
1255
create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
1256
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
1257
insert into t1(f1) values(1);
1258
select @a:=f2 from t1;
1262
select @b:=f2 from t1;
1265
select if(@a=@b,"ok","wrong");
1266
if(@a=@b,"ok","wrong")
1268
insert into t1(f1) values (1) on duplicate key update f1="1";
1269
select @b:=f2 from t1;
1272
select if(@a=@b,"ok","wrong");
1273
if(@a=@b,"ok","wrong")
1275
insert into t1(f1) select f1 from t1 on duplicate key update f1="1";
1276
select @b:=f2 from t1;
1279
select if(@a=@b,"ok","wrong");
1280
if(@a=@b,"ok","wrong")
1283
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
1284
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1285
CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1286
ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match
1287
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1288
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1289
ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
1290
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1291
CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1292
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1293
ALTER TABLE t2 DROP FOREIGN KEY c2;
1295
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1296
FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1297
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
1298
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1299
FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1300
ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match
1301
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1302
CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1303
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1304
FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1305
FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1306
SHOW CREATE TABLE t2;
1308
t2 CREATE TABLE `t2` (
1311
PRIMARY KEY (`c`,`d`),
1312
CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,
1313
CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
1314
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
1315
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
1319
create table t1 (a int auto_increment primary key) engine=innodb;
1320
alter table t1 order by a;
1321
ERROR HY000: order_st BY ignored because there is a user-defined clustered index in the table 't1'
1324
(vid integer NOT NULL,
1325
tid integer NOT NULL,
1326
idx integer NOT NULL,
1327
name varchar(128) NOT NULL,
1328
type varchar(128) NULL,
1329
PRIMARY KEY(idx, vid, tid),
1330
UNIQUE(vid, tid, name)
1332
INSERT INTO t1 VALUES
1333
(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
1334
(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
1335
(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
1336
(4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
1337
(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
1338
EXPLAIN SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1339
id select_type table type possible_keys key key_len ref rows Extra
1340
1 SIMPLE t1 index vid PRIMARY 12 NULL 16 Using where
1341
SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1342
vid tid idx name type
1348
DROP TABLE IF EXISTS t1;
1349
DROP TABLE IF EXISTS t2;
1350
CREATE TABLE t1(id INT PRIMARY KEY)
1353
t1_id INT PRIMARY KEY,
1354
CONSTRAINT fk1 FOREIGN KEY (t1_id) REFERENCES t1(id))
1357
ALTER TABLE t1 CHANGE id id2 INT;
added
removed
tests/r/innodb_mysql.result
