1
-- source include/have_innodb.inc
3
let $MYSQLD_DATADIR= `select @@datadir`;
5
# Save the original values of some variables in order to be able to
6
# estimate how much they have changed during the tests. Previously this
7
# test assumed that e.g. rows_deleted is 0 here and after deleting 23
8
# rows it expected that rows_deleted will be 23. Now we do not make
9
# assumptions about the values of the variables at the beginning, e.g.
10
# rows_deleted should be 23 + "rows_deleted before the test". This allows
11
# the test to be run multiple times without restarting the mysqld server.
12
# See Bug#43309 Test main.innodb can't be run twice
14
SET @innodb_thread_concurrency_orig = @@innodb_thread_concurrency;
18
drop table if exists t1,t2,t3,t4;
19
drop database if exists mysqltest;
22
# InnoDB specific varchar tests
23
create table t1 (v varchar(16384)) engine=innodb;
27
# BUG#11039 Wrong key length in min()
30
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
31
insert into t1 values ('8', '6'), ('4', '7');
32
select min(a) from t1;
33
select min(b) from t1 where a='8';
37
# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error
40
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
41
insert into t1 (b) values (1);
42
replace into t1 (b) values (2), (1), (3);
45
insert into t1 (b) values (1);
46
replace into t1 (b) values (2);
47
replace into t1 (b) values (1);
48
replace into t1 (b) values (3);
52
create table t1 (rowid int not null auto_increment, val int not null,primary
53
key (rowid), unique(val)) engine=innodb;
54
replace into t1 (val) values ('1'),('2');
55
replace into t1 (val) values ('1'),('2');
57
insert into t1 (val) values ('1'),('2');
62
# Test that update does not change internal auto-increment value
65
create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
66
insert into t1 (val) values (1);
67
update t1 set a=2 where a=1;
68
# We should get the following error because InnoDB does not update the counter
70
insert into t1 (val) values (1);
78
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
80
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
81
SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
82
SELECT GRADE FROM t1 WHERE GRADE= 151;
86
# Bug #12340 multitable delete deletes only one record
88
create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
89
create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
90
insert into t2 values ('aa','cc');
91
insert into t1 values ('aa','bb'),('aa','cc');
92
delete t1 from t1,t2 where f1=f3 and f4='cc';
97
# Test that the slow TRUNCATE implementation resets autoincrement columns
102
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
107
FOREIGN KEY (id) REFERENCES t1 (id)
110
INSERT INTO t1 (id) VALUES (NULL);
112
--error ER_TRUNCATE_ILLEGAL_FK
114
INSERT INTO t1 (id) VALUES (NULL);
117
# continued from above; test that doing a slow TRUNCATE on a table with 0
118
# rows resets autoincrement columns
120
--error ER_TRUNCATE_ILLEGAL_FK
122
INSERT INTO t1 (id) VALUES (NULL);
126
# Test that foreign keys in temporary tables are not accepted (bug #12084)
133
CREATE TEMPORARY TABLE t2
135
id INT NOT NULL PRIMARY KEY,
137
FOREIGN KEY (b) REFERENCES test.t1(id)
142
# Test that index column max sizes are honored (bug #13315)
146
create table t1 (col1 varchar(2000), index (col1(767)))
147
character set = latin1 engine = innodb;
150
create table t2 (col1 char(255), index (col1))
151
character set = latin1 engine = innodb;
152
create table t3 (col1 binary(255), index (col1))
153
character set = latin1 engine = innodb;
154
create table t4 (col1 varchar(767), index (col1))
155
character set = latin1 engine = innodb;
156
create table t5 (col1 varchar(767) primary key)
157
character set = latin1 engine = innodb;
158
create table t6 (col1 varbinary(767) primary key)
159
character set = latin1 engine = innodb;
160
create table t7 (col1 text, index(col1(767)))
161
character set = latin1 engine = innodb;
162
create table t8 (col1 blob, index(col1(767)))
163
character set = latin1 engine = innodb;
165
# multi-column indexes are allowed to be longer
166
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
167
character set = latin1 engine = innodb;
169
show create table t9;
171
drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
173
# these should have their index length trimmed
174
create table t1 (col1 varchar(768), index(col1))
175
character set = latin1 engine = innodb;
176
create table t2 (col1 varbinary(768), index(col1))
177
character set = latin1 engine = innodb;
178
create table t3 (col1 text, index(col1(768)))
179
character set = latin1 engine = innodb;
180
create table t4 (col1 blob, index(col1(768)))
181
character set = latin1 engine = innodb;
183
show create table t1;
185
drop table t1, t2, t3, t4;
187
# these should be refused
189
create table t1 (col1 varchar(768) primary key)
190
character set = latin1 engine = innodb;
192
create table t2 (col1 varbinary(768) primary key)
193
character set = latin1 engine = innodb;
195
create table t3 (col1 text, primary key(col1(768)))
196
character set = latin1 engine = innodb;
198
create table t4 (col1 blob, primary key(col1(768)))
199
character set = latin1 engine = innodb;
202
# Test improved foreign key error messages (bug #3443)
213
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
217
INSERT INTO t2 VALUES(2);
219
INSERT INTO t1 VALUES(1);
220
INSERT INTO t2 VALUES(1);
223
DELETE FROM t1 WHERE id = 1;
228
SET FOREIGN_KEY_CHECKS=0;
230
SET FOREIGN_KEY_CHECKS=1;
233
INSERT INTO t2 VALUES(3);
237
# Test that checksum table uses a consistent read Bug #12669
239
connect (a,localhost,root,,);
240
connect (b,localhost,root,,);
242
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
243
insert into t1 values (1),(2);
247
insert into t1 values(3);
250
# Here checksum should not see insert
262
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
263
insert into t1 values (1),(2);
268
insert into t1 values(3);
271
# Here checksum sees insert
280
# tests for bugs #9802 and #13778
282
# test that FKs between invalid types are not accepted
284
set foreign_key_checks=0;
285
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
286
# Embedded server doesn't chdir to data directory
287
--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''
289
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
290
set foreign_key_checks=1;
293
# test that FKs between different charsets are not accepted in CREATE even
296
set foreign_key_checks=0;
297
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
298
# Embedded server doesn't chdir to data directory
299
--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''
301
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
302
set foreign_key_checks=1;
305
# test that invalid datatype conversions with ALTER are not allowed
307
set foreign_key_checks=0;
308
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
309
create table t1(a varchar(10) primary key) engine = innodb;
311
alter table t1 modify column a int;
312
set foreign_key_checks=1;
315
# test that charset conversions with ALTER are allowed when f_k_c is 0
317
set foreign_key_checks=0;
318
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
319
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
320
alter table t1 convert to character set utf8;
321
set foreign_key_checks=1;
324
# test that RENAME does not allow invalid charsets when f_k_c is 0
326
set foreign_key_checks=0;
327
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
328
create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
329
# Embedded server doesn't chdir to data directory
330
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
332
rename table t3 to t1;
333
set foreign_key_checks=1;
336
# test that foreign key errors are reported correctly (Bug #15550)
338
create table t1(a int primary key) row_format=redundant engine=innodb;
339
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
340
create table t3(a int primary key) row_format=compact engine=innodb;
341
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
343
insert into t1 values(1);
344
insert into t3 values(1);
346
insert into t2 values(2);
348
insert into t4 values(2);
349
insert into t2 values(1);
350
insert into t4 values(1);
359
--error ER_TRUNCATE_ILLEGAL_FK
361
--error ER_TRUNCATE_ILLEGAL_FK
365
--error ER_TRUNCATE_ILLEGAL_FK
367
--error ER_TRUNCATE_ILLEGAL_FK
370
drop table t4,t3,t2,t1;
374
# Test that we can create a large (>1K) key
376
create table t1 (a varchar(255) character set utf8,
377
b varchar(255) character set utf8,
378
c varchar(255) character set utf8,
379
d varchar(255) character set utf8,
380
key (a,b,c,d)) engine=innodb;
382
--error ER_TOO_LONG_KEY
383
create table t1 (a varchar(255) character set utf8,
384
b varchar(255) character set utf8,
385
c varchar(255) character set utf8,
386
d varchar(255) character set utf8,
387
e varchar(255) character set utf8,
388
key (a,b,c,d,e)) engine=innodb;
391
# test the padding of BINARY types and collations (Bug #14189)
393
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
394
create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
395
create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
396
create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
398
insert into t1 values (0x41),(0x4120),(0x4100);
399
-- error ER_DUP_ENTRY
400
insert into t2 values (0x41),(0x4120),(0x4100);
401
insert into t2 values (0x41),(0x4120);
402
-- error ER_DUP_ENTRY
403
insert into t3 values (0x41),(0x4120),(0x4100);
404
insert into t3 values (0x41),(0x4100);
405
-- error ER_DUP_ENTRY
406
insert into t4 values (0x41),(0x4120),(0x4100);
407
insert into t4 values (0x41),(0x4100);
408
select hex(s1) from t1;
409
select hex(s1) from t2;
410
select hex(s1) from t3;
411
select hex(s1) from t4;
412
drop table t1,t2,t3,t4;
414
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
415
create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
417
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
419
insert into t2 values(0x42);
420
insert into t2 values(0x41);
421
select hex(s1) from t2;
422
update t1 set s1=0x123456 where a=2;
423
select hex(s1) from t2;
425
update t1 set s1=0x12 where a=1;
427
update t1 set s1=0x12345678 where a=1;
429
update t1 set s1=0x123457 where a=1;
430
update t1 set s1=0x1220 where a=1;
431
select hex(s1) from t2;
432
update t1 set s1=0x1200 where a=1;
433
select hex(s1) from t2;
434
update t1 set s1=0x4200 where a=1;
435
select hex(s1) from t2;
437
delete from t1 where a=1;
438
delete from t1 where a=2;
439
update t2 set s1=0x4120;
442
delete from t1 where a!=3;
443
select a,hex(s1) from t1;
444
select hex(s1) from t2;
448
create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
449
create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
451
insert into t1 values(1,0x4100),(2,0x41);
452
insert into t2 values(0x41);
453
select hex(s1) from t2;
454
update t1 set s1=0x1234 where a=1;
455
select hex(s1) from t2;
456
update t1 set s1=0x12 where a=2;
457
select hex(s1) from t2;
458
delete from t1 where a=1;
460
delete from t1 where a=2;
461
select a,hex(s1) from t1;
462
select hex(s1) from t2;
465
# Ensure that <tablename>_ibfk_0 is not mistreated as a
466
# generated foreign key identifier. (Bug #16387)
468
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
469
CREATE TABLE t2(a INT) ENGINE=InnoDB;
470
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
471
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
472
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
473
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
474
SHOW CREATE TABLE t2;
478
# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing
481
connect (a,localhost,root,,);
482
connect (b,localhost,root,,);
484
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
485
insert into t1(a) values (1),(2),(3);
488
# in 5.5+, this needs to be created before the UPDATE due to meta-data locking
489
create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
493
update t1 set b = 5 where a = 2;
497
insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
498
(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
499
(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
500
(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
501
(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
511
# Another trigger test
513
connect (a,localhost,root,,);
514
connect (b,localhost,root,,);
516
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
517
create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
518
create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
519
create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
520
create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
521
insert into t1(a) values (1),(2),(3);
522
insert into t2(a) values (1),(2),(3);
523
insert into t3(a) values (1),(2),(3);
524
insert into t4(a) values (1),(2),(3);
525
insert into t3(a) values (5),(7),(8);
526
insert into t4(a) values (5),(7),(8);
527
insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
530
create trigger t1t before insert on t1 for each row begin
531
INSERT INTO t2 SET a = NEW.a;
534
create trigger t2t before insert on t2 for each row begin
535
DELETE FROM t3 WHERE a = NEW.a;
538
create trigger t3t before delete on t3 for each row begin
539
UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
542
create trigger t4t before update on t4 for each row begin
543
UPDATE t5 SET b = b + 1 where a = NEW.a;
548
update t1 set b = b + 5 where a = 1;
549
update t2 set b = b + 5 where a = 1;
550
update t3 set b = b + 5 where a = 1;
551
update t4 set b = b + 5 where a = 1;
552
insert into t5(a) values(20);
555
insert into t1(a) values(7);
556
insert into t2(a) values(8);
557
delete from t2 where a = 3;
558
update t4 set b = b + 1 where a = 3;
565
drop table t1, t2, t3, t4, t5;
571
# Test that cascading updates leading to duplicate keys give the correct
572
# error message (bug #9680)
576
field1 varchar(8) NOT NULL DEFAULT '',
577
field2 varchar(8) NOT NULL DEFAULT '',
578
PRIMARY KEY (field1, field2)
582
field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
583
FOREIGN KEY (field1) REFERENCES t1 (field1)
584
ON DELETE CASCADE ON UPDATE CASCADE
587
INSERT INTO t1 VALUES ('old', 'somevalu');
588
INSERT INTO t1 VALUES ('other', 'anyvalue');
590
INSERT INTO t2 VALUES ('old');
591
INSERT INTO t2 VALUES ('other');
593
--error ER_FOREIGN_DUPLICATE_KEY
594
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
600
# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
614
alter table t1 add constraint c2_fk foreign key (c2)
615
references t2(c1) on delete cascade;
616
show create table t1;
618
alter table t1 drop foreign key c2_fk;
619
show create table t1;
624
# Bug #14360: problem with intervals
627
create table t1(a date) engine=innodb;
628
create table t2(a date, key(a)) engine=innodb;
629
insert into t1 values('2005-10-01');
630
insert into t2 values('2005-10-01');
632
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
635
create table t1 (id int not null, f_id int not null, f int not null,
636
primary key(f_id, id)) engine=innodb;
637
create table t2 (id int not null,s_id int not null,s varchar(200),
638
primary key(id)) engine=innodb;
639
INSERT INTO t1 VALUES (8, 1, 3);
640
INSERT INTO t1 VALUES (1, 2, 1);
641
INSERT INTO t2 VALUES (1, 0, '');
642
INSERT INTO t2 VALUES (8, 1, '');
644
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
646
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
647
where mm.id is null lock in share mode;
651
# Test case where X-locks on unused rows should be released in a
652
# update (because READ COMMITTED isolation level)
655
connect (a,localhost,root,,);
656
connect (b,localhost,root,,);
658
create table t1(a int not null, b int, primary key(a)) engine=innodb;
659
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
661
SET binlog_format='MIXED';
663
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
664
update t1 set b = 5 where b = 1;
666
SET binlog_format='MIXED';
668
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
670
# X-lock to record (7,3) should be released in a update
672
select * from t1 where a = 7 and b = 3 for update;
683
# Test case where no locks should be released (because we are not
684
# using READ COMMITTED isolation level)
687
connect (a,localhost,root,,);
688
connect (b,localhost,root,,);
690
create table t1(a int not null, b int, primary key(a)) engine=innodb;
691
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
694
select * from t1 lock in share mode;
695
update t1 set b = 5 where b = 1;
699
# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
702
select * from t1 where a = 2 and b = 2 for update;
704
# X-lock to record (1,1),(3,1),(5,1) should not be released in a update
717
# Consistent read should be used in following selects
719
# 1) INSERT INTO ... SELECT
720
# 2) UPDATE ... = ( SELECT ...)
721
# 3) CREATE ... SELECT
723
connect (a,localhost,root,,);
724
connect (b,localhost,root,,);
726
create table t1(a int not null, b int, primary key(a)) engine=innodb;
727
insert into t1 values (1,2),(5,3),(4,2);
728
create table t2(d int not null, e int, primary key(d)) engine=innodb;
729
insert into t2 values (8,6),(12,1),(3,1);
732
select * from t2 for update;
734
SET binlog_format='MIXED';
736
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
737
insert into t1 select * from t2;
738
update t1 set b = (select e from t2 where a = d);
739
create table t3(d int not null, e int, primary key(d)) engine=innodb
747
drop table t1, t2, t3;
750
# Consistent read should not be used if
752
# (a) isolation level is serializable OR
753
# (b) select ... lock in share mode OR
754
# (c) select ... for update
756
# in following queries:
758
# 1) INSERT INTO ... SELECT
759
# 2) UPDATE ... = ( SELECT ...)
760
# 3) CREATE ... SELECT
762
connect (a,localhost,root,,);
763
connect (b,localhost,root,,);
764
connect (c,localhost,root,,);
765
connect (d,localhost,root,,);
766
connect (e,localhost,root,,);
767
connect (f,localhost,root,,);
768
connect (g,localhost,root,,);
769
connect (h,localhost,root,,);
770
connect (i,localhost,root,,);
771
connect (j,localhost,root,,);
773
create table t1(a int not null, b int, primary key(a)) engine=innodb;
774
insert into t1 values (1,2),(5,3),(4,2);
775
create table t2(a int not null, b int, primary key(a)) engine=innodb;
776
insert into t2 values (8,6),(12,1),(3,1);
777
create table t3(d int not null, b int, primary key(d)) engine=innodb;
778
insert into t3 values (8,6),(12,1),(3,1);
779
create table t5(a int not null, b int, primary key(a)) engine=innodb;
780
insert into t5 values (1,2),(5,3),(4,2);
781
create table t6(d int not null, e int, primary key(d)) engine=innodb;
782
insert into t6 values (8,6),(12,1),(3,1);
783
create table t8(a int not null, b int, primary key(a)) engine=innodb;
784
insert into t8 values (1,2),(5,3),(4,2);
785
create table t9(d int not null, e int, primary key(d)) engine=innodb;
786
insert into t9 values (8,6),(12,1),(3,1);
789
select * from t2 for update;
791
SET binlog_format='MIXED';
793
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
795
insert into t1 select * from t2;
797
SET binlog_format='MIXED';
799
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
801
update t3 set b = (select b from t2 where a = d);
803
SET binlog_format='MIXED';
805
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
807
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
809
SET binlog_format='MIXED';
811
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
813
insert into t5 (select * from t2 lock in share mode);
815
SET binlog_format='MIXED';
817
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
819
update t6 set e = (select b from t2 where a = d lock in share mode);
821
SET binlog_format='MIXED';
823
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
825
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
827
SET binlog_format='MIXED';
829
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
831
insert into t8 (select * from t2 for update);
833
SET binlog_format='MIXED';
835
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
837
update t9 set e = (select b from t2 where a = d for update);
839
SET binlog_format='MIXED';
841
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
843
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
895
drop table t1, t2, t3, t5, t6, t8, t9;
897
# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"
898
--error ER_WRONG_COLUMN_NAME
899
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
902
# Bug #17152: Wrong result with BINARY comparison on aliased column
906
a BIGINT(20) NOT NULL,
908
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
911
a BIGINT(20) NOT NULL,
912
b VARCHAR(128) NOT NULL,
915
KEY idx_t2_b_c (b,c(200)),
916
CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
918
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
920
INSERT INTO t1 VALUES (1);
921
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
922
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
923
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
924
INSERT INTO t2 VALUES (1, 'customer_over', '1');
926
SELECT * FROM t2 WHERE b = 'customer_over';
927
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
928
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
929
/* Bang: Empty result set, above was expected: */
930
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
931
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
936
# Test optimize on table with open transaction
939
CREATE TABLE t1 ( a int ) ENGINE=innodb;
941
INSERT INTO t1 VALUES (1);
946
# Bug #24741 (existing cascade clauses disappear when adding foreign keys)
949
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
951
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
952
CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
953
ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
955
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
956
DELETE CASCADE ON UPDATE CASCADE;
958
SHOW CREATE TABLE t2;
962
# Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns
963
# for which there is a foreign key constraint ON ... SET NULL.
966
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
967
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
968
INSERT INTO t1 VALUES (1);
969
INSERT INTO t2 VALUES (1);
970
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
971
# mysqltest first does replace_regex, then replace_result
972
--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
973
# Embedded server doesn't chdir to data directory
974
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
976
ALTER TABLE t2 MODIFY a INT NOT NULL;
981
# Bug #26835: table corruption after delete+insert
984
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
986
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
988
INSERT INTO t1 VALUES ('DDD');
993
# Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)
994
# Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))
997
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
1000
INSERT INTO t1 VALUES (0),(347),(0);
1003
SHOW CREATE TABLE t1;
1005
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
1006
INSERT INTO t2 VALUES(42),(347),(348);
1007
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
1008
SHOW CREATE TABLE t1;
1013
# Bug #21101 (Prints wrong error message if max row size is too large)
1015
SET innodb_strict_mode=ON;
1018
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
1019
c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
1020
c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
1021
c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
1022
c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
1023
c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
1024
c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
1025
c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
1027
SET innodb_strict_mode=OFF;
1030
# Bug #31860 InnoDB assumes AUTOINC values can only be positive.
1032
DROP TABLE IF EXISTS t1;
1034
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
1036
INSERT INTO t1 VALUES(-10);
1039
# NOTE: The server really needs to be restarted at this point
1040
# for the test to be useful.
1042
# Without the fix InnoDB would trip over an assertion here.
1043
INSERT INTO t1 VALUES(NULL);
1044
# The next value should be 1 and not -9 or a -ve number
1049
# Bug #21409 Incorrect result returned when in READ-COMMITTED with
1052
CONNECT (c1,localhost,root,,);
1053
CONNECT (c2,localhost,root,,);
1055
SET binlog_format='MIXED';
1056
SET TX_ISOLATION='read-committed';
1058
DROP TABLE IF EXISTS t1, t2;
1059
CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
1060
CREATE TABLE t2 LIKE t1;
1063
SET binlog_format='MIXED';
1064
SET TX_ISOLATION='read-committed';
1066
INSERT INTO t1 VALUES (1);
1069
SELECT * FROM t1 WHERE a=1;
1072
CONNECT (c1,localhost,root,,);
1073
CONNECT (c2,localhost,root,,);
1075
SET binlog_format='MIXED';
1076
SET TX_ISOLATION='read-committed';
1080
SET binlog_format='MIXED';
1081
SET TX_ISOLATION='read-committed';
1083
INSERT INTO t1 VALUES (2);
1086
# The result set below should be the same for both selects
1087
SELECT * FROM t1 WHERE a=2;
1088
SELECT * FROM t1 WHERE a=2;
1096
# Bug #29157 UPDATE, changed rows incorrect
1098
create table t1 (i int, j int) engine=innodb;
1099
insert into t1 (i, j) values (1, 1), (2, 2);
1101
update t1 set j = 2;
1106
# Bug #32440 InnoDB free space info does not appear in SHOW TABLE STATUS or
1109
create table t1 (id int) comment='this is a comment' engine=innodb;
1110
select table_comment, data_free > 0 as data_free_is_set
1111
from information_schema.tables
1112
where table_schema='test' and table_name = 't1';
1120
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
1121
c2 VARCHAR(128) NOT NULL,
1123
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
1126
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
1127
c2 INT(10) UNSIGNED DEFAULT NULL,
1129
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
1131
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
1132
ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
1133
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
1138
# Bug #29507 TRUNCATE shows to many rows effected
1141
CREATE TABLE t1 (c1 int default NULL,
1143
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1148
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
1154
# Bug#35537 Innodb doesn't increment handler_update and handler_delete.
1156
-- disable_query_log
1157
-- disable_result_log
1159
CONNECT (c1,localhost,root,,);
1161
DROP TABLE IF EXISTS bug35537;
1162
CREATE TABLE bug35537 (
1166
INSERT INTO bug35537 VALUES (1);
1168
-- enable_result_log
1170
SHOW SESSION STATUS LIKE 'Handler_update%';
1171
SHOW SESSION STATUS LIKE 'Handler_delete%';
1173
UPDATE bug35537 SET c1 = 2 WHERE c1 = 1;
1174
DELETE FROM bug35537 WHERE c1 = 2;
1176
SHOW SESSION STATUS LIKE 'Handler_update%';
1177
SHOW SESSION STATUS LIKE 'Handler_delete%';
1179
DROP TABLE bug35537;
1184
SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig;