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;
5
drop procedure if exists p1;
7
c_id int(11) not null default '0',
8
org_id int(11) default null,
9
unique key contacts$c_id (c_id),
10
key contacts$org_id (org_id)
13
(2,null),(120,null),(141,null),(218,7), (128,1),
14
(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
15
(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
17
slai_id int(11) not null default '0',
18
owner_tbl int(11) default null,
19
owner_id int(11) default null,
20
sla_id int(11) default null,
21
inc_web int(11) default null,
22
inc_email int(11) default null,
23
inc_chat int(11) default null,
24
inc_csr int(11) default null,
25
inc_total int(11) default null,
26
time_billed int(11) default null,
27
activedate timestamp null default null,
28
expiredate timestamp null default null,
29
state int(11) default null,
30
sla_set int(11) default null,
31
unique key t2$slai_id (slai_id),
32
key t2$owner_id (owner_id),
33
key t2$sla_id (sla_id)
35
insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
36
(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
37
(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
40
from t1 c join t2 si on
41
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
42
( si.owner_tbl = 2 and si.owner_id = c.c_id))
44
c.c_id = 218 and expiredate is null;
47
select * from t1 where org_id is null;
53
from t1 c join t2 si on
54
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
55
( si.owner_tbl = 2 and si.owner_id = c.c_id))
57
c.c_id = 218 and expiredate is null;
61
CREATE TABLE t1 (a int, b int, KEY b (b));
62
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b));
63
CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
64
UNIQUE KEY b (b,c), KEY a (a,b,c));
65
INSERT INTO t1 VALUES (1, 1);
66
INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
67
INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
68
INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
69
INSERT INTO t2 SELECT a + 1, b FROM t2;
70
DELETE FROM t2 WHERE a = 1 AND b < 2;
71
INSERT INTO t3 VALUES (1,1,1),(2,1,2);
72
INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3;
73
INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3;
74
SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
75
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
76
ORDER BY t1.b LIMIT 2;
80
SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
81
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
82
ORDER BY t1.b LIMIT 5;
89
DROP TABLE t1, t2, t3;
90
CREATE TABLE `t1` (`id1` INT) ;
91
INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
100
INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
107
SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
111
create table t1 (c1 int) engine=innodb;
113
handler t1 read first;
115
Before and after comparison
118
CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
119
ENGINE=INNODB CHARACTER SET UTF8;
120
INSERT INTO t1 (c1) VALUES ('1a');
124
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
129
CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
130
ENGINE=INNODB CHARACTER SET UTF8;
131
INSERT INTO t1 (c1) VALUES ('1a');
135
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
140
CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
141
ENGINE=INNODB CHARACTER SET UTF8;
142
INSERT INTO t1 (c1) VALUES ('1a');
146
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
152
a1 decimal(10,0) DEFAULT NULL,
154
a3 time DEFAULT NULL,
156
a5 char(175) DEFAULT NULL,
157
a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
159
INDEX idx (a6,a7(239),a5)
161
EXPLAIN SELECT a4 FROM t1 WHERE
163
a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
164
id select_type table type possible_keys key key_len ref rows Extra
165
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
166
EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
167
t.a6=t.a6 AND t1.a6=NULL AND
168
t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
169
id select_type table type possible_keys key key_len ref rows Extra
170
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
172
create table t1m (a int) engine = MEMORY;
173
create table t1i (a int);
174
create table t2m (a int) engine = MEMORY;
175
create table t2i (a int);
176
insert into t2m values (5);
177
insert into t2i values (5);
178
select min(a) from t1i;
181
select min(7) from t1i;
184
select min(7) from DUAL;
187
explain select min(7) from t2i join t1i;
188
id select_type table type possible_keys key key_len ref rows Extra
189
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
190
1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer
191
select min(7) from t2i join t1i;
194
select max(a) from t1i;
197
select max(7) from t1i;
200
select max(7) from DUAL;
203
explain select max(7) from t2i join t1i;
204
id select_type table type possible_keys key key_len ref rows Extra
205
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
206
1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer
207
select max(7) from t2i join t1i;
210
select 1, min(a) from t1i where a=99;
213
select 1, min(a) from t1i where 1=99;
216
select 1, min(1) from t1i where a=99;
219
select 1, min(1) from t1i where 1=99;
222
select 1, max(a) from t1i where a=99;
225
select 1, max(a) from t1i where 1=99;
228
select 1, max(1) from t1i where a=99;
231
select 1, max(1) from t1i where 1=99;
234
explain select count(*), min(7), max(7) from t1m, t1i;
235
id select_type table type possible_keys key key_len ref rows Extra
236
1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
237
1 SIMPLE t1i ALL NULL NULL NULL NULL 1
238
select count(*), min(7), max(7) from t1m, t1i;
239
count(*) min(7) max(7)
241
explain select count(*), min(7), max(7) from t1m, t2i;
242
id select_type table type possible_keys key key_len ref rows Extra
243
1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
244
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
245
select count(*), min(7), max(7) from t1m, t2i;
246
count(*) min(7) max(7)
248
explain select count(*), min(7), max(7) from t2m, t1i;
249
id select_type table type possible_keys key key_len ref rows Extra
250
1 SIMPLE t2m system NULL NULL NULL NULL 1
251
1 SIMPLE t1i ALL NULL NULL NULL NULL 1
252
select count(*), min(7), max(7) from t2m, t1i;
253
count(*) min(7) max(7)
255
drop table t1m, t1i, t2m, t2i;
257
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
259
insert into t1 (a1, a2, b, c, d) values
260
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
261
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
262
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
263
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
264
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
265
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
266
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
267
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
268
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
269
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
270
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
271
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
272
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
273
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
274
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
275
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
276
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
277
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
278
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
279
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
280
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
281
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
282
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
283
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
284
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
285
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
286
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
287
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
288
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
289
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
290
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
291
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
293
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 ' '
295
insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
296
create index idx12672_0 on t4 (a1);
297
create index idx12672_1 on t4 (a1,a2,b,c);
298
create index idx12672_2 on t4 (a1,a2,b);
300
Table Op Msg_type Msg_text
301
test.t4 analyze status OK
302
select distinct a1 from t4 where pk_col not in (1,2,3,4);
309
DROP TABLE IF EXISTS t2, t1;
310
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB;
313
FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION
315
INSERT INTO t1 VALUES (1);
316
INSERT INTO t2 VALUES (1);
317
DELETE IGNORE FROM t1 WHERE i = 1;
319
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)
320
SELECT * FROM t1, t2;
326
a varchar(30), b varchar(30), primary key(a), key(b)
328
select distinct a from t1;
331
create table t1(a int, key(a));
332
insert into t1 values(1);
333
select a, count(a) from t1 group by a with rollup;
338
create table t1 (f1 int, f2 char(1), primary key(f1,f2));
339
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
340
alter table t1 drop primary key, add primary key (f2, f1);
341
explain select distinct f1 a, f1 b from t1;
342
id select_type table type possible_keys key key_len ref rows Extra
343
1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index; Using temporary
344
explain select distinct f1, f2 from t1;
345
id select_type table type possible_keys key key_len ref rows Extra
346
1 SIMPLE t1 range NULL PRIMARY 5 NULL 3 Using index for group-by; Using temporary
348
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
350
CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11));
351
ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
352
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
353
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
355
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
356
WHERE t1.name LIKE 'A%';
357
id select_type table type possible_keys key key_len ref rows Extra
358
1 SIMPLE t1 index PRIMARY,name PRIMARY 4 NULL 3 Using where
359
1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
361
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
362
WHERE t1.name LIKE 'A%' OR FALSE;
363
id select_type table type possible_keys key key_len ref rows Extra
364
1 SIMPLE t2 index NULL PRIMARY 4 NULL 5
365
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where
369
name varchar(20) NOT NULL,
370
dept varchar(20) NOT NULL,
371
age tinyint(3) unsigned NOT NULL,
375
INSERT INTO t1(id, dept, age, name) VALUES
376
(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
377
(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
378
(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
379
(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
380
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
381
id select_type table type possible_keys key key_len ref rows Extra
382
1 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by
383
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
388
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
389
id select_type table type possible_keys key key_len ref rows Extra
390
1 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by
391
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
394
drop table if exists t1;
395
show variables like 'innodb_rollback_on_timeout';
397
innodb_rollback_on_timeout OFF
398
create table t1 (a int unsigned not null primary key) engine = innodb;
399
insert into t1 values (1);
402
insert into t1 values (2);
408
insert into t1 values (5);
413
insert into t1 values (2);
414
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
431
set @save_qcache_size=@@global.query_cache_size;
432
set @save_qcache_type=@@global.query_cache_type;
433
set global query_cache_size=10*1024*1024;
434
set global query_cache_type=1;
435
drop table if exists `test`;
437
Note 1051 Unknown table 'test'
438
CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
439
`test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`))
440
ENGINE=InnoDB DEFAULT CHARSET=latin1;
441
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
445
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
446
ON DUPLICATE KEY UPDATE `test2` = '1234';
455
set global query_cache_type=@save_qcache_type;
456
set global query_cache_size=@save_qcache_size;
457
drop table if exists t1;
458
show variables like 'innodb_rollback_on_timeout';
460
innodb_rollback_on_timeout OFF
461
create table t1 (a int unsigned not null primary key) engine = innodb;
462
insert into t1 values (1);
465
insert into t1 values (2);
471
insert into t1 values (5);
476
insert into t1 values (2);
477
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
495
id int auto_increment,
497
counter int not null default 1,
501
insert into t1 (id, c) values
504
on duplicate key update id = values(id), counter = counter + 1;
508
insert into t1 (id, c) values
510
on duplicate key update id = values(id), counter = counter + 1;
516
insert into t1 (id, c) values (NULL, 'a');
520
insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
521
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
526
insert into t1 (id, c) values (NULL, 'a')
527
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
534
id int AUTO_INCREMENT PRIMARY KEY,
535
stat_id int NOT NULL,
536
acct_id int DEFAULT NULL,
537
INDEX idx1 (stat_id, acct_id),
541
id int AUTO_INCREMENT PRIMARY KEY,
542
stat_id int NOT NULL,
543
acct_id int DEFAULT NULL,
544
INDEX idx1 (stat_id, acct_id),
547
INSERT INTO t1(stat_id,acct_id) VALUES
548
(1,759), (2,831), (3,785), (4,854), (1,921),
549
(1,553), (2,589), (3,743), (2,827), (2,545),
550
(4,779), (4,783), (1,597), (1,785), (4,832),
551
(1,741), (1,833), (3,788), (2,973), (1,907);
552
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
553
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
554
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
555
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
556
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
557
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
558
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
559
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
560
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
561
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
562
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
563
UPDATE t1 SET acct_id=785
564
WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
566
Table Op Msg_type Msg_text
567
test.t1 optimize status OK
568
SELECT COUNT(*) FROM t1;
571
SELECT COUNT(*) FROM t1 WHERE acct_id=785;
574
EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
575
id select_type table type possible_keys key key_len ref rows Extra
576
1 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
577
INSERT INTO t2 SELECT * FROM t1;
579
Table Op Msg_type Msg_text
580
test.t2 optimize status OK
581
EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
582
id select_type table type possible_keys key key_len ref rows Extra
583
1 SIMPLE t2 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
585
create table t1(a int) engine=innodb;
586
alter table t1 comment '123';
587
show create table t1;
589
t1 CREATE TABLE `t1` (
590
`a` int(11) DEFAULT NULL
591
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='123'
593
CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8;
594
INSERT INTO t1 VALUES ('uk'),('bg');
595
SELECT * FROM t1 WHERE a = 'uk';
598
DELETE FROM t1 WHERE a = 'uk';
599
SELECT * FROM t1 WHERE a = 'uk';
601
UPDATE t1 SET a = 'us' WHERE a = 'uk';
602
SELECT * FROM t1 WHERE a = 'uk';
604
CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
605
INSERT INTO t2 VALUES ('uk'),('bg');
606
SELECT * FROM t2 WHERE a = 'uk';
609
DELETE FROM t2 WHERE a = 'uk';
610
SELECT * FROM t2 WHERE a = 'uk';
612
INSERT INTO t2 VALUES ('uk');
613
UPDATE t2 SET a = 'us' WHERE a = 'uk';
614
SELECT * FROM t2 WHERE a = 'uk';
616
CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
617
INSERT INTO t3 VALUES ('uk'),('bg');
618
SELECT * FROM t3 WHERE a = 'uk';
621
DELETE FROM t3 WHERE a = 'uk';
622
SELECT * FROM t3 WHERE a = 'uk';
624
INSERT INTO t3 VALUES ('uk');
625
UPDATE t3 SET a = 'us' WHERE a = 'uk';
626
SELECT * FROM t3 WHERE a = 'uk';
629
create table t1 (a int) engine=innodb;
630
select * from bug29807;
631
ERROR 42S02: Table 'test.bug29807' doesn't exist
634
ERROR 42S02: Unknown table 'bug29807'
635
create table bug29807 (a int);
637
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
638
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
639
switch to connection c1
641
INSERT INTO t2 VALUES (1);
642
switch to connection c2
644
LOCK TABLES t1 READ, t2 READ;
645
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
646
switch to connection c1
648
INSERT INTO t1 VALUES (1);
649
switch to connection default
650
SET AUTOCOMMIT=default;
653
id int NOT NULL auto_increment PRIMARY KEY,
660
b int NOT NULL auto_increment PRIMARY KEY,
663
INSERT INTO t2(c) VALUES ('2007-01-01');
664
INSERT INTO t2(c) SELECT c FROM t2;
665
INSERT INTO t2(c) SELECT c FROM t2;
666
INSERT INTO t2(c) SELECT c FROM t2;
667
INSERT INTO t2(c) SELECT c FROM t2;
668
INSERT INTO t2(c) SELECT c FROM t2;
669
INSERT INTO t2(c) SELECT c FROM t2;
670
INSERT INTO t2(c) SELECT c FROM t2;
671
INSERT INTO t2(c) SELECT c FROM t2;
672
INSERT INTO t2(c) SELECT c FROM t2;
673
INSERT INTO t2(c) SELECT c FROM t2;
674
INSERT INTO t1(b,c) SELECT b,c FROM t2;
675
UPDATE t2 SET c='2007-01-02';
676
INSERT INTO t1(b,c) SELECT b,c FROM t2;
677
UPDATE t2 SET c='2007-01-03';
678
INSERT INTO t1(b,c) SELECT b,c FROM t2;
679
set @@sort_buffer_size=8192;
681
Warning 1292 Truncated incorrect sort_buffer_size value: '8192'
682
SELECT COUNT(*) FROM t1;
686
SELECT COUNT(*) FROM t1
687
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
688
id select_type table type possible_keys key key_len ref rows Extra
689
1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where
690
SELECT COUNT(*) FROM t1
691
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
695
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
696
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
697
id select_type table type possible_keys key key_len ref rows Extra
698
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
699
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
700
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
703
set @@sort_buffer_size=default;
705
CREATE TABLE t1 (a int, b int);
706
insert into t1 values (1,1),(1,2);
707
CREATE TABLE t2 (primary key (a)) select * from t1;
708
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
709
drop table if exists t2;
711
Note 1051 Unknown table 't2'
712
CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
713
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
714
drop table if exists t2;
716
Note 1051 Unknown table 't2'
717
CREATE TABLE t2 (a int, b int, primary key (a));
719
INSERT INTO t2 values(100,100);
720
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
721
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
730
INSERT INTO t2 select * from t1;
731
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
735
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
737
INSERT INTO t2 values(100,100);
738
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
739
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
745
INSERT INTO t2 values(101,101);
746
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
747
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
757
INSERT INTO t2 select * from t1;
758
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
762
create table t1(f1 varchar(800) binary not null, key(f1))
763
character set utf8 collate utf8_general_ci;
765
Warning 1071 Specified key was too long; max key length is 767 bytes
766
insert into t1 values('aaa');
768
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
769
INSERT INTO t1 VALUES ( 1 , 1 , 1);
770
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
771
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
772
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
773
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
774
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
775
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
776
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
777
EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
778
id select_type table type possible_keys key key_len ref rows Extra
779
1 SIMPLE t1 index NULL b 5 NULL 128
780
EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
781
id select_type table type possible_keys key key_len ref rows Extra
782
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
784
drop table if exists t1;
785
show variables like 'innodb_rollback_on_timeout';
787
innodb_rollback_on_timeout OFF
788
create table t1 (a int unsigned not null primary key) engine = innodb;
789
insert into t1 values (1);
792
insert into t1 values (2);
798
insert into t1 values (5);
803
insert into t1 values (2);
804
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
821
drop table if exists t1;
822
create table t1 (a int) engine=innodb;
823
alter table t1 alter a set default 1;
826
Bug#24918 drop table and lock / inconsistent between
829
Check transactional tables under LOCK TABLES
831
drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp,
833
create table t24918_access (id int);
834
create table t24918 (id int) engine=myisam;
835
create temporary table t24918_tmp (id int) engine=myisam;
836
create table t24918_trans (id int) engine=innodb;
837
create temporary table t24918_trans_tmp (id int) engine=innodb;
838
lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write;
840
select * from t24918_access;
841
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
842
drop table t24918_trans;
843
select * from t24918_access;
844
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
845
drop table t24918_trans_tmp;
846
select * from t24918_access;
847
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
848
drop table t24918_tmp;
849
select * from t24918_access;
850
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
852
drop table t24918_access;
853
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
854
INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
855
INSERT INTO t1 SELECT a + 8, 2 FROM t1;
856
INSERT INTO t1 SELECT a + 16, 1 FROM t1;
857
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
867
Extra Using where; Using index
868
SELECT * FROM t1 WHERE b=2 ORDER BY a;
886
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
897
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
931
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
941
Extra Using where; Using index
942
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
976
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
978
INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
979
INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
980
INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
981
EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
991
Extra Using where; Using index
992
SELECT * FROM t2 WHERE b=1 ORDER BY a;
1010
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
1020
Extra Using where; Using index
1021
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
1039
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
1049
Extra Using where; Using index
1050
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
1068
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
1078
Extra Using where; Using index
1079
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
1098
CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB;
1099
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
1100
INSERT INTO t1 SELECT a + 8 FROM t1;
1101
INSERT INTO t1 SELECT a + 16 FROM t1;
1102
CREATE PROCEDURE p1 ()
1104
DECLARE i INT DEFAULT 50;
1107
ALTER TABLE t1 ENGINE=InnoDB;
1112
SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE;
1121
create table t1(a text) engine=innodb default charset=utf8;
1122
insert into t1 values('aaa');
1123
alter table t1 add index(a(1024));
1125
Warning 1071 Specified key was too long; max key length is 767 bytes
1126
Warning 1071 Specified key was too long; max key length is 767 bytes
1127
Warning 1071 Specified key was too long; max key length is 767 bytes
1128
show create table t1;
1130
t1 CREATE TABLE `t1` (
1133
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1140
INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
1142
SELECT * FROM t1 WHERE b=20 FOR UPDATE;
1146
SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
1160
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
1161
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
1162
id select_type table type possible_keys key key_len ref rows Extra
1163
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1164
SELECT c,b,d FROM t1 GROUP BY c,b,d;
1169
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1170
id select_type table type possible_keys key key_len ref rows Extra
1171
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
1172
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1177
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
1178
id select_type table type possible_keys key key_len ref rows Extra
1179
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1180
SELECT c,b,d FROM t1 ORDER BY c,b,d;
1185
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
1186
id select_type table type possible_keys key key_len ref rows Extra
1187
1 SIMPLE t1 index NULL c 8 NULL 3
1188
SELECT c,b,d FROM t1 GROUP BY c,b;
1193
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
1194
id select_type table type possible_keys key key_len ref rows Extra
1195
1 SIMPLE t1 index NULL c 8 NULL 3 Using index
1196
SELECT c,b FROM t1 GROUP BY c,b;
1202
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
1203
INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
1204
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1214
Extra Using where; Using index
1215
SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1219
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1229
Extra Using where; Using index
1230
SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1234
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
1245
SELECT * FROM t1 ORDER BY b ASC, a ASC;
1250
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
1261
SELECT * FROM t1 ORDER BY b DESC, a DESC;
1266
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
1276
Extra Using filesort
1277
SELECT * FROM t1 ORDER BY b ASC, a DESC;
1282
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
1292
Extra Using filesort
1293
SELECT * FROM t1 ORDER BY b DESC, a ASC;
1301
# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
1306
DROP TABLE IF EXISTS t1;
1308
CREATE TABLE t1(c INT)
1310
ROW_FORMAT = COMPACT;
1314
SELECT table_schema, table_name, row_format
1315
FROM INFORMATION_SCHEMA.TABLES
1316
WHERE table_schema = DATABASE() AND table_name = 't1';
1317
table_schema table_name row_format
1320
# - change ROW_FORMAT and check;
1322
ALTER TABLE t1 ROW_FORMAT = REDUNDANT;
1324
SELECT table_schema, table_name, row_format
1325
FROM INFORMATION_SCHEMA.TABLES
1326
WHERE table_schema = DATABASE() AND table_name = 't1';
1327
table_schema table_name row_format
1330
# - that's it, cleanup.
1333
create table t1(a char(10) not null, unique key aa(a(1)),
1334
b char(4) not null, unique key bb(b(4))) engine=innodb;
1336
Field Type Null Key Default Extra
1337
a char(10) NO UNI NULL
1338
b char(4) NO PRI NULL
1339
show create table t1;
1341
t1 CREATE TABLE `t1` (
1342
`a` char(10) NOT NULL,
1343
`b` char(4) NOT NULL,
1344
UNIQUE KEY `bb` (`b`),
1345
UNIQUE KEY `aa` (`a`(1))
1346
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1348
CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
1349
INSERT INTO t1 VALUES
1350
(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
1351
EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1352
id select_type table type possible_keys key key_len ref rows Extra
1353
1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort
1354
SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1360
set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
1361
set global innodb_autoextend_increment=8;
1362
set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
1363
set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;
1364
set global innodb_commit_concurrency=0;
1365
set global innodb_commit_concurrency=@my_innodb_commit_concurrency;
1368
`k` int(11) NOT NULL auto_increment,
1369
`a` int(11) default NULL,
1370
`c` int(11) default NULL,
1372
UNIQUE KEY `idx_1` (`a`)
1374
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1377
insert into t2 ( a ) values ( 7 ) on duplicate key update c =
1380
select last_insert_id();
1387
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1390
select last_insert_id();
1393
select last_insert_id(0);
1396
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1399
select last_insert_id();
1406
insert ignore into t2 values (null,6,1),(10,8,1);
1407
select last_insert_id();
1410
insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
1411
select last_insert_id();
1421
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1423
0 ) + 1, k=last_insert_id(k);
1424
select last_insert_id();
1435
drop table if exists t1, t2;
1436
create table t1 (i int);
1437
alter table t1 modify i int default 1;
1438
alter table t1 modify i int default 2, rename t2;
1439
lock table t2 write;
1440
alter table t2 modify i int default 3;
1442
lock table t2 write;
1443
alter table t2 modify i int default 4, rename t1;
1446
drop table if exists t1;
1447
create table t1 (i int);
1448
insert into t1 values ();
1449
lock table t1 write;
1450
alter table t1 modify i int default 1;
1451
insert into t1 values ();
1456
alter table t1 change i c char(10) default "Two";
1457
insert into t1 values ();
1470
create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
1471
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
1472
insert into t1(f1) values(1);
1473
select @a:=f2 from t1;
1477
select @b:=f2 from t1;
1480
select if(@a=@b,"ok","wrong");
1481
if(@a=@b,"ok","wrong")
1483
insert into t1(f1) values (1) on duplicate key update f1="1";
1484
select @b:=f2 from t1;
1487
select if(@a=@b,"ok","wrong");
1488
if(@a=@b,"ok","wrong")
1490
insert into t1(f1) select f1 from t1 on duplicate key update f1="1";
1491
select @b:=f2 from t1;
1494
select if(@a=@b,"ok","wrong");
1495
if(@a=@b,"ok","wrong")
1498
SET SESSION AUTOCOMMIT = 0;
1499
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1500
# Switch to connection con1
1501
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256))
1503
INSERT INTO t1 VALUES (1,2);
1504
# 1. test for locking:
1506
UPDATE t1 SET b = 12 WHERE a = 1;
1508
info: Rows matched: 1 Changed: 1 Warnings: 0
1512
# Switch to connection con2
1513
UPDATE t1 SET b = 21 WHERE a = 1;
1514
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1515
# Switch to connection con1
1520
# 2. test for serialized update:
1521
CREATE TABLE t2 (a INT);
1523
INSERT INTO t1 VALUES (1,'init');
1524
CREATE PROCEDURE p1()
1526
UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1;
1527
INSERT INTO t2 VALUES ();
1530
UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1;
1532
info: Rows matched: 1 Changed: 1 Warnings: 0
1536
# Switch to connection con2
1538
# Switch to connection con1
1546
# Switch to connection con2
1550
# Switch to connection con1
1551
# 3. test for updated key column:
1554
INSERT INTO t1 VALUES (1,'init');
1556
UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1;
1558
info: Rows matched: 1 Changed: 1 Warnings: 0
1562
# Switch to connection con2
1564
# Switch to connection con1
1572
# Switch to connection con2
1578
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
1579
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1580
CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1581
ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match
1582
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1583
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1584
ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
1585
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1586
CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1587
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1588
ALTER TABLE t2 DROP FOREIGN KEY c2;
1590
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1591
FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1592
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
1593
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1594
FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1595
ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match
1596
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1597
CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1598
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1599
FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1600
FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1601
SHOW CREATE TABLE t2;
1603
t2 CREATE TABLE `t2` (
1604
`c` int(11) NOT NULL,
1605
`d` int(11) NOT NULL,
1606
PRIMARY KEY (`c`,`d`),
1607
CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,
1608
CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
1609
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
1610
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
1611
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1614
create table t1 (a int auto_increment primary key) engine=innodb;
1615
alter table t1 order by a;
1617
Warning 1105 ORDER BY ignored as there is a user-defined clustered index in the table 't1'
1620
(vid integer NOT NULL,
1621
tid integer NOT NULL,
1622
idx integer NOT NULL,
1623
name varchar(128) NOT NULL,
1624
type varchar(128) NULL,
1625
PRIMARY KEY(idx, vid, tid),
1626
UNIQUE(vid, tid, name)
1628
INSERT INTO t1 VALUES
1629
(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
1630
(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
1631
(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
1632
(4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
1633
(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
1634
EXPLAIN SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1635
id select_type table type possible_keys key key_len ref rows Extra
1636
1 SIMPLE t1 index vid PRIMARY 12 NULL 16 Using where
1637
SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1638
vid tid idx name type
1644
DROP TABLE IF EXISTS t1;
1645
DROP TABLE IF EXISTS t2;
1646
CREATE TABLE t1(id INT PRIMARY KEY)
1649
t1_id INT PRIMARY KEY,
1650
CONSTRAINT fk1 FOREIGN KEY (t1_id) REFERENCES t1(id))
1653
ALTER TABLE t1 CHANGE id id2 INT;
added
removed
mysql-test/r/innodb_mysql.result
