1
drop table if exists t1,t2,t3,t4;
2
drop database if exists mysqltest;
3
create table t1 (v varchar(16384)) engine=innodb;
5
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
6
insert into t1 values ('8', '6'), ('4', '7');
10
select min(b) from t1 where a='8';
14
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
15
insert into t1 (b) values (1);
16
replace into t1 (b) values (2), (1), (3);
23
insert into t1 (b) values (1);
24
replace into t1 (b) values (2);
25
replace into t1 (b) values (1);
26
replace into t1 (b) values (3);
33
create table t1 (rowid int not null auto_increment, val int not null,primary
34
key (rowid), unique(val)) engine=innodb;
35
replace into t1 (val) values ('1'),('2');
36
replace into t1 (val) values ('1'),('2');
37
insert into t1 (val) values ('1'),('2');
38
ERROR 23000: Duplicate entry '1' for key 'val'
44
create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
45
insert into t1 (val) values (1);
46
update t1 set a=2 where a=1;
47
insert into t1 (val) values (1);
48
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
53
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
54
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
55
SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
58
SELECT GRADE FROM t1 WHERE GRADE= 151;
62
create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
63
create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
64
insert into t2 values ('aa','cc');
65
insert into t1 values ('aa','bb'),('aa','cc');
66
delete t1 from t1,t2 where f1=f3 and f4='cc';
71
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
75
FOREIGN KEY (id) REFERENCES t1 (id)
77
INSERT INTO t1 (id) VALUES (NULL);
82
INSERT INTO t1 (id) VALUES (NULL);
88
INSERT INTO t1 (id) VALUES (NULL);
97
CREATE TEMPORARY TABLE t2
99
id INT NOT NULL PRIMARY KEY,
101
FOREIGN KEY (b) REFERENCES test.t1(id)
103
Got one of the listed errors
105
create table t1 (col1 varchar(2000), index (col1(767)))
106
character set = latin1 engine = innodb;
107
create table t2 (col1 char(255), index (col1))
108
character set = latin1 engine = innodb;
109
create table t3 (col1 binary(255), index (col1))
110
character set = latin1 engine = innodb;
111
create table t4 (col1 varchar(767), index (col1))
112
character set = latin1 engine = innodb;
113
create table t5 (col1 varchar(767) primary key)
114
character set = latin1 engine = innodb;
115
create table t6 (col1 varbinary(767) primary key)
116
character set = latin1 engine = innodb;
117
create table t7 (col1 text, index(col1(767)))
118
character set = latin1 engine = innodb;
119
create table t8 (col1 blob, index(col1(767)))
120
character set = latin1 engine = innodb;
121
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
122
character set = latin1 engine = innodb;
123
show create table t9;
125
t9 CREATE TABLE `t9` (
126
`col1` varchar(512) DEFAULT NULL,
127
`col2` varchar(512) DEFAULT NULL,
128
KEY `col1` (`col1`,`col2`)
129
) ENGINE=InnoDB DEFAULT CHARSET=latin1
130
drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
131
create table t1 (col1 varchar(768), index(col1))
132
character set = latin1 engine = innodb;
134
Warning 1071 Specified key was too long; max key length is 767 bytes
135
create table t2 (col1 varbinary(768), index(col1))
136
character set = latin1 engine = innodb;
138
Warning 1071 Specified key was too long; max key length is 767 bytes
139
create table t3 (col1 text, index(col1(768)))
140
character set = latin1 engine = innodb;
142
Warning 1071 Specified key was too long; max key length is 767 bytes
143
create table t4 (col1 blob, index(col1(768)))
144
character set = latin1 engine = innodb;
146
Warning 1071 Specified key was too long; max key length is 767 bytes
147
show create table t1;
149
t1 CREATE TABLE `t1` (
150
`col1` varchar(768) DEFAULT NULL,
151
KEY `col1` (`col1`(767))
152
) ENGINE=InnoDB DEFAULT CHARSET=latin1
153
drop table t1, t2, t3, t4;
154
create table t1 (col1 varchar(768) primary key)
155
character set = latin1 engine = innodb;
156
ERROR 42000: Specified key was too long; max key length is 767 bytes
157
create table t2 (col1 varbinary(768) primary key)
158
character set = latin1 engine = innodb;
159
ERROR 42000: Specified key was too long; max key length is 767 bytes
160
create table t3 (col1 text, primary key(col1(768)))
161
character set = latin1 engine = innodb;
162
ERROR 42000: Specified key was too long; max key length is 767 bytes
163
create table t4 (col1 blob, primary key(col1(768)))
164
character set = latin1 engine = innodb;
165
ERROR 42000: Specified key was too long; max key length is 767 bytes
173
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
175
INSERT INTO t2 VALUES(2);
176
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
177
INSERT INTO t1 VALUES(1);
178
INSERT INTO t2 VALUES(1);
179
DELETE FROM t1 WHERE id = 1;
180
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
182
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
183
SET FOREIGN_KEY_CHECKS=0;
185
SET FOREIGN_KEY_CHECKS=1;
186
INSERT INTO t2 VALUES(3);
187
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c1` FOREIGN KEY (`v`) REFERENCES `t1` (`id`))
189
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
190
insert into t1 values (1),(2);
195
insert into t1 values(3);
205
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
206
insert into t1 values (1),(2);
212
insert into t1 values(3);
217
set foreign_key_checks=0;
218
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
219
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
220
ERROR HY000: Can't create table 'test.t1' (errno: 150)
221
set foreign_key_checks=1;
223
set foreign_key_checks=0;
224
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
225
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
226
ERROR HY000: Can't create table 'test.t2' (errno: 150)
227
set foreign_key_checks=1;
229
set foreign_key_checks=0;
230
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
231
create table t1(a varchar(10) primary key) engine = innodb;
232
alter table t1 modify column a int;
233
Got one of the listed errors
234
set foreign_key_checks=1;
236
set foreign_key_checks=0;
237
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
238
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
239
alter table t1 convert to character set utf8;
240
set foreign_key_checks=1;
242
set foreign_key_checks=0;
243
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
244
create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
245
rename table t3 to t1;
246
ERROR HY000: Error on rename of './test/t3' to './test/t1' (errno: 150)
247
set foreign_key_checks=1;
249
create table t1(a int primary key) row_format=redundant engine=innodb;
250
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
251
create table t3(a int primary key) row_format=compact engine=innodb;
252
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
253
insert into t1 values(1);
254
insert into t3 values(1);
255
insert into t2 values(2);
256
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
257
insert into t4 values(2);
258
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))
259
insert into t2 values(1);
260
insert into t4 values(1);
262
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
264
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
266
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))
268
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))
270
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`))
272
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t3` (`a`))
277
drop table t4,t3,t2,t1;
278
create table t1 (a varchar(255) character set utf8,
279
b varchar(255) character set utf8,
280
c varchar(255) character set utf8,
281
d varchar(255) character set utf8,
282
key (a,b,c,d)) engine=innodb;
284
create table t1 (a varchar(255) character set utf8,
285
b varchar(255) character set utf8,
286
c varchar(255) character set utf8,
287
d varchar(255) character set utf8,
288
e varchar(255) character set utf8,
289
key (a,b,c,d,e)) engine=innodb;
290
ERROR 42000: Specified key was too long; max key length is 3072 bytes
291
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
292
create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
293
create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
294
create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
295
insert into t1 values (0x41),(0x4120),(0x4100);
296
insert into t2 values (0x41),(0x4120),(0x4100);
297
ERROR 23000: Duplicate entry 'A' for key 'PRIMARY'
298
insert into t2 values (0x41),(0x4120);
299
insert into t3 values (0x41),(0x4120),(0x4100);
300
ERROR 23000: Duplicate entry 'A ' for key 'PRIMARY'
301
insert into t3 values (0x41),(0x4100);
302
insert into t4 values (0x41),(0x4120),(0x4100);
303
ERROR 23000: Duplicate entry 'A' for key 'PRIMARY'
304
insert into t4 values (0x41),(0x4100);
305
select hex(s1) from t1;
310
select hex(s1) from t2;
314
select hex(s1) from t3;
318
select hex(s1) from t4;
322
drop table t1,t2,t3,t4;
323
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
324
create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
325
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
326
insert into t2 values(0x42);
327
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
328
insert into t2 values(0x41);
329
select hex(s1) from t2;
332
update t1 set s1=0x123456 where a=2;
333
select hex(s1) from t2;
336
update t1 set s1=0x12 where a=1;
337
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
338
update t1 set s1=0x12345678 where a=1;
339
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
340
update t1 set s1=0x123457 where a=1;
341
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
342
update t1 set s1=0x1220 where a=1;
343
select hex(s1) from t2;
346
update t1 set s1=0x1200 where a=1;
347
select hex(s1) from t2;
350
update t1 set s1=0x4200 where a=1;
351
select hex(s1) from t2;
354
delete from t1 where a=1;
355
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
356
delete from t1 where a=2;
357
update t2 set s1=0x4120;
359
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
360
delete from t1 where a!=3;
361
select a,hex(s1) from t1;
364
select hex(s1) from t2;
368
create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
369
create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
370
insert into t1 values(1,0x4100),(2,0x41);
371
insert into t2 values(0x41);
372
select hex(s1) from t2;
375
update t1 set s1=0x1234 where a=1;
376
select hex(s1) from t2;
379
update t1 set s1=0x12 where a=2;
380
select hex(s1) from t2;
383
delete from t1 where a=1;
384
delete from t1 where a=2;
385
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `c` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
386
select a,hex(s1) from t1;
389
select hex(s1) from t2;
393
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
394
CREATE TABLE t2(a INT) ENGINE=InnoDB;
395
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
396
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
397
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
398
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
399
SHOW CREATE TABLE t2;
401
t2 CREATE TABLE `t2` (
402
`a` int(11) DEFAULT NULL,
403
KEY `t2_ibfk_0` (`a`)
404
) ENGINE=InnoDB DEFAULT CHARSET=latin1
406
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
407
insert into t1(a) values (1),(2),(3);
410
update t1 set b = 5 where a = 2;
411
create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
413
insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
414
(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
415
(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
416
(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
417
(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
422
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
423
create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
424
create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
425
create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
426
create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
427
insert into t1(a) values (1),(2),(3);
428
insert into t2(a) values (1),(2),(3);
429
insert into t3(a) values (1),(2),(3);
430
insert into t4(a) values (1),(2),(3);
431
insert into t3(a) values (5),(7),(8);
432
insert into t4(a) values (5),(7),(8);
433
insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
434
create trigger t1t before insert on t1 for each row begin
435
INSERT INTO t2 SET a = NEW.a;
437
create trigger t2t before insert on t2 for each row begin
438
DELETE FROM t3 WHERE a = NEW.a;
440
create trigger t3t before delete on t3 for each row begin
441
UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
443
create trigger t4t before update on t4 for each row begin
444
UPDATE t5 SET b = b + 1 where a = NEW.a;
448
update t1 set b = b + 5 where a = 1;
449
update t2 set b = b + 5 where a = 1;
450
update t3 set b = b + 5 where a = 1;
451
update t4 set b = b + 5 where a = 1;
452
insert into t5(a) values(20);
454
insert into t1(a) values(7);
455
insert into t2(a) values(8);
456
delete from t2 where a = 3;
457
update t4 set b = b + 1 where a = 3;
463
drop table t1, t2, t3, t4, t5;
465
field1 varchar(8) NOT NULL DEFAULT '',
466
field2 varchar(8) NOT NULL DEFAULT '',
467
PRIMARY KEY (field1, field2)
470
field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
471
FOREIGN KEY (field1) REFERENCES t1 (field1)
472
ON DELETE CASCADE ON UPDATE CASCADE
474
INSERT INTO t1 VALUES ('old', 'somevalu');
475
INSERT INTO t1 VALUES ('other', 'anyvalue');
476
INSERT INTO t2 VALUES ('old');
477
INSERT INTO t2 VALUES ('other');
478
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
479
ERROR 23000: Upholding foreign key constraints for table 't1', entry 'other-somevalu', key 1 would lead to a duplicate entry
492
alter table t1 add constraint c2_fk foreign key (c2)
493
references t2(c1) on delete cascade;
494
show create table t1;
496
t1 CREATE TABLE `t1` (
497
`c1` bigint(20) NOT NULL,
498
`c2` bigint(20) NOT NULL,
500
UNIQUE KEY `c2` (`c2`),
501
CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE
502
) ENGINE=InnoDB DEFAULT CHARSET=latin1
503
alter table t1 drop foreign key c2_fk;
504
show create table t1;
506
t1 CREATE TABLE `t1` (
507
`c1` bigint(20) NOT NULL,
508
`c2` bigint(20) NOT NULL,
510
UNIQUE KEY `c2` (`c2`)
511
) ENGINE=InnoDB DEFAULT CHARSET=latin1
513
create table t1(a date) engine=innodb;
514
create table t2(a date, key(a)) engine=innodb;
515
insert into t1 values('2005-10-01');
516
insert into t2 values('2005-10-01');
518
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
520
2005-10-01 2005-10-01
522
create table t1 (id int not null, f_id int not null, f int not null,
523
primary key(f_id, id)) engine=innodb;
524
create table t2 (id int not null,s_id int not null,s varchar(200),
525
primary key(id)) engine=innodb;
526
INSERT INTO t1 VALUES (8, 1, 3);
527
INSERT INTO t1 VALUES (1, 2, 1);
528
INSERT INTO t2 VALUES (1, 0, '');
529
INSERT INTO t2 VALUES (8, 1, '');
531
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
533
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
534
where mm.id is null lock in share mode;
537
create table t1(a int not null, b int, primary key(a)) engine=innodb;
538
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
540
SET binlog_format='MIXED';
542
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
543
update t1 set b = 5 where b = 1;
544
SET binlog_format='MIXED';
546
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
547
select * from t1 where a = 7 and b = 3 for update;
553
create table t1(a int not null, b int, primary key(a)) engine=innodb;
554
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
557
select * from t1 lock in share mode;
565
update t1 set b = 5 where b = 1;
567
select * from t1 where a = 2 and b = 2 for update;
568
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
572
create table t1(a int not null, b int, primary key(a)) engine=innodb;
573
insert into t1 values (1,2),(5,3),(4,2);
574
create table t2(d int not null, e int, primary key(d)) engine=innodb;
575
insert into t2 values (8,6),(12,1),(3,1);
578
select * from t2 for update;
583
SET binlog_format='MIXED';
585
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
586
insert into t1 select * from t2;
587
update t1 set b = (select e from t2 where a = d);
588
create table t3(d int not null, e int, primary key(d)) engine=innodb
592
drop table t1, t2, t3;
593
create table t1(a int not null, b int, primary key(a)) engine=innodb;
594
insert into t1 values (1,2),(5,3),(4,2);
595
create table t2(a int not null, b int, primary key(a)) engine=innodb;
596
insert into t2 values (8,6),(12,1),(3,1);
597
create table t3(d int not null, b int, primary key(d)) engine=innodb;
598
insert into t3 values (8,6),(12,1),(3,1);
599
create table t5(a int not null, b int, primary key(a)) engine=innodb;
600
insert into t5 values (1,2),(5,3),(4,2);
601
create table t6(d int not null, e int, primary key(d)) engine=innodb;
602
insert into t6 values (8,6),(12,1),(3,1);
603
create table t8(a int not null, b int, primary key(a)) engine=innodb;
604
insert into t8 values (1,2),(5,3),(4,2);
605
create table t9(d int not null, e int, primary key(d)) engine=innodb;
606
insert into t9 values (8,6),(12,1),(3,1);
609
select * from t2 for update;
614
SET binlog_format='MIXED';
616
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
617
insert into t1 select * from t2;
618
SET binlog_format='MIXED';
620
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
621
update t3 set b = (select b from t2 where a = d);
622
SET binlog_format='MIXED';
624
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
625
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
626
SET binlog_format='MIXED';
628
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
629
insert into t5 (select * from t2 lock in share mode);
630
SET binlog_format='MIXED';
632
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
633
update t6 set e = (select b from t2 where a = d lock in share mode);
634
SET binlog_format='MIXED';
636
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
637
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
638
SET binlog_format='MIXED';
640
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
641
insert into t8 (select * from t2 for update);
642
SET binlog_format='MIXED';
644
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
645
update t9 set e = (select b from t2 where a = d for update);
646
SET binlog_format='MIXED';
648
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
649
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
650
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
651
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
652
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
653
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
654
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
655
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
656
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
657
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
658
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
660
drop table t1, t2, t3, t5, t6, t8, t9;
661
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
662
ERROR 42000: Incorrect column name 'DB_ROW_ID'
664
a BIGINT(20) NOT NULL,
666
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
668
a BIGINT(20) NOT NULL,
669
b VARCHAR(128) NOT NULL,
672
KEY idx_t2_b_c (b,c(200)),
673
CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
675
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
676
INSERT INTO t1 VALUES (1);
677
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
678
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
679
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
680
INSERT INTO t2 VALUES (1, 'customer_over', '1');
681
SELECT * FROM t2 WHERE b = 'customer_over';
684
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
687
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
690
/* Bang: Empty result set, above was expected: */
691
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
694
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
698
CREATE TABLE t1 ( a int ) ENGINE=innodb;
700
INSERT INTO t1 VALUES (1);
702
Table Op Msg_type Msg_text
703
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
704
test.t1 optimize status OK
706
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
707
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
708
CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
709
ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
710
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
711
DELETE CASCADE ON UPDATE CASCADE;
712
SHOW CREATE TABLE t2;
714
t2 CREATE TABLE `t2` (
715
`id` int(11) NOT NULL,
716
`f` int(11) NOT NULL,
719
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f`) REFERENCES `t1` (`f`) ON DELETE CASCADE ON UPDATE CASCADE,
720
CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
721
) ENGINE=InnoDB DEFAULT CHARSET=latin1
723
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
724
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
725
INSERT INTO t1 VALUES (1);
726
INSERT INTO t2 VALUES (1);
727
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
728
ALTER TABLE t2 MODIFY a INT NOT NULL;
729
ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150)
732
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
734
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
736
INSERT INTO t1 VALUES ('DDD');
741
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
743
INSERT INTO t1 VALUES (0),(347),(0);
749
SHOW CREATE TABLE t1;
751
t1 CREATE TABLE `t1` (
752
`id` int(11) NOT NULL AUTO_INCREMENT,
754
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
755
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
756
INSERT INTO t2 VALUES(42),(347),(348);
757
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
758
SHOW CREATE TABLE t1;
760
t1 CREATE TABLE `t1` (
761
`id` int(11) NOT NULL AUTO_INCREMENT,
763
CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)
764
) ENGINE=InnoDB AUTO_INCREMENT=349 DEFAULT CHARSET=latin1
766
SET innodb_strict_mode=ON;
768
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
769
c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
770
c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
771
c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
772
c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
773
c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
774
c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
775
c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
777
ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
778
SET innodb_strict_mode=OFF;
779
DROP TABLE IF EXISTS t1;
781
Note 1051 Unknown table 't1'
783
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
785
INSERT INTO t1 VALUES(-10);
789
INSERT INTO t1 VALUES(NULL);
795
SET binlog_format='MIXED';
796
SET TX_ISOLATION='read-committed';
798
DROP TABLE IF EXISTS t1, t2;
800
Note 1051 Unknown table 't1'
801
Note 1051 Unknown table 't2'
802
CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
803
CREATE TABLE t2 LIKE t1;
806
SET binlog_format='MIXED';
807
SET TX_ISOLATION='read-committed';
809
INSERT INTO t1 VALUES (1);
811
SELECT * FROM t1 WHERE a=1;
814
SET binlog_format='MIXED';
815
SET TX_ISOLATION='read-committed';
819
SET binlog_format='MIXED';
820
SET TX_ISOLATION='read-committed';
822
INSERT INTO t1 VALUES (2);
824
SELECT * FROM t1 WHERE a=2;
827
SELECT * FROM t1 WHERE a=2;
832
create table t1 (i int, j int) engine=innodb;
833
insert into t1 (i, j) values (1, 1), (2, 2);
836
info: Rows matched: 2 Changed: 1 Warnings: 0
838
create table t1 (id int) comment='this is a comment' engine=innodb;
839
select table_comment, data_free > 0 as data_free_is_set
840
from information_schema.tables
841
where table_schema='test' and table_name = 't1';
842
table_comment data_free_is_set
846
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
847
c2 VARCHAR(128) NOT NULL,
849
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
851
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
852
c2 INT(10) UNSIGNED DEFAULT NULL,
854
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
855
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
858
ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
859
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
864
CREATE TABLE t1 (c1 int default NULL,
866
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
869
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
871
info: Records: 5 Duplicates: 0 Warnings: 0