1
#######################################################################
3
# Please, DO NOT TOUCH this file as well as the innodb.result file. #
4
# These files are to be modified ONLY BY INNOBASE guys. #
6
# Use innodb_mysql.[test|result] files instead. #
8
# If nevertheless you need to make some changes here, please, forward #
9
# your commit message #
10
# To: innodb_dev_ww@oracle.com #
11
# Cc: dev-innodb@mysql.com #
12
# (otherwise your changes may be erased). #
14
#######################################################################
16
-- source include/have_innodb_plugin.inc
18
let $MYSQLD_DATADIR= `select @@datadir`;
20
# Save the original values of some variables in order to be able to
21
# estimate how much they have changed during the tests. Previously this
22
# test assumed that e.g. rows_deleted is 0 here and after deleting 23
23
# rows it expected that rows_deleted will be 23. Now we do not make
24
# assumptions about the values of the variables at the beginning, e.g.
25
# rows_deleted should be 23 + "rows_deleted before the test". This allows
26
# the test to be run multiple times without restarting the mysqld server.
27
# See Bug#43309 Test main.innodb can't be run twice
29
SET @innodb_thread_concurrency_orig = @@innodb_thread_concurrency;
31
SET @innodb_rows_deleted_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted');
32
SET @innodb_rows_inserted_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted');
33
SET @innodb_rows_updated_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated');
34
SET @innodb_row_lock_waits_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits');
35
SET @innodb_row_lock_current_waits_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits');
36
SET @innodb_row_lock_time_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time');
37
SET @innodb_row_lock_time_max_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max');
38
SET @innodb_row_lock_time_avg_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg');
42
drop table if exists t1,t2,t3,t4;
43
drop database if exists mysqltest;
47
# Small basic test with ignore
50
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
52
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
53
select id, code, name from t1 order by id;
55
update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
56
select id, code, name from t1 order by id;
57
update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
58
select id, code, name from t1 order by id;
64
# The 'replace_column' statements are needed because the cardinality calculated
65
# by innodb is not always the same between runs
69
id int(11) NOT NULL auto_increment,
70
parent_id int(11) DEFAULT '0' NOT NULL,
71
level tinyint(4) DEFAULT '0' NOT NULL,
73
KEY parent_id (parent_id),
76
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);
77
update t1 set parent_id=parent_id+100;
78
select * from t1 where parent_id=102;
79
update t1 set id=id+1000;
80
-- error ER_DUP_ENTRY,1022
81
update t1 set id=1024 where id=1009;
83
update ignore t1 set id=id+1; # This will change all rows
85
update ignore t1 set id=1023 where id=1010;
86
select * from t1 where parent_id=102;
88
explain select level from t1 where level=1;
90
explain select level,id from t1 where level=1;
92
explain select level,id,parent_id from t1 where level=1;
93
select level,id from t1 where level=1;
94
select level,id,parent_id from t1 where level=1;
105
gesuchnr int(11) DEFAULT '0' NOT NULL,
106
benutzer_id int(11) DEFAULT '0' NOT NULL,
107
PRIMARY KEY (gesuchnr,benutzer_id)
110
replace into t1 (gesuchnr,benutzer_id) values (2,1);
111
replace into t1 (gesuchnr,benutzer_id) values (1,1);
112
replace into t1 (gesuchnr,benutzer_id) values (1,1);
117
# test delete using hidden_primary_key
120
create table t1 (a int) engine=innodb;
121
insert into t1 values (1), (2);
123
delete from t1 where a = 1;
128
create table t1 (a int,b varchar(20)) engine=innodb;
129
insert into t1 values (1,""), (2,"testing");
130
delete from t1 where a = 1;
132
create index skr on t1 (a);
133
insert into t1 values (3,""), (4,"testing");
140
# Test of reading on secondary key with may be null
142
create table t1 (a int,b varchar(20),key(a)) engine=innodb;
143
insert into t1 values (1,""), (2,"testing");
144
select * from t1 where a = 1;
151
create table t1 (n int not null primary key) engine=innodb;
153
insert into t1 values (4);
155
select n, "after rollback" from t1;
156
insert into t1 values (4);
158
select n, "after commit" from t1;
160
insert into t1 values (5);
161
-- error ER_DUP_ENTRY
162
insert into t1 values (4);
164
select n, "after commit" from t1;
166
insert into t1 values (6);
167
-- error ER_DUP_ENTRY
168
insert into t1 values (4);
175
savepoint `my_savepoint`;
176
insert into t1 values (7);
178
insert into t1 values (3);
181
rollback to savepoint savept2;
183
rollback to savepoint savept3;
184
rollback to savepoint savept2;
185
release savepoint `my_savepoint`;
188
rollback to savepoint `my_savepoint`;
190
rollback to savepoint savept2;
191
insert into t1 values (8);
201
# Test for commit and FLUSH TABLES WITH READ LOCK
204
create table t1 (n int not null primary key) engine=innodb;
206
insert into t1 values (4);
207
flush tables with read lock;
209
# Current code can't handle a read lock in middle of transaction
218
# Testing transactions
221
create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
223
insert into t1 values(1,'hamdouni');
224
select id as afterbegin_id,nom as afterbegin_nom from t1;
226
select id as afterrollback_id,nom as afterrollback_nom from t1;
228
insert into t1 values(2,'mysql');
229
select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
231
select id as afterrollback_id,nom as afterrollback_nom from t1;
236
# Simple not autocommit test
239
CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
240
insert into t1 values ('pippo', 12);
241
-- error ER_DUP_ENTRY
242
insert into t1 values ('pippo', 12); # Gives error
244
delete from t1 where id = 'pippo';
247
insert into t1 values ('pippo', 12);
258
# Test of active transactions
261
create table t1 (a integer) engine=innodb;
263
rename table t1 to t2;
264
create table t1 (b integer) engine=innodb;
265
insert into t1 values (1);
268
rename table t2 to t1;
273
# The following simple tests failed at some point
276
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
277
INSERT INTO t1 VALUES (1, 'Jochen');
281
CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
283
INSERT INTO t1 SET _userid='marc@anyware.co.uk';
286
SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
291
# Test when reading on part of unique key
294
user_id int(10) DEFAULT '0' NOT NULL,
297
ref_email varchar(100) DEFAULT '' NOT NULL,
299
PRIMARY KEY (user_id,ref_email)
302
INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');
303
select * from t1 where user_id=10292;
304
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
305
select * from t1 where user_id=10292;
306
select * from t1 where user_id>=10292;
307
select * from t1 where user_id>10292;
308
select * from t1 where user_id<10292;
312
# Test that keys are created in right order
315
CREATE TABLE t1 (a int not null, b int not null,c int not null,
316
key(a),primary key(a,b), unique(c),key(a),unique(b));
322
# Test of ALTER TABLE and innodb tables
325
create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
326
alter table t1 engine=innodb;
327
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
329
update t1 set col2='7' where col1='4';
331
alter table t1 add co3 int not null;
333
update t1 set col2='9' where col1='2';
338
# INSERT INTO innodb tables
341
create table t1 (a int not null , b int, primary key (a)) engine = innodb;
342
create table t2 (a int not null , b int, primary key (a)) engine = myisam;
343
insert into t1 VALUES (1,3) , (2,3), (3,3);
345
insert into t2 select * from t1;
347
delete from t1 where b = 3;
349
insert into t1 select * from t2;
355
# ORDER BY on not primary key
359
user_name varchar(12),
362
user_id int(11) DEFAULT '0' NOT NULL,
368
dummy_primary_key int(11) NOT NULL auto_increment,
369
PRIMARY KEY (dummy_primary_key)
371
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
372
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
373
INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);
374
INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);
375
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
376
select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
380
# Testing of tables without primary keys
384
id int(11) NOT NULL auto_increment,
385
parent_id int(11) DEFAULT '0' NOT NULL,
386
level tinyint(4) DEFAULT '0' NOT NULL,
388
KEY parent_id (parent_id),
391
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1);
392
INSERT INTO t1 values (179,5,2);
393
update t1 set parent_id=parent_id+100;
394
select * from t1 where parent_id=102;
395
update t1 set id=id+1000;
396
update t1 set id=1024 where id=1009;
398
update ignore t1 set id=id+1; # This will change all rows
400
update ignore t1 set id=1023 where id=1010;
401
select * from t1 where parent_id=102;
403
explain select level from t1 where level=1;
404
select level,id from t1 where level=1;
405
select level,id,parent_id from t1 where level=1;
406
select level,id from t1 where level=1 order by id;
407
delete from t1 where level=1;
412
# Test of index only reads
415
sca_code char(6) NOT NULL,
416
cat_code char(6) NOT NULL,
417
sca_desc varchar(50),
418
lan_code char(2) NOT NULL,
419
sca_pic varchar(100),
420
sca_sdesc varchar(50),
421
sca_sch_desc varchar(16),
422
PRIMARY KEY (sca_code, cat_code, lan_code),
423
INDEX sca_pic (sca_pic)
426
INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');
427
select count(*) from t1 where sca_code = 'PD';
428
select count(*) from t1 where sca_code <= 'PD';
429
select count(*) from t1 where sca_pic is null;
430
# this should be fixed by MySQL (see Bug #51451)
431
# now that http://bugs.mysql.com/49838 is fixed the following ALTER does
432
# copy the table instead of failing
433
# --error ER_WRONG_NAME_FOR_INDEX
434
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
435
alter table t1 drop index sca_pic;
436
alter table t1 add index sca_pic (cat_code, sca_pic);
437
select count(*) from t1 where sca_code='PD' and sca_pic is null;
438
select count(*) from t1 where cat_code='E';
440
# this should be fixed by MySQL (see Bug #51451)
441
--error ER_WRONG_NAME_FOR_INDEX
442
alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
443
alter table t1 drop index sca_pic;
444
alter table t1 add index (sca_pic, cat_code);
445
select count(*) from t1 where sca_code='PD' and sca_pic is null;
446
select count(*) from t1 where sca_pic >= 'n';
447
select sca_pic from t1 where sca_pic is null;
448
update t1 set sca_pic="test" where sca_pic is null;
449
delete from t1 where sca_code='pd';
453
# Test of opening table twice and timestamps
456
CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
457
insert into t1 (a) values(1),(2),(3);
458
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
459
select a from t1 natural join t1 as t2 where b >= @a order by a;
460
update t1 set a=5 where a=1;
465
# Test with variable length primary key
467
create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
468
insert into t1 values("hello",1),("world",2);
469
select * from t1 order by b desc;
476
# Test of create index with NULL columns
478
create table t1 (i int, j int ) ENGINE=innodb;
479
insert into t1 values (1,2);
480
select * from t1 where i=1 and j=2;
481
create index ax1 on t1 (i,j);
482
select * from t1 where i=1 and j=2;
486
# Test min-max optimization
490
a int3 unsigned NOT NULL,
491
b int1 unsigned NOT NULL,
495
INSERT INTO t1 VALUES (1, 1);
496
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
500
# Test INSERT DELAYED
503
CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb;
504
# Can't test this in 3.23
505
# INSERT DELAYED INTO t1 VALUES (1);
506
INSERT INTO t1 VALUES (1);
512
# Crash when using many tables (Test case by Jeremy D Zawodny)
515
create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = innodb;
516
insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
518
explain select * from t1 where a > 0 and a < 50;
525
create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb;
526
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
527
LOCK TABLES t1 WRITE;
529
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
535
create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb;
536
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
537
LOCK TABLES t1 WRITE;
540
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
542
insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
544
select id,id3 from t1;
551
create table t1 (a char(20), unique (a(5))) engine=innodb;
553
create table t1 (a char(20), index (a(5))) engine=innodb;
554
show create table t1;
558
# Test using temporary table and auto_increment
561
create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
562
insert into t1 values (NULL),(NULL),(NULL);
563
delete from t1 where a=3;
564
insert into t1 values (NULL);
566
alter table t1 add b int;
573
id int auto_increment primary key,
574
name varchar(32) not null,
579
insert into t1 values (1,'one','one value',101),
580
(2,'two','two value',102),(3,'three','three value',103);
582
replace into t1 (value,name,uid) values ('other value','two',102);
583
delete from t1 where uid=102;
585
replace into t1 (value,name,uid) values ('other value','two',102);
587
replace into t1 (value,name,uid) values ('other value','two',102);
595
create database mysqltest;
596
create table mysqltest.t1 (a int not null) engine= innodb;
597
insert into mysqltest.t1 values(1);
598
create table mysqltest.t2 (a int not null) engine= myisam;
599
insert into mysqltest.t2 values(1);
600
create table mysqltest.t3 (a int not null) engine= heap;
601
insert into mysqltest.t3 values(1);
603
drop database mysqltest;
604
# Don't check error message
606
show tables from mysqltest;
609
# Test truncate table with and without auto_commit
613
create table t1 (a int not null) engine= innodb;
614
insert into t1 values(1),(2);
620
insert into t1 values(1),(2);
627
create table t1 (a int not null) engine= innodb;
628
insert into t1 values(1),(2);
630
insert into t1 values(1),(2);
633
insert into t1 values(1),(2);
639
# Test of how ORDER BY works when doing it on the whole table
642
create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
643
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
645
explain select * from t1 order by a;
647
explain select * from t1 order by b;
649
explain select * from t1 order by c;
651
explain select a from t1 order by a;
653
explain select b from t1 order by b;
655
explain select a,b from t1 order by b;
657
explain select a,b from t1;
659
explain select a,b,c from t1;
666
create table t1 (t int not null default 1, key (t)) engine=innodb;
671
# Test of multi-table-delete
675
number bigint(20) NOT NULL default '0',
676
cname char(15) NOT NULL default '',
677
carrier_id smallint(6) NOT NULL default '0',
678
privacy tinyint(4) NOT NULL default '0',
679
last_mod_date timestamp NOT NULL,
680
last_mod_id smallint(6) NOT NULL default '0',
681
last_app_date timestamp NOT NULL,
682
last_app_id smallint(6) default '-1',
683
version smallint(6) NOT NULL default '0',
684
assigned_scps int(11) default '0',
685
status tinyint(4) default '0'
687
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
688
INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
689
INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
690
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
691
INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
692
INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
694
number bigint(20) NOT NULL default '0',
695
cname char(15) NOT NULL default '',
696
carrier_id smallint(6) NOT NULL default '0',
697
privacy tinyint(4) NOT NULL default '0',
698
last_mod_date timestamp NOT NULL,
699
last_mod_id smallint(6) NOT NULL default '0',
700
last_app_date timestamp NOT NULL,
701
last_app_id smallint(6) default '-1',
702
version smallint(6) NOT NULL default '0',
703
assigned_scps int(11) default '0',
704
status tinyint(4) default '0'
706
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
707
INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
708
INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
709
INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
712
delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);
719
# A simple test with some isolation levels
720
# TODO: Make this into a test using replication to really test how
724
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
727
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
728
SELECT @@tx_isolation,@@global.tx_isolation;
729
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
730
select id, code, name from t1 order by id;
734
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
735
insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
736
select id, code, name from t1 order by id;
739
SET binlog_format='MIXED';
741
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
742
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
743
select id, code, name from t1 order by id;
748
# Test of multi-table-update
750
create table t1 (n int(10), d int(10)) engine=innodb;
751
create table t2 (n int(10), d int(10)) engine=innodb;
752
insert into t1 values(1,1),(1,2);
753
insert into t2 values(1,10),(2,20);
754
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
760
# Bug #29136 erred multi-delete on trans table does not rollback
765
drop table if exists t1, t2;
767
CREATE TABLE t1 (a int, PRIMARY KEY (a));
768
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
769
create trigger trg_del_t2 after delete on t2 for each row
770
insert into t1 values (1);
771
insert into t1 values (1);
772
insert into t2 values (1),(2);
775
# exec cases A, B - see multi_update.test
777
# A. send_error() w/o send_eof() branch
784
select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
792
# Bug #29136 erred multi-delete on trans table does not rollback
797
drop table if exists t1, t2;
799
CREATE TABLE t1 (a int, PRIMARY KEY (a));
800
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
801
create trigger trg_del_t2 after delete on t2 for each row
802
insert into t1 values (1);
803
insert into t1 values (1);
804
insert into t2 values (1),(2);
807
# exec cases A, B - see multi_update.test
809
# A. send_error() w/o send_eof() branch
816
select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
826
create table t1 (a int, b int) engine=innodb;
827
insert into t1 values(20,null);
828
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
830
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
831
t2.b=t3.a order by 1;
832
insert into t1 values(10,null);
833
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
834
t2.b=t3.a order by 1;
838
# Test of read_through not existing const_table
841
create table t1 (a varchar(10) not null) engine=myisam;
842
create table t2 (b varchar(10) not null unique) engine=innodb;
843
select t1.a from t1,t2 where t1.a=t2.b;
845
create table t1 (a int not null, b int, primary key (a)) engine = innodb;
846
create table t2 (a int not null, b int, primary key (a)) engine = innodb;
847
insert into t1 values (10, 20);
848
insert into t2 values (10, 20);
849
update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
853
# Test of multi-table-delete with foreign key constraints
856
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
857
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=INNODB;
858
insert into t1 set id=1;
859
insert into t2 set id=1, t1_id=1;
860
delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
864
CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
865
CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;
866
INSERT INTO t1 VALUES(1);
867
INSERT INTO t2 VALUES(1, 1);
869
UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
871
UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
876
# Test of range_optimizer
881
CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
883
CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
885
CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
887
INSERT INTO t3 VALUES("my-test-1", "my-test-2");
890
INSERT INTO t1 VALUES("this-key", "will disappear");
891
INSERT INTO t2 VALUES("this-key", "will also disappear");
892
DELETE FROM t3 WHERE id1="my-test-1";
902
SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
908
# Check update with conflicting key
911
CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
912
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
913
# We need the a < 1000 test here to quard against the halloween problems
914
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
918
CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
919
CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
921
INSERT INTO t1 ( B_ID ) VALUES ( 1 );
922
INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
926
create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
927
insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
928
select distinct parent,child from t1 order by parent;
932
# Test that MySQL priorities clustered indexes
934
create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
935
create table t2 (a int not null auto_increment primary key, b int);
936
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
937
insert into t2 (a) select b from t1;
938
insert into t1 (b) select b from t2;
939
insert into t2 (a) select b from t1;
940
insert into t1 (a) select b from t2;
941
insert into t2 (a) select b from t1;
942
insert into t1 (a) select b from t2;
943
insert into t2 (a) select b from t1;
944
insert into t1 (a) select b from t2;
945
insert into t2 (a) select b from t1;
946
insert into t1 (a) select b from t2;
947
select count(*) from t1;
949
explain select * from t1 where c between 1 and 2500;
952
explain select * from t1 where c between 1 and 2500;
956
# Test of UPDATE ... ORDER BY
959
create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
961
insert into t1 (id) values (null),(null),(null),(null),(null);
962
update t1 set fk=69 where fk is null order by id limit 1;
966
create table t1 (a int not null, b int not null, key (a));
967
insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
969
update t1 set b=(@tmp:=@tmp+1) order by a;
970
update t1 set b=99 where a=1 order by b asc limit 1;
971
update t1 set b=100 where a=1 order by b desc limit 2;
972
update t1 set a=a+10+b where a=1 order by b;
973
select * from t1 order by a,b;
977
# Test of multi-table-updates (bug #1980).
980
create table t1 ( c char(8) not null ) engine=innodb;
981
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
982
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
984
alter table t1 add b char(8) not null;
985
alter table t1 add a char(8) not null;
986
alter table t1 add primary key (a,b,c);
987
update t1 set a=c, b=c;
989
create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb;
990
insert into t2 select * from t1;
992
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
996
# test autoincrement with TRUNCATE
1000
create table t1 (a integer auto_increment primary key) engine=innodb;
1001
insert into t1 (a) values (NULL),(NULL);
1003
insert into t1 (a) values (NULL),(NULL);
1008
# Test dictionary handling with spaceand quoting
1011
CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
1012
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=INNODB;
1013
#show create table t2;
1017
# Test of multi updated and foreign keys
1020
create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;
1021
insert into `t1`values ( 1 ) ;
1022
create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = innodb;
1023
insert into `t2`values ( 1 ) ;
1024
create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb;
1025
insert into `t3`values ( 1 ) ;
1027
delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1029
update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1031
update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1032
drop table t3,t2,t1;
1035
# test for recursion depth limit
1041
foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1042
insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1043
(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1045
delete from t1 where id=0;
1046
delete from t1 where id=15;
1047
delete from t1 where id=0;
1055
CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1056
CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1057
(stamp))ENGINE=InnoDB;
1058
insert into t1 values (1),(2),(3);
1059
# Note that timestamp 3 is wrong
1060
insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1061
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1062
'20020204120000' GROUP BY col1;
1066
# Test by Francois MASUREL
1070
`id` int(10) unsigned NOT NULL auto_increment,
1071
`id_object` int(10) unsigned default '0',
1072
`id_version` int(10) unsigned NOT NULL default '1',
1073
`label` varchar(100) NOT NULL default '',
1076
KEY `id_object` (`id_object`),
1077
KEY `id_version` (`id_version`)
1080
INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);
1083
`id` int(10) unsigned NOT NULL auto_increment,
1084
`id_version` int(10) unsigned NOT NULL default '1',
1086
KEY `id_version` (`id_version`)
1089
INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1091
SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1092
(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1093
ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1096
create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1097
create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1098
create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1099
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1100
insert t2 select * from t1;
1101
insert t3 select * from t1;
1102
checksum table t1, t2, t3, t4 quick;
1103
checksum table t1, t2, t3, t4;
1104
checksum table t1, t2, t3, t4 extended;
1106
drop table t1,t2,t3;
1109
# Test problem with refering to different fields in same table in UNION
1112
create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;
1113
insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1114
select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;
1120
create table t1 (a int) engine=innodb;
1121
create table t2 like t1;
1125
# Test of automaticly created foreign keys
1128
create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
1129
create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1130
show create table t1;
1131
show create table t2;
1132
create index id on t2 (id);
1133
show create table t2;
1134
create index id2 on t2 (id);
1135
show create table t2;
1136
drop index id2 on t2;
1137
--error ER_DROP_INDEX_FK
1138
drop index id on t2;
1139
show create table t2;
1142
create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = innodb;
1143
show create table t2;
1144
create unique index id on t2 (id,id2);
1145
show create table t2;
1148
# Check foreign key columns created in different order than key columns
1149
create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
1150
show create table t2;
1153
create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = innodb;
1154
show create table t2;
1157
create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
1158
show create table t2;
1161
create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = innodb;
1162
show create table t2;
1165
create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= innodb;
1166
show create table t2;
1167
alter table t2 add index id_test (id), add index id_test2 (id,id2);
1168
show create table t2;
1171
# Test error handling
1173
# Embedded server doesn't chdir to data directory
1174
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1175
--error ER_WRONG_FK_DEF
1176
create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;
1180
create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1181
show create table t2;
1183
create table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=innodb;
1184
show create table t2;
1189
# Bug #6126: Duplicate columns in keys gives misleading error message
1192
create table t1 (c char(10), index (c,c)) engine=innodb;
1194
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1196
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1198
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1199
create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1201
alter table t1 add key (c1,c1);
1203
alter table t1 add key (c2,c1,c1);
1205
alter table t1 add key (c1,c2,c1);
1207
alter table t1 add key (c1,c1,c2);
1211
# Bug #4082: integer truncation
1214
create table t1(a int(1) , b int(1)) engine=innodb;
1215
insert into t1 values ('1111', '3333');
1216
select distinct concat(a, b) from t1;
1220
# BUG#7709 test case - Boolean fulltext query against unsupported
1221
# engines does not fail
1224
CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
1226
SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
1230
# check null values #1
1234
CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1235
INSERT INTO t1 VALUES (1),(2),(3);
1236
CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),
1237
CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1239
INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1240
SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz;
1245
# Bug#11816 - Truncate table doesn't work with temporary innodb tables
1246
# This is not an innodb bug, but we test it using innodb.
1248
create temporary table t1 (a int) engine=innodb;
1249
insert into t1 values (4711);
1251
insert into t1 values (42);
1254
# Show that it works with permanent tables too.
1255
create table t1 (a int) engine=innodb;
1256
insert into t1 values (4711);
1258
insert into t1 values (42);
1263
# Bug #13025 Server crash during filesort
1266
create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=innodb;
1267
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1268
select * from t1 order by a,b,c,d;
1269
explain select * from t1 order by a,b,c,d;
1273
# BUG#11039,#13218 Wrong key length in min()
1276
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1277
insert into t1 values ('8', '6'), ('4', '7');
1278
select min(a) from t1;
1279
select min(b) from t1 where a='8';
1285
# range optimizer problem
1288
create table t1 (x bigint unsigned not null primary key) engine=innodb;
1289
insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1291
select count(*) from t1 where x>0;
1292
select count(*) from t1 where x=0;
1293
select count(*) from t1 where x<0;
1294
select count(*) from t1 where x < -16;
1295
select count(*) from t1 where x = -16;
1296
explain select count(*) from t1 where x > -16;
1297
select count(*) from t1 where x > -16;
1298
select * from t1 where x > -16;
1299
select count(*) from t1 where x = 18446744073709551601;
1303
# Test for testable InnoDB status variables. This test
1304
# uses previous ones(pages_created, rows_deleted, ...).
1305
--replace_result 8192 8191
1306
SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
1307
SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size';
1308
SELECT variable_value - @innodb_rows_deleted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted';
1309
SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted';
1310
SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated';
1312
# Test for row locks InnoDB status variables.
1313
SELECT variable_value - @innodb_row_lock_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits';
1314
SELECT variable_value - @innodb_row_lock_current_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits';
1315
SELECT variable_value - @innodb_row_lock_time_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time';
1316
SELECT variable_value - @innodb_row_lock_time_max_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max';
1317
SELECT variable_value - @innodb_row_lock_time_avg_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg';
1319
# Test for innodb_sync_spin_loops variable
1320
SET @innodb_sync_spin_loops_orig = @@innodb_sync_spin_loops;
1321
show variables like "innodb_sync_spin_loops";
1322
set global innodb_sync_spin_loops=1000;
1323
show variables like "innodb_sync_spin_loops";
1324
set global innodb_sync_spin_loops=0;
1325
show variables like "innodb_sync_spin_loops";
1326
set global innodb_sync_spin_loops=20;
1327
show variables like "innodb_sync_spin_loops";
1328
set global innodb_sync_spin_loops=@innodb_sync_spin_loops_orig;
1330
# Test for innodb_thread_concurrency variable
1331
show variables like "innodb_thread_concurrency";
1332
set global innodb_thread_concurrency=1001;
1333
show variables like "innodb_thread_concurrency";
1334
set global innodb_thread_concurrency=0;
1335
show variables like "innodb_thread_concurrency";
1336
set global innodb_thread_concurrency=16;
1337
show variables like "innodb_thread_concurrency";
1339
# Test for innodb_concurrency_tickets variable
1340
show variables like "innodb_concurrency_tickets";
1341
set global innodb_concurrency_tickets=1000;
1342
show variables like "innodb_concurrency_tickets";
1343
set global innodb_concurrency_tickets=0;
1344
show variables like "innodb_concurrency_tickets";
1345
set global innodb_concurrency_tickets=500;
1346
show variables like "innodb_concurrency_tickets";
1348
# Test for innodb_thread_sleep_delay variable
1349
show variables like "innodb_thread_sleep_delay";
1350
set global innodb_thread_sleep_delay=100000;
1351
show variables like "innodb_thread_sleep_delay";
1352
set global innodb_thread_sleep_delay=0;
1353
show variables like "innodb_thread_sleep_delay";
1354
set global innodb_thread_sleep_delay=10000;
1355
show variables like "innodb_thread_sleep_delay";
1361
let $default=`select @@storage_engine`;
1362
set storage_engine=INNODB;
1363
# this should be fixed by MySQL (see Bug #51451)
1364
set session old_alter_table=1;
1365
source include/varchar.inc;
1366
set session old_alter_table=0;
1369
# Some errors/warnings on create
1372
# Embedded server doesn't chdir to data directory
1373
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1374
create table t1 (v varchar(65530), key(v));
1376
create table t1 (v varchar(65536));
1377
show create table t1;
1379
create table t1 (v varchar(65530) character set utf8);
1380
show create table t1;
1383
eval set storage_engine=$default;
1385
# InnoDB specific varchar tests
1386
create table t1 (v varchar(16384)) engine=innodb;
1390
# BUG#11039 Wrong key length in min()
1393
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1394
insert into t1 values ('8', '6'), ('4', '7');
1395
select min(a) from t1;
1396
select min(b) from t1 where a='8';
1400
# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error
1403
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
1404
insert into t1 (b) values (1);
1405
replace into t1 (b) values (2), (1), (3);
1408
insert into t1 (b) values (1);
1409
replace into t1 (b) values (2);
1410
replace into t1 (b) values (1);
1411
replace into t1 (b) values (3);
1415
create table t1 (rowid int not null auto_increment, val int not null,primary
1416
key (rowid), unique(val)) engine=innodb;
1417
replace into t1 (val) values ('1'),('2');
1418
replace into t1 (val) values ('1'),('2');
1419
--error ER_DUP_ENTRY
1420
insert into t1 (val) values ('1'),('2');
1425
# Test that update does not change internal auto-increment value
1428
create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
1429
insert into t1 (val) values (1);
1430
update t1 set a=2 where a=1;
1431
# We should get the following error because InnoDB does not update the counter
1432
--error ER_DUP_ENTRY
1433
insert into t1 (val) values (1);
1441
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
1443
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
1444
SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
1445
SELECT GRADE FROM t1 WHERE GRADE= 151;
1449
# Bug #12340 multitable delete deletes only one record
1451
create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
1452
create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
1453
insert into t2 values ('aa','cc');
1454
insert into t1 values ('aa','bb'),('aa','cc');
1455
delete t1 from t1,t2 where f1=f3 and f4='cc';
1460
# Test that the slow TRUNCATE implementation resets autoincrement columns
1465
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
1469
id INTEGER NOT NULL,
1470
FOREIGN KEY (id) REFERENCES t1 (id)
1473
INSERT INTO t1 (id) VALUES (NULL);
1476
INSERT INTO t1 (id) VALUES (NULL);
1479
# continued from above; test that doing a slow TRUNCATE on a table with 0
1480
# rows resets autoincrement columns
1483
INSERT INTO t1 (id) VALUES (NULL);
1487
# Test that foreign keys in temporary tables are not accepted (bug #12084)
1494
CREATE TEMPORARY TABLE t2
1496
id INT NOT NULL PRIMARY KEY,
1498
FOREIGN KEY (b) REFERENCES test.t1(id)
1503
# Test that index column max sizes are honored (bug #13315)
1507
create table t1 (col1 varchar(2000), index (col1(767)))
1508
character set = latin1 engine = innodb;
1511
create table t2 (col1 char(255), index (col1))
1512
character set = latin1 engine = innodb;
1513
create table t3 (col1 binary(255), index (col1))
1514
character set = latin1 engine = innodb;
1515
create table t4 (col1 varchar(767), index (col1))
1516
character set = latin1 engine = innodb;
1517
create table t5 (col1 varchar(767) primary key)
1518
character set = latin1 engine = innodb;
1519
create table t6 (col1 varbinary(767) primary key)
1520
character set = latin1 engine = innodb;
1521
create table t7 (col1 text, index(col1(767)))
1522
character set = latin1 engine = innodb;
1523
create table t8 (col1 blob, index(col1(767)))
1524
character set = latin1 engine = innodb;
1526
# multi-column indexes are allowed to be longer
1527
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
1528
character set = latin1 engine = innodb;
1530
show create table t9;
1532
drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
1534
# these should have their index length trimmed
1535
create table t1 (col1 varchar(768), index(col1))
1536
character set = latin1 engine = innodb;
1537
create table t2 (col1 varbinary(768), index(col1))
1538
character set = latin1 engine = innodb;
1539
create table t3 (col1 text, index(col1(768)))
1540
character set = latin1 engine = innodb;
1541
create table t4 (col1 blob, index(col1(768)))
1542
character set = latin1 engine = innodb;
1544
show create table t1;
1546
drop table t1, t2, t3, t4;
1548
# these should be refused
1550
create table t1 (col1 varchar(768) primary key)
1551
character set = latin1 engine = innodb;
1553
create table t2 (col1 varbinary(768) primary key)
1554
character set = latin1 engine = innodb;
1556
create table t3 (col1 text, primary key(col1(768)))
1557
character set = latin1 engine = innodb;
1559
create table t4 (col1 blob, primary key(col1(768)))
1560
character set = latin1 engine = innodb;
1563
# Test improved foreign key error messages (bug #3443)
1574
CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
1578
INSERT INTO t2 VALUES(2);
1580
INSERT INTO t1 VALUES(1);
1581
INSERT INTO t2 VALUES(1);
1584
DELETE FROM t1 WHERE id = 1;
1589
SET FOREIGN_KEY_CHECKS=0;
1591
SET FOREIGN_KEY_CHECKS=1;
1594
INSERT INTO t2 VALUES(3);
1598
# Test that checksum table uses a consistent read Bug #12669
1600
connect (a,localhost,root,,);
1601
connect (b,localhost,root,,);
1603
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1604
insert into t1 values (1),(2);
1608
insert into t1 values(3);
1611
# Here checksum should not see insert
1623
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1624
insert into t1 values (1),(2);
1629
insert into t1 values(3);
1632
# Here checksum sees insert
1641
# tests for bugs #9802 and #13778
1643
# test that FKs between invalid types are not accepted
1645
set foreign_key_checks=0;
1646
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
1647
# Embedded server doesn't chdir to data directory
1648
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1650
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
1651
set foreign_key_checks=1;
1654
# test that FKs between different charsets are not accepted in CREATE even
1657
set foreign_key_checks=0;
1658
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1659
# Embedded server doesn't chdir to data directory
1660
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1662
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
1663
set foreign_key_checks=1;
1666
# test that invalid datatype conversions with ALTER are not allowed
1668
set foreign_key_checks=0;
1669
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
1670
create table t1(a varchar(10) primary key) engine = innodb;
1672
alter table t1 modify column a int;
1673
set foreign_key_checks=1;
1676
# test that charset conversions with ALTER are allowed when f_k_c is 0
1678
set foreign_key_checks=0;
1679
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1680
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1681
alter table t1 convert to character set utf8;
1682
set foreign_key_checks=1;
1685
# test that RENAME does not allow invalid charsets when f_k_c is 0
1687
set foreign_key_checks=0;
1688
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1689
create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
1690
# Embedded server doesn't chdir to data directory
1691
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
1693
rename table t3 to t1;
1694
set foreign_key_checks=1;
1697
# test that foreign key errors are reported correctly (Bug #15550)
1699
create table t1(a int primary key) row_format=redundant engine=innodb;
1700
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
1701
create table t3(a int primary key) row_format=compact engine=innodb;
1702
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
1704
insert into t1 values(1);
1705
insert into t3 values(1);
1707
insert into t2 values(2);
1709
insert into t4 values(2);
1710
insert into t2 values(1);
1711
insert into t4 values(1);
1729
drop table t4,t3,t2,t1;
1733
# Test that we can create a large (>1K) key
1735
create table t1 (a varchar(255) character set utf8,
1736
b varchar(255) character set utf8,
1737
c varchar(255) character set utf8,
1738
d varchar(255) character set utf8,
1739
key (a,b,c,d)) engine=innodb;
1741
--error ER_TOO_LONG_KEY
1742
create table t1 (a varchar(255) character set utf8,
1743
b varchar(255) character set utf8,
1744
c varchar(255) character set utf8,
1745
d varchar(255) character set utf8,
1746
e varchar(255) character set utf8,
1747
key (a,b,c,d,e)) engine=innodb;
1750
# test the padding of BINARY types and collations (Bug #14189)
1752
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
1753
create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
1754
create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
1755
create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
1757
insert into t1 values (0x41),(0x4120),(0x4100);
1758
-- error ER_DUP_ENTRY
1759
insert into t2 values (0x41),(0x4120),(0x4100);
1760
insert into t2 values (0x41),(0x4120);
1761
-- error ER_DUP_ENTRY
1762
insert into t3 values (0x41),(0x4120),(0x4100);
1763
insert into t3 values (0x41),(0x4100);
1764
-- error ER_DUP_ENTRY
1765
insert into t4 values (0x41),(0x4120),(0x4100);
1766
insert into t4 values (0x41),(0x4100);
1767
select hex(s1) from t1;
1768
select hex(s1) from t2;
1769
select hex(s1) from t3;
1770
select hex(s1) from t4;
1771
drop table t1,t2,t3,t4;
1773
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
1774
create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1776
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
1778
insert into t2 values(0x42);
1779
insert into t2 values(0x41);
1780
select hex(s1) from t2;
1781
update t1 set s1=0x123456 where a=2;
1782
select hex(s1) from t2;
1784
update t1 set s1=0x12 where a=1;
1786
update t1 set s1=0x12345678 where a=1;
1788
update t1 set s1=0x123457 where a=1;
1789
update t1 set s1=0x1220 where a=1;
1790
select hex(s1) from t2;
1791
update t1 set s1=0x1200 where a=1;
1792
select hex(s1) from t2;
1793
update t1 set s1=0x4200 where a=1;
1794
select hex(s1) from t2;
1796
delete from t1 where a=1;
1797
delete from t1 where a=2;
1798
update t2 set s1=0x4120;
1801
delete from t1 where a!=3;
1802
select a,hex(s1) from t1;
1803
select hex(s1) from t2;
1807
create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
1808
create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1810
insert into t1 values(1,0x4100),(2,0x41);
1811
insert into t2 values(0x41);
1812
select hex(s1) from t2;
1813
update t1 set s1=0x1234 where a=1;
1814
select hex(s1) from t2;
1815
update t1 set s1=0x12 where a=2;
1816
select hex(s1) from t2;
1817
delete from t1 where a=1;
1819
delete from t1 where a=2;
1820
select a,hex(s1) from t1;
1821
select hex(s1) from t2;
1824
# Ensure that <tablename>_ibfk_0 is not mistreated as a
1825
# generated foreign key identifier. (Bug #16387)
1827
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
1828
CREATE TABLE t2(a INT) ENGINE=InnoDB;
1829
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
1830
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
1831
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
1832
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
1833
SHOW CREATE TABLE t2;
1837
# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing
1840
connect (a,localhost,root,,);
1841
connect (b,localhost,root,,);
1843
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1844
insert into t1(a) values (1),(2),(3);
1848
update t1 set b = 5 where a = 2;
1851
create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
1855
insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
1856
(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
1857
(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
1858
(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
1859
(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
1869
# Another trigger test
1871
connect (a,localhost,root,,);
1872
connect (b,localhost,root,,);
1874
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1875
create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1876
create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1877
create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1878
create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1879
insert into t1(a) values (1),(2),(3);
1880
insert into t2(a) values (1),(2),(3);
1881
insert into t3(a) values (1),(2),(3);
1882
insert into t4(a) values (1),(2),(3);
1883
insert into t3(a) values (5),(7),(8);
1884
insert into t4(a) values (5),(7),(8);
1885
insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
1888
create trigger t1t before insert on t1 for each row begin
1889
INSERT INTO t2 SET a = NEW.a;
1892
create trigger t2t before insert on t2 for each row begin
1893
DELETE FROM t3 WHERE a = NEW.a;
1896
create trigger t3t before delete on t3 for each row begin
1897
UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
1900
create trigger t4t before update on t4 for each row begin
1901
UPDATE t5 SET b = b + 1 where a = NEW.a;
1906
update t1 set b = b + 5 where a = 1;
1907
update t2 set b = b + 5 where a = 1;
1908
update t3 set b = b + 5 where a = 1;
1909
update t4 set b = b + 5 where a = 1;
1910
insert into t5(a) values(20);
1913
insert into t1(a) values(7);
1914
insert into t2(a) values(8);
1915
delete from t2 where a = 3;
1916
update t4 set b = b + 1 where a = 3;
1922
drop table t1, t2, t3, t4, t5;
1928
# Test that cascading updates leading to duplicate keys give the correct
1929
# error message (bug #9680)
1933
field1 varchar(8) NOT NULL DEFAULT '',
1934
field2 varchar(8) NOT NULL DEFAULT '',
1935
PRIMARY KEY (field1, field2)
1939
field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
1940
FOREIGN KEY (field1) REFERENCES t1 (field1)
1941
ON DELETE CASCADE ON UPDATE CASCADE
1944
INSERT INTO t1 VALUES ('old', 'somevalu');
1945
INSERT INTO t1 VALUES ('other', 'anyvalue');
1947
INSERT INTO t2 VALUES ('old');
1948
INSERT INTO t2 VALUES ('other');
1950
--error ER_FOREIGN_DUPLICATE_KEY
1951
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
1957
# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
1971
alter table t1 add constraint c2_fk foreign key (c2)
1972
references t2(c1) on delete cascade;
1973
show create table t1;
1975
alter table t1 drop foreign key c2_fk;
1976
show create table t1;
1981
# Bug #14360: problem with intervals
1984
create table t1(a date) engine=innodb;
1985
create table t2(a date, key(a)) engine=innodb;
1986
insert into t1 values('2005-10-01');
1987
insert into t2 values('2005-10-01');
1988
select * from t1, t2
1989
where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
1992
create table t1 (id int not null, f_id int not null, f int not null,
1993
primary key(f_id, id)) engine=innodb;
1994
create table t2 (id int not null,s_id int not null,s varchar(200),
1995
primary key(id)) engine=innodb;
1996
INSERT INTO t1 VALUES (8, 1, 3);
1997
INSERT INTO t1 VALUES (1, 2, 1);
1998
INSERT INTO t2 VALUES (1, 0, '');
1999
INSERT INTO t2 VALUES (8, 1, '');
2001
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
2002
WHERE mm.id IS NULL;
2003
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
2004
where mm.id is null lock in share mode;
2008
# Test case where X-locks on unused rows should be released in a
2009
# update (because READ COMMITTED isolation level)
2012
connect (a,localhost,root,,);
2013
connect (b,localhost,root,,);
2015
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2016
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
2018
SET binlog_format='MIXED';
2020
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2021
update t1 set b = 5 where b = 1;
2023
SET binlog_format='MIXED';
2025
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2027
# X-lock to record (7,3) should be released in a update
2029
select * from t1 where a = 7 and b = 3 for update;
2040
# Test case where no locks should be released (because we are not
2041
# using READ COMMITTED isolation level)
2044
connect (a,localhost,root,,);
2045
connect (b,localhost,root,,);
2047
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2048
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
2051
select * from t1 lock in share mode;
2052
update t1 set b = 5 where b = 1;
2056
# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
2059
select * from t1 where a = 2 and b = 2 for update;
2061
# X-lock to record (1,1),(3,1),(5,1) should not be released in a update
2074
# Consistent read should be used in following selects
2076
# 1) INSERT INTO ... SELECT
2077
# 2) UPDATE ... = ( SELECT ...)
2078
# 3) CREATE ... SELECT
2080
connect (a,localhost,root,,);
2081
connect (b,localhost,root,,);
2083
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2084
insert into t1 values (1,2),(5,3),(4,2);
2085
create table t2(d int not null, e int, primary key(d)) engine=innodb;
2086
insert into t2 values (8,6),(12,1),(3,1);
2089
select * from t2 for update;
2091
SET binlog_format='MIXED';
2093
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2094
insert into t1 select * from t2;
2095
update t1 set b = (select e from t2 where a = d);
2096
create table t3(d int not null, e int, primary key(d)) engine=innodb
2104
drop table t1, t2, t3;
2107
# Consistent read should not be used if
2109
# (a) isolation level is serializable OR
2110
# (b) select ... lock in share mode OR
2111
# (c) select ... for update
2113
# in following queries:
2115
# 1) INSERT INTO ... SELECT
2116
# 2) UPDATE ... = ( SELECT ...)
2117
# 3) CREATE ... SELECT
2119
connect (a,localhost,root,,);
2120
connect (b,localhost,root,,);
2121
connect (c,localhost,root,,);
2122
connect (d,localhost,root,,);
2123
connect (e,localhost,root,,);
2124
connect (f,localhost,root,,);
2125
connect (g,localhost,root,,);
2126
connect (h,localhost,root,,);
2127
connect (i,localhost,root,,);
2128
connect (j,localhost,root,,);
2130
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2131
insert into t1 values (1,2),(5,3),(4,2);
2132
create table t2(a int not null, b int, primary key(a)) engine=innodb;
2133
insert into t2 values (8,6),(12,1),(3,1);
2134
create table t3(d int not null, b int, primary key(d)) engine=innodb;
2135
insert into t3 values (8,6),(12,1),(3,1);
2136
create table t5(a int not null, b int, primary key(a)) engine=innodb;
2137
insert into t5 values (1,2),(5,3),(4,2);
2138
create table t6(d int not null, e int, primary key(d)) engine=innodb;
2139
insert into t6 values (8,6),(12,1),(3,1);
2140
create table t8(a int not null, b int, primary key(a)) engine=innodb;
2141
insert into t8 values (1,2),(5,3),(4,2);
2142
create table t9(d int not null, e int, primary key(d)) engine=innodb;
2143
insert into t9 values (8,6),(12,1),(3,1);
2146
select * from t2 for update;
2148
SET binlog_format='MIXED';
2150
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2152
insert into t1 select * from t2;
2154
SET binlog_format='MIXED';
2156
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2158
update t3 set b = (select b from t2 where a = d);
2160
SET binlog_format='MIXED';
2162
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2164
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
2166
SET binlog_format='MIXED';
2168
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2170
insert into t5 (select * from t2 lock in share mode);
2172
SET binlog_format='MIXED';
2174
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2176
update t6 set e = (select b from t2 where a = d lock in share mode);
2178
SET binlog_format='MIXED';
2180
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2182
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
2184
SET binlog_format='MIXED';
2186
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2188
insert into t8 (select * from t2 for update);
2190
SET binlog_format='MIXED';
2192
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2194
update t9 set e = (select b from t2 where a = d for update);
2196
SET binlog_format='MIXED';
2198
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2200
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
2252
drop table t1, t2, t3, t5, t6, t8, t9;
2254
# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"
2255
--error ER_WRONG_COLUMN_NAME
2256
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
2259
# Bug #17152: Wrong result with BINARY comparison on aliased column
2263
a BIGINT(20) NOT NULL,
2265
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2268
a BIGINT(20) NOT NULL,
2269
b VARCHAR(128) NOT NULL,
2272
KEY idx_t2_b_c (b,c(200)),
2273
CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
2275
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2277
INSERT INTO t1 VALUES (1);
2278
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
2279
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
2280
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
2281
INSERT INTO t2 VALUES (1, 'customer_over', '1');
2283
SELECT * FROM t2 WHERE b = 'customer_over';
2284
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
2285
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
2286
/* Bang: Empty result set, above was expected: */
2287
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2288
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2293
# Test optimize on table with open transaction
2296
CREATE TABLE t1 ( a int ) ENGINE=innodb;
2298
INSERT INTO t1 VALUES (1);
2303
# Bug #24741 (existing cascade clauses disappear when adding foreign keys)
2306
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
2308
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
2309
CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
2310
ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
2312
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
2313
DELETE CASCADE ON UPDATE CASCADE;
2315
SHOW CREATE TABLE t2;
2319
# Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns
2320
# for which there is a foreign key constraint ON ... SET NULL.
2323
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
2324
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
2325
INSERT INTO t1 VALUES (1);
2326
INSERT INTO t2 VALUES (1);
2327
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
2328
# mysqltest first does replace_regex, then replace_result
2329
--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
2330
# Embedded server doesn't chdir to data directory
2331
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
2333
ALTER TABLE t2 MODIFY a INT NOT NULL;
2338
# Bug #26835: table corruption after delete+insert
2341
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
2343
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
2345
INSERT INTO t1 VALUES ('DDD');
2350
# Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)
2351
# Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))
2354
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
2357
INSERT INTO t1 VALUES (0),(347),(0);
2360
SHOW CREATE TABLE t1;
2362
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
2363
INSERT INTO t2 VALUES(42),(347),(348);
2364
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
2365
SHOW CREATE TABLE t1;
2370
# Bug #21101 (Prints wrong error message if max row size is too large)
2372
set innodb_strict_mode=on;
2375
c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
2376
c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
2377
c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
2378
c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
2379
c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
2380
c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
2381
c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
2382
c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
2386
# Bug #31860 InnoDB assumes AUTOINC values can only be positive.
2388
DROP TABLE IF EXISTS t1;
2390
id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
2392
INSERT INTO t1 VALUES(-10);
2395
# NOTE: The server really needs to be restarted at this point
2396
# for the test to be useful.
2398
# Without the fix InnoDB would trip over an assertion here.
2399
INSERT INTO t1 VALUES(NULL);
2400
# The next value should be 1 and not -9 or a -ve number
2405
# Bug #21409 Incorrect result returned when in READ-COMMITTED with
2408
CONNECT (c1,localhost,root,,);
2409
CONNECT (c2,localhost,root,,);
2411
SET binlog_format='MIXED';
2412
SET TX_ISOLATION='read-committed';
2414
DROP TABLE IF EXISTS t1, t2;
2415
CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
2416
CREATE TABLE t2 LIKE t1;
2419
SET binlog_format='MIXED';
2420
SET TX_ISOLATION='read-committed';
2422
INSERT INTO t1 VALUES (1);
2425
SELECT * FROM t1 WHERE a=1;
2428
CONNECT (c1,localhost,root,,);
2429
CONNECT (c2,localhost,root,,);
2431
SET binlog_format='MIXED';
2432
SET TX_ISOLATION='read-committed';
2436
SET binlog_format='MIXED';
2437
SET TX_ISOLATION='read-committed';
2439
INSERT INTO t1 VALUES (2);
2442
# The result set below should be the same for both selects
2443
SELECT * FROM t1 WHERE a=2;
2444
SELECT * FROM t1 WHERE a=2;
2452
# Bug #29157 UPDATE, changed rows incorrect
2454
create table t1 (i int, j int) engine=innodb;
2455
insert into t1 (i, j) values (1, 1), (2, 2);
2457
update t1 set j = 2;
2462
# Bug #32440 InnoDB free space info does not appear in SHOW TABLE STATUS or
2465
create table t1 (id int) comment='this is a comment' engine=innodb;
2466
select table_comment, data_free > 0 as data_free_is_set
2467
from information_schema.tables
2468
where table_schema='test' and table_name = 't1';
2476
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
2477
c2 VARCHAR(128) NOT NULL,
2479
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
2482
c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
2483
c2 INT(10) UNSIGNED DEFAULT NULL,
2485
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
2487
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
2488
ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
2489
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
2494
# Bug #29507 TRUNCATE shows to many rows effected
2497
CREATE TABLE t1 (c1 int default NULL,
2499
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2504
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
2510
# Bug#35537 Innodb doesn't increment handler_update and handler_delete.
2512
-- disable_query_log
2513
-- disable_result_log
2515
CONNECT (c1,localhost,root,,);
2517
DROP TABLE IF EXISTS bug35537;
2518
CREATE TABLE bug35537 (
2522
INSERT INTO bug35537 VALUES (1);
2524
-- enable_result_log
2526
SHOW SESSION STATUS LIKE 'Handler_update%';
2527
SHOW SESSION STATUS LIKE 'Handler_delete%';
2529
UPDATE bug35537 SET c1 = 2 WHERE c1 = 1;
2530
DELETE FROM bug35537 WHERE c1 = 2;
2532
SHOW SESSION STATUS LIKE 'Handler_update%';
2533
SHOW SESSION STATUS LIKE 'Handler_delete%';
2535
DROP TABLE bug35537;
2540
SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig;
2544
#######################################################################
2546
# Please, DO NOT TOUCH this file as well as the innodb.result file. #
2547
# These files are to be modified ONLY BY INNOBASE guys. #
2549
# Use innodb_mysql.[test|result] files instead. #
2551
# If nevertheless you need to make some changes here, please, forward #
2552
# your commit message #
2553
# To: innodb_dev_ww@oracle.com #
2554
# Cc: dev-innodb@mysql.com #
2555
# (otherwise your changes may be erased). #
2557
#######################################################################
added
removed
mysql-test/suite/innodb_plugin/t/innodb.test
