Debian Codegraph @

mysql-test/suite/innodb/t/innodb.test

raw
1 -- source include/have_innodb.inc
2 -- source include/have_innodb_16k.inc
3
4 let $MYSQLD_DATADIR= `select @@datadir`;
5
6 let collation=utf8mb3_unicode_ci;
7 --source include/have_collation.inc
8
9 --source include/test_db_charset_latin1.inc
10
11 create temporary table t (a char(1) character set filename) engine=innodb;
12 drop temporary table t;
13
14 set optimizer_switch = 'mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
15
16 # Save the original values of some variables in order to be able to
17 # estimate how much they have changed during the tests. Previously this
18 # test assumed that e.g. rows_deleted is 0 here and after deleting 23
19 # rows it expected that rows_deleted will be 23. Now we do not make
20 # assumptions about the values of the variables at the beginning, e.g.
21 # rows_deleted should be 23 + "rows_deleted before the test". This allows
22 # the test to be run multiple times without restarting the mysqld server.
23 # See Bug#43309 Test main.innodb can't be run twice
24 -- disable_query_log
25 call mtr.add_suppression("Cannot add field .* in table .* because after adding it, the row size is");
26 call mtr.add_suppression("\\[ERROR\\] InnoDB: in ALTER TABLE `test`.`t1`");
27 call mtr.add_suppression("\\[ERROR\\] InnoDB: in RENAME TABLE table `test`.`t1`");
28
29 --disable_warnings
30 SET @innodb_row_lock_waits_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits');
31 SET @innodb_row_lock_current_waits_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits');
32 SET @innodb_row_lock_time_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time');
33 SET @innodb_row_lock_time_max_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max');
34 SET @innodb_row_lock_time_avg_orig = (SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg');
35 --enable_warnings
36 -- enable_query_log
37
38 --disable_warnings
39 drop table if exists t1,t2,t3,t4;
40 drop database if exists mysqltest;
41 --enable_warnings
42
43 # Bug#58912 InnoDB unnecessarily avoids update-in-place on column prefixes
44 CREATE TABLE bug58912 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB;
45 INSERT INTO bug58912 VALUES(REPEAT('a',8000),REPEAT('b',8000));
46 UPDATE bug58912 SET a=REPEAT('a',7999);
47 # The above statements used to trigger a failure during purge when
48 # Bug#55284 was fixed while Bug#58912 was not. Defer the DROP TABLE,
49 # so that purge gets a chance to run (and a double free of the
50 # off-page column can be detected, if one is to occur.)
51
52 #
53 # Small basic test with ignore
54 #
55
56 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;
57
58 insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
59 select id, code, name from t1 order by id;
60
61 update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
62 select id, code, name from t1 order by id;
63 update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
64 select id, code, name from t1 order by id;
65
66 drop table t1;
67
68 #
69 # A bit bigger test
70 # The 'replace_column' statements are needed because the cardinality calculated
71 # by innodb is not always the same between runs
72 #
73
74 CREATE TABLE t1 (
75 id int(11) NOT NULL auto_increment,
76 parent_id int(11) DEFAULT '0' NOT NULL,
77 level tinyint(4) DEFAULT '0' NOT NULL,
78 PRIMARY KEY (id),
79 KEY parent_id (parent_id),
80 KEY level (level)
81 ) engine=innodb stats_persistent=0;
82 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);
83 update t1 set parent_id=parent_id+100;
84 select * from t1 where parent_id=102;
85 update t1 set id=id+1000;
86 -- error ER_DUP_ENTRY,1022
87 update t1 set id=1024 where id=1009;
88 select * from t1;
89 update ignore t1 set id=id+1; # This will change all rows
90 select * from t1;
91 update ignore t1 set id=1023 where id=1010;
92 select * from t1 where parent_id=102;
93 --replace_column 9 #
94 explain select level from t1 where level=1;
95 --replace_column 9 #
96 explain select level,id from t1 where level=1;
97 --replace_column 9 #
98 explain select level,id,parent_id from t1 where level=1;
99 select level,id from t1 where level=1;
100 select level,id,parent_id from t1 where level=1;
101 optimize table t1;
102 --replace_column 7 #
103 show keys from t1;
104 drop table t1;
105
106 #
107 # Test replace
108 #
109
110 CREATE TABLE t1 (
111 gesuchnr int(11) DEFAULT '0' NOT NULL,
112 benutzer_id int(11) DEFAULT '0' NOT NULL,
113 PRIMARY KEY (gesuchnr,benutzer_id)
114 ) engine=innodb;
115
116 replace into t1 (gesuchnr,benutzer_id) values (2,1);
117 replace into t1 (gesuchnr,benutzer_id) values (1,1);
118 replace into t1 (gesuchnr,benutzer_id) values (1,1);
119 select * from t1;
120 drop table t1;
121
122 #
123 # test delete using hidden_primary_key
124 #
125
126 create table t1 (a int) engine=innodb;
127 insert into t1 values (1), (2);
128 optimize table t1;
129 delete from t1 where a = 1;
130 select * from t1;
131 check table t1;
132 drop table t1;
133
134 create table t1 (a int,b varchar(20)) engine=innodb;
135 insert into t1 values (1,""), (2,"testing");
136 delete from t1 where a = 1;
137 select * from t1;
138 create index skr on t1 (a);
139 insert into t1 values (3,""), (4,"testing");
140 analyze table t1;
141 --replace_column 7 #
142 show keys from t1;
143 drop table t1;
144
145
146 # Test of reading on secondary key with may be null
147
148 create table t1 (a int,b varchar(20),key(a)) engine=innodb;
149 insert into t1 values (1,""), (2,"testing");
150 select * from t1 where a = 1;
151 drop table t1;
152
153 #
154 # Test rollback
155 #
156
157 create table t1 (n int not null primary key) engine=innodb;
158 set autocommit=0;
159 insert into t1 values (4);
160 rollback;
161 select n, "after rollback" from t1;
162 insert into t1 values (4);
163 commit;
164 select n, "after commit" from t1;
165 commit;
166 insert into t1 values (5);
167 -- error ER_DUP_ENTRY
168 insert into t1 values (4);
169 commit;
170 select n, "after commit" from t1;
171 set autocommit=1;
172 insert into t1 values (6);
173 -- error ER_DUP_ENTRY
174 insert into t1 values (4);
175 select n from t1;
176 set autocommit=0;
177 #
178 # savepoints
179 #
180 begin;
181 savepoint `my_savepoint`;
182 insert into t1 values (7);
183 savepoint `savept2`;
184 insert into t1 values (3);
185 select n from t1;
186 savepoint savept3;
187 rollback to savepoint savept2;
188 --error 1305
189 rollback to savepoint savept3;
190 rollback to savepoint savept2;
191 release savepoint `my_savepoint`;
192 select n from t1;
193 -- error 1305
194 rollback to savepoint `my_savepoint`;
195 --error 1305
196 rollback to savepoint savept2;
197 insert into t1 values (8);
198 savepoint sv;
199 commit;
200 savepoint sv;
201 set autocommit=1;
202 # nop
203 rollback;
204 drop table t1;
205
206 #
207 # Test for commit and FLUSH TABLES WITH READ LOCK
208 #
209
210 create table t1 (n int not null primary key) engine=innodb;
211 start transaction;
212 insert into t1 values (4);
213 flush tables with read lock;
214 #
215 # Current code can't handle a read lock in middle of transaction
216 #--error 1223;
217 commit;
218 unlock tables;
219 commit;
220 select * from t1;
221 drop table t1;
222
223 #
224 # Testing transactions
225 #
226
227 create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
228 begin;
229 insert into t1 values(1,'hamdouni');
230 select id as afterbegin_id,nom as afterbegin_nom from t1;
231 rollback;
232 select id as afterrollback_id,nom as afterrollback_nom from t1;
233 set autocommit=0;
234 insert into t1 values(2,'mysql');
235 select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
236 rollback;
237 select id as afterrollback_id,nom as afterrollback_nom from t1;
238 set autocommit=1;
239 drop table t1;
240
241 #
242 # Simple not autocommit test
243 #
244
245 CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
246 insert into t1 values ('pippo', 12);
247 -- error ER_DUP_ENTRY
248 insert into t1 values ('pippo', 12); # Gives error
249 delete from t1;
250 delete from t1 where id = 'pippo';
251 select * from t1;
252
253 insert into t1 values ('pippo', 12);
254 set autocommit=0;
255 delete from t1;
256 rollback;
257 select * from t1;
258 delete from t1;
259 commit;
260 select * from t1;
261 drop table t1;
262
263 #
264 # Test of active transactions
265 #
266
267 create table t1 (a integer) engine=innodb;
268 start transaction;
269 rename table t1 to t2;
270 create table t1 (b integer) engine=innodb;
271 insert into t1 values (1);
272 rollback;
273 drop table t1;
274 rename table t2 to t1;
275 drop table t1;
276 set autocommit=1;
277
278 #
279 # The following simple tests failed at some point
280 #
281
282 CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
283 INSERT INTO t1 VALUES (1, 'Jochen');
284 select * from t1;
285 drop table t1;
286
287 CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
288 set autocommit=0;
289 INSERT INTO t1 SET _userid='[email protected]';
290 COMMIT;
291 SELECT * FROM t1;
292 SELECT _userid FROM t1 WHERE _userid='[email protected]';
293 drop table t1;
294 set autocommit=1;
295
296 #
297 # Test when reading on part of unique key
298 #
299 CREATE TABLE t1 (
300 user_id int(10) DEFAULT '0' NOT NULL,
301 name varchar(100),
302 phone varchar(100),
303 ref_email varchar(100) DEFAULT '' NOT NULL,
304 detail varchar(200),
305 PRIMARY KEY (user_id,ref_email)
306 )engine=innodb;
307
308 INSERT INTO t1 VALUES (10292,'sanjeev','29153373','[email protected]','xxx'),(10292,'shirish','2333604','[email protected]','ddsds'),(10292,'sonali','323232','[email protected]','filmstar');
309 select * from t1 where user_id=10292;
310 INSERT INTO t1 VALUES (10291,'sanjeev','29153373','[email protected]','xxx'),(10293,'shirish','2333604','[email protected]','ddsds');
311 select * from t1 where user_id=10292;
312 select * from t1 where user_id>=10292;
313 select * from t1 where user_id>10292;
314 select * from t1 where user_id<10292;
315 drop table t1;
316
317 #
318 # Test that keys are created in right order
319 #
320
321 CREATE TABLE t1 (a int not null, b int not null,c int not null,
322 key(a),primary key(a,b), unique(c),key(a),unique(b));
323 --replace_column 7 #
324 show index from t1;
325 drop table t1;
326
327 #
328 # Test of ALTER TABLE and innodb tables
329 #
330
331 create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
332 alter table t1 engine=innodb;
333 insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
334 select * from t1;
335 update t1 set col2='7' where col1='4';
336 select * from t1;
337 alter table t1 add co3 int not null;
338 select * from t1;
339 update t1 set col2='9' where col1='2';
340 select * from t1;
341 drop table t1;
342
343 #
344 # INSERT INTO innodb tables
345 #
346
347 create table t1 (a int not null , b int, primary key (a)) engine = innodb;
348 create table t2 (a int not null , b int, primary key (a)) engine = myisam;
349 insert into t1 VALUES (1,3) , (2,3), (3,3);
350 select * from t1;
351 insert into t2 select * from t1;
352 select * from t2;
353 delete from t1 where b = 3;
354 select * from t1;
355 insert into t1 select * from t2;
356 select * from t1;
357 select * from t2;
358 drop table t1,t2;
359
360 #
361 # ORDER BY on not primary key
362 #
363
364 CREATE TABLE t1 (
365 user_name varchar(12),
366 password text,
367 subscribed char(1),
368 user_id int(11) DEFAULT '0' NOT NULL,
369 quota bigint(20),
370 weight double,
371 access_date date,
372 access_time time,
373 approved datetime,
374 dummy_primary_key int(11) NOT NULL auto_increment,
375 PRIMARY KEY (dummy_primary_key)
376 ) ENGINE=innodb;
377 INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
378 INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
379 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);
380 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);
381 INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
382 select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
383 drop table t1;
384
385 #
386 # Testing of tables without primary keys
387 #
388
389 CREATE TABLE t1 (
390 id int(11) NOT NULL auto_increment,
391 parent_id int(11) DEFAULT '0' NOT NULL,
392 level tinyint(4) DEFAULT '0' NOT NULL,
393 KEY (id),
394 KEY parent_id (parent_id),
395 KEY level (level)
396 ) engine=innodb;
397 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);
398 INSERT INTO t1 values (179,5,2);
399 update t1 set parent_id=parent_id+100;
400 select * from t1 where parent_id=102;
401 update t1 set id=id+1000;
402 update t1 set id=1024 where id=1009;
403 select * from t1;
404 update ignore t1 set id=id+1; # This will change all rows
405 select * from t1;
406 update ignore t1 set id=1023 where id=1010;
407 select * from t1 where parent_id=102;
408 --replace_column 9 #
409 explain select level from t1 where level=1;
410 select level,id from t1 where level=1;
411 select level,id,parent_id from t1 where level=1;
412 select level,id from t1 where level=1 order by id;
413 delete from t1 where level=1;
414 select * from t1;
415 drop table t1;
416
417 #
418 # Test of index only reads
419 #
420 CREATE TABLE t1 (
421 sca_code char(6) NOT NULL,
422 cat_code char(6) NOT NULL,
423 sca_desc varchar(50),
424 lan_code char(2) NOT NULL,
425 sca_pic varchar(100),
426 sca_sdesc varchar(50),
427 sca_sch_desc varchar(16),
428 PRIMARY KEY (sca_code, cat_code, lan_code),
429 INDEX sca_pic (sca_pic)
430 ) engine = innodb ;
431
432 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');
433 select count(*) from t1 where sca_code = 'PD';
434 select count(*) from t1 where sca_code <= 'PD';
435 select count(*) from t1 where sca_pic is null;
436 alter table t1 drop index sca_pic, 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';
439
440 alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
441 select count(*) from t1 where sca_code='PD' and sca_pic is null;
442 select count(*) from t1 where sca_pic >= 'n';
443 select sca_pic from t1 where sca_pic is null;
444 update t1 set sca_pic="test" where sca_pic is null;
445 delete from t1 where sca_code='pd';
446 drop table t1;
447
448 #
449 # Test of opening table twice and timestamps
450 #
451 set @a:=now();
452 CREATE TABLE t1 (a int not null, b timestamp not null default
453 current_timestamp on update current_timestamp, primary key (a)) engine=innodb;
454 insert into t1 (a) values(1),(2),(3);
455 select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
456 select a from t1 natural join t1 as t2 where b >= @a order by a;
457 update t1 set a=5 where a=1;
458 select a from t1;
459 drop table t1;
460
461 #
462 # Test with variable length primary key
463 #
464 create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
465 insert into t1 values("hello",1),("world",2);
466 select * from t1 order by b desc;
467 optimize table t1;
468 --replace_column 7 #
469 show keys from t1;
470 drop table t1;
471
472 #
473 # Test of create index with NULL columns
474 #
475 create table t1 (i int, j int ) ENGINE=innodb;
476 insert into t1 values (1,2);
477 select * from t1 where i=1 and j=2;
478 create index ax1 on t1 (i,j);
479 select * from t1 where i=1 and j=2;
480 drop table t1;
481
482 #
483 # Test min-max optimization
484 #
485
486 CREATE TABLE t1 (
487 a int3 unsigned NOT NULL,
488 b int1 unsigned NOT NULL,
489 UNIQUE (a, b)
490 ) ENGINE = innodb;
491
492 INSERT INTO t1 VALUES (1, 1);
493 SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
494 drop table t1;
495
496 #
497 # Test INSERT DELAYED
498 #
499
500 CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb;
501 # Can't test this in 3.23
502 # INSERT DELAYED INTO t1 VALUES (1);
503 INSERT INTO t1 VALUES (1);
504 SELECT * FROM t1;
505 DROP TABLE t1;
506
507
508 #
509 # Crash when using many tables (Test case by Jeremy D Zawodny)
510 #
511
512 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;
513 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);
514 --replace_column 9 #
515 --replace_result "Using where" "Using index condition"
516 explain select * from t1 where a > 0 and a < 50;
517 drop table t1;
518
519 #
520 # Test lock tables
521 #
522
523 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;
524 insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
525 LOCK TABLES t1 WRITE;
526 --error ER_DUP_ENTRY
527 insert into t1 values (99,1,2,'D'),(1,1,2,'D');
528 select id from t1;
529 select id from t1;
530 UNLOCK TABLES;
531 DROP TABLE t1;
532
533 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;
534 insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
535 LOCK TABLES t1 WRITE;
536 begin;
537 --error ER_DUP_ENTRY
538 insert into t1 values (99,1,2,'D'),(1,1,2,'D');
539 select id from t1;
540 insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
541 commit;
542 select id,id3 from t1;
543 UNLOCK TABLES;
544 DROP TABLE t1;
545
546 #
547 # Test prefix key
548 #
549 create table t1 (a char(20), unique (a(5))) engine=innodb;
550 drop table t1;
551 create table t1 (a char(20), index (a(5))) engine=innodb;
552 show create table t1;
553 drop table t1;
554
555 #
556 # Test using temporary table and auto_increment
557 #
558
559 create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
560 insert into t1 values (NULL),(NULL),(NULL);
561 delete from t1 where a=3;
562 insert into t1 values (NULL);
563 select * from t1;
564 alter table t1 add b int;
565 select * from t1;
566 drop table t1;
567
568 #Slashdot bug
569 create table t1
570 (
571 id int auto_increment primary key,
572 name varchar(32) not null,
573 value text not null,
574 uid int not null,
575 unique key(name,uid)
576 ) engine=innodb;
577 insert into t1 values (1,'one','one value',101),
578 (2,'two','two value',102),(3,'three','three value',103);
579 set insert_id=5;
580 replace into t1 (value,name,uid) values ('other value','two',102);
581 delete from t1 where uid=102;
582 set insert_id=5;
583 replace into t1 (value,name,uid) values ('other value','two',102);
584 set insert_id=6;
585 replace into t1 (value,name,uid) values ('other value','two',102);
586 select * from t1;
587 drop table t1;
588
589 #
590 # Test DROP DATABASE
591 #
592
593 create database mysqltest;
594 create table mysqltest.t1 (a int not null) engine= innodb;
595 insert into mysqltest.t1 values(1);
596 create table mysqltest.t2 (a int not null) engine= myisam;
597 insert into mysqltest.t2 values(1);
598 create table mysqltest.t3 (a int not null) engine= heap;
599 insert into mysqltest.t3 values(1);
600 commit;
601 drop database mysqltest;
602 # Don't check error message
603 --error 1049
604 show tables from mysqltest;
605
606 #
607 # Test truncate table with and without auto_commit
608 #
609
610 set autocommit=0;
611 create table t1 (a int not null) engine= innodb;
612 insert into t1 values(1),(2);
613 truncate table t1;
614 commit;
615 truncate table t1;
616 truncate table t1;
617 select * from t1;
618 insert into t1 values(1),(2);
619 delete from t1;
620 select * from t1;
621 commit;
622 drop table t1;
623 set autocommit=1;
624
625 create table t1 (a int not null) engine= innodb;
626 insert into t1 values(1),(2);
627 truncate table t1;
628 insert into t1 values(1),(2);
629 select * from t1;
630 truncate table t1;
631 insert into t1 values(1),(2);
632 delete from t1;
633 select * from t1;
634 drop table t1;
635
636 #
637 # Test of how ORDER BY works when doing it on the whole table
638 #
639
640 create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
641 insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
642 --replace_column 9 #
643 explain select * from t1 order by a;
644 --replace_column 9 #
645 explain select * from t1 order by b;
646 --replace_column 9 #
647 explain select * from t1 order by c;
648 --replace_column 9 #
649 explain select a from t1 order by a;
650 --replace_column 9 #
651 explain select b from t1 order by b;
652 --replace_column 9 #
653 explain select a,b from t1 order by b;
654 --replace_column 9 #
655 explain select a,b from t1;
656 --replace_column 9 #
657 explain select a,b,c from t1;
658 drop table t1;
659
660 #
661 # Check describe
662 #
663
664 create table t1 (t int not null default 1, key (t)) engine=innodb;
665 desc t1;
666 drop table t1;
667
668 #
669 # Test of multi-table-delete
670 #
671
672 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
673
674 CREATE TABLE t1 (
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'
686 ) ENGINE=InnoDB;
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);
693 CREATE TABLE t2 (
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'
705 ) ENGINE=InnoDB;
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);
710 select * from t1;
711 select * from t2;
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);
713 select * from t1;
714 select * from t2;
715 select * from t2;
716 drop table t1,t2;
717 SET sql_mode = default;
718
719 #
720 # A simple test with some isolation levels
721 # TODO: Make this into a test using replication to really test how
722 # this works.
723 #
724
725 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;
726
727 BEGIN;
728 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
729 SELECT @@transaction_isolation, @@global.transaction_isolation;
730 insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
731 select id, code, name from t1 order by id;
732 COMMIT;
733
734 BEGIN;
735 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
736 insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
737 select id, code, name from t1 order by id;
738 COMMIT;
739
740 SET binlog_format='MIXED';
741 BEGIN;
742 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
743 insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
744 select id, code, name from t1 order by id;
745 COMMIT;
746 DROP TABLE t1;
747
748 #
749 # Test of multi-table-update
750 #
751 create table t1 (n int(10), d int(10)) engine=innodb;
752 create table t2 (n int(10), d int(10)) engine=innodb;
753 insert into t1 values(1,1),(1,2);
754 insert into t2 values(1,10),(2,20);
755 UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
756 select * from t1;
757 select * from t2;
758 drop table t1,t2;
759
760 #
761 # Bug #29136 erred multi-delete on trans table does not rollback
762 #
763
764 # prepare
765 --disable_warnings
766 drop table if exists t1, t2;
767 --enable_warnings
768 CREATE TABLE t1 (a int, PRIMARY KEY (a));
769 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
770 create trigger trg_del_t2 after delete on t2 for each row
771 insert into t1 values (1);
772 insert into t1 values (1);
773 insert into t2 values (1),(2);
774
775
776 # exec cases A, B - see multi_update.test
777
778 # A. send_error() w/o send_eof() branch
779
780 --error ER_DUP_ENTRY
781 delete t2 from t2;
782
783 # check
784
785 select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
786
787 # cleanup bug#29136
788
789 drop table t1, t2;
790
791
792 #
793 # Bug #29136 erred multi-delete on trans table does not rollback
794 #
795
796 # prepare
797 --disable_warnings
798 drop table if exists t1, t2;
799 --enable_warnings
800 CREATE TABLE t1 (a int, PRIMARY KEY (a));
801 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
802 create trigger trg_del_t2 after delete on t2 for each row
803 insert into t1 values (1);
804 insert into t1 values (1);
805 insert into t2 values (1),(2);
806
807
808 # exec cases A, B - see multi_update.test
809
810 # A. send_error() w/o send_eof() branch
811
812 --error ER_DUP_ENTRY
813 delete t2 from t2;
814
815 # check
816
817 select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
818
819 # cleanup bug#29136
820
821 drop table t1, t2;
822
823
824 #
825 # Testing of IFNULL
826 #
827 create table t1 (a int, b int) engine=innodb;
828 insert into t1 values(20,null);
829 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
830 t2.b=t3.a;
831 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
832 t2.b=t3.a order by 1;
833 insert into t1 values(10,null);
834 select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
835 t2.b=t3.a order by 1;
836 drop table t1;
837
838 #
839 # Test of read_through not existing const_table
840 #
841
842 create table t1 (a varchar(10) not null) engine=myisam;
843 create table t2 (b varchar(10) not null unique) engine=innodb;
844 select t1.a from t1,t2 where t1.a=t2.b;
845 drop table t1,t2;
846 create table t1 (a int not null, b int, primary key (a)) engine = innodb;
847 create table t2 (a int not null, b int, primary key (a)) engine = innodb;
848 insert into t1 values (10, 20);
849 insert into t2 values (10, 20);
850 update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
851 drop table t1,t2;
852
853 #
854 # Test of multi-table-delete with foreign key constraints
855 #
856
857 CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
858 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;
859 insert into t1 set id=1;
860 insert into t2 set id=1, t1_id=1;
861 delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
862 select * from t1;
863 select * from t2;
864 drop table t2,t1;
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) ) ENGINE=INNODB;
867 INSERT INTO t1 VALUES(1);
868 INSERT INTO t2 VALUES(1, 1);
869 SELECT * from t1;
870 UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
871 SELECT * from t1;
872 UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
873 SELECT * from t1;
874 DROP TABLE t1,t2;
875
876 #
877 # Test of range_optimizer
878 #
879
880 set autocommit=0;
881
882 CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
883
884 CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
885
886 CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
887
888 INSERT INTO t3 VALUES("my-test-1", "my-test-2");
889 COMMIT;
890
891 INSERT INTO t1 VALUES("this-key", "will disappear");
892 INSERT INTO t2 VALUES("this-key", "will also disappear");
893 DELETE FROM t3 WHERE id1="my-test-1";
894
895 SELECT * FROM t1;
896 SELECT * FROM t2;
897 SELECT * FROM t3;
898 ROLLBACK;
899
900 SELECT * FROM t1;
901 SELECT * FROM t2;
902 SELECT * FROM t3;
903 SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
904 COMMIT;
905 set autocommit=1;
906 DROP TABLE t1,t2,t3;
907
908 #
909 # Check update with conflicting key
910 #
911
912 CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
913 INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
914 # We need the a < 1000 test here to quard against the halloween problems
915 UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
916 SELECT * from t1;
917 drop table t1;
918
919 CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
920 CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
921 SET AUTOCOMMIT=0;
922 INSERT INTO t1 ( B_ID ) VALUES ( 1 );
923 INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
924 ROLLBACK;
925 SELECT * FROM t1;
926 drop table t1,t2;
927 create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;
928 insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);
929 select distinct parent,child from t1 order by parent;
930 drop table t1;
931
932 #
933 # Test that MySQL priorities clustered indexes
934 #
935 create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
936 create table t2 (a int not null auto_increment primary key, b int);
937 insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
938 insert into t2 (a) select b from t1;
939 insert into t1 (b) select b from t2;
940 insert into t2 (a) select b from t1;
941 insert into t1 (a) select b from t2;
942 insert into t2 (a) select b from t1;
943 insert into t1 (a) select b from t2;
944 insert into t2 (a) select b from t1;
945 insert into t1 (a) select b from t2;
946 insert into t2 (a) select b from t1;
947 insert into t1 (a) select b from t2;
948 select count(*) from t1;
949 --replace_column 9 # 10 #
950 explain select * from t1 where c between 1 and 2500;
951 update t1 set c=a;
952 --replace_column 9 # 10 #
953 explain select * from t1 where c between 1 and 2500;
954 drop table t1,t2;
955
956 #
957 # Test of UPDATE ... ORDER BY
958 #
959
960 create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
961
962 insert into t1 (id) values (null),(null),(null),(null),(null);
963 update t1 set fk=69 where fk is null order by id limit 1;
964 SELECT * from t1;
965 drop table t1;
966
967 create table t1 (a int not null, b int not null, key (a));
968 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 SET @tmp=0;
970 update t1 set b=(@tmp:=@tmp+1) order by a;
971 update t1 set b=99 where a=1 order by b asc limit 1;
972 update t1 set b=100 where a=1 order by b desc limit 2;
973 update t1 set a=a+10+b where a=1 order by b;
974 select * from t1 order by a,b;
975 drop table t1;
976
977 #
978 # Test of multi-table-updates (bug #1980).
979 #
980
981 create table t1 ( c char(8) not null ) engine=innodb;
982 insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
983 insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
984
985 alter table t1 add b char(8) not null;
986 alter table t1 add a char(8) not null;
987 alter table t1 add primary key (a,b,c);
988 update t1 set a=c, b=c;
989
990 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;
991 insert into t2 select * from t1;
992
993 delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
994 drop table t1,t2;
995
996 #
997 # test autoincrement with TRUNCATE
998 #
999
1000 SET AUTOCOMMIT=1;
1001 create table t1 (a integer auto_increment primary key) engine=innodb;
1002 insert into t1 (a) values (NULL),(NULL);
1003 truncate table t1;
1004 insert into t1 (a) values (NULL),(NULL);
1005 SELECT * from t1;
1006 drop table t1;
1007
1008 #
1009 # Test dictionary handling with spaceand quoting
1010 #
1011
1012 CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
1013 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;
1014 #show create table t2;
1015 drop table t2,t1;
1016
1017 #
1018 # Test of multi updated and foreign keys
1019 #
1020
1021 create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = innodb;
1022 insert into `t1`values ( 1 ) ;
1023 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;
1024 insert into `t2`values ( 1 ) ;
1025 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;
1026 insert into `t3`values ( 1 ) ;
1027 --error ER_ROW_IS_REFERENCED_2
1028 delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1029 --error ER_ROW_IS_REFERENCED_2
1030 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 --error ER_BAD_FIELD_ERROR
1032 update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
1033 drop table t3,t2,t1;
1034
1035 # test ON UPDATE CASCADE
1036 CREATE TABLE t1 (
1037 c1 VARCHAR(8), c2 VARCHAR(8),
1038 PRIMARY KEY (c1, c2)
1039 ) ENGINE=InnoDB;
1040
1041 CREATE TABLE t2 (
1042 c0 INT PRIMARY KEY,
1043 c1 VARCHAR(8) UNIQUE,
1044 FOREIGN KEY (c1) REFERENCES t1 (c1) ON UPDATE CASCADE
1045 ) ENGINE=InnoDB;
1046
1047 INSERT INTO t1 VALUES ('old', 'somevalu'), ('other', 'anyvalue');
1048 INSERT INTO t2 VALUES (10, 'old'), (20, 'other');
1049 --error ER_FOREIGN_DUPLICATE_KEY_WITH_CHILD_INFO
1050 UPDATE t1 SET c1 = 'other' WHERE c1 = 'old';
1051 DROP TABLE t2,t1;
1052
1053 #
1054 # test for FK cascade depth limit
1055 #
1056 call mtr.add_suppression("Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15\\. Please drop excessive foreign constraints and try again");
1057
1058 create table t1(
1059 id int primary key,
1060 pid int,
1061 index(pid),
1062 foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1063 insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1064 (8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1065 --error ER_GET_ERRMSG,ER_ROW_IS_REFERENCED_2
1066 delete from t1 where id=0;
1067 delete from t1 where id=15;
1068 delete from t1 where id=0;
1069
1070 drop table t1;
1071
1072 #
1073 # Test timestamps
1074 #
1075
1076 CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1077 CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1078 (stamp))ENGINE=InnoDB;
1079 insert into t1 values (1),(2),(3);
1080 # Note that timestamp 3 is wrong
1081 insert ignore into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1082 SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1083 '20020204120000' GROUP BY col1;
1084 drop table t1,t2;
1085
1086 #
1087 # Test by Francois MASUREL
1088 #
1089
1090 CREATE TABLE t1 (
1091 `id` int(10) unsigned NOT NULL auto_increment,
1092 `id_object` int(10) unsigned default '0',
1093 `id_version` int(10) unsigned NOT NULL default '1',
1094 `label` varchar(100) NOT NULL default '',
1095 `description` text,
1096 PRIMARY KEY (`id`),
1097 KEY `id_object` (`id_object`),
1098 KEY `id_version` (`id_version`)
1099 ) ENGINE=InnoDB;
1100
1101 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);
1102
1103 CREATE TABLE t2 (
1104 `id` int(10) unsigned NOT NULL auto_increment,
1105 `id_version` int(10) unsigned NOT NULL default '1',
1106 PRIMARY KEY (`id`),
1107 KEY `id_version` (`id_version`)
1108 ) ENGINE=InnoDB;
1109
1110 INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1111 # We have to analyze the tables to make the row count stable
1112 ANALYZE table t1,t2;
1113
1114 explain SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1115 (SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl
1116 ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1117 --sorted_result
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);
1121 drop table t1,t2;
1122
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;
1132 #show table status;
1133 drop table t1,t2,t3;
1134
1135 #
1136 # Test problem with referring to different fields in same table in UNION
1137 # (Bug #2552)
1138 #
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;
1142 drop table t1;
1143
1144 #
1145 # Bug2160
1146 #
1147 create table t1 (a int) engine=innodb;
1148 create table t2 like t1;
1149 drop table t1,t2;
1150
1151 #
1152 # Test of automaticly created foreign keys
1153 #
1154
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;
1167 drop table t2;
1168
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;
1173 drop table t2;
1174
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;
1178 drop table t2;
1179
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;
1182 drop table t2;
1183
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;
1186 drop table t2;
1187
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;
1190 drop table t2;
1191
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;
1196 drop table t2;
1197
1198 # Test error handling
1199
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;
1204
1205 # bug#3749
1206
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;
1209 drop 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;
1212 drop table t2, t1;
1213
1214
1215 #
1216 # Bug #6126: Duplicate columns in keys gives misleading error message
1217 #
1218 --error 1060
1219 create table t1 (c char(10), index (c,c)) engine=innodb;
1220 --error 1060
1221 create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1222 --error 1060
1223 create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1224 --error 1060
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;
1227 --error 1060
1228 alter table t1 add key (c1,c1);
1229 --error 1060
1230 alter table t1 add key (c2,c1,c1);
1231 --error 1060
1232 alter table t1 add key (c1,c2,c1);
1233 --error 1060
1234 alter table t1 add key (c1,c1,c2);
1235 drop table t1;
1236
1237 #
1238 # Bug #4082: integer truncation
1239 #
1240
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;
1244 drop table t1;
1245
1246 #
1247 # check null values #1
1248 #
1249
1250 --disable_warnings
1251 CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1252 INSERT INTO t1 VALUES (1),(2),(3);
1253 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),
1254 CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1255 --enable_warnings
1256 INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1257 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;
1258 DROP TABLE t2;
1259 DROP TABLE t1;
1260
1261 #
1262 # Bug#11816 - Truncate table doesn't work with temporary innodb tables
1263 # This is not an innodb bug, but we test it using innodb.
1264 #
1265 create temporary table t1 (a int) engine=innodb;
1266 insert into t1 values (4711);
1267 truncate t1;
1268 insert into t1 values (42);
1269 select * from t1;
1270 drop table t1;
1271 # Show that it works with permanent tables too.
1272 create table t1 (a int) engine=innodb;
1273 insert into t1 values (4711);
1274 truncate t1;
1275 insert into t1 values (42);
1276 select * from t1;
1277 drop table t1;
1278
1279 #
1280 # Bug #13025 Server crash during filesort
1281 #
1282
1283 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;
1284 insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1285 -- disable_result_log
1286 analyze table t1;
1287 -- enable_result_log
1288 select * from t1 order by a,b,c,d;
1289 explain select * from t1 order by a,b,c,d;
1290 drop table t1;
1291
1292 #
1293 # BUG#11039,#13218 Wrong key length in min()
1294 #
1295
1296 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1297 insert into t1 values ('8', '6'), ('4', '7');
1298 select min(a) from t1;
1299 select min(b) from t1 where a='8';
1300 drop table t1;
1301
1302 # End of 4.1 tests
1303
1304 #
1305 # range optimizer problem
1306 #
1307
1308 create table t1 (x bigint unsigned not null primary key) engine=innodb;
1309 insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1310 select * from t1;
1311 select count(*) from t1 where x>0;
1312 select count(*) from t1 where x=0;
1313 select count(*) from t1 where x<0;
1314 select count(*) from t1 where x < -16;
1315 select count(*) from t1 where x = -16;
1316 explain select count(*) from t1 where x > -16;
1317 select count(*) from t1 where x > -16;
1318 select * from t1 where x > -16;
1319 select count(*) from t1 where x = 18446744073709551601;
1320 drop table t1;
1321
1322
1323 # Test for testable InnoDB status variables. This test
1324 # uses previous ones(pages_created, rows_deleted, ...).
1325 SELECT IF(variable_value BETWEEN 488 AND 512, 'OK', variable_value) FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
1326 SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size';
1327
1328 # Test for row locks InnoDB status variables.
1329 SELECT variable_value - @innodb_row_lock_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits';
1330 SELECT variable_value - @innodb_row_lock_current_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_current_waits';
1331 SELECT variable_value - @innodb_row_lock_time_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time';
1332 SELECT variable_value - @innodb_row_lock_time_max_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_max';
1333 SELECT variable_value - @innodb_row_lock_time_avg_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_time_avg';
1334
1335
1336 #
1337 # Test varchar
1338 #
1339
1340 let $default=`select @@default_storage_engine`;
1341 set default_storage_engine=INNODB;
1342 source include/varchar.inc;
1343
1344 #
1345 # Some errors/warnings on create
1346 #
1347 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
1348 # Embedded server doesn't chdir to data directory
1349 --replace_result $MYSQLTEST_VARDIR . master-data/ ''
1350 create table t1 (v varchar(65530), key(v));
1351 drop table t1;
1352 create table t1 (v varchar(65536));
1353 show create table t1;
1354 drop table t1;
1355 create table t1 (v varchar(65530) character set utf8);
1356 show create table t1;
1357 drop table t1;
1358 SET sql_mode = default;
1359 eval set default_storage_engine=$default;
1360
1361 # InnoDB specific varchar tests
1362 create table t1 (v varchar(16384)) engine=innodb;
1363 drop table t1;
1364
1365 #
1366 # BUG#11039 Wrong key length in min()
1367 #
1368
1369 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1370 insert into t1 values ('8', '6'), ('4', '7');
1371 select min(a) from t1;
1372 select min(b) from t1 where a='8';
1373 drop table t1;
1374
1375 #
1376 # Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error
1377 #
1378
1379 CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
1380 insert into t1 (b) values (1);
1381 replace into t1 (b) values (2), (1), (3);
1382 select * from t1;
1383 truncate table t1;
1384 insert into t1 (b) values (1);
1385 replace into t1 (b) values (2);
1386 replace into t1 (b) values (1);
1387 replace into t1 (b) values (3);
1388 select * from t1;
1389 drop table t1;
1390
1391 create table t1 (rowid int not null auto_increment, val int not null,primary
1392 key (rowid), unique(val)) engine=innodb;
1393 replace into t1 (val) values ('1'),('2');
1394 replace into t1 (val) values ('1'),('2');
1395 --error ER_DUP_ENTRY
1396 insert into t1 (val) values ('1'),('2');
1397 select * from t1;
1398 drop table t1;
1399
1400 #
1401 # Test that update does change internal auto-increment value
1402 #
1403
1404 create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
1405 insert into t1 (val) values (1);
1406 update t1 set a=2 where a=1;
1407 # This should insert 3, since the counter has been updated to 2 already
1408 insert into t1 (val) values (3);
1409 select * from t1;
1410 # We should get the following error because InnoDB does update the counter
1411 --error ER_DUP_ENTRY
1412 insert into t1 values (2, 2);
1413 select * from t1;
1414 drop table t1;
1415 #
1416 # Bug #10465
1417 #
1418
1419 --disable_warnings
1420 CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
1421 --enable_warnings
1422 INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
1423 SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
1424 SELECT GRADE FROM t1 WHERE GRADE= 151;
1425 DROP TABLE t1;
1426
1427 #
1428 # Bug #12340 multitable delete deletes only one record
1429 #
1430 create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
1431 create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
1432 insert into t2 values ('aa','cc');
1433 insert into t1 values ('aa','bb'),('aa','cc');
1434 delete t1 from t1,t2 where f1=f3 and f4='cc';
1435 select * from t1;
1436 drop table t1,t2;
1437
1438 #
1439 # Test that the slow TRUNCATE implementation resets autoincrement columns
1440 # (bug #11946)
1441 #
1442
1443 CREATE TABLE t1 (
1444 id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
1445 ) ENGINE=InnoDB;
1446
1447 CREATE TABLE t2 (
1448 id INTEGER NOT NULL,
1449 FOREIGN KEY (id) REFERENCES t1 (id)
1450 ) ENGINE=InnoDB;
1451
1452 INSERT INTO t1 (id) VALUES (NULL);
1453 SELECT * FROM t1;
1454 --error ER_TRUNCATE_ILLEGAL_FK
1455 TRUNCATE t1;
1456 INSERT INTO t1 (id) VALUES (NULL);
1457 SELECT * FROM t1;
1458
1459 # continued from above; test that doing a TRUNCATE resets AUTO_INCREMENT
1460 DELETE FROM t1;
1461 --error ER_TRUNCATE_ILLEGAL_FK
1462 TRUNCATE t1;
1463 INSERT INTO t1 (id) VALUES (NULL);
1464 SELECT * FROM t1;
1465 DROP TABLE t2;
1466 TRUNCATE t1;
1467 INSERT INTO t1 (id) VALUES (NULL);
1468 SELECT * FROM t1;
1469 DROP TABLE t1;
1470
1471 # Test that foreign keys in temporary tables are not accepted (bug #12084)
1472 CREATE TABLE t1
1473 (
1474 id INT PRIMARY KEY
1475 ) ENGINE=InnoDB;
1476
1477 --error ER_CANT_CREATE_TABLE
1478 CREATE TEMPORARY TABLE t2
1479 (
1480 id INT NOT NULL PRIMARY KEY,
1481 b INT,
1482 FOREIGN KEY (b) REFERENCES test.t1(id)
1483 ) ENGINE=InnoDB;
1484 DROP TABLE t1;
1485
1486 #
1487 # Test that index column max sizes are honored (bug #13315)
1488 #
1489
1490 # prefix index
1491 create table t1 (col1 varchar(2000), index (col1(767)))
1492 character set = latin1 engine = innodb;
1493
1494 # normal indexes
1495 create table t2 (col1 char(255), index (col1))
1496 character set = latin1 engine = innodb;
1497 create table t3 (col1 binary(255), index (col1))
1498 character set = latin1 engine = innodb;
1499 create table t4 (col1 varchar(767), index (col1))
1500 character set = latin1 engine = innodb;
1501 create table t5 (col1 varchar(767) primary key)
1502 character set = latin1 engine = innodb;
1503 create table t6 (col1 varbinary(767) primary key)
1504 character set = latin1 engine = innodb;
1505 create table t7 (col1 text, index(col1(767)))
1506 character set = latin1 engine = innodb;
1507 create table t8 (col1 blob, index(col1(767)))
1508 character set = latin1 engine = innodb;
1509
1510 # multi-column indexes are allowed to be longer
1511 create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
1512 character set = latin1 engine = innodb;
1513
1514 show create table t9;
1515
1516 drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
1517
1518 #
1519 # Test improved foreign key error messages (bug #3443)
1520 #
1521
1522 CREATE TABLE t1
1523 (
1524 id INT PRIMARY KEY
1525 ) ENGINE=InnoDB;
1526
1527 CREATE TABLE t2
1528 (
1529 v INT,
1530 CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
1531 ) ENGINE=InnoDB;
1532
1533 --error ER_NO_REFERENCED_ROW_2
1534 INSERT INTO t2 VALUES(2);
1535
1536 INSERT INTO t1 VALUES(1);
1537 INSERT INTO t2 VALUES(1);
1538
1539 --error ER_ROW_IS_REFERENCED_2
1540 DELETE FROM t1 WHERE id = 1;
1541
1542 --error ER_ROW_IS_REFERENCED_2
1543 DROP TABLE t1;
1544
1545 SET FOREIGN_KEY_CHECKS=0;
1546 DROP TABLE t1;
1547 SET FOREIGN_KEY_CHECKS=1;
1548
1549 --error ER_NO_REFERENCED_ROW_2
1550 INSERT INTO t2 VALUES(3);
1551
1552 DROP TABLE t2;
1553 #
1554 # Test that checksum table uses a consistent read Bug #12669
1555 #
1556 connect (a,localhost,root,,);
1557 connect (b,localhost,root,,);
1558 connection a;
1559 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1560 insert into t1 values (1),(2);
1561 set autocommit=0;
1562 checksum table t1;
1563 connection b;
1564 insert into t1 values(3);
1565 connection a;
1566 #
1567 # Here checksum should not see insert
1568 #
1569 checksum table t1;
1570 connection a;
1571 commit;
1572 checksum table t1;
1573 commit;
1574 drop table t1;
1575 #
1576 # autocommit = 1
1577 #
1578 connection a;
1579 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1580 insert into t1 values (1),(2);
1581 set autocommit=1;
1582 checksum table t1;
1583 connection b;
1584 set autocommit=1;
1585 insert into t1 values(3);
1586 connection a;
1587 #
1588 # Here checksum sees insert
1589 #
1590 checksum table t1;
1591 drop table t1;
1592
1593 connection default;
1594 disconnect a;
1595 disconnect b;
1596
1597 # tests for bugs #9802 and #13778
1598
1599 # test that FKs between invalid types are not accepted
1600
1601 set foreign_key_checks=0;
1602 create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
1603 create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
1604 set foreign_key_checks=1;
1605 --error ER_NO_REFERENCED_ROW_2
1606 insert into t2 values (1,1);
1607 set foreign_key_checks=0;
1608 drop table t1;
1609 set foreign_key_checks=1;
1610 --error ER_NO_REFERENCED_ROW_2
1611 insert into t2 values (1,1);
1612 # Embedded server doesn't chdir to data directory
1613 --replace_result $MYSQLTEST_VARDIR . master-data/ ''
1614 --error ER_CANT_CREATE_TABLE
1615 create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
1616 drop table t2;
1617 create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
1618 drop table t1;
1619
1620 # test that FKs between different charsets are not accepted in CREATE even
1621 # when f_k_c is 0
1622
1623 set foreign_key_checks=0;
1624 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1625 # Embedded server doesn't chdir to data directory
1626 --replace_result $MYSQLTEST_VARDIR . master-data/ ''
1627 --error ER_CANT_CREATE_TABLE
1628 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
1629 set foreign_key_checks=1;
1630 drop table t1;
1631
1632 # test that invalid datatype conversions with ALTER are not allowed
1633
1634 set foreign_key_checks=0;
1635 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
1636 create table t1(a varchar(10) primary key) engine = innodb;
1637 --error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
1638 alter table t1 modify column a int;
1639 set foreign_key_checks=1;
1640 drop table t2,t1;
1641
1642 # test that charset conversions with ALTER are allowed when f_k_c is 0
1643
1644 set foreign_key_checks=0;
1645 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1646 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1647 --error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
1648 alter table t1 convert to character set utf8;
1649 set foreign_key_checks=1;
1650 drop table t2,t1;
1651
1652 # test that RENAME does not allow invalid charsets when f_k_c is 0
1653
1654 call mtr.add_suppression("\\[Warning\\] InnoDB: In ALTER TABLE `test`.`t1` has or is referenced in foreign key constraints which are not compatible with the new table definition.");
1655 set foreign_key_checks=0;
1656 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1657 create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
1658 # Embedded server doesn't chdir to data directory
1659 --replace_result $MYSQLD_DATADIR ./ master-data/ ''
1660 -- error 1025
1661 rename table t3 to t1;
1662 set foreign_key_checks=1;
1663 drop table t2,t3;
1664
1665 # test that foreign key errors are reported correctly (Bug #15550)
1666
1667 create table t1(a int primary key) row_format=redundant engine=innodb;
1668 create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
1669 create table t3(a int primary key) row_format=compact engine=innodb;
1670 create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
1671
1672 insert into t1 values(1);
1673 insert into t3 values(1);
1674 -- error 1452
1675 insert into t2 values(2);
1676 -- error 1452
1677 insert into t4 values(2);
1678 insert into t2 values(1);
1679 insert into t4 values(1);
1680 -- error ER_ROW_IS_REFERENCED_2
1681 update t1 set a=2;
1682 -- error 1452
1683 update t2 set a=2;
1684 -- error ER_ROW_IS_REFERENCED_2
1685 update t3 set a=2;
1686 -- error 1452
1687 update t4 set a=2;
1688 -- error ER_TRUNCATE_ILLEGAL_FK
1689 truncate t1;
1690 -- error ER_TRUNCATE_ILLEGAL_FK
1691 truncate t3;
1692 truncate t2;
1693 truncate t4;
1694 -- error ER_TRUNCATE_ILLEGAL_FK
1695 truncate t1;
1696 -- error ER_TRUNCATE_ILLEGAL_FK
1697 truncate t3;
1698
1699 drop table t4,t3,t2,t1;
1700
1701
1702 #
1703 # Test that we can create a large (>1K) key
1704 #
1705 create table t1 (a varchar(255) character set utf8,
1706 b varchar(255) character set utf8,
1707 c varchar(255) character set utf8,
1708 d varchar(255) character set utf8,
1709 key (a,b,c,d)) engine=innodb;
1710 drop table t1;
1711 --error ER_TOO_LONG_KEY
1712 create table t1 (a varchar(255) character set utf8,
1713 b varchar(255) character set utf8,
1714 c varchar(255) character set utf8,
1715 d varchar(255) character set utf8,
1716 e varchar(255) character set utf8,
1717 key (a,b,c,d,e)) engine=innodb;
1718
1719
1720 # test the padding of BINARY types and collations (Bug #14189)
1721 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
1722 create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
1723 create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
1724 create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
1725 create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
1726
1727 insert into t1 values (0x41),(0x4120),(0x4100);
1728 -- error ER_DUP_ENTRY
1729 insert into t2 values (0x41),(0x4120),(0x4100);
1730 insert into t2 values (0x41),(0x4120);
1731 -- error ER_DUP_ENTRY
1732 insert into t3 values (0x41),(0x4120),(0x4100);
1733 insert into t3 values (0x41),(0x4100);
1734 -- error ER_DUP_ENTRY
1735 insert into t4 values (0x41),(0x4120),(0x4100);
1736 insert into t4 values (0x41),(0x4100);
1737 select hex(s1) from t1;
1738 select hex(s1) from t2;
1739 select hex(s1) from t3;
1740 select hex(s1) from t4;
1741 drop table t1,t2,t3,t4;
1742
1743 create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
1744 create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1745
1746 insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
1747 -- error 1452
1748 insert into t2 values(0x42);
1749 insert into t2 values(0x41);
1750 select hex(s1) from t2;
1751 update t1 set s1=0x123456 where a=2;
1752 select hex(s1) from t2;
1753 -- error ER_ROW_IS_REFERENCED_2
1754 update t1 set s1=0x12 where a=1;
1755 -- error ER_ROW_IS_REFERENCED_2
1756 update t1 set s1=0x12345678 where a=1;
1757 -- error ER_ROW_IS_REFERENCED_2
1758 update t1 set s1=0x123457 where a=1;
1759 update t1 set s1=0x1220 where a=1;
1760 select hex(s1) from t2;
1761 update t1 set s1=0x1200 where a=1;
1762 select hex(s1) from t2;
1763 update t1 set s1=0x4200 where a=1;
1764 select hex(s1) from t2;
1765 -- error ER_ROW_IS_REFERENCED_2
1766 delete from t1 where a=1;
1767 delete from t1 where a=2;
1768 update t2 set s1=0x4120;
1769 -- error ER_ROW_IS_REFERENCED_2
1770 delete from t1;
1771 delete from t1 where a!=3;
1772 select a,hex(s1) from t1;
1773 select hex(s1) from t2;
1774
1775 drop table t2,t1;
1776
1777 create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
1778 create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1779
1780 insert into t1 values(1,0x4100),(2,0x41);
1781 insert into t2 values(0x41);
1782 select hex(s1) from t2;
1783 update t1 set s1=0x1234 where a=1;
1784 select hex(s1) from t2;
1785 update t1 set s1=0x12 where a=2;
1786 select hex(s1) from t2;
1787 delete from t1 where a=1;
1788 -- error ER_ROW_IS_REFERENCED_2
1789 delete from t1 where a=2;
1790 select a,hex(s1) from t1;
1791 select hex(s1) from t2;
1792
1793 drop table t2,t1;
1794 # Ensure that <tablename>_ibfk_0 is not mistreated as a
1795 # generated foreign key identifier. (Bug #16387)
1796
1797 CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
1798 CREATE TABLE t2(a INT) ENGINE=InnoDB;
1799 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
1800 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
1801 ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
1802 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
1803 SHOW CREATE TABLE t2;
1804 DROP TABLE t2,t1;
1805 SET sql_mode = default;
1806 #
1807 # Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing
1808 #
1809 ## the following cannot be tested after the introduction of metadata locks
1810 ## because the create trigger command blocks and waits for connection b to
1811 ## commit
1812 ## begin disabled_mdl
1813 #connect (a,localhost,root,,);
1814 #connect (b,localhost,root,,);
1815 #connection a;
1816 #create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1817 #insert into t1(a) values (1),(2),(3);
1818 #commit;
1819 #connection b;
1820 #set autocommit = 0;
1821 #update t1 set b = 5 where a = 2;
1822 #connection a;
1823 #delimiter |;
1824 #create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
1825 #delimiter ;|
1826 #set autocommit = 0;
1827 #connection a;
1828 #insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
1829 #(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
1830 #(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
1831 #(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
1832 #(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
1833 #connection b;
1834 #commit;
1835 #connection a;
1836 #commit;
1837 #drop trigger t1t;
1838 #drop table t1;
1839 #disconnect a;
1840 #disconnect b;
1841 ##
1842 ## Another trigger test
1843 ##
1844 #connect (a,localhost,root,,);
1845 #connect (b,localhost,root,,);
1846 #connection a;
1847 #create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1848 #create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1849 #create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1850 #create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1851 #create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1852 #insert into t1(a) values (1),(2),(3);
1853 #insert into t2(a) values (1),(2),(3);
1854 #insert into t3(a) values (1),(2),(3);
1855 #insert into t4(a) values (1),(2),(3);
1856 #insert into t3(a) values (5),(7),(8);
1857 #insert into t4(a) values (5),(7),(8);
1858 #insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
1859 #
1860 #delimiter |;
1861 #create trigger t1t before insert on t1 for each row begin
1862 # INSERT INTO t2 SET a = NEW.a;
1863 #end |
1864 #
1865 #create trigger t2t before insert on t2 for each row begin
1866 # DELETE FROM t3 WHERE a = NEW.a;
1867 #end |
1868 #
1869 #create trigger t3t before delete on t3 for each row begin
1870 # UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
1871 #end |
1872 #
1873 #create trigger t4t before update on t4 for each row begin
1874 # UPDATE t5 SET b = b + 1 where a = NEW.a;
1875 #end |
1876 #delimiter ;|
1877 #commit;
1878 #set autocommit = 0;
1879 #update t1 set b = b + 5 where a = 1;
1880 #update t2 set b = b + 5 where a = 1;
1881 #update t3 set b = b + 5 where a = 1;
1882 #update t4 set b = b + 5 where a = 1;
1883 #insert into t5(a) values(20);
1884 #connection b;
1885 #set autocommit = 0;
1886 #insert into t1(a) values(7);
1887 #insert into t2(a) values(8);
1888 #delete from t2 where a = 3;
1889 #update t4 set b = b + 1 where a = 3;
1890 #commit;
1891 #drop trigger t1t;
1892 #drop trigger t2t;
1893 #drop trigger t3t;
1894 #drop trigger t4t;
1895 #drop table t1, t2, t3, t4, t5;
1896 #connection default;
1897 #disconnect a;
1898 #disconnect b;
1899 ## end disabled_mdl
1900
1901 #
1902 # Test that cascading updates leading to duplicate keys give the correct
1903 # error message (bug #9680)
1904 #
1905
1906 CREATE TABLE t1 (
1907 field1 varchar(8) NOT NULL DEFAULT '',
1908 field2 varchar(8) NOT NULL DEFAULT '',
1909 PRIMARY KEY (field1, field2)
1910 ) ENGINE=InnoDB;
1911
1912 CREATE TABLE t2 (
1913 field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
1914 FOREIGN KEY (field1) REFERENCES t1 (field1)
1915 ON DELETE CASCADE ON UPDATE CASCADE
1916 ) ENGINE=InnoDB;
1917
1918 INSERT INTO t1 VALUES ('old', 'somevalu');
1919 INSERT INTO t1 VALUES ('other', 'anyvalue');
1920
1921 INSERT INTO t2 VALUES ('old');
1922 INSERT INTO t2 VALUES ('other');
1923
1924 --error ER_FOREIGN_DUPLICATE_KEY_WITH_CHILD_INFO
1925 UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
1926
1927 DROP TABLE t2;
1928 DROP TABLE t1;
1929
1930 #
1931 # Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
1932 #
1933 create table t1 (
1934 c1 bigint not null,
1935 c2 bigint not null,
1936 primary key (c1),
1937 unique key (c2)
1938 ) engine=innodb;
1939 #
1940 create table t2 (
1941 c1 bigint not null,
1942 primary key (c1)
1943 ) engine=innodb;
1944 #
1945 alter table t1 add constraint c2_fk foreign key (c2)
1946 references t2(c1) on delete cascade;
1947 show create table t1;
1948 #
1949 alter table t1 drop foreign key c2_fk;
1950 show create table t1;
1951 #
1952 drop table t1, t2;
1953
1954 #
1955 # Bug #14360: problem with intervals
1956 #
1957
1958 create table t1(a date) engine=innodb;
1959 create table t2(a date, key(a)) engine=innodb;
1960 insert into t1 values('2005-10-01');
1961 insert into t2 values('2005-10-01');
1962 select * from t1, t2
1963 where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
1964 drop table t1, t2;
1965
1966 create table t1 (id int not null, f_id int not null, f int not null,
1967 primary key(f_id, id)) engine=innodb;
1968 create table t2 (id int not null,s_id int not null,s varchar(200),
1969 primary key(id)) engine=innodb;
1970 INSERT INTO t1 VALUES (8, 1, 3);
1971 INSERT INTO t1 VALUES (1, 2, 1);
1972 INSERT INTO t2 VALUES (1, 0, '');
1973 INSERT INTO t2 VALUES (8, 1, '');
1974 commit;
1975 DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
1976 WHERE mm.id IS NULL;
1977 select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
1978 where mm.id is null lock in share mode;
1979 drop table t1,t2;
1980
1981 #
1982 # Test case where X-locks on unused rows should be released in a
1983 # update (because READ COMMITTED isolation level)
1984 #
1985
1986 connect (a,localhost,root,,);
1987 connect (b,localhost,root,,);
1988 connection a;
1989 create table t1(a int not null, b int, primary key(a)) engine=innodb;
1990 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
1991 commit;
1992 SET binlog_format='MIXED';
1993 set autocommit = 0;
1994 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1995 update t1 set b = 5 where b = 1;
1996 connection b;
1997 SET binlog_format='MIXED';
1998 set autocommit = 0;
1999 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2000 #
2001 # X-lock to record (7,3) should be released in a update
2002 #
2003 select * from t1 where a = 7 and b = 3 for update;
2004 connection a;
2005 commit;
2006 connection b;
2007 commit;
2008 drop table t1;
2009 connection default;
2010 disconnect a;
2011 disconnect b;
2012
2013 #
2014 # Test case where no locks should be released (because we are not
2015 # using READ COMMITTED isolation level)
2016 #
2017
2018 connect (a,localhost,root,,);
2019 connect (b,localhost,root,,);
2020 connection a;
2021 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2022 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
2023 commit;
2024 set autocommit = 0;
2025 select * from t1 lock in share mode;
2026 update t1 set b = 5 where b = 1;
2027 connection b;
2028 set autocommit = 0;
2029 #
2030 # S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
2031 #
2032 --error ER_LOCK_WAIT_TIMEOUT
2033 select * from t1 where a = 2 and b = 2 for update;
2034 #
2035 # X-lock to record (1,1),(3,1),(5,1) should not be released in a update
2036 #
2037 --error ER_LOCK_WAIT_TIMEOUT
2038 connection a;
2039 commit;
2040 connection b;
2041 commit;
2042 connection default;
2043 disconnect a;
2044 disconnect b;
2045 drop table t1;
2046
2047 #
2048 # Consistent read should be used in following selects
2049 #
2050 # 1) INSERT INTO ... SELECT
2051 # 2) UPDATE ... = ( SELECT ...)
2052 # 3) CREATE ... SELECT
2053
2054 connect (a,localhost,root,,);
2055 connect (b,localhost,root,,);
2056 connection a;
2057 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2058 insert into t1 values (1,2),(5,3),(4,2);
2059 create table t2(d int not null, e int, primary key(d)) engine=innodb;
2060 insert into t2 values (8,6),(12,1),(3,1);
2061 commit;
2062 set autocommit = 0;
2063 select * from t2 for update;
2064 connection b;
2065 SET binlog_format='MIXED';
2066 set autocommit = 0;
2067 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2068 insert into t1 select * from t2;
2069 update t1 set b = (select e from t2 where a = d);
2070 create table t3(d int not null, e int, primary key(d)) engine=innodb
2071 select * from t2;
2072 commit;
2073 connection a;
2074 commit;
2075 connection default;
2076 disconnect a;
2077 disconnect b;
2078 drop table t1, t2, t3;
2079
2080 #
2081 # Consistent read should not be used if
2082 #
2083 # (a) isolation level is serializable OR
2084 # (b) select ... lock in share mode OR
2085 # (c) select ... for update
2086 #
2087 # in following queries:
2088 #
2089 # 1) INSERT INTO ... SELECT
2090 # 2) UPDATE ... = ( SELECT ...)
2091 # 3) CREATE ... SELECT
2092
2093 connect (a,localhost,root,,);
2094 connect (b,localhost,root,,);
2095 connect (c,localhost,root,,);
2096 connect (d,localhost,root,,);
2097 connect (e,localhost,root,,);
2098 connect (f,localhost,root,,);
2099 connect (g,localhost,root,,);
2100 connect (h,localhost,root,,);
2101 connect (i,localhost,root,,);
2102 connect (j,localhost,root,,);
2103 connection a;
2104 create table t1(a int not null, b int, primary key(a)) engine=innodb;
2105 insert into t1 values (1,2),(5,3),(4,2);
2106 create table t2(a int not null, b int, primary key(a)) engine=innodb;
2107 insert into t2 values (8,6),(12,1),(3,1);
2108 create table t3(d int not null, b int, primary key(d)) engine=innodb;
2109 insert into t3 values (8,6),(12,1),(3,1);
2110 create table t5(a int not null, b int, primary key(a)) engine=innodb;
2111 insert into t5 values (1,2),(5,3),(4,2);
2112 create table t6(d int not null, e int, primary key(d)) engine=innodb;
2113 insert into t6 values (8,6),(12,1),(3,1);
2114 create table t8(a int not null, b int, primary key(a)) engine=innodb;
2115 insert into t8 values (1,2),(5,3),(4,2);
2116 create table t9(d int not null, e int, primary key(d)) engine=innodb;
2117 insert into t9 values (8,6),(12,1),(3,1);
2118 commit;
2119 set autocommit = 0;
2120 select * from t2 for update;
2121 connection b;
2122 SET binlog_format='MIXED';
2123 set autocommit = 0;
2124 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2125 --send
2126 insert into t1 select * from t2;
2127 connection c;
2128 SET binlog_format='MIXED';
2129 set autocommit = 0;
2130 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2131 --send
2132 update t3 set b = (select b from t2 where a = d);
2133 connection d;
2134 SET binlog_format='MIXED';
2135 set autocommit = 0;
2136 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2137 --send
2138 create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
2139 connection e;
2140 SET binlog_format='MIXED';
2141 set autocommit = 0;
2142 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2143 --send
2144 insert into t5 (select * from t2 lock in share mode);
2145 connection f;
2146 SET binlog_format='MIXED';
2147 set autocommit = 0;
2148 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2149 --send
2150 update t6 set e = (select b from t2 where a = d lock in share mode);
2151 connection g;
2152 SET binlog_format='MIXED';
2153 set autocommit = 0;
2154 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2155 --send
2156 create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
2157 connection h;
2158 SET binlog_format='MIXED';
2159 set autocommit = 0;
2160 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2161 --send
2162 insert into t8 (select * from t2 for update);
2163 connection i;
2164 SET binlog_format='MIXED';
2165 set autocommit = 0;
2166 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2167 --send
2168 update t9 set e = (select b from t2 where a = d for update);
2169 connection j;
2170 SET binlog_format='MIXED';
2171 set autocommit = 0;
2172 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2173 --send
2174 create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
2175
2176 connection b;
2177 --error ER_LOCK_WAIT_TIMEOUT
2178 reap;
2179
2180 connection c;
2181 --error ER_LOCK_WAIT_TIMEOUT
2182 reap;
2183
2184 connection d;
2185 --error ER_LOCK_WAIT_TIMEOUT
2186 reap;
2187
2188 connection e;
2189 --error ER_LOCK_WAIT_TIMEOUT
2190 reap;
2191
2192 connection f;
2193 --error ER_LOCK_WAIT_TIMEOUT
2194 reap;
2195
2196 connection g;
2197 --error ER_LOCK_WAIT_TIMEOUT
2198 reap;
2199
2200 connection h;
2201 --error ER_LOCK_WAIT_TIMEOUT
2202 reap;
2203
2204 connection i;
2205 --error ER_LOCK_WAIT_TIMEOUT
2206 reap;
2207
2208 connection j;
2209 --error ER_LOCK_WAIT_TIMEOUT
2210 reap;
2211
2212 connection a;
2213 commit;
2214
2215 connection default;
2216 disconnect a;
2217 disconnect b;
2218 disconnect c;
2219 disconnect d;
2220 disconnect e;
2221 disconnect f;
2222 disconnect g;
2223 disconnect h;
2224 disconnect i;
2225 disconnect j;
2226 drop table t1, t2, t3, t5, t6, t8, t9;
2227
2228 # bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"
2229 --error ER_WRONG_COLUMN_NAME
2230 CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
2231
2232 #
2233 # Bug #17152: Wrong result with BINARY comparison on aliased column
2234 #
2235
2236 CREATE TABLE t1 (
2237 a BIGINT(20) NOT NULL,
2238 PRIMARY KEY (a)
2239 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2240
2241 CREATE TABLE t2 (
2242 a BIGINT(20) NOT NULL,
2243 b VARCHAR(128) NOT NULL,
2244 c TEXT NOT NULL,
2245 PRIMARY KEY (a,b),
2246 KEY idx_t2_b_c (b,c(100)),
2247 CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
2248 ON DELETE CASCADE
2249 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2250
2251 INSERT INTO t1 VALUES (1);
2252 INSERT INTO t2 VALUES (1, 'bar', 'vbar');
2253 INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
2254 INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
2255 INSERT INTO t2 VALUES (1, 'customer_over', '1');
2256
2257 SELECT * FROM t2 WHERE b = 'customer_over';
2258 SELECT * FROM t2 WHERE BINARY b = 'customer_over';
2259 SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
2260 /* Bang: Empty result set, above was expected: */
2261 SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2262 SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2263
2264 drop table t2, t1;
2265
2266 #
2267 # Test optimize on table with open transaction
2268 #
2269
2270 CREATE TABLE t1 ( a int ) ENGINE=innodb;
2271 BEGIN;
2272 INSERT INTO t1 VALUES (1);
2273 OPTIMIZE TABLE t1;
2274 DROP TABLE t1;
2275
2276 #
2277 # Bug #24741 (existing cascade clauses disappear when adding foreign keys)
2278 #
2279
2280 CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
2281
2282 CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
2283 CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
2284 ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
2285
2286 ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
2287 DELETE CASCADE ON UPDATE CASCADE;
2288
2289 SHOW CREATE TABLE t2;
2290 DROP TABLE t2, t1;
2291
2292 #
2293 # Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns
2294 # for which there is a foreign key constraint ON ... SET NULL.
2295 #
2296
2297 CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
2298 CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
2299 INSERT INTO t1 VALUES (1);
2300 INSERT INTO t2 VALUES (1);
2301 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
2302 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
2303 set @old_sql_mode = @@sql_mode;
2304 set @@sql_mode = 'STRICT_TRANS_TABLES';
2305 --error ER_FK_COLUMN_NOT_NULL
2306 ALTER TABLE t2 MODIFY a INT NOT NULL;
2307 set @@sql_mode = @old_sql_mode;
2308 DELETE FROM t1;
2309 DROP TABLE t2,t1;
2310
2311 #
2312 # Bug #26835: table corruption after delete+insert
2313 #
2314
2315 CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
2316 ENGINE=InnoDB;
2317 INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
2318 DELETE FROM t1;
2319 INSERT INTO t1 VALUES ('DDD');
2320 SELECT * FROM t1;
2321 DROP TABLE t1;
2322
2323 #
2324 # Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)
2325 # Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))
2326 #
2327
2328 CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
2329 AUTO_INCREMENT=42;
2330
2331 INSERT INTO t1 VALUES (0),(347),(0);
2332 SELECT * FROM t1;
2333
2334 SHOW CREATE TABLE t1;
2335
2336 CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
2337 INSERT INTO t2 VALUES(42),(347),(348);
2338 ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
2339 SHOW CREATE TABLE t1;
2340
2341 DROP TABLE t1,t2;
2342
2343 #
2344 # Bug #21101 (Prints wrong error message if max row size is too large)
2345 #
2346 SET innodb_strict_mode=ON;
2347 --replace_result 8126 {checked_valid} 4030 {checked_valid} 1982 {checked_valid}
2348 --error ER_TOO_BIG_ROWSIZE
2349 CREATE TABLE t1 (
2350 c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
2351 c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
2352 c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
2353 c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
2354 c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
2355 c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
2356 c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
2357 c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
2358 ) ENGINE = InnoDB;
2359 SET innodb_strict_mode=OFF;
2360
2361 #
2362 # Bug #31860 InnoDB assumes AUTOINC values can only be positive.
2363 #
2364 DROP TABLE IF EXISTS t1;
2365 CREATE TABLE t1(
2366 id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
2367 ) ENGINE=InnoDB;
2368 INSERT INTO t1 VALUES(-10);
2369 SELECT * FROM t1;
2370 #
2371 # NOTE: The server really needs to be restarted at this point
2372 # for the test to be useful.
2373 #
2374 # Without the fix InnoDB would trip over an assertion here.
2375 INSERT INTO t1 VALUES(NULL);
2376 # The next value should be 1 and not -9 or a -ve number
2377 SELECT * FROM t1;
2378 DROP TABLE t1;
2379
2380 #
2381 # Bug #21409 Incorrect result returned when in READ-COMMITTED with
2382 # query_cache ON
2383 #
2384 CONNECT (c1,localhost,root,,);
2385 CONNECT (c2,localhost,root,,);
2386 CONNECTION c1;
2387 SET binlog_format='MIXED';
2388 SET TRANSACTION_ISOLATION='read-committed';
2389 SET AUTOCOMMIT=0;
2390 DROP TABLE IF EXISTS t1, t2;
2391 CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
2392 CREATE TABLE t2 LIKE t1;
2393 SELECT * FROM t2;
2394 CONNECTION c2;
2395 SET binlog_format='MIXED';
2396 SET TRANSACTION_ISOLATION='read-committed';
2397 SET AUTOCOMMIT=0;
2398 INSERT INTO t1 VALUES (1);
2399 COMMIT;
2400 CONNECTION c1;
2401 SELECT * FROM t1 WHERE a=1;
2402 DISCONNECT c1;
2403 DISCONNECT c2;
2404 CONNECT (c1,localhost,root,,);
2405 CONNECT (c2,localhost,root,,);
2406 CONNECTION c1;
2407 SET binlog_format='MIXED';
2408 SET TRANSACTION_ISOLATION='read-committed';
2409 SET AUTOCOMMIT=0;
2410 SELECT * FROM t2;
2411 CONNECTION c2;
2412 SET binlog_format='MIXED';
2413 SET TRANSACTION_ISOLATION='read-committed';
2414 SET AUTOCOMMIT=0;
2415 INSERT INTO t1 VALUES (2);
2416 COMMIT;
2417 CONNECTION c1;
2418 # The result set below should be the same for both selects
2419 SELECT * FROM t1 WHERE a=2;
2420 SELECT * FROM t1 WHERE a=2;
2421 DROP TABLE t1;
2422 DROP TABLE t2;
2423 DISCONNECT c1;
2424 DISCONNECT c2;
2425 CONNECTION default;
2426
2427 #
2428 # Bug #29157 UPDATE, changed rows incorrect
2429 #
2430 create table t1 (i int, j int) engine=innodb;
2431 insert into t1 (i, j) values (1, 1), (2, 2);
2432 --enable_info
2433 update t1 set j = 2;
2434 --disable_info
2435 drop table t1;
2436
2437 #
2438 # Bug #32440 InnoDB free space info does not appear in SHOW TABLE STATUS or
2439 # I_S
2440 #
2441 create table t1 (id int) comment='this is a comment' engine=innodb;
2442 select table_comment, data_free > 0 as data_free_is_set
2443 from information_schema.tables
2444 where table_schema='test' and table_name = 't1';
2445 drop table t1;
2446
2447 #
2448 # Bug 34920 test
2449 #
2450 CONNECTION default;
2451 CREATE TABLE t1 (
2452 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
2453 c2 VARCHAR(128) NOT NULL,
2454 PRIMARY KEY(c1)
2455 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
2456
2457 CREATE TABLE t2 (
2458 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
2459 c2 INT(10) UNSIGNED DEFAULT NULL,
2460 PRIMARY KEY(c1)
2461 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
2462
2463 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
2464 ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
2465 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
2466 DROP TABLE t2;
2467 DROP TABLE t1;
2468 # End 34920 test
2469 #
2470 # Bug #29507 TRUNCATE shows to many rows effected
2471 #
2472 CONNECTION default;
2473 CREATE TABLE t1 (c1 int default NULL,
2474 c2 int default NULL
2475 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2476
2477 --enable_info
2478 TRUNCATE TABLE t1;
2479
2480 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
2481 TRUNCATE TABLE t1;
2482
2483 --disable_info
2484 DROP TABLE t1;
2485 #
2486 # Bug#35537 Innodb doesn't increment handler_update and handler_delete.
2487 #
2488 -- disable_query_log
2489 -- disable_result_log
2490
2491 CONNECT (c1,localhost,root,,);
2492
2493 DROP TABLE IF EXISTS bug35537;
2494 CREATE TABLE bug35537 (
2495 c1 int
2496 ) ENGINE=InnoDB;
2497
2498 INSERT INTO bug35537 VALUES (1);
2499
2500 -- enable_result_log
2501
2502 SHOW SESSION STATUS LIKE 'Handler_update%';
2503 SHOW SESSION STATUS LIKE 'Handler_delete%';
2504
2505 UPDATE bug35537 SET c1 = 2 WHERE c1 = 1;
2506 DELETE FROM bug35537 WHERE c1 = 2;
2507
2508 SHOW SESSION STATUS LIKE 'Handler_update%';
2509 SHOW SESSION STATUS LIKE 'Handler_delete%';
2510
2511 DROP TABLE bug35537;
2512
2513 DISCONNECT c1;
2514 CONNECTION default;
2515
2516 -- enable_query_log
2517
2518 # Clean up after the Bug#55284/Bug#58912 test case.
2519 DROP TABLE bug58912;
2520
2521 #
2522 # Test fix for bug 13117023. InnoDB increments HA_READ_KEY_COUNT (aka
2523 # HANDLER_READ_KEY) when it should not.
2524 #
2525 create table t1 (f1 integer primary key) engine=innodb;
2526 flush status;
2527 show status like "handler_read_key";
2528 select f1 from t1;
2529 show status like "handler_read_key";
2530 drop table t1;
2531
2532 #
2533 # Test handling of writes to TEMPORARY tables for read-only transactions
2534 #
2535 CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
2536 CREATE TEMPORARY TABLE t2 (c1 INT) ENGINE=InnoDB;
2537
2538 # Check that the rollback works
2539 START TRANSACTION READ ONLY;
2540 INSERT INTO t2 VALUES(0);
2541 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
2542 INSERT INTO t1 VALUES(0);
2543 ROLLBACK;
2544
2545 SELECT * FROM t1;
2546 SELECT * FROM t2;
2547
2548 START TRANSACTION READ ONLY;
2549 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
2550 INSERT INTO t1 VALUES(0);
2551 INSERT INTO t2 VALUES(1);
2552 COMMIT;
2553
2554 SET TRANSACTION READ ONLY;
2555 START TRANSACTION;
2556 INSERT INTO t2 VALUES(3);
2557 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
2558 INSERT INTO t1 VALUES(0);
2559 COMMIT;
2560
2561 SELECT * FROM t1;
2562 SELECT * FROM t2;
2563
2564 DROP TABLE t2;
2565
2566 # This time with some indexes
2567 CREATE TEMPORARY TABLE t2 (
2568 c1 INT AUTO_INCREMENT PRIMARY KEY,
2569 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
2570
2571 SHOW CREATE TABLE t2;
2572
2573 # Check that the rollback works
2574 START TRANSACTION READ ONLY;
2575 INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3);
2576 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
2577 INSERT INTO t1 VALUES(0);
2578 ROLLBACK;
2579
2580 SELECT * FROM t1;
2581 SELECT * FROM t2;
2582
2583 START TRANSACTION READ ONLY;
2584 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
2585 INSERT INTO t1 VALUES(0);
2586 INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3);
2587 COMMIT;
2588
2589 SET TRANSACTION READ ONLY;
2590 START TRANSACTION;
2591 INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3);
2592 --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
2593 INSERT INTO t1 VALUES(0);
2594 COMMIT;
2595
2596 SHOW CREATE TABLE t2;
2597 SELECT * FROM t1;
2598 SELECT * FROM t2;
2599
2600 DROP TABLE t1;
2601 DROP TABLE t2;
2602
2603 --echo #
2604 --echo # MDEV-24748 Extern field check missing
2605 --echo # in btr_index_rec_validate()
2606 --echo #
2607 CREATE TABLE t1 (pk INT, c1 char(255),
2608 c2 char(255), c3 char(255), c4 char(255),
2609 c5 char(255), c6 char(255), c7 char(255),
2610 c8 char(255), primary key (pk)
2611 ) CHARACTER SET utf32 ENGINE=InnoDB;
2612
2613 INSERT INTO t1 VALUES
2614 (1, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'),
2615 (2, 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p');
2616 CHECK TABLE t1;
2617 ALTER TABLE t1 FORCE;
2618 # Cleanup
2619 DROP TABLE t1;
2620
2621 --echo #
2622 --echo # MDEV-35723: applying zero offset to null pointer on INSERT
2623 --echo #
2624 CREATE TABLE t1(c TEXT(1) NOT NULL, INDEX (c)) ENGINE=InnoDB;
2625 INSERT INTO t1 SET c='';
2626 DROP TABLE t1;
2627
2628 --source include/test_db_charset_restore.inc