Debian Codegraph @

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

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