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;
46 # Bug#58912 InnoDB unnecessarily avoids update-in-place on column prefixes
47 CREATE TABLE bug58912 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB;
48 INSERT INTO bug58912 VALUES(REPEAT('a',8000),REPEAT('b',8000));
49 UPDATE bug58912 SET a=REPEAT('a',7999);
50 # The above statements used to trigger a failure during purge when
51 # Bug#55284 was fixed while Bug#58912 was not. Defer the DROP TABLE,
52 # so that purge gets a chance to run (and a double free of the
53 # off-page column can be detected, if one is to occur.)
56 # Small basic test with ignore
59 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;
61 insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
62 select id, code, name from t1 order by id;
64 update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
65 select id, code, name from t1 order by id;
66 update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
67 select id, code, name from t1 order by id;
73 # The 'replace_column' statements are needed because the cardinality calculated
74 # by innodb is not always the same between runs
78 id int(11) NOT NULL auto_increment,
79 parent_id int(11) DEFAULT '0' NOT NULL,
80 level tinyint(4) DEFAULT '0' NOT NULL,
82 KEY parent_id (parent_id),
85 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);
86 update t1 set parent_id=parent_id+100;
87 select * from t1 where parent_id=102;
88 update t1 set id=id+1000;
89 -- error ER_DUP_ENTRY,1022
90 update t1 set id=1024 where id=1009;
92 update ignore t1 set id=id+1; # This will change all rows
94 update ignore t1 set id=1023 where id=1010;
95 select * from t1 where parent_id=102;
97 explain select level from t1 where level=1;
99 explain select level,id from t1 where level=1;
101 explain select level,id,parent_id from t1 where level=1;
102 select level,id from t1 where level=1;
103 select level,id,parent_id from t1 where level=1;
114 gesuchnr int(11) DEFAULT '0' NOT NULL,
115 benutzer_id int(11) DEFAULT '0' NOT NULL,
116 PRIMARY KEY (gesuchnr,benutzer_id)
119 replace into t1 (gesuchnr,benutzer_id) values (2,1);
120 replace into t1 (gesuchnr,benutzer_id) values (1,1);
121 replace into t1 (gesuchnr,benutzer_id) values (1,1);
126 # test delete using hidden_primary_key
129 create table t1 (a int) engine=innodb;
130 insert into t1 values (1), (2);
132 delete from t1 where a = 1;
137 create table t1 (a int,b varchar(20)) engine=innodb;
138 insert into t1 values (1,""), (2,"testing");
139 delete from t1 where a = 1;
141 create index skr on t1 (a);
142 insert into t1 values (3,""), (4,"testing");
149 # Test of reading on secondary key with may be null
151 create table t1 (a int,b varchar(20),key(a)) engine=innodb;
152 insert into t1 values (1,""), (2,"testing");
153 select * from t1 where a = 1;
160 create table t1 (n int not null primary key) engine=innodb;
162 insert into t1 values (4);
164 select n, "after rollback" from t1;
165 insert into t1 values (4);
167 select n, "after commit" from t1;
169 insert into t1 values (5);
170 -- error ER_DUP_ENTRY
171 insert into t1 values (4);
173 select n, "after commit" from t1;
175 insert into t1 values (6);
176 -- error ER_DUP_ENTRY
177 insert into t1 values (4);
184 savepoint `my_savepoint`;
185 insert into t1 values (7);
187 insert into t1 values (3);
190 rollback to savepoint savept2;
192 rollback to savepoint savept3;
193 rollback to savepoint savept2;
194 release savepoint `my_savepoint`;
197 rollback to savepoint `my_savepoint`;
199 rollback to savepoint savept2;
200 insert into t1 values (8);
210 # Test for commit and FLUSH TABLES WITH READ LOCK
213 create table t1 (n int not null primary key) engine=innodb;
215 insert into t1 values (4);
216 flush tables with read lock;
218 # Current code can't handle a read lock in middle of transaction
227 # Testing transactions
230 create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
232 insert into t1 values(1,'hamdouni');
233 select id as afterbegin_id,nom as afterbegin_nom from t1;
235 select id as afterrollback_id,nom as afterrollback_nom from t1;
237 insert into t1 values(2,'mysql');
238 select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
240 select id as afterrollback_id,nom as afterrollback_nom from t1;
245 # Simple not autocommit test
248 CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
249 insert into t1 values ('pippo', 12);
250 -- error ER_DUP_ENTRY
251 insert into t1 values ('pippo', 12); # Gives error
253 delete from t1 where id = 'pippo';
256 insert into t1 values ('pippo', 12);
267 # Test of active transactions
270 create table t1 (a integer) engine=innodb;
272 rename table t1 to t2;
273 create table t1 (b integer) engine=innodb;
274 insert into t1 values (1);
277 rename table t2 to t1;
282 # The following simple tests failed at some point
285 CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
286 INSERT INTO t1 VALUES (1, 'Jochen');
290 CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
292 INSERT INTO t1 SET _userid='marc@anyware.co.uk';
295 SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
300 # Test when reading on part of unique key
303 user_id int(10) DEFAULT '0' NOT NULL,
306 ref_email varchar(100) DEFAULT '' NOT NULL,
308 PRIMARY KEY (user_id,ref_email)
311 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');
312 select * from t1 where user_id=10292;
313 INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
314 select * from t1 where user_id=10292;
315 select * from t1 where user_id>=10292;
316 select * from t1 where user_id>10292;
317 select * from t1 where user_id<10292;
321 # Test that keys are created in right order
324 CREATE TABLE t1 (a int not null, b int not null,c int not null,
325 key(a),primary key(a,b), unique(c),key(a),unique(b));
331 # Test of ALTER TABLE and innodb tables
334 create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
335 alter table t1 engine=innodb;
336 insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
338 update t1 set col2='7' where col1='4';
340 alter table t1 add co3 int not null;
342 update t1 set col2='9' where col1='2';
347 # INSERT INTO innodb tables
350 create table t1 (a int not null , b int, primary key (a)) engine = innodb;
351 create table t2 (a int not null , b int, primary key (a)) engine = myisam;
352 insert into t1 VALUES (1,3) , (2,3), (3,3);
354 insert into t2 select * from t1;
356 delete from t1 where b = 3;
358 insert into t1 select * from t2;
364 # ORDER BY on not primary key
368 user_name varchar(12),
371 user_id int(11) DEFAULT '0' NOT NULL,
377 dummy_primary_key int(11) NOT NULL auto_increment,
378 PRIMARY KEY (dummy_primary_key)
380 INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
381 INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
382 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);
383 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);
384 INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
385 select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
389 # Testing of tables without primary keys
393 id int(11) NOT NULL auto_increment,
394 parent_id int(11) DEFAULT '0' NOT NULL,
395 level tinyint(4) DEFAULT '0' NOT NULL,
397 KEY parent_id (parent_id),
400 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);
401 INSERT INTO t1 values (179,5,2);
402 update t1 set parent_id=parent_id+100;
403 select * from t1 where parent_id=102;
404 update t1 set id=id+1000;
405 update t1 set id=1024 where id=1009;
407 update ignore t1 set id=id+1; # This will change all rows
409 update ignore t1 set id=1023 where id=1010;
410 select * from t1 where parent_id=102;
412 explain select level from t1 where level=1;
413 select level,id from t1 where level=1;
414 select level,id,parent_id from t1 where level=1;
415 select level,id from t1 where level=1 order by id;
416 delete from t1 where level=1;
421 # Test of index only reads
424 sca_code char(6) NOT NULL,
425 cat_code char(6) NOT NULL,
426 sca_desc varchar(50),
427 lan_code char(2) NOT NULL,
428 sca_pic varchar(100),
429 sca_sdesc varchar(50),
430 sca_sch_desc varchar(16),
431 PRIMARY KEY (sca_code, cat_code, lan_code),
432 INDEX sca_pic (sca_pic)
435 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');
436 select count(*) from t1 where sca_code = 'PD';
437 select count(*) from t1 where sca_code <= 'PD';
438 select count(*) from t1 where sca_pic is null;
439 # this should be fixed by MySQL (see Bug #51451)
440 # now that http://bugs.mysql.com/49838 is fixed the following ALTER does
441 # copy the table instead of failing
442 # --error ER_WRONG_NAME_FOR_INDEX
443 alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
444 alter table t1 drop index sca_pic;
445 alter table t1 add index sca_pic (cat_code, sca_pic);
446 select count(*) from t1 where sca_code='PD' and sca_pic is null;
447 select count(*) from t1 where cat_code='E';
449 # this should be fixed by MySQL (see Bug #51451)
450 --error ER_WRONG_NAME_FOR_INDEX
451 alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
452 alter table t1 drop index sca_pic;
453 alter table t1 add index (sca_pic, cat_code);
454 select count(*) from t1 where sca_code='PD' and sca_pic is null;
455 select count(*) from t1 where sca_pic >= 'n';
456 select sca_pic from t1 where sca_pic is null;
457 update t1 set sca_pic="test" where sca_pic is null;
458 delete from t1 where sca_code='pd';
462 # Test of opening table twice and timestamps
465 CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
466 insert into t1 (a) values(1),(2),(3);
467 select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
468 select a from t1 natural join t1 as t2 where b >= @a order by a;
469 update t1 set a=5 where a=1;
474 # Test with variable length primary key
476 create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
477 insert into t1 values("hello",1),("world",2);
478 select * from t1 order by b desc;
485 # Test of create index with NULL columns
487 create table t1 (i int, j int ) ENGINE=innodb;
488 insert into t1 values (1,2);
489 select * from t1 where i=1 and j=2;
490 create index ax1 on t1 (i,j);
491 select * from t1 where i=1 and j=2;
495 # Test min-max optimization
499 a int3 unsigned NOT NULL,
500 b int1 unsigned NOT NULL,
504 INSERT INTO t1 VALUES (1, 1);
505 SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
509 # Test INSERT DELAYED
512 CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb;
513 # Can't test this in 3.23
514 # INSERT DELAYED INTO t1 VALUES (1);
515 INSERT INTO t1 VALUES (1);
521 # Crash when using many tables (Test case by Jeremy D Zawodny)
524 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;
525 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);
527 explain select * from t1 where a > 0 and a < 50;
534 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;
535 insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
536 LOCK TABLES t1 WRITE;
538 insert into t1 values (99,1,2,'D'),(1,1,2,'D');
544 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;
545 insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
546 LOCK TABLES t1 WRITE;
549 insert into t1 values (99,1,2,'D'),(1,1,2,'D');
551 insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
553 select id,id3 from t1;
560 create table t1 (a char(20), unique (a(5))) engine=innodb;
562 create table t1 (a char(20), index (a(5))) engine=innodb;
563 show create table t1;
567 # Test using temporary table and auto_increment
570 create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
571 insert into t1 values (NULL),(NULL),(NULL);
572 delete from t1 where a=3;
573 insert into t1 values (NULL);
575 alter table t1 add b int;
582 id int auto_increment primary key,
583 name varchar(32) not null,
588 insert into t1 values (1,'one','one value',101),
589 (2,'two','two value',102),(3,'three','three value',103);
591 replace into t1 (value,name,uid) values ('other value','two',102);
592 delete from t1 where uid=102;
594 replace into t1 (value,name,uid) values ('other value','two',102);
596 replace into t1 (value,name,uid) values ('other value','two',102);
604 create database mysqltest;
605 create table mysqltest.t1 (a int not null) engine= innodb;
606 insert into mysqltest.t1 values(1);
607 create table mysqltest.t2 (a int not null) engine= myisam;
608 insert into mysqltest.t2 values(1);
609 create table mysqltest.t3 (a int not null) engine= heap;
610 insert into mysqltest.t3 values(1);
612 drop database mysqltest;
613 # Don't check error message
615 show tables from mysqltest;
618 # Test truncate table with and without auto_commit
622 create table t1 (a int not null) engine= innodb;
623 insert into t1 values(1),(2);
629 insert into t1 values(1),(2);
636 create table t1 (a int not null) engine= innodb;
637 insert into t1 values(1),(2);
639 insert into t1 values(1),(2);
642 insert into t1 values(1),(2);
648 # Test of how ORDER BY works when doing it on the whole table
651 create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
652 insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
654 explain select * from t1 order by a;
656 explain select * from t1 order by b;
658 explain select * from t1 order by c;
660 explain select a from t1 order by a;
662 explain select b from t1 order by b;
664 explain select a,b from t1 order by b;
666 explain select a,b from t1;
668 explain select a,b,c from t1;
675 create table t1 (t int not null default 1, key (t)) engine=innodb;
680 # Test of multi-table-delete
684 number bigint(20) NOT NULL default '0',
685 cname char(15) NOT NULL default '',
686 carrier_id smallint(6) NOT NULL default '0',
687 privacy tinyint(4) NOT NULL default '0',
688 last_mod_date timestamp NOT NULL,
689 last_mod_id smallint(6) NOT NULL default '0',
690 last_app_date timestamp NOT NULL,
691 last_app_id smallint(6) default '-1',
692 version smallint(6) NOT NULL default '0',
693 assigned_scps int(11) default '0',
694 status tinyint(4) default '0'
696 INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
697 INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
698 INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
699 INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
700 INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
701 INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
703 number bigint(20) NOT NULL default '0',
704 cname char(15) NOT NULL default '',
705 carrier_id smallint(6) NOT NULL default '0',
706 privacy tinyint(4) NOT NULL default '0',
707 last_mod_date timestamp NOT NULL,
708 last_mod_id smallint(6) NOT NULL default '0',
709 last_app_date timestamp NOT NULL,
710 last_app_id smallint(6) default '-1',
711 version smallint(6) NOT NULL default '0',
712 assigned_scps int(11) default '0',
713 status tinyint(4) default '0'
715 INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
716 INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
717 INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
718 INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
721 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);
728 # A simple test with some isolation levels
729 # TODO: Make this into a test using replication to really test how
733 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;
736 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
737 SELECT @@tx_isolation,@@global.tx_isolation;
738 insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
739 select id, code, name from t1 order by id;
743 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
744 insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
745 select id, code, name from t1 order by id;
748 SET binlog_format='MIXED';
750 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
751 insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
752 select id, code, name from t1 order by id;
757 # Test of multi-table-update
759 create table t1 (n int(10), d int(10)) engine=innodb;
760 create table t2 (n int(10), d int(10)) engine=innodb;
761 insert into t1 values(1,1),(1,2);
762 insert into t2 values(1,10),(2,20);
763 UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
769 # Bug #29136 erred multi-delete on trans table does not rollback
774 drop table if exists t1, t2;
776 CREATE TABLE t1 (a int, PRIMARY KEY (a));
777 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
778 create trigger trg_del_t2 after delete on t2 for each row
779 insert into t1 values (1);
780 insert into t1 values (1);
781 insert into t2 values (1),(2);
784 # exec cases A, B - see multi_update.test
786 # A. send_error() w/o send_eof() branch
793 select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
801 # Bug #29136 erred multi-delete on trans table does not rollback
806 drop table if exists t1, t2;
808 CREATE TABLE t1 (a int, PRIMARY KEY (a));
809 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
810 create trigger trg_del_t2 after delete on t2 for each row
811 insert into t1 values (1);
812 insert into t1 values (1);
813 insert into t2 values (1),(2);
816 # exec cases A, B - see multi_update.test
818 # A. send_error() w/o send_eof() branch
825 select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
835 create table t1 (a int, b int) engine=innodb;
836 insert into t1 values(20,null);
837 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
839 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
840 t2.b=t3.a order by 1;
841 insert into t1 values(10,null);
842 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
843 t2.b=t3.a order by 1;
847 # Test of read_through not existing const_table
850 create table t1 (a varchar(10) not null) engine=myisam;
851 create table t2 (b varchar(10) not null unique) engine=innodb;
852 select t1.a from t1,t2 where t1.a=t2.b;
854 create table t1 (a int not null, b int, primary key (a)) engine = innodb;
855 create table t2 (a int not null, b int, primary key (a)) engine = innodb;
856 insert into t1 values (10, 20);
857 insert into t2 values (10, 20);
858 update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
862 # Test of multi-table-delete with foreign key constraints
865 CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
866 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;
867 insert into t1 set id=1;
868 insert into t2 set id=1, t1_id=1;
869 delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
873 CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
874 CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;
875 INSERT INTO t1 VALUES(1);
876 INSERT INTO t2 VALUES(1, 1);
878 UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
880 UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
885 # Test of range_optimizer
890 CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
892 CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
894 CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
896 INSERT INTO t3 VALUES("my-test-1", "my-test-2");
899 INSERT INTO t1 VALUES("this-key", "will disappear");
900 INSERT INTO t2 VALUES("this-key", "will also disappear");
901 DELETE FROM t3 WHERE id1="my-test-1";
911 SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
917 # Check update with conflicting key
920 CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
921 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
922 # We need the a < 1000 test here to quard against the halloween problems
923 UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
927 CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
928 CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
930 INSERT INTO t1 ( B_ID ) VALUES ( 1 );
931 INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
935 create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
936 insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
937 select distinct parent,child from t1 order by parent;
941 # Test that MySQL priorities clustered indexes
943 create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
944 create table t2 (a int not null auto_increment primary key, b int);
945 insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
946 insert into t2 (a) select b from t1;
947 insert into t1 (b) select b from t2;
948 insert into t2 (a) select b from t1;
949 insert into t1 (a) select b from t2;
950 insert into t2 (a) select b from t1;
951 insert into t1 (a) select b from t2;
952 insert into t2 (a) select b from t1;
953 insert into t1 (a) select b from t2;
954 insert into t2 (a) select b from t1;
955 insert into t1 (a) select b from t2;
956 select count(*) from t1;
958 explain select * from t1 where c between 1 and 2500;
961 explain select * from t1 where c between 1 and 2500;
965 # Test of UPDATE ... ORDER BY
968 create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
970 insert into t1 (id) values (null),(null),(null),(null),(null);
971 update t1 set fk=69 where fk is null order by id limit 1;
975 create table t1 (a int not null, b int not null, key (a));
976 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);
978 update t1 set b=(@tmp:=@tmp+1) order by a;
979 update t1 set b=99 where a=1 order by b asc limit 1;
980 update t1 set b=100 where a=1 order by b desc limit 2;
981 update t1 set a=a+10+b where a=1 order by b;
982 select * from t1 order by a,b;
986 # Test of multi-table-updates (bug #1980).
989 create table t1 ( c char(8) not null ) engine=innodb;
990 insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
991 insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
993 alter table t1 add b char(8) not null;
994 alter table t1 add a char(8) not null;
995 alter table t1 add primary key (a,b,c);
996 update t1 set a=c, b=c;
998 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;
999 insert into t2 select * from t1;
1001 delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
1005 # test autoincrement with TRUNCATE
1009 create table t1 (a integer auto_increment primary key) engine=innodb;
1010 insert into t1 (a) values (NULL),(NULL);
1012 insert into t1 (a) values (NULL),(NULL);
1017 # Test dictionary handling with spaceand quoting
1020 CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
1021 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;
1022 #show create table t2;
1026 # Test of multi updated and foreign keys
1029 create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;
1030 insert into `t1`values ( 1 ) ;
1031 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;
1032 insert into `t2`values ( 1 ) ;
1033 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;
1034 insert into `t3`values ( 1 ) ;
1036 delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1038 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;
1040 update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1041 drop table t3,t2,t1;
1043 # test ON UPDATE CASCADE
1045 c1 VARCHAR(8), c2 VARCHAR(8),
1046 PRIMARY KEY (c1, c2)
1051 c1 VARCHAR(8) UNIQUE,
1052 FOREIGN KEY (c1) REFERENCES t1 (c1) ON UPDATE CASCADE
1055 INSERT INTO t1 VALUES ('old', 'somevalu'), ('other', 'anyvalue');
1056 INSERT INTO t2 VALUES (10, 'old'), (20, 'other');
1057 -- error ER_FOREIGN_DUPLICATE_KEY
1058 UPDATE t1 SET c1 = 'other' WHERE c1 = 'old';
1062 # test for recursion depth limit
1068 foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1069 insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1070 (8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1072 delete from t1 where id=0;
1073 delete from t1 where id=15;
1074 delete from t1 where id=0;
1082 CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1083 CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1084 (stamp))ENGINE=InnoDB;
1085 insert into t1 values (1),(2),(3);
1086 # Note that timestamp 3 is wrong
1087 insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1088 SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1089 '20020204120000' GROUP BY col1;
1093 # Test by Francois MASUREL
1097 `id` int(10) unsigned NOT NULL auto_increment,
1098 `id_object` int(10) unsigned default '0',
1099 `id_version` int(10) unsigned NOT NULL default '1',
1100 `label` varchar(100) NOT NULL default '',
1103 KEY `id_object` (`id_object`),
1104 KEY `id_version` (`id_version`)
1107 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);
1110 `id` int(10) unsigned NOT NULL auto_increment,
1111 `id_version` int(10) unsigned NOT NULL default '1',
1113 KEY `id_version` (`id_version`)
1116 INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1118 SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1119 (SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1120 ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1123 create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1124 create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1125 create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1126 insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1127 insert t2 select * from t1;
1128 insert t3 select * from t1;
1129 checksum table t1, t2, t3, t4 quick;
1130 checksum table t1, t2, t3, t4;
1131 checksum table t1, t2, t3, t4 extended;
1133 drop table t1,t2,t3;
1136 # Test problem with refering to different fields in same table in UNION
1139 create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=innodb;
1140 insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1141 select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;
1147 create table t1 (a int) engine=innodb;
1148 create table t2 like t1;
1152 # Test of automaticly created foreign keys
1155 create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
1156 create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1157 show create table t1;
1158 show create table t2;
1159 create index id on t2 (id);
1160 show create table t2;
1161 create index id2 on t2 (id);
1162 show create table t2;
1163 drop index id2 on t2;
1164 --error ER_DROP_INDEX_FK
1165 drop index id on t2;
1166 show create table t2;
1169 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;
1170 show create table t2;
1171 create unique index id on t2 (id,id2);
1172 show create table t2;
1175 # Check foreign key columns created in different order than key columns
1176 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;
1177 show create table t2;
1180 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;
1181 show create table t2;
1184 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;
1185 show create table t2;
1188 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;
1189 show create table t2;
1192 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;
1193 show create table t2;
1194 alter table t2 add index id_test (id), add index id_test2 (id,id2);
1195 show create table t2;
1198 # Test error handling
1200 # Embedded server doesn't chdir to data directory
1201 --replace_result $MYSQLTEST_VARDIR . master-data/ ''
1202 --error ER_WRONG_FK_DEF
1203 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;
1207 create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1208 show create table t2;
1210 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;
1211 show create table t2;
1216 # Bug #6126: Duplicate columns in keys gives misleading error message
1219 create table t1 (c char(10), index (c,c)) engine=innodb;
1221 create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1223 create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1225 create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1226 create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1228 alter table t1 add key (c1,c1);
1230 alter table t1 add key (c2,c1,c1);
1232 alter table t1 add key (c1,c2,c1);
1234 alter table t1 add key (c1,c1,c2);
1238 # Bug #4082: integer truncation
1241 create table t1(a int(1) , b int(1)) engine=innodb;
1242 insert into t1 values ('1111', '3333');
1243 select distinct concat(a, b) from t1;
1247 # BUG#7709 test case - Boolean fulltext query against unsupported
1248 # engines does not fail
1251 CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
1253 SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
1257 # check null values #1
1261 CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1262 INSERT INTO t1 VALUES (1),(2),(3);
1263 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),
1264 CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1266 INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1267 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;
1272 # Bug#11816 - Truncate table doesn't work with temporary innodb tables
1273 # This is not an innodb bug, but we test it using innodb.
1275 create temporary table t1 (a int) engine=innodb;
1276 insert into t1 values (4711);
1278 insert into t1 values (42);
1281 # Show that it works with permanent tables too.
1282 create table t1 (a int) engine=innodb;
1283 insert into t1 values (4711);
1285 insert into t1 values (42);
1290 # Bug #13025 Server crash during filesort
1293 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;
1294 insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1295 select * from t1 order by a,b,c,d;
1296 explain select * from t1 order by a,b,c,d;
1300 # BUG#11039,#13218 Wrong key length in min()
1303 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1304 insert into t1 values ('8', '6'), ('4', '7');
1305 select min(a) from t1;
1306 select min(b) from t1 where a='8';
1312 # range optimizer problem
1315 create table t1 (x bigint unsigned not null primary key) engine=innodb;
1316 insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1318 select count(*) from t1 where x>0;
1319 select count(*) from t1 where x=0;
1320 select count(*) from t1 where x<0;
1321 select count(*) from t1 where x < -16;
1322 select count(*) from t1 where x = -16;
1323 explain select count(*) from t1 where x > -16;
1324 select count(*) from t1 where x > -16;
1325 select * from t1 where x > -16;
1326 select count(*) from t1 where x = 18446744073709551601;
1330 # Test for testable InnoDB status variables. This test
1331 # uses previous ones(pages_created, rows_deleted, ...).
1332 --replace_result 8192 8191
1333 SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
1334 SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size';
1335 SELECT variable_value - @innodb_rows_deleted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted';
1336 SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted';
1337 SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated';
1339 # Test for row locks InnoDB status variables.
1340 SELECT variable_value - @innodb_row_lock_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits';
1341 SELECT variable_value - @innodb_row_lock_current_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits';
1342 SELECT variable_value - @innodb_row_lock_time_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time';
1343 SELECT variable_value - @innodb_row_lock_time_max_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max';
1344 SELECT variable_value - @innodb_row_lock_time_avg_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg';
1346 # Test for innodb_sync_spin_loops variable
1347 SET @innodb_sync_spin_loops_orig = @@innodb_sync_spin_loops;
1348 show variables like "innodb_sync_spin_loops";
1349 set global innodb_sync_spin_loops=1000;
1350 show variables like "innodb_sync_spin_loops";
1351 set global innodb_sync_spin_loops=0;
1352 show variables like "innodb_sync_spin_loops";
1353 set global innodb_sync_spin_loops=20;
1354 show variables like "innodb_sync_spin_loops";
1355 set global innodb_sync_spin_loops=@innodb_sync_spin_loops_orig;
1357 # Test for innodb_thread_concurrency variable
1358 show variables like "innodb_thread_concurrency";
1359 set global innodb_thread_concurrency=1001;
1360 show variables like "innodb_thread_concurrency";
1361 set global innodb_thread_concurrency=0;
1362 show variables like "innodb_thread_concurrency";
1363 set global innodb_thread_concurrency=16;
1364 show variables like "innodb_thread_concurrency";
1366 # Test for innodb_concurrency_tickets variable
1367 show variables like "innodb_concurrency_tickets";
1368 set global innodb_concurrency_tickets=1000;
1369 show variables like "innodb_concurrency_tickets";
1370 set global innodb_concurrency_tickets=0;
1371 show variables like "innodb_concurrency_tickets";
1372 set global innodb_concurrency_tickets=500;
1373 show variables like "innodb_concurrency_tickets";
1375 # Test for innodb_thread_sleep_delay variable
1376 show variables like "innodb_thread_sleep_delay";
1377 set global innodb_thread_sleep_delay=100000;
1378 show variables like "innodb_thread_sleep_delay";
1379 set global innodb_thread_sleep_delay=0;
1380 show variables like "innodb_thread_sleep_delay";
1381 set global innodb_thread_sleep_delay=10000;
1382 show variables like "innodb_thread_sleep_delay";
1388 let $default=`select @@storage_engine`;
1389 set storage_engine=INNODB;
1390 # this should be fixed by MySQL (see Bug #51451)
1391 set session old_alter_table=1;
1392 source include/varchar.inc;
1393 set session old_alter_table=0;
1396 # Some errors/warnings on create
1399 # Embedded server doesn't chdir to data directory
1400 --replace_result $MYSQLTEST_VARDIR . master-data/ ''
1401 create table t1 (v varchar(65530), key(v));
1403 create table t1 (v varchar(65536));
1404 show create table t1;
1406 create table t1 (v varchar(65530) character set utf8);
1407 show create table t1;
1410 eval set storage_engine=$default;
1412 # InnoDB specific varchar tests
1413 create table t1 (v varchar(16384)) engine=innodb;
1417 # BUG#11039 Wrong key length in min()
1420 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1421 insert into t1 values ('8', '6'), ('4', '7');
1422 select min(a) from t1;
1423 select min(b) from t1 where a='8';
1427 # Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error
1430 CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
1431 insert into t1 (b) values (1);
1432 replace into t1 (b) values (2), (1), (3);
1435 insert into t1 (b) values (1);
1436 replace into t1 (b) values (2);
1437 replace into t1 (b) values (1);
1438 replace into t1 (b) values (3);
1442 create table t1 (rowid int not null auto_increment, val int not null,primary
1443 key (rowid), unique(val)) engine=innodb;
1444 replace into t1 (val) values ('1'),('2');
1445 replace into t1 (val) values ('1'),('2');
1446 --error ER_DUP_ENTRY
1447 insert into t1 (val) values ('1'),('2');
1452 # Test that update does not change internal auto-increment value
1455 create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
1456 insert into t1 (val) values (1);
1457 update t1 set a=2 where a=1;
1458 # We should get the following error because InnoDB does not update the counter
1459 --error ER_DUP_ENTRY
1460 insert into t1 (val) values (1);
1468 CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
1470 INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
1471 SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
1472 SELECT GRADE FROM t1 WHERE GRADE= 151;
1476 # Bug #12340 multitable delete deletes only one record
1478 create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
1479 create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
1480 insert into t2 values ('aa','cc');
1481 insert into t1 values ('aa','bb'),('aa','cc');
1482 delete t1 from t1,t2 where f1=f3 and f4='cc';
1487 # Test that the slow TRUNCATE implementation resets autoincrement columns
1492 id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
1496 id INTEGER NOT NULL,
1497 FOREIGN KEY (id) REFERENCES t1 (id)
1500 INSERT INTO t1 (id) VALUES (NULL);
1503 INSERT INTO t1 (id) VALUES (NULL);
1506 # continued from above; test that doing a slow TRUNCATE on a table with 0
1507 # rows resets autoincrement columns
1510 INSERT INTO t1 (id) VALUES (NULL);
1514 # Test that foreign keys in temporary tables are not accepted (bug #12084)
1521 CREATE TEMPORARY TABLE t2
1523 id INT NOT NULL PRIMARY KEY,
1525 FOREIGN KEY (b) REFERENCES test.t1(id)
1530 # Test that index column max sizes are honored (bug #13315)
1534 create table t1 (col1 varchar(2000), index (col1(767)))
1535 character set = latin1 engine = innodb;
1538 create table t2 (col1 char(255), index (col1))
1539 character set = latin1 engine = innodb;
1540 create table t3 (col1 binary(255), index (col1))
1541 character set = latin1 engine = innodb;
1542 create table t4 (col1 varchar(767), index (col1))
1543 character set = latin1 engine = innodb;
1544 create table t5 (col1 varchar(767) primary key)
1545 character set = latin1 engine = innodb;
1546 create table t6 (col1 varbinary(767) primary key)
1547 character set = latin1 engine = innodb;
1548 create table t7 (col1 text, index(col1(767)))
1549 character set = latin1 engine = innodb;
1550 create table t8 (col1 blob, index(col1(767)))
1551 character set = latin1 engine = innodb;
1553 # multi-column indexes are allowed to be longer
1554 create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
1555 character set = latin1 engine = innodb;
1557 show create table t9;
1559 drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
1561 # these should have their index length trimmed
1562 create table t1 (col1 varchar(768), index(col1))
1563 character set = latin1 engine = innodb;
1564 create table t2 (col1 varbinary(768), index(col1))
1565 character set = latin1 engine = innodb;
1566 create table t3 (col1 text, index(col1(768)))
1567 character set = latin1 engine = innodb;
1568 create table t4 (col1 blob, index(col1(768)))
1569 character set = latin1 engine = innodb;
1571 show create table t1;
1573 drop table t1, t2, t3, t4;
1575 # these should be refused
1577 create table t1 (col1 varchar(768) primary key)
1578 character set = latin1 engine = innodb;
1580 create table t2 (col1 varbinary(768) primary key)
1581 character set = latin1 engine = innodb;
1583 create table t3 (col1 text, primary key(col1(768)))
1584 character set = latin1 engine = innodb;
1586 create table t4 (col1 blob, primary key(col1(768)))
1587 character set = latin1 engine = innodb;
1590 # Test improved foreign key error messages (bug #3443)
1601 CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
1605 INSERT INTO t2 VALUES(2);
1607 INSERT INTO t1 VALUES(1);
1608 INSERT INTO t2 VALUES(1);
1611 DELETE FROM t1 WHERE id = 1;
1616 SET FOREIGN_KEY_CHECKS=0;
1618 SET FOREIGN_KEY_CHECKS=1;
1621 INSERT INTO t2 VALUES(3);
1625 # Test that checksum table uses a consistent read Bug #12669
1627 connect (a,localhost,root,,);
1628 connect (b,localhost,root,,);
1630 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1631 insert into t1 values (1),(2);
1635 insert into t1 values(3);
1638 # Here checksum should not see insert
1650 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1651 insert into t1 values (1),(2);
1656 insert into t1 values(3);
1659 # Here checksum sees insert
1668 # tests for bugs #9802 and #13778
1670 # test that FKs between invalid types are not accepted
1672 set foreign_key_checks=0;
1673 create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
1674 # Embedded server doesn't chdir to data directory
1675 --replace_result $MYSQLTEST_VARDIR . master-data/ ''
1677 create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
1678 set foreign_key_checks=1;
1681 # test that FKs between different charsets are not accepted in CREATE even
1684 set foreign_key_checks=0;
1685 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1686 # Embedded server doesn't chdir to data directory
1687 --replace_result $MYSQLTEST_VARDIR . master-data/ ''
1689 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
1690 set foreign_key_checks=1;
1693 # test that invalid datatype conversions with ALTER are not allowed
1695 set foreign_key_checks=0;
1696 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
1697 create table t1(a varchar(10) primary key) engine = innodb;
1699 alter table t1 modify column a int;
1700 set foreign_key_checks=1;
1703 # test that charset conversions with ALTER are allowed when f_k_c is 0
1705 set foreign_key_checks=0;
1706 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1707 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1708 alter table t1 convert to character set utf8;
1709 set foreign_key_checks=1;
1712 # test that RENAME does not allow invalid charsets when f_k_c is 0
1714 set foreign_key_checks=0;
1715 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1716 create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
1717 # Embedded server doesn't chdir to data directory
1718 --replace_result $MYSQLD_DATADIR ./ master-data/ ''
1720 rename table t3 to t1;
1721 set foreign_key_checks=1;
1724 # test that foreign key errors are reported correctly (Bug #15550)
1726 create table t1(a int primary key) row_format=redundant engine=innodb;
1727 create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
1728 create table t3(a int primary key) row_format=compact engine=innodb;
1729 create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
1731 insert into t1 values(1);
1732 insert into t3 values(1);
1734 insert into t2 values(2);
1736 insert into t4 values(2);
1737 insert into t2 values(1);
1738 insert into t4 values(1);
1756 drop table t4,t3,t2,t1;
1760 # Test that we can create a large (>1K) key
1762 create table t1 (a varchar(255) character set utf8,
1763 b varchar(255) character set utf8,
1764 c varchar(255) character set utf8,
1765 d varchar(255) character set utf8,
1766 key (a,b,c,d)) engine=innodb;
1768 --error ER_TOO_LONG_KEY
1769 create table t1 (a varchar(255) character set utf8,
1770 b varchar(255) character set utf8,
1771 c varchar(255) character set utf8,
1772 d varchar(255) character set utf8,
1773 e varchar(255) character set utf8,
1774 key (a,b,c,d,e)) engine=innodb;
1777 # test the padding of BINARY types and collations (Bug #14189)
1779 create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
1780 create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
1781 create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
1782 create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
1784 insert into t1 values (0x41),(0x4120),(0x4100);
1785 -- error ER_DUP_ENTRY
1786 insert into t2 values (0x41),(0x4120),(0x4100);
1787 insert into t2 values (0x41),(0x4120);
1788 -- error ER_DUP_ENTRY
1789 insert into t3 values (0x41),(0x4120),(0x4100);
1790 insert into t3 values (0x41),(0x4100);
1791 -- error ER_DUP_ENTRY
1792 insert into t4 values (0x41),(0x4120),(0x4100);
1793 insert into t4 values (0x41),(0x4100);
1794 select hex(s1) from t1;
1795 select hex(s1) from t2;
1796 select hex(s1) from t3;
1797 select hex(s1) from t4;
1798 drop table t1,t2,t3,t4;
1800 create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
1801 create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1803 insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
1805 insert into t2 values(0x42);
1806 insert into t2 values(0x41);
1807 select hex(s1) from t2;
1808 update t1 set s1=0x123456 where a=2;
1809 select hex(s1) from t2;
1811 update t1 set s1=0x12 where a=1;
1813 update t1 set s1=0x12345678 where a=1;
1815 update t1 set s1=0x123457 where a=1;
1816 update t1 set s1=0x1220 where a=1;
1817 select hex(s1) from t2;
1818 update t1 set s1=0x1200 where a=1;
1819 select hex(s1) from t2;
1820 update t1 set s1=0x4200 where a=1;
1821 select hex(s1) from t2;
1823 delete from t1 where a=1;
1824 delete from t1 where a=2;
1825 update t2 set s1=0x4120;
1828 delete from t1 where a!=3;
1829 select a,hex(s1) from t1;
1830 select hex(s1) from t2;
1834 create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
1835 create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1837 insert into t1 values(1,0x4100),(2,0x41);
1838 insert into t2 values(0x41);
1839 select hex(s1) from t2;
1840 update t1 set s1=0x1234 where a=1;
1841 select hex(s1) from t2;
1842 update t1 set s1=0x12 where a=2;
1843 select hex(s1) from t2;
1844 delete from t1 where a=1;
1846 delete from t1 where a=2;
1847 select a,hex(s1) from t1;
1848 select hex(s1) from t2;
1851 # Ensure that <tablename>_ibfk_0 is not mistreated as a
1852 # generated foreign key identifier. (Bug #16387)
1854 CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
1855 CREATE TABLE t2(a INT) ENGINE=InnoDB;
1856 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
1857 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
1858 ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
1859 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
1860 SHOW CREATE TABLE t2;
1864 # Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing
1867 connect (a,localhost,root,,);
1868 connect (b,localhost,root,,);
1870 create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1871 insert into t1(a) values (1),(2),(3);
1875 update t1 set b = 5 where a = 2;
1878 create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
1882 insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
1883 (11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
1884 (12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
1885 (13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
1886 (14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
1896 # Another trigger test
1898 connect (a,localhost,root,,);
1899 connect (b,localhost,root,,);
1901 create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1902 create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1903 create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1904 create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1905 create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1906 insert into t1(a) values (1),(2),(3);
1907 insert into t2(a) values (1),(2),(3);
1908 insert into t3(a) values (1),(2),(3);
1909 insert into t4(a) values (1),(2),(3);
1910 insert into t3(a) values (5),(7),(8);
1911 insert into t4(a) values (5),(7),(8);
1912 insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
1915 create trigger t1t before insert on t1 for each row begin
1916 INSERT INTO t2 SET a = NEW.a;
1919 create trigger t2t before insert on t2 for each row begin
1920 DELETE FROM t3 WHERE a = NEW.a;
1923 create trigger t3t before delete on t3 for each row begin
1924 UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
1927 create trigger t4t before update on t4 for each row begin
1928 UPDATE t5 SET b = b + 1 where a = NEW.a;
1933 update t1 set b = b + 5 where a = 1;
1934 update t2 set b = b + 5 where a = 1;
1935 update t3 set b = b + 5 where a = 1;
1936 update t4 set b = b + 5 where a = 1;
1937 insert into t5(a) values(20);
1940 insert into t1(a) values(7);
1941 insert into t2(a) values(8);
1942 delete from t2 where a = 3;
1943 update t4 set b = b + 1 where a = 3;
1949 drop table t1, t2, t3, t4, t5;
1955 # Test that cascading updates leading to duplicate keys give the correct
1956 # error message (bug #9680)
1960 field1 varchar(8) NOT NULL DEFAULT '',
1961 field2 varchar(8) NOT NULL DEFAULT '',
1962 PRIMARY KEY (field1, field2)
1966 field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
1967 FOREIGN KEY (field1) REFERENCES t1 (field1)
1968 ON DELETE CASCADE ON UPDATE CASCADE
1971 INSERT INTO t1 VALUES ('old', 'somevalu');
1972 INSERT INTO t1 VALUES ('other', 'anyvalue');
1974 INSERT INTO t2 VALUES ('old');
1975 INSERT INTO t2 VALUES ('other');
1977 --error ER_FOREIGN_DUPLICATE_KEY
1978 UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
1984 # Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
1998 alter table t1 add constraint c2_fk foreign key (c2)
1999 references t2(c1) on delete cascade;
2000 show create table t1;
2002 alter table t1 drop foreign key c2_fk;
2003 show create table t1;
2008 # Bug #14360: problem with intervals
2011 create table t1(a date) engine=innodb;
2012 create table t2(a date, key(a)) engine=innodb;
2013 insert into t1 values('2005-10-01');
2014 insert into t2 values('2005-10-01');
2015 select * from t1, t2
2016 where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
2019 create table t1 (id int not null, f_id int not null, f int not null,
2020 primary key(f_id, id)) engine=innodb;
2021 create table t2 (id int not null,s_id int not null,s varchar(200),
2022 primary key(id)) engine=innodb;
2023 INSERT INTO t1 VALUES (8, 1, 3);
2024 INSERT INTO t1 VALUES (1, 2, 1);
2025 INSERT INTO t2 VALUES (1, 0, '');
2026 INSERT INTO t2 VALUES (8, 1, '');
2028 DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
2029 WHERE mm.id IS NULL;
2030 select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
2031 where mm.id is null lock in share mode;
2035 # Test case where X-locks on unused rows should be released in a
2036 # update (because READ COMMITTED isolation level)
2039 connect (a,localhost,root,,);
2040 connect (b,localhost,root,,);
2042 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2043 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
2045 SET binlog_format='MIXED';
2047 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2048 update t1 set b = 5 where b = 1;
2050 SET binlog_format='MIXED';
2052 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2054 # X-lock to record (7,3) should be released in a update
2056 select * from t1 where a = 7 and b = 3 for update;
2067 # Test case where no locks should be released (because we are not
2068 # using READ COMMITTED isolation level)
2071 connect (a,localhost,root,,);
2072 connect (b,localhost,root,,);
2074 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2075 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
2078 select * from t1 lock in share mode;
2079 update t1 set b = 5 where b = 1;
2083 # S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
2086 select * from t1 where a = 2 and b = 2 for update;
2088 # X-lock to record (1,1),(3,1),(5,1) should not be released in a update
2101 # Consistent read should be used in following selects
2103 # 1) INSERT INTO ... SELECT
2104 # 2) UPDATE ... = ( SELECT ...)
2105 # 3) CREATE ... SELECT
2107 connect (a,localhost,root,,);
2108 connect (b,localhost,root,,);
2110 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2111 insert into t1 values (1,2),(5,3),(4,2);
2112 create table t2(d int not null, e int, primary key(d)) engine=innodb;
2113 insert into t2 values (8,6),(12,1),(3,1);
2116 select * from t2 for update;
2118 SET binlog_format='MIXED';
2120 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2121 insert into t1 select * from t2;
2122 update t1 set b = (select e from t2 where a = d);
2123 create table t3(d int not null, e int, primary key(d)) engine=innodb
2131 drop table t1, t2, t3;
2134 # Consistent read should not be used if
2136 # (a) isolation level is serializable OR
2137 # (b) select ... lock in share mode OR
2138 # (c) select ... for update
2140 # in following queries:
2142 # 1) INSERT INTO ... SELECT
2143 # 2) UPDATE ... = ( SELECT ...)
2144 # 3) CREATE ... SELECT
2146 connect (a,localhost,root,,);
2147 connect (b,localhost,root,,);
2148 connect (c,localhost,root,,);
2149 connect (d,localhost,root,,);
2150 connect (e,localhost,root,,);
2151 connect (f,localhost,root,,);
2152 connect (g,localhost,root,,);
2153 connect (h,localhost,root,,);
2154 connect (i,localhost,root,,);
2155 connect (j,localhost,root,,);
2157 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2158 insert into t1 values (1,2),(5,3),(4,2);
2159 create table t2(a int not null, b int, primary key(a)) engine=innodb;
2160 insert into t2 values (8,6),(12,1),(3,1);
2161 create table t3(d int not null, b int, primary key(d)) engine=innodb;
2162 insert into t3 values (8,6),(12,1),(3,1);
2163 create table t5(a int not null, b int, primary key(a)) engine=innodb;
2164 insert into t5 values (1,2),(5,3),(4,2);
2165 create table t6(d int not null, e int, primary key(d)) engine=innodb;
2166 insert into t6 values (8,6),(12,1),(3,1);
2167 create table t8(a int not null, b int, primary key(a)) engine=innodb;
2168 insert into t8 values (1,2),(5,3),(4,2);
2169 create table t9(d int not null, e int, primary key(d)) engine=innodb;
2170 insert into t9 values (8,6),(12,1),(3,1);
2173 select * from t2 for update;
2175 SET binlog_format='MIXED';
2177 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2179 insert into t1 select * from t2;
2181 SET binlog_format='MIXED';
2183 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2185 update t3 set b = (select b from t2 where a = d);
2187 SET binlog_format='MIXED';
2189 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2191 create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
2193 SET binlog_format='MIXED';
2195 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2197 insert into t5 (select * from t2 lock in share mode);
2199 SET binlog_format='MIXED';
2201 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2203 update t6 set e = (select b from t2 where a = d lock in share mode);
2205 SET binlog_format='MIXED';
2207 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2209 create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
2211 SET binlog_format='MIXED';
2213 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2215 insert into t8 (select * from t2 for update);
2217 SET binlog_format='MIXED';
2219 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2221 update t9 set e = (select b from t2 where a = d for update);
2223 SET binlog_format='MIXED';
2225 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2227 create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
2279 drop table t1, t2, t3, t5, t6, t8, t9;
2281 # bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"
2282 --error ER_WRONG_COLUMN_NAME
2283 CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
2286 # Bug #17152: Wrong result with BINARY comparison on aliased column
2290 a BIGINT(20) NOT NULL,
2292 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2295 a BIGINT(20) NOT NULL,
2296 b VARCHAR(128) NOT NULL,
2299 KEY idx_t2_b_c (b,c(200)),
2300 CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
2302 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2304 INSERT INTO t1 VALUES (1);
2305 INSERT INTO t2 VALUES (1, 'bar', 'vbar');
2306 INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
2307 INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
2308 INSERT INTO t2 VALUES (1, 'customer_over', '1');
2310 SELECT * FROM t2 WHERE b = 'customer_over';
2311 SELECT * FROM t2 WHERE BINARY b = 'customer_over';
2312 SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
2313 /* Bang: Empty result set, above was expected: */
2314 SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2315 SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2320 # Test optimize on table with open transaction
2323 CREATE TABLE t1 ( a int ) ENGINE=innodb;
2325 INSERT INTO t1 VALUES (1);
2330 # Bug #24741 (existing cascade clauses disappear when adding foreign keys)
2333 CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
2335 CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
2336 CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
2337 ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
2339 ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
2340 DELETE CASCADE ON UPDATE CASCADE;
2342 SHOW CREATE TABLE t2;
2346 # Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns
2347 # for which there is a foreign key constraint ON ... SET NULL.
2350 CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
2351 CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
2352 INSERT INTO t1 VALUES (1);
2353 INSERT INTO t2 VALUES (1);
2354 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
2355 # mysqltest first does replace_regex, then replace_result
2356 --replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
2357 # Embedded server doesn't chdir to data directory
2358 --replace_result $MYSQLD_DATADIR ./ master-data/ ''
2360 ALTER TABLE t2 MODIFY a INT NOT NULL;
2365 # Bug #26835: table corruption after delete+insert
2368 CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
2370 INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
2372 INSERT INTO t1 VALUES ('DDD');
2377 # Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)
2378 # Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))
2381 CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
2384 INSERT INTO t1 VALUES (0),(347),(0);
2387 SHOW CREATE TABLE t1;
2389 CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
2390 INSERT INTO t2 VALUES(42),(347),(348);
2391 ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
2392 SHOW CREATE TABLE t1;
2397 # Bug #21101 (Prints wrong error message if max row size is too large)
2399 set innodb_strict_mode=on;
2402 c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
2403 c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
2404 c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
2405 c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
2406 c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
2407 c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
2408 c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
2409 c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
2413 # Bug #31860 InnoDB assumes AUTOINC values can only be positive.
2415 DROP TABLE IF EXISTS t1;
2417 id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
2419 INSERT INTO t1 VALUES(-10);
2422 # NOTE: The server really needs to be restarted at this point
2423 # for the test to be useful.
2425 # Without the fix InnoDB would trip over an assertion here.
2426 INSERT INTO t1 VALUES(NULL);
2427 # The next value should be 1 and not -9 or a -ve number
2432 # Bug #21409 Incorrect result returned when in READ-COMMITTED with
2435 CONNECT (c1,localhost,root,,);
2436 CONNECT (c2,localhost,root,,);
2438 SET binlog_format='MIXED';
2439 SET TX_ISOLATION='read-committed';
2441 DROP TABLE IF EXISTS t1, t2;
2442 CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
2443 CREATE TABLE t2 LIKE t1;
2446 SET binlog_format='MIXED';
2447 SET TX_ISOLATION='read-committed';
2449 INSERT INTO t1 VALUES (1);
2452 SELECT * FROM t1 WHERE a=1;
2455 CONNECT (c1,localhost,root,,);
2456 CONNECT (c2,localhost,root,,);
2458 SET binlog_format='MIXED';
2459 SET TX_ISOLATION='read-committed';
2463 SET binlog_format='MIXED';
2464 SET TX_ISOLATION='read-committed';
2466 INSERT INTO t1 VALUES (2);
2469 # The result set below should be the same for both selects
2470 SELECT * FROM t1 WHERE a=2;
2471 SELECT * FROM t1 WHERE a=2;
2479 # Bug #29157 UPDATE, changed rows incorrect
2481 create table t1 (i int, j int) engine=innodb;
2482 insert into t1 (i, j) values (1, 1), (2, 2);
2484 update t1 set j = 2;
2489 # Bug #32440 InnoDB free space info does not appear in SHOW TABLE STATUS or
2492 create table t1 (id int) comment='this is a comment' engine=innodb;
2493 select table_comment, data_free > 0 as data_free_is_set
2494 from information_schema.tables
2495 where table_schema='test' and table_name = 't1';
2503 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
2504 c2 VARCHAR(128) NOT NULL,
2506 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
2509 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
2510 c2 INT(10) UNSIGNED DEFAULT NULL,
2512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
2514 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
2515 ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
2516 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
2521 # Bug #29507 TRUNCATE shows to many rows effected
2524 CREATE TABLE t1 (c1 int default NULL,
2526 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2531 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
2537 # Bug#35537 Innodb doesn't increment handler_update and handler_delete.
2539 -- disable_query_log
2540 -- disable_result_log
2542 CONNECT (c1,localhost,root,,);
2544 DROP TABLE IF EXISTS bug35537;
2545 CREATE TABLE bug35537 (
2549 INSERT INTO bug35537 VALUES (1);
2551 -- enable_result_log
2553 SHOW SESSION STATUS LIKE 'Handler_update%';
2554 SHOW SESSION STATUS LIKE 'Handler_delete%';
2556 UPDATE bug35537 SET c1 = 2 WHERE c1 = 1;
2557 DELETE FROM bug35537 WHERE c1 = 2;
2559 SHOW SESSION STATUS LIKE 'Handler_update%';
2560 SHOW SESSION STATUS LIKE 'Handler_delete%';
2562 DROP TABLE bug35537;
2567 SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig;
2571 # Clean up after the Bug#55284/Bug#58912 test case.
2572 DROP TABLE bug58912;
2575 # Test fix for bug 13117023. InnoDB increments HA_READ_KEY_COUNT (aka
2576 # HANDLER_READ_KEY) when it should not.
2578 create table t1 (f1 integer primary key) engine=innodb;
2580 show status like "handler_read_key";
2582 show status like "handler_read_key";
2585 #######################################################################
2587 # Please, DO NOT TOUCH this file as well as the innodb.result file. #
2588 # These files are to be modified ONLY BY INNOBASE guys. #
2590 # Use innodb_mysql.[test|result] files instead. #
2592 # If nevertheless you need to make some changes here, please, forward #
2593 # your commit message #
2594 # To: innodb_dev_ww@oracle.com #
2595 # Cc: dev-innodb@mysql.com #
2596 # (otherwise your changes may be erased). #
2598 #######################################################################