1
-- source include/have_innodb_plugin.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);
113
INSERT INTO t1 (id) VALUES (NULL);
116
# continued from above; test that doing a slow TRUNCATE on a table with 0
117
# rows resets autoincrement columns
120
INSERT INTO t1 (id) VALUES (NULL);
124
# Test that foreign keys in temporary tables are not accepted (bug #12084)
131
CREATE TEMPORARY TABLE t2
133
id INT NOT NULL PRIMARY KEY,
135
FOREIGN KEY (b) REFERENCES test.t1(id)
140
# Test that index column max sizes are honored (bug #13315)
144
create table t1 (col1 varchar(2000), index (col1(767)))
145
character set = latin1 engine = innodb;
148
create table t2 (col1 char(255), index (col1))
149
character set = latin1 engine = innodb;
150
create table t3 (col1 binary(255), index (col1))
151
character set = latin1 engine = innodb;
152
create table t4 (col1 varchar(767), index (col1))
153
character set = latin1 engine = innodb;
154
create table t5 (col1 varchar(767) primary key)
155
character set = latin1 engine = innodb;
156
create table t6 (col1 varbinary(767) primary key)
157
character set = latin1 engine = innodb;
158
create table t7 (col1 text, index(col1(767)))
159
character set = latin1 engine = innodb;
160
create table t8 (col1 blob, index(col1(767)))
161
character set = latin1 engine = innodb;
163
# multi-column indexes are allowed to be longer
164
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
165
character set = latin1 engine = innodb;
167
show create table t9;
169
drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
171
# these should have their index length trimmed
172
create table t1 (col1 varchar(768), index(col1))
173
character set = latin1 engine = innodb;
174
create table t2 (col1 varbinary(768), index(col1))
175
character set = latin1 engine = innodb;
176
create table t3 (col1 text, index(col1(768)))
177
character set = latin1 engine = innodb;
178
create table t4 (col1 blob, index(col1(768)))
179
character set = latin1 engine = innodb;
181
show create table t1;
183
drop table t1, t2, t3, t4;
185
# these should be refused
187
create table t1 (col1 varchar(768) primary key)
188
character set = latin1 engine = innodb;
190
create table t2 (col1 varbinary(768) primary key)
191
character set = latin1 engine = innodb;
193
create table t3 (col1 text, primary key(col1(768)))
194
character set = latin1 engine = innodb;
196
create table t4 (col1 blob, primary key(col1(768)))
197
character set = latin1 engine = innodb;
200
# Test improved foreign key error messages (bug #3443)
211
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
215
INSERT INTO t2 VALUES(2);
217
INSERT INTO t1 VALUES(1);
218
INSERT INTO t2 VALUES(1);
221
DELETE FROM t1 WHERE id = 1;
226
SET FOREIGN_KEY_CHECKS=0;
228
SET FOREIGN_KEY_CHECKS=1;
231
INSERT INTO t2 VALUES(3);
235
# Test that checksum table uses a consistent read Bug #12669
237
connect (a,localhost,root,,);
238
connect (b,localhost,root,,);
240
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
241
insert into t1 values (1),(2);
245
insert into t1 values(3);
248
# Here checksum should not see insert
260
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
261
insert into t1 values (1),(2);
266
insert into t1 values(3);
269
# Here checksum sees insert
278
# tests for bugs #9802 and #13778
280
# test that FKs between invalid types are not accepted
282
set foreign_key_checks=0;
283
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
284
# Embedded server doesn't chdir to data directory
285
--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''
287
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
288
set foreign_key_checks=1;
291
# test that FKs between different charsets are not accepted in CREATE even
294
set foreign_key_checks=0;
295
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
296
# Embedded server doesn't chdir to data directory
297
--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''
299
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
300
set foreign_key_checks=1;
303
# test that invalid datatype conversions with ALTER are not allowed
305
set foreign_key_checks=0;
306
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
307
create table t1(a varchar(10) primary key) engine = innodb;
309
alter table t1 modify column a int;
310
set foreign_key_checks=1;
313
# test that charset conversions with ALTER are allowed when f_k_c is 0
315
set foreign_key_checks=0;
316
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
317
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
318
alter table t1 convert to character set utf8;
319
set foreign_key_checks=1;
322
# test that RENAME does not allow invalid charsets when f_k_c is 0
324
set foreign_key_checks=0;
325
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
326
create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
327
# Embedded server doesn't chdir to data directory
328
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
330
rename table t3 to t1;
331
set foreign_key_checks=1;
334
# test that foreign key errors are reported correctly (Bug #15550)
336
create table t1(a int primary key) row_format=redundant engine=innodb;
337
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
338
create table t3(a int primary key) row_format=compact engine=innodb;
339
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
341
insert into t1 values(1);
342
insert into t3 values(1);
344
insert into t2 values(2);
346
insert into t4 values(2);
347
insert into t2 values(1);
348
insert into t4 values(1);
366
drop table t4,t3,t2,t1;
370
# Test that we can create a large (>1K) key
372
create table t1 (a varchar(255) character set utf8,
373
b varchar(255) character set utf8,
374
c varchar(255) character set utf8,
375
d varchar(255) character set utf8,
376
key (a,b,c,d)) engine=innodb;
378
--error ER_TOO_LONG_KEY
379
create table t1 (a varchar(255) character set utf8,
380
b varchar(255) character set utf8,
381
c varchar(255) character set utf8,
382
d varchar(255) character set utf8,
383
e varchar(255) character set utf8,
384
key (a,b,c,d,e)) engine=innodb;
387
# test the padding of BINARY types and collations (Bug #14189)
389
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
390
create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
391
create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
392
create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
394
insert into t1 values (0x41),(0x4120),(0x4100);
395
-- error ER_DUP_ENTRY
396
insert into t2 values (0x41),(0x4120),(0x4100);
397
insert into t2 values (0x41),(0x4120);
398
-- error ER_DUP_ENTRY
399
insert into t3 values (0x41),(0x4120),(0x4100);
400
insert into t3 values (0x41),(0x4100);
401
-- error ER_DUP_ENTRY
402
insert into t4 values (0x41),(0x4120),(0x4100);
403
insert into t4 values (0x41),(0x4100);
404
select hex(s1) from t1;
405
select hex(s1) from t2;
406
select hex(s1) from t3;
407
select hex(s1) from t4;
408
drop table t1,t2,t3,t4;
410
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
411
create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
413
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
415
insert into t2 values(0x42);
416
insert into t2 values(0x41);
417
select hex(s1) from t2;
418
update t1 set s1=0x123456 where a=2;
419
select hex(s1) from t2;
421
update t1 set s1=0x12 where a=1;
423
update t1 set s1=0x12345678 where a=1;
425
update t1 set s1=0x123457 where a=1;
426
update t1 set s1=0x1220 where a=1;
427
select hex(s1) from t2;
428
update t1 set s1=0x1200 where a=1;
429
select hex(s1) from t2;
430
update t1 set s1=0x4200 where a=1;
431
select hex(s1) from t2;
433
delete from t1 where a=1;
434
delete from t1 where a=2;
435
update t2 set s1=0x4120;
438
delete from t1 where a!=3;
439
select a,hex(s1) from t1;
440
select hex(s1) from t2;
444
create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
445
create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
447
insert into t1 values(1,0x4100),(2,0x41);
448
insert into t2 values(0x41);
449
select hex(s1) from t2;
450
update t1 set s1=0x1234 where a=1;
451
select hex(s1) from t2;
452
update t1 set s1=0x12 where a=2;
453
select hex(s1) from t2;
454
delete from t1 where a=1;
456
delete from t1 where a=2;
457
select a,hex(s1) from t1;
458
select hex(s1) from t2;
461
# Ensure that <tablename>_ibfk_0 is not mistreated as a
462
# generated foreign key identifier. (Bug #16387)
464
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
465
CREATE TABLE t2(a INT) ENGINE=InnoDB;
466
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
467
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
468
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
469
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
470
SHOW CREATE TABLE t2;
474
# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing
477
connect (a,localhost,root,,);
478
connect (b,localhost,root,,);
480
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
481
insert into t1(a) values (1),(2),(3);
485
update t1 set b = 5 where a = 2;
488
create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
492
insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
493
(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
494
(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
495
(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
496
(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
506
# Another trigger test
508
connect (a,localhost,root,,);
509
connect (b,localhost,root,,);
511
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
512
create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
513
create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
514
create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
515
create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
516
insert into t1(a) values (1),(2),(3);
517
insert into t2(a) values (1),(2),(3);
518
insert into t3(a) values (1),(2),(3);
519
insert into t4(a) values (1),(2),(3);
520
insert into t3(a) values (5),(7),(8);
521
insert into t4(a) values (5),(7),(8);
522
insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
525
create trigger t1t before insert on t1 for each row begin
526
INSERT INTO t2 SET a = NEW.a;
529
create trigger t2t before insert on t2 for each row begin
530
DELETE FROM t3 WHERE a = NEW.a;
533
create trigger t3t before delete on t3 for each row begin
534
UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
537
create trigger t4t before update on t4 for each row begin
538
UPDATE t5 SET b = b + 1 where a = NEW.a;
543
update t1 set b = b + 5 where a = 1;
544
update t2 set b = b + 5 where a = 1;
545
update t3 set b = b + 5 where a = 1;
546
update t4 set b = b + 5 where a = 1;
547
insert into t5(a) values(20);
550
insert into t1(a) values(7);
551
insert into t2(a) values(8);
552
delete from t2 where a = 3;
553
update t4 set b = b + 1 where a = 3;
559
drop table t1, t2, t3, t4, t5;
565
# Test that cascading updates leading to duplicate keys give the correct
566
# error message (bug #9680)
570
field1 varchar(8) NOT NULL DEFAULT '',
571
field2 varchar(8) NOT NULL DEFAULT '',
572
PRIMARY KEY (field1, field2)
576
field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
577
FOREIGN KEY (field1) REFERENCES t1 (field1)
578
ON DELETE CASCADE ON UPDATE CASCADE
581
INSERT INTO t1 VALUES ('old', 'somevalu');
582
INSERT INTO t1 VALUES ('other', 'anyvalue');
584
INSERT INTO t2 VALUES ('old');
585
INSERT INTO t2 VALUES ('other');
587
--error ER_FOREIGN_DUPLICATE_KEY
588
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
594
# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
608
alter table t1 add constraint c2_fk foreign key (c2)
609
references t2(c1) on delete cascade;
610
show create table t1;
612
alter table t1 drop foreign key c2_fk;
613
show create table t1;
618
# Bug #14360: problem with intervals
621
create table t1(a date) engine=innodb;
622
create table t2(a date, key(a)) engine=innodb;
623
insert into t1 values('2005-10-01');
624
insert into t2 values('2005-10-01');
626
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
629
create table t1 (id int not null, f_id int not null, f int not null,
630
primary key(f_id, id)) engine=innodb;
631
create table t2 (id int not null,s_id int not null,s varchar(200),
632
primary key(id)) engine=innodb;
633
INSERT INTO t1 VALUES (8, 1, 3);
634
INSERT INTO t1 VALUES (1, 2, 1);
635
INSERT INTO t2 VALUES (1, 0, '');
636
INSERT INTO t2 VALUES (8, 1, '');
638
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
640
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
641
where mm.id is null lock in share mode;
645
# Test case where X-locks on unused rows should be released in a
646
# update (because READ COMMITTED isolation level)
649
connect (a,localhost,root,,);
650
connect (b,localhost,root,,);
652
create table t1(a int not null, b int, primary key(a)) engine=innodb;
653
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
655
SET binlog_format='MIXED';
657
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
658
update t1 set b = 5 where b = 1;
660
SET binlog_format='MIXED';
662
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
664
# X-lock to record (7,3) should be released in a update
666
select * from t1 where a = 7 and b = 3 for update;
677
# Test case where no locks should be released (because we are not
678
# using READ COMMITTED isolation level)
681
connect (a,localhost,root,,);
682
connect (b,localhost,root,,);
684
create table t1(a int not null, b int, primary key(a)) engine=innodb;
685
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
688
select * from t1 lock in share mode;
689
update t1 set b = 5 where b = 1;
693
# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
696
select * from t1 where a = 2 and b = 2 for update;
698
# X-lock to record (1,1),(3,1),(5,1) should not be released in a update
711
# Consistent read should be used in following selects
713
# 1) INSERT INTO ... SELECT
714
# 2) UPDATE ... = ( SELECT ...)
715
# 3) CREATE ... SELECT
717
connect (a,localhost,root,,);
718
connect (b,localhost,root,,);
720
create table t1(a int not null, b int, primary key(a)) engine=innodb;
721
insert into t1 values (1,2),(5,3),(4,2);
722
create table t2(d int not null, e int, primary key(d)) engine=innodb;
723
insert into t2 values (8,6),(12,1),(3,1);
726
select * from t2 for update;
728
SET binlog_format='MIXED';
730
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
731
insert into t1 select * from t2;
732
update t1 set b = (select e from t2 where a = d);
733
create table t3(d int not null, e int, primary key(d)) engine=innodb
741
drop table t1, t2, t3;
744
# Consistent read should not be used if
746
# (a) isolation level is serializable OR
747
# (b) select ... lock in share mode OR
748
# (c) select ... for update
750
# in following queries:
752
# 1) INSERT INTO ... SELECT
753
# 2) UPDATE ... = ( SELECT ...)
754
# 3) CREATE ... SELECT
756
connect (a,localhost,root,,);
757
connect (b,localhost,root,,);
758
connect (c,localhost,root,,);
759
connect (d,localhost,root,,);
760
connect (e,localhost,root,,);
761
connect (f,localhost,root,,);
762
connect (g,localhost,root,,);
763
connect (h,localhost,root,,);
764
connect (i,localhost,root,,);
765
connect (j,localhost,root,,);
767
create table t1(a int not null, b int, primary key(a)) engine=innodb;
768
insert into t1 values (1,2),(5,3),(4,2);
769
create table t2(a int not null, b int, primary key(a)) engine=innodb;
770
insert into t2 values (8,6),(12,1),(3,1);
771
create table t3(d int not null, b int, primary key(d)) engine=innodb;
772
insert into t3 values (8,6),(12,1),(3,1);
773
create table t5(a int not null, b int, primary key(a)) engine=innodb;
774
insert into t5 values (1,2),(5,3),(4,2);
775
create table t6(d int not null, e int, primary key(d)) engine=innodb;
776
insert into t6 values (8,6),(12,1),(3,1);
777
create table t8(a int not null, b int, primary key(a)) engine=innodb;
778
insert into t8 values (1,2),(5,3),(4,2);
779
create table t9(d int not null, e int, primary key(d)) engine=innodb;
780
insert into t9 values (8,6),(12,1),(3,1);
783
select * from t2 for update;
785
SET binlog_format='MIXED';
787
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
789
insert into t1 select * from t2;
791
SET binlog_format='MIXED';
793
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
795
update t3 set b = (select b from t2 where a = d);
797
SET binlog_format='MIXED';
799
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
801
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
803
SET binlog_format='MIXED';
805
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
807
insert into t5 (select * from t2 lock in share mode);
809
SET binlog_format='MIXED';
811
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
813
update t6 set e = (select b from t2 where a = d lock in share mode);
815
SET binlog_format='MIXED';
817
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
819
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
821
SET binlog_format='MIXED';
823
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
825
insert into t8 (select * from t2 for update);
827
SET binlog_format='MIXED';
829
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
831
update t9 set e = (select b from t2 where a = d for update);
833
SET binlog_format='MIXED';
835
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
837
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
889
drop table t1, t2, t3, t5, t6, t8, t9;
891
# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"
892
--error ER_WRONG_COLUMN_NAME
893
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
896
# Bug #17152: Wrong result with BINARY comparison on aliased column
900
a BIGINT(20) NOT NULL,
902
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
905
a BIGINT(20) NOT NULL,
906
b VARCHAR(128) NOT NULL,
909
KEY idx_t2_b_c (b,c(200)),
910
CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
912
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
914
INSERT INTO t1 VALUES (1);
915
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
916
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
917
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
918
INSERT INTO t2 VALUES (1, 'customer_over', '1');
920
SELECT * FROM t2 WHERE b = 'customer_over';
921
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
922
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
923
/* Bang: Empty result set, above was expected: */
924
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
925
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
930
# Test optimize on table with open transaction
933
CREATE TABLE t1 ( a int ) ENGINE=innodb;
935
INSERT INTO t1 VALUES (1);
940
# Bug #24741 (existing cascade clauses disappear when adding foreign keys)
943
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
945
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
946
CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
947
ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
949
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
950
DELETE CASCADE ON UPDATE CASCADE;
952
SHOW CREATE TABLE t2;
956
# Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns
957
# for which there is a foreign key constraint ON ... SET NULL.
960
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
961
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
962
INSERT INTO t1 VALUES (1);
963
INSERT INTO t2 VALUES (1);
964
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
965
# mysqltest first does replace_regex, then replace_result
966
--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
967
# Embedded server doesn't chdir to data directory
968
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
970
ALTER TABLE t2 MODIFY a INT NOT NULL;
975
# Bug #26835: table corruption after delete+insert
978
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
980
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
982
INSERT INTO t1 VALUES ('DDD');
987
# Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)
988
# Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))
991
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
994
INSERT INTO t1 VALUES (0),(347),(0);
997
SHOW CREATE TABLE t1;
999
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
1000
INSERT INTO t2 VALUES(42),(347),(348);
1001
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
1002
SHOW CREATE TABLE t1;
1007
# Bug #21101 (Prints wrong error message if max row size is too large)
1009
SET innodb_strict_mode=ON;
1012
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
1013
c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
1014
c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
1015
c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
1016
c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
1017
c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
1018
c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
1019
c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
1021
SET innodb_strict_mode=OFF;
1024
# Bug #31860 InnoDB assumes AUTOINC values can only be positive.
1026
DROP TABLE IF EXISTS t1;
1028
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
1030
INSERT INTO t1 VALUES(-10);
1033
# NOTE: The server really needs to be restarted at this point
1034
# for the test to be useful.
1036
# Without the fix InnoDB would trip over an assertion here.
1037
INSERT INTO t1 VALUES(NULL);
1038
# The next value should be 1 and not -9 or a -ve number
1043
# Bug #21409 Incorrect result returned when in READ-COMMITTED with
1046
CONNECT (c1,localhost,root,,);
1047
CONNECT (c2,localhost,root,,);
1049
SET binlog_format='MIXED';
1050
SET TX_ISOLATION='read-committed';
1052
DROP TABLE IF EXISTS t1, t2;
1053
CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
1054
CREATE TABLE t2 LIKE t1;
1057
SET binlog_format='MIXED';
1058
SET TX_ISOLATION='read-committed';
1060
INSERT INTO t1 VALUES (1);
1063
SELECT * FROM t1 WHERE a=1;
1066
CONNECT (c1,localhost,root,,);
1067
CONNECT (c2,localhost,root,,);
1069
SET binlog_format='MIXED';
1070
SET TX_ISOLATION='read-committed';
1074
SET binlog_format='MIXED';
1075
SET TX_ISOLATION='read-committed';
1077
INSERT INTO t1 VALUES (2);
1080
# The result set below should be the same for both selects
1081
SELECT * FROM t1 WHERE a=2;
1082
SELECT * FROM t1 WHERE a=2;
1090
# Bug #29157 UPDATE, changed rows incorrect
1092
create table t1 (i int, j int) engine=innodb;
1093
insert into t1 (i, j) values (1, 1), (2, 2);
1095
update t1 set j = 2;
1100
# Bug #32440 InnoDB free space info does not appear in SHOW TABLE STATUS or
1103
create table t1 (id int) comment='this is a comment' engine=innodb;
1104
select table_comment, data_free > 0 as data_free_is_set
1105
from information_schema.tables
1106
where table_schema='test' and table_name = 't1';
1114
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
1115
c2 VARCHAR(128) NOT NULL,
1117
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
1120
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
1121
c2 INT(10) UNSIGNED DEFAULT NULL,
1123
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
1125
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
1126
ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
1127
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
1132
# Bug #29507 TRUNCATE shows to many rows effected
1135
CREATE TABLE t1 (c1 int default NULL,
1137
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1142
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
1148
# Bug#35537 Innodb doesn't increment handler_update and handler_delete.
1150
-- disable_query_log
1151
-- disable_result_log
1153
CONNECT (c1,localhost,root,,);
1155
DROP TABLE IF EXISTS bug35537;
1156
CREATE TABLE bug35537 (
1160
INSERT INTO bug35537 VALUES (1);
1162
-- enable_result_log
1164
SHOW SESSION STATUS LIKE 'Handler_update%';
1165
SHOW SESSION STATUS LIKE 'Handler_delete%';
1167
UPDATE bug35537 SET c1 = 2 WHERE c1 = 1;
1168
DELETE FROM bug35537 WHERE c1 = 2;
1170
SHOW SESSION STATUS LIKE 'Handler_update%';
1171
SHOW SESSION STATUS LIKE 'Handler_delete%';
1173
DROP TABLE bug35537;
1178
SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig;
added
removed
mysql-test/suite/innodb_plugin/t/innodb_misc1.test
