1
set global innodb_support_xa=default;
2
set session innodb_support_xa=default;
3
SET SESSION STORAGE_ENGINE = InnoDB;
4
SET @orig_lock_wait_timeout= @@innodb_lock_wait_timeout;
5
SET GLOBAL innodb_lock_wait_timeout=2;
6
drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4;
8
c_id int not null default '0',
9
org_id int default null,
10
unique key contacts$c_id (c_id),
11
key contacts$org_id (org_id)
14
(2,null),(120,null),(141,null),(218,7), (128,1),
15
(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
16
(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
18
slai_id int not null default '0',
19
owner_tbl int default null,
20
owner_id int default null,
21
sla_id int default null,
22
inc_web int default null,
23
inc_email int default null,
24
inc_chat int default null,
25
inc_csr int default null,
26
inc_total int default null,
27
time_billed int default null,
28
activedate timestamp null default null,
29
expiredate timestamp null default null,
30
state int default null,
31
sla_set int default null,
32
unique key t2$slai_id (slai_id),
33
key t2$owner_id (owner_id),
34
key t2$sla_id (sla_id)
36
insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
37
(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
38
(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
41
from t1 c join t2 si on
42
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
43
( si.owner_tbl = 2 and si.owner_id = c.c_id))
45
c.c_id = 218 and expiredate is null;
48
select * from t1 where org_id is null;
54
from t1 c join t2 si on
55
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
56
( si.owner_tbl = 2 and si.owner_id = c.c_id))
58
c.c_id = 218 and expiredate is null;
62
CREATE TABLE t1 (a int, b int, KEY b (b));
63
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b));
64
CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
65
UNIQUE KEY b (b,c), KEY a (a,b,c));
66
INSERT INTO t1 VALUES (1, 1);
67
INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
68
INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
69
INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
70
INSERT INTO t2 SELECT a + 1, b FROM t2;
71
DELETE FROM t2 WHERE a = 1 AND b < 2;
72
INSERT INTO t3 VALUES (1,1,1),(2,1,2);
73
INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3;
74
INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3;
75
SELECT STRAIGHT_JOIN t1.b, t1.a FROM t1, t3, t2 WHERE
76
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
77
ORDER BY t1.b LIMIT 2;
81
SELECT STRAIGHT_JOIN t1.b, t1.a FROM t1, t3, t2 WHERE
82
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
83
ORDER BY t1.b LIMIT 5;
90
DROP TABLE t1, t2, t3;
91
CREATE TABLE `t1` (`id1` INT) ;
92
INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
101
INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
108
SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
112
CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
114
INSERT INTO t1 (c1) VALUES ('1a');
118
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
123
CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
125
INSERT INTO t1 (c1) VALUES ('1a');
129
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
134
CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
136
INSERT INTO t1 (c1) VALUES ('1a');
140
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
146
a1 decimal(10,0) DEFAULT NULL,
149
a5 char(175) DEFAULT NULL,
150
a6 timestamp NOT NULL DEFAULT NOW(),
152
INDEX idx (a6,a7(239),a5)
154
EXPLAIN SELECT a4 FROM t1 WHERE
156
a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
157
id select_type table type possible_keys key key_len ref rows Extra
158
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
159
EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
160
t.a6=t.a6 AND t1.a6=NULL AND
161
t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
162
id select_type table type possible_keys key key_len ref rows Extra
163
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
165
create temporary table t1m (a int) engine = MEMORY;
166
create table t1i (a int);
167
create temporary table t2m (a int) engine = MEMORY;
168
create table t2i (a int);
169
insert into t2m values (5);
170
insert into t2i values (5);
171
select 1, min(a) from t1i where a=99;
174
select 1, min(a) from t1i where 1=99;
177
select 1, min(1) from t1i where a=99;
180
select 1, min(1) from t1i where 1=99;
183
select 1, max(a) from t1i where a=99;
186
select 1, max(a) from t1i where 1=99;
189
select 1, max(1) from t1i where a=99;
192
select 1, max(1) from t1i where 1=99;
195
explain select count(*), min(7), max(7) from t1m, t1i;
196
id select_type table type possible_keys key key_len ref rows Extra
197
1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
198
1 SIMPLE t1i ALL NULL NULL NULL NULL 1
199
select count(*), min(7), max(7) from t1m, t1i;
200
count(*) min(7) max(7)
202
explain select count(*), min(7), max(7) from t1m, t2i;
203
id select_type table type possible_keys key key_len ref rows Extra
204
1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
205
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
206
select count(*), min(7), max(7) from t1m, t2i;
207
count(*) min(7) max(7)
209
explain select count(*), min(7), max(7) from t2m, t1i;
210
id select_type table type possible_keys key key_len ref rows Extra
211
1 SIMPLE t2m system NULL NULL NULL NULL 1
212
1 SIMPLE t1i ALL NULL NULL NULL NULL 1
213
select count(*), min(7), max(7) from t2m, t1i;
214
count(*) min(7) max(7)
216
drop table t1m, t1i, t2m, t2i;
217
create TEMPORARY table t1 (
218
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
220
insert into t1 (a1, a2, b, c, d) values
221
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
222
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
223
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
224
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
225
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
226
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
227
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
228
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
229
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
230
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
231
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
232
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
233
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
234
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
235
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
236
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
237
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
238
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
239
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
240
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
241
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
242
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
243
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
244
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
245
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
246
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
247
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
248
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
249
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
250
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
251
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
252
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
254
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 ' '
256
insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
257
create index idx12672_0 on t4 (a1);
258
create index idx12672_1 on t4 (a1,a2,b,c);
259
create index idx12672_2 on t4 (a1,a2,b);
261
Table Op Msg_type Msg_text
262
test.t4 analyze status OK
263
select distinct a1 from t4 where pk_col not in (1,2,3,4);
270
DROP TABLE IF EXISTS t2, t1;
271
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB;
274
FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION
276
INSERT INTO t1 VALUES (1);
277
INSERT INTO t2 VALUES (1);
278
DELETE IGNORE FROM t1 WHERE i = 1;
280
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)
281
SELECT * FROM t1, t2;
287
a varchar(30), b varchar(30), primary key(a), key(b)
289
select distinct a from t1;
292
create table t1(a int, key(a));
293
insert into t1 values(1);
294
select a, count(a) from t1 group by a with rollup;
299
create table t1 (f1 int, f2 char(1), primary key(f1,f2));
300
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
301
alter table t1 drop primary key, add primary key (f2, f1);
302
explain select distinct f1 a, f1 b from t1;
303
id select_type table type possible_keys key key_len ref rows Extra
304
1 SIMPLE t1 index NULL PRIMARY 10 NULL 4 Using index; Using temporary
305
explain select distinct f1, f2 from t1;
306
id select_type table type possible_keys key key_len ref rows Extra
307
1 SIMPLE t1 range NULL PRIMARY 10 NULL 3 Using index for group-by; Using temporary
309
CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, name varchar(20),
311
CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, fkey int);
312
ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
313
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
314
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
316
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
317
WHERE t1.name LIKE 'A%';
318
id select_type table type possible_keys key key_len ref rows Extra
319
1 SIMPLE t1 index PRIMARY,name PRIMARY 4 NULL 3 Using where
320
1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index
322
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
323
WHERE t1.name LIKE 'A%' OR FALSE;
324
id select_type table type possible_keys key key_len ref rows Extra
325
1 SIMPLE t2 index NULL PRIMARY 4 NULL 5
326
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where
330
name varchar(20) NOT NULL,
331
dept varchar(20) NOT NULL,
336
INSERT INTO t1(id, dept, age, name) VALUES
337
(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
338
(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
339
(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
340
(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
341
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
342
id select_type table type possible_keys key key_len ref rows Extra
343
1 SIMPLE t1 range name name 164 NULL 2 Using where; Using index for group-by
344
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
349
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
350
id select_type table type possible_keys key key_len ref rows Extra
351
1 SIMPLE t1 range name name 164 NULL 2 Using where; Using index for group-by
352
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
355
drop table if exists t1;
356
create table t1 (a int not null primary key) engine = innodb;
357
insert into t1 values (1);
360
insert into t1 values (2);
366
insert into t1 values (5);
371
insert into t1 values (2);
372
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
389
drop table if exists `test`;
391
Note 1051 Unknown table 'test'
392
CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
393
`test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`))
395
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
399
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
400
ON DUPLICATE KEY UPDATE `test2` = '1234';
409
drop table if exists t1;
410
create table t1 (a int not null primary key) engine = innodb;
411
insert into t1 values (1);
414
insert into t1 values (2);
420
insert into t1 values (5);
425
insert into t1 values (2);
426
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
444
id int auto_increment,
446
counter int not null default 1,
450
insert into t1 (id, c) values
453
on duplicate key update id = values(id), counter = counter + 1;
457
insert into t1 (id, c) values
459
on duplicate key update id = values(id), counter = counter + 1;
465
insert into t1 (id, c) values (NULL, 'a');
469
insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
470
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
475
insert into t1 (id, c) values (NULL, 'a')
476
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
482
create table t1(a int) engine=innodb;
483
alter table t1 comment='123';
484
show create table t1;
486
t1 CREATE TABLE `t1` (
488
) ENGINE=InnoDB COMMENT='123' COLLATE = utf8_general_ci
490
CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
491
INSERT INTO t1 VALUES ('uk'),('bg');
492
SELECT * FROM t1 WHERE a = 'uk';
495
DELETE FROM t1 WHERE a = 'uk';
496
SELECT * FROM t1 WHERE a = 'uk';
498
UPDATE t1 SET a = 'us' WHERE a = 'uk';
499
SELECT * FROM t1 WHERE a = 'uk';
501
CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
502
INSERT INTO t2 VALUES ('uk'),('bg');
503
SELECT * FROM t2 WHERE a = 'uk';
506
DELETE FROM t2 WHERE a = 'uk';
507
SELECT * FROM t2 WHERE a = 'uk';
509
INSERT INTO t2 VALUES ('uk');
510
UPDATE t2 SET a = 'us' WHERE a = 'uk';
511
SELECT * FROM t2 WHERE a = 'uk';
513
CREATE TEMPORARY TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
514
INSERT INTO t3 VALUES ('uk'),('bg');
515
SELECT * FROM t3 WHERE a = 'uk';
518
DELETE FROM t3 WHERE a = 'uk';
519
SELECT * FROM t3 WHERE a = 'uk';
521
INSERT INTO t3 VALUES ('uk');
522
UPDATE t3 SET a = 'us' WHERE a = 'uk';
523
SELECT * FROM t3 WHERE a = 'uk';
527
id int NOT NULL auto_increment PRIMARY KEY,
534
b int NOT NULL auto_increment PRIMARY KEY,
537
INSERT INTO t2(c) VALUES ('2007-01-01');
538
INSERT INTO t2(c) SELECT c FROM t2;
539
INSERT INTO t2(c) SELECT c FROM t2;
540
INSERT INTO t2(c) SELECT c FROM t2;
541
INSERT INTO t2(c) SELECT c FROM t2;
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 t1(b,c) SELECT b,c FROM t2;
549
UPDATE t2 SET c='2007-01-02';
550
INSERT INTO t1(b,c) SELECT b,c FROM t2;
551
UPDATE t2 SET c='2007-01-03';
552
INSERT INTO t1(b,c) SELECT b,c FROM t2;
553
set @@sort_buffer_size=8192;
555
Error 1292 Truncated incorrect sort_buffer_size value: '8192'
556
SELECT COUNT(*) FROM t1;
560
SELECT COUNT(*) FROM t1
561
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
562
id select_type table type possible_keys key key_len ref rows Extra
563
1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where
564
SELECT COUNT(*) FROM t1
565
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
569
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
570
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
571
id select_type table type possible_keys key key_len ref rows Extra
572
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
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;
577
set @@sort_buffer_size=default;
579
CREATE TABLE t1 (a int, b int);
580
insert into t1 values (1,1),(1,2);
581
CREATE TABLE t2 (primary key (a)) select * from t1;
582
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
583
drop table if exists t2;
585
Note 1051 Unknown table 't2'
586
CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
587
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
588
drop table if exists t2;
590
Note 1051 Unknown table 't2'
591
CREATE TABLE t2 (a int, b int, primary key (a));
593
INSERT INTO t2 values(100,100);
594
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
595
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
604
INSERT INTO t2 select * from t1;
605
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
609
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
611
INSERT INTO t2 values(100,100);
612
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
613
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
619
INSERT INTO t2 values(101,101);
620
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
621
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
631
INSERT INTO t2 select * from t1;
632
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
636
create table t1(f1 varchar(800) not null, key(f1));
638
Warning 1071 Specified key was too long; max key length is 1023 bytes
639
insert into t1 values('aaa');
641
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
642
INSERT INTO t1 VALUES ( 1 , 1 , 1);
643
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
644
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
645
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
646
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
647
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
648
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
649
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
650
EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
651
id select_type table type possible_keys key key_len ref rows Extra
652
1 SIMPLE t1 index NULL b 5 NULL 128
653
EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
654
id select_type table type possible_keys key key_len ref rows Extra
655
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
657
drop table if exists t1;
658
create table t1 (a int not null primary key) engine = innodb;
659
insert into t1 values (1);
662
insert into t1 values (2);
668
insert into t1 values (5);
673
insert into t1 values (2);
674
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
691
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
692
INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
693
INSERT INTO t1 SELECT a + 8, 2 FROM t1;
694
INSERT INTO t1 SELECT a + 16, 1 FROM t1;
695
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
705
Extra Using where; Using index
706
SELECT * FROM t1 WHERE b=2 ORDER BY a;
724
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
735
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
769
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
779
Extra Using where; Using index
780
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
814
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
816
INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
817
INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
818
INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
819
EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
829
Extra Using where; Using index
830
SELECT * FROM t2 WHERE b=1 ORDER BY a;
848
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
858
Extra Using where; Using index
859
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
877
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
887
Extra Using where; Using index
888
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
906
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
916
Extra Using where; Using index
917
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
936
create table t1(a text) engine=innodb;
937
insert into t1 values('aaa');
938
alter table t1 add index(a(1024));
940
Warning 1071 Specified key was too long; max key length is 1023 bytes
941
show create table t1;
943
t1 CREATE TABLE `t1` (
944
`a` TEXT COLLATE utf8_general_ci,
946
) ENGINE=InnoDB COLLATE = utf8_general_ci
953
INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
955
SELECT * FROM t1 WHERE b=20 FOR UPDATE;
959
SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
973
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
974
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
975
id select_type table type possible_keys key key_len ref rows Extra
976
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
977
SELECT c,b,d FROM t1 GROUP BY c,b,d;
982
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
983
id select_type table type possible_keys key key_len ref rows Extra
984
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
985
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
990
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
991
id select_type table type possible_keys key key_len ref rows Extra
992
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
993
SELECT c,b,d FROM t1 ORDER BY c,b,d;
998
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
999
id select_type table type possible_keys key key_len ref rows Extra
1000
1 SIMPLE t1 index NULL c 8 NULL 3
1001
SELECT c,b,d FROM t1 GROUP BY c,b;
1006
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
1007
id select_type table type possible_keys key key_len ref rows Extra
1008
1 SIMPLE t1 index NULL c 8 NULL 3 Using index
1009
SELECT c,b FROM t1 GROUP BY c,b;
1015
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
1016
INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
1017
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1027
Extra Using where; Using index
1028
SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1032
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1042
Extra Using where; Using index
1043
SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1047
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
1058
SELECT * FROM t1 ORDER BY b ASC, a ASC;
1063
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
1074
SELECT * FROM t1 ORDER BY b DESC, a DESC;
1079
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
1089
Extra Using filesort
1090
SELECT * FROM t1 ORDER BY b ASC, a DESC;
1095
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
1105
Extra Using filesort
1106
SELECT * FROM t1 ORDER BY b DESC, a ASC;
1114
# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
1119
DROP TABLE IF EXISTS t1;
1121
CREATE TABLE t1(c INT)
1123
ROW_FORMAT = COMPACT;
1127
SELECT table_schema, table_name, row_format
1128
FROM data_dictionary.TABLES
1129
WHERE table_schema = DATABASE() AND table_name = 't1';
1130
table_schema table_name row_format
1133
# - change ROW_FORMAT and check;
1135
ALTER TABLE t1 ROW_FORMAT = REDUNDANT;
1137
SELECT table_schema, table_name, row_format
1138
FROM data_dictionary.TABLES
1139
WHERE table_schema = DATABASE() AND table_name = 't1';
1140
table_schema table_name row_format
1143
# - that's it, cleanup.
1146
create table t1(a char(10) not null, unique key aa(a(1)),
1147
b char(4) not null, unique key bb(b(4))) engine=innodb;
1149
Field Type Null Default Default_is_NULL On_Update
1152
show create table t1;
1154
t1 CREATE TABLE `t1` (
1155
`a` VARCHAR(10) COLLATE utf8_general_ci NOT NULL,
1156
`b` VARCHAR(4) COLLATE utf8_general_ci NOT NULL,
1157
UNIQUE KEY `bb` (`b`),
1158
UNIQUE KEY `aa` (`a`(1))
1159
) ENGINE=InnoDB COLLATE = utf8_general_ci
1161
CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
1162
INSERT INTO t1 VALUES
1163
(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
1164
EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1165
id select_type table type possible_keys key key_len ref rows Extra
1166
1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort
1167
SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1173
set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
1174
set global innodb_autoextend_increment=8;
1175
set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
1176
set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;
1177
set global innodb_commit_concurrency=0;
1178
set global innodb_commit_concurrency=@my_innodb_commit_concurrency;
1181
`k` int NOT NULL auto_increment,
1182
`a` int default NULL,
1183
`c` int default NULL,
1185
UNIQUE KEY `idx_1` (`a`)
1187
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1190
insert into t2 ( a ) values ( 7 ) on duplicate key update c =
1193
select last_insert_id();
1200
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1203
select last_insert_id();
1206
select last_insert_id(0);
1209
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1212
select last_insert_id();
1219
insert ignore into t2 values (null,6,1),(10,8,1);
1220
select last_insert_id();
1223
insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
1224
select last_insert_id();
1234
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1236
0 ) + 1, k=last_insert_id(k);
1237
select last_insert_id();
1248
create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
1249
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
1250
insert into t1(f1) values(1);
1251
select @a:=f2 from t1;
1255
select @b:=f2 from t1;
1258
select if(@a=@b,"ok","wrong");
1259
if(@a=@b,"ok","wrong")
1261
insert into t1(f1) values (1) on duplicate key update f1="1";
1262
select @b:=f2 from t1;
1265
select if(@a=@b,"ok","wrong");
1266
if(@a=@b,"ok","wrong")
1268
insert into t1(f1) select f1 from t1 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")
1276
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
1277
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1278
CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1279
ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
1280
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1281
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1282
ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
1283
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1284
CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1285
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1286
ALTER TABLE t2 DROP FOREIGN KEY c2;
1288
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1289
FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1290
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
1291
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1292
FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1293
ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match
1294
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1295
CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1296
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1297
FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1298
FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1299
SHOW CREATE TABLE t2;
1301
t2 CREATE TABLE `t2` (
1304
PRIMARY KEY (`c`,`d`),
1305
CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,
1306
CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
1307
CONSTRAINT `f3` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
1308
CONSTRAINT `t2_ibfk_4` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
1309
) ENGINE=InnoDB COLLATE = utf8_general_ci
1312
create table t1 (a int auto_increment primary key) engine=innodb;
1313
alter table t1 order by a;
1314
ERROR HY000: order_st BY ignored because there is a user-defined clustered index in the table 't1'
1317
(vid integer NOT NULL,
1318
tid integer NOT NULL,
1319
idx integer NOT NULL,
1320
name varchar(128) NOT NULL,
1321
type varchar(128) NULL,
1322
PRIMARY KEY(idx, vid, tid),
1323
UNIQUE(vid, tid, name)
1325
INSERT INTO t1 VALUES
1326
(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
1327
(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
1328
(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
1329
(4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
1330
(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
1331
EXPLAIN SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1332
id select_type table type possible_keys key key_len ref rows Extra
1333
1 SIMPLE t1 index vid PRIMARY 12 NULL 16 Using where
1334
SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1335
vid tid idx name type
1341
DROP TABLE IF EXISTS t1;
1342
DROP TABLE IF EXISTS t2;
1343
CREATE TABLE t1(id INT PRIMARY KEY)
1346
t1_id INT PRIMARY KEY,
1347
CONSTRAINT fk1 FOREIGN KEY (t1_id) REFERENCES t1(id))
1350
ALTER TABLE t1 CHANGE id id2 INT;
1354
SET innodb_lock_wait_timeout=@orig_lock_wait_timeout ;