Debian Codegraph @

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

raw
1
2 -- source include/have_innodb_max_16k.inc
3
4 let $MYSQLD_DATADIR= `select @@datadir`;
5 # Save the original values of some variables in order to be able to
6 # estimate how much they have changed during the tests. Previously this
7 # test assumed that e.g. rows_deleted is 0 here and after deleting 23
8 # rows it expected that rows_deleted will be 23. Now we do not make
9 # assumptions about the values of the variables at the beginning, e.g.
10 # rows_deleted should be 23 + "rows_deleted before the test". This allows
11 # the test to be run multiple times without restarting the mysqld server.
12 # See Bug#43309 Test main.innodb can't be run twice
13 -- disable_query_log
14
15 call mtr.add_suppression("Cannot create the table .* because the record size will exceed the maximum allowed size for a record.");
16 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* in ALTER TABLE `test`.`t1`");
17 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* in RENAME TABLE table `test`.`t1`");
18 SET @innodb_thread_concurrency_orig = @@innodb_thread_concurrency;
19 -- enable_query_log
20
21 --disable_warnings
22 drop table if exists t1,t2,t3,t4;
23 drop database if exists mysqltest;
24 --enable_warnings
25
26 # InnoDB specific varchar tests
27 create table t1 (v varchar(16384)) charset latin1 engine=innodb;
28 drop table t1;
29
30 #
31 # BUG#11039 Wrong key length in min()
32 #
33
34 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
35 insert into t1 values ('8', '6'), ('4', '7');
36 select min(a) from t1;
37 select min(b) from t1 where a='8';
38 drop table t1;
39
40 #
41 # Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error
42 #
43
44 CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
45 insert into t1 (b) values (1);
46 replace into t1 (b) values (2), (1), (3);
47 --sorted_result
48 select * from t1;
49 truncate table t1;
50 insert into t1 (b) values (1);
51 replace into t1 (b) values (2);
52 replace into t1 (b) values (1);
53 replace into t1 (b) values (3);
54 --sorted_result
55 select * from t1;
56 drop table t1;
57
58 create table t1 (rowid int not null auto_increment, val int not null,primary
59 key (rowid), unique(val)) engine=innodb;
60 replace into t1 (val) values ('1'),('2');
61 replace into t1 (val) values ('1'),('2');
62 --error ER_DUP_ENTRY
63 insert into t1 (val) values ('1'),('2');
64 --sorted_result
65 select * from t1;
66 drop table t1;
67
68 #
69 # Test that update does change internal auto-increment value
70 #
71
72 create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
73 insert into t1 (val) values (1);
74 update t1 set a=2 where a=1;
75 # We should get the following error because InnoDB does update the counter
76 --error ER_DUP_ENTRY
77 insert into t1 values (2, 2);
78 insert into t1 (val) value (3);
79 --sorted_result
80 select * from t1;
81 drop table t1;
82 #
83 # Bug #10465
84 #
85
86 --disable_warnings
87 CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
88 --enable_warnings
89 INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
90 SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;
91 SELECT GRADE FROM t1 WHERE GRADE= 151;
92 DROP TABLE t1;
93
94 #
95 # Bug #12340 multitable delete deletes only one record
96 #
97 create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
98 create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
99 insert into t2 values ('aa','cc');
100 insert into t1 values ('aa','bb'),('aa','cc');
101 delete t1 from t1,t2 where f1=f3 and f4='cc';
102 --sorted_result
103 select * from t1;
104 drop table t1,t2;
105
106 #
107 # Test that the slow TRUNCATE implementation resets autoincrement columns
108 # (bug #11946)
109 #
110
111 CREATE TABLE t1 (
112 id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
113 ) ENGINE=InnoDB;
114
115 CREATE TABLE t2 (
116 id INTEGER NOT NULL,
117 FOREIGN KEY (id) REFERENCES t1 (id)
118 ) ENGINE=InnoDB;
119
120 INSERT INTO t1 (id) VALUES (NULL);
121 SELECT * FROM t1;
122 --error ER_TRUNCATE_ILLEGAL_FK
123 TRUNCATE t1;
124 INSERT INTO t1 (id) VALUES (NULL);
125 SELECT * FROM t1;
126
127 # continued from above; test that doing a slow TRUNCATE on a table with 0
128 # rows resets autoincrement columns
129 DELETE FROM t1;
130 --error ER_TRUNCATE_ILLEGAL_FK
131 TRUNCATE t1;
132 INSERT INTO t1 (id) VALUES (NULL);
133 SELECT * FROM t1;
134 DROP TABLE t2, t1;
135
136 # Test that foreign keys in temporary tables are not accepted (bug #12084)
137 CREATE TABLE t1
138 (
139 id INT PRIMARY KEY
140 ) ENGINE=InnoDB;
141
142 --error ER_CANNOT_ADD_FOREIGN
143 CREATE TEMPORARY TABLE t2
144 (
145 id INT NOT NULL PRIMARY KEY,
146 b INT,
147 FOREIGN KEY (b) REFERENCES test.t1(id)
148 ) ENGINE=InnoDB;
149 DROP TABLE t1;
150
151 #
152 # Test that index column max sizes are honored (bug #13315)
153 #
154
155 # prefix index
156 create table t1 (col1 varchar(2000), index (col1(767)))
157 character set = latin1 engine = innodb;
158
159 # normal indexes
160 create table t2 (col1 char(255), index (col1))
161 character set = latin1 engine = innodb;
162 create table t3 (col1 binary(255), index (col1))
163 character set = latin1 engine = innodb;
164 create table t4 (col1 varchar(767), index (col1))
165 character set = latin1 engine = innodb;
166 create table t5 (col1 varchar(767) primary key)
167 character set = latin1 engine = innodb;
168 create table t6 (col1 varbinary(767) primary key)
169 character set = latin1 engine = innodb;
170 create table t7 (col1 text, index(col1(767)))
171 character set = latin1 engine = innodb;
172 create table t8 (col1 blob, index(col1(767)))
173 character set = latin1 engine = innodb;
174
175 # multi-column tests can be found in innodb_16k, innodb_8k & innodb_4k
176
177 drop table t1, t2, t3, t4, t5, t6, t7, t8;
178
179 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
180
181 SET GLOBAL innodb_default_row_format=compact;
182 --error ER_INDEX_COLUMN_TOO_LONG
183 create table t1 (col1 varchar(768) primary key)
184 character set = latin1 engine = innodb;
185 --error ER_INDEX_COLUMN_TOO_LONG
186 create table t2 (col1 varbinary(768) primary key)
187 character set = latin1 engine = innodb;
188 --error ER_INDEX_COLUMN_TOO_LONG
189 create table t3 (col1 text, primary key(col1(768)))
190 character set = latin1 engine = innodb;
191 --error ER_INDEX_COLUMN_TOO_LONG
192 create table t4 (col1 blob, primary key(col1(768)))
193 character set = latin1 engine = innodb;
194
195 SET GLOBAL innodb_default_row_format=dynamic;
196 create table t1 (col1 varchar(768) primary key)
197 character set = latin1 engine = innodb;
198 drop table t1;
199 create table t2 (col1 varbinary(768) primary key)
200 character set = latin1 engine = innodb;
201 drop table t2;
202 create table t3 (col1 text, primary key(col1(768)))
203 character set = latin1 engine = innodb;
204 drop table t3;
205 create table t4 (col1 blob, primary key(col1(768)))
206 character set = latin1 engine = innodb;
207 drop table t4;
208 SET GLOBAL innodb_default_row_format=default;
209
210 SET sql_mode = default;
211
212 # these should be refused
213 SET GLOBAL innodb_default_row_format=compact;
214 --error ER_INDEX_COLUMN_TOO_LONG
215 create table t1 (col1 varchar(768) primary key)
216 character set = latin1 engine = innodb;
217 --error ER_INDEX_COLUMN_TOO_LONG
218 create table t2 (col1 varbinary(768) primary key)
219 character set = latin1 engine = innodb;
220 --error ER_INDEX_COLUMN_TOO_LONG
221 create table t3 (col1 text, primary key(col1(768)))
222 character set = latin1 engine = innodb;
223 --error ER_INDEX_COLUMN_TOO_LONG
224 create table t4 (col1 blob, primary key(col1(768)))
225 character set = latin1 engine = innodb;
226 SET GLOBAL innodb_default_row_format=default;
227
228 #
229 # Test improved foreign key error messages (bug #3443)
230 #
231
232 CREATE TABLE t1
233 (
234 id INT PRIMARY KEY
235 ) ENGINE=InnoDB;
236
237 CREATE TABLE t2
238 (
239 v INT,
240 CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
241 ) ENGINE=InnoDB;
242
243 --error 1452
244 INSERT INTO t2 VALUES(2);
245
246 INSERT INTO t1 VALUES(1);
247 INSERT INTO t2 VALUES(1);
248
249 --error 1451
250 DELETE FROM t1 WHERE id = 1;
251
252 --error ER_FK_CANNOT_DROP_PARENT
253 DROP TABLE t1;
254
255 SET FOREIGN_KEY_CHECKS=0;
256 DROP TABLE t1;
257 SET FOREIGN_KEY_CHECKS=1;
258
259 --error 1452
260 INSERT INTO t2 VALUES(3);
261
262 DROP TABLE t2;
263 #
264 # Test that checksum table uses a consistent read Bug #12669
265 #
266 connect (a,localhost,root,,);
267 connect (b,localhost,root,,);
268 connection a;
269 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
270 insert into t1 values (1),(2);
271 set autocommit=0;
272 checksum table t1;
273 connection b;
274 insert into t1 values(3);
275 connection a;
276 #
277 # Here checksum should not see insert
278 #
279 checksum table t1;
280 connection a;
281 commit;
282 checksum table t1;
283 commit;
284 drop table t1;
285 #
286 # autocommit = 1
287 #
288 connection a;
289 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
290 insert into t1 values (1),(2);
291 set autocommit=1;
292 checksum table t1;
293 connection b;
294 set autocommit=1;
295 insert into t1 values(3);
296 connection a;
297 #
298 # Here checksum sees insert
299 #
300 checksum table t1;
301 drop table t1;
302
303 connection default;
304 disconnect a;
305 disconnect b;
306
307 # tests for bugs #9802 and #13778
308
309 # test that FKs between invalid types are not accepted
310 set foreign_key_checks=0;
311 create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
312 --error ER_FK_INCOMPATIBLE_COLUMNS
313 create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
314 set foreign_key_checks=1;
315 drop table t2;
316
317 # test that FKs between different charsets are not accepted in CREATE even
318 # when f_k_c is 0
319
320 set foreign_key_checks=0;
321 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
322 --error ER_FK_INCOMPATIBLE_COLUMNS
323 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
324 set foreign_key_checks=1;
325 drop table t1;
326
327 # test that invalid datatype conversions with ALTER are not allowed
328
329 set foreign_key_checks=0;
330 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
331 create table t1(a varchar(10) primary key) engine = innodb;
332 --error ER_FK_INCOMPATIBLE_COLUMNS
333 alter table t1 modify column a int;
334 set foreign_key_checks=1;
335 drop table t2,t1;
336
337 # test that charset conversions with ALTER are allowed when f_k_c is 0
338
339 set foreign_key_checks=0;
340 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
341 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
342 alter table t1 convert to character set utf8;
343 set foreign_key_checks=1;
344 drop table t2,t1;
345
346 # test that RENAME does not allow invalid charsets when f_k_c is 0
347
348 set foreign_key_checks=0;
349 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
350 create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
351 --error ER_FK_INCOMPATIBLE_COLUMNS
352 rename table t3 to t1;
353 set foreign_key_checks=1;
354 drop table t2,t3;
355
356 # test that foreign key errors are reported correctly (Bug #15550)
357
358 create table t1(a int primary key) row_format=redundant engine=innodb;
359 create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
360 create table t3(a int primary key) row_format=compact engine=innodb;
361 create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
362
363 insert into t1 values(1);
364 insert into t3 values(1);
365 -- error 1452
366 insert into t2 values(2);
367 -- error 1452
368 insert into t4 values(2);
369 insert into t2 values(1);
370 insert into t4 values(1);
371 -- error 1451
372 update t1 set a=2;
373 -- error 1452
374 update t2 set a=2;
375 -- error 1451
376 update t3 set a=2;
377 -- error 1452
378 update t4 set a=2;
379 -- error ER_TRUNCATE_ILLEGAL_FK
380 truncate t1;
381 -- error ER_TRUNCATE_ILLEGAL_FK
382 truncate t3;
383 truncate t2;
384 truncate t4;
385 -- error ER_TRUNCATE_ILLEGAL_FK
386 truncate t1;
387 -- error ER_TRUNCATE_ILLEGAL_FK
388 truncate t3;
389
390 drop table t4,t3,t2,t1;
391
392 #
393 # Test that we can create a large (>1K) key
394 # Moved to innodb_16k, innodb_8k, and innodb_4k
395 # since each page size has its own maximum key length.
396 #
397
398 # test the padding of BINARY types and collations (Bug #14189)
399
400 create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
401 create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
402 create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
403 create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
404
405 insert into t1 values (0x41),(0x4120),(0x4100);
406 -- error ER_DUP_ENTRY
407 insert into t2 values (0x41),(0x4120),(0x4100);
408 insert into t2 values (0x41),(0x4120);
409 -- error ER_DUP_ENTRY
410 insert into t3 values (0x41),(0x4120),(0x4100);
411 insert into t3 values (0x41),(0x4100);
412 -- error ER_DUP_ENTRY
413 insert into t4 values (0x41),(0x4120),(0x4100);
414 insert into t4 values (0x41),(0x4100);
415 select hex(s1) from t1;
416 select hex(s1) from t2;
417 select hex(s1) from t3;
418 select hex(s1) from t4;
419 drop table t1,t2,t3,t4;
420
421 create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
422 create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
423
424 insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
425 -- error 1452
426 insert into t2 values(0x42);
427 insert into t2 values(0x41);
428 select hex(s1) from t2;
429 update t1 set s1=0x123456 where a=2;
430 select hex(s1) from t2;
431 -- error 1451
432 update t1 set s1=0x12 where a=1;
433 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
434 -- error 1451
435 update t1 set s1=0x12345678 where a=1;
436 SET sql_mode = default;
437 -- error 1451
438 update t1 set s1=0x123457 where a=1;
439 update t1 set s1=0x1220 where a=1;
440 select hex(s1) from t2;
441 update t1 set s1=0x1200 where a=1;
442 select hex(s1) from t2;
443 update t1 set s1=0x4200 where a=1;
444 select hex(s1) from t2;
445 -- error 1451
446 delete from t1 where a=1;
447 delete from t1 where a=2;
448 update t2 set s1=0x4120;
449 -- error 1451
450 delete from t1;
451 delete from t1 where a!=3;
452 select a,hex(s1) from t1;
453 select hex(s1) from t2;
454
455 drop table t2,t1;
456
457 create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
458 create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
459
460 insert into t1 values(1,0x4100),(2,0x41);
461 insert into t2 values(0x41);
462 select hex(s1) from t2;
463 update t1 set s1=0x1234 where a=1;
464 select hex(s1) from t2;
465 update t1 set s1=0x12 where a=2;
466 select hex(s1) from t2;
467 delete from t1 where a=1;
468 -- error 1451
469 delete from t1 where a=2;
470 select a,hex(s1) from t1;
471 select hex(s1) from t2;
472
473 drop table t2,t1;
474 # Ensure that <tablename>_ibfk_0 is not mistreated as a
475 # generated foreign key identifier. (Bug #16387)
476
477 CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
478 CREATE TABLE t2(a INT) ENGINE=InnoDB;
479 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
480 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
481 ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
482 ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
483 SHOW CREATE TABLE t2;
484 DROP TABLE t2,t1;
485
486 #
487 # Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing
488 #
489 ## the following cannot be tested after the introduction of metadata locks
490 ## because the create trigger command blocks and waits for connection b to
491 ## commit
492 ## begin disabled_mdl
493 #connect (a,localhost,root,,);
494 #connect (b,localhost,root,,);
495 #connection a;
496 #create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
497 #insert into t1(a) values (1),(2),(3);
498 #commit;
499 #delimiter |;
500 ## in 5.5+, this needs to be created before the UPDATE due to meta-data locking
501 #create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
502 #delimiter ;|
503 #connection b;
504 #set autocommit = 0;
505 #update t1 set b = 5 where a = 2;
506 #connection a;
507 #set autocommit = 0;
508 #connection a;
509 #insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
510 #(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
511 #(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
512 #(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
513 #(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
514 #connection b;
515 #commit;
516 #connection a;
517 #commit;
518 #drop trigger t1t;
519 #drop table t1;
520 #disconnect a;
521 #disconnect b;
522 ##
523 ## Another trigger test
524 ##
525 #connect (a,localhost,root,,);
526 #connect (b,localhost,root,,);
527 #connection a;
528 #create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
529 #create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
530 #create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
531 #create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
532 #create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
533 #insert into t1(a) values (1),(2),(3);
534 #insert into t2(a) values (1),(2),(3);
535 #insert into t3(a) values (1),(2),(3);
536 #insert into t4(a) values (1),(2),(3);
537 #insert into t3(a) values (5),(7),(8);
538 #insert into t4(a) values (5),(7),(8);
539 #insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
540 #
541 #delimiter |;
542 #create trigger t1t before insert on t1 for each row begin
543 # INSERT INTO t2 SET a = NEW.a;
544 #end |
545 #
546 #create trigger t2t before insert on t2 for each row begin
547 # DELETE FROM t3 WHERE a = NEW.a;
548 #end |
549 #
550 #create trigger t3t before delete on t3 for each row begin
551 # UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
552 #end |
553 #
554 #create trigger t4t before update on t4 for each row begin
555 # UPDATE t5 SET b = b + 1 where a = NEW.a;
556 #end |
557 #delimiter ;|
558 #commit;
559 #set autocommit = 0;
560 #update t1 set b = b + 5 where a = 1;
561 #update t2 set b = b + 5 where a = 1;
562 #update t3 set b = b + 5 where a = 1;
563 #update t4 set b = b + 5 where a = 1;
564 #insert into t5(a) values(20);
565 #connection b;
566 #set autocommit = 0;
567 #insert into t1(a) values(7);
568 #insert into t2(a) values(8);
569 #delete from t2 where a = 3;
570 #update t4 set b = b + 1 where a = 3;
571 #commit;
572 #connection a;
573 #drop trigger t1t;
574 #drop trigger t2t;
575 #drop trigger t3t;
576 #drop trigger t4t;
577 #drop table t1, t2, t3, t4, t5;
578 #connection default;
579 #disconnect a;
580 #disconnect b;
581 ## end disabled_mdl
582
583 #
584 # Test that cascading updates leading to duplicate keys give the correct
585 # error message (bug #9680)
586 #
587
588 CREATE TABLE t1 (
589 field1 varchar(8) NOT NULL DEFAULT '',
590 field2 varchar(8) NOT NULL DEFAULT '',
591 PRIMARY KEY (field1, field2)
592 ) ENGINE=InnoDB;
593
594 CREATE TABLE t2 (
595 field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
596 FOREIGN KEY (field1) REFERENCES t1 (field1)
597 ON DELETE CASCADE ON UPDATE CASCADE
598 ) ENGINE=InnoDB;
599
600 INSERT INTO t1 VALUES ('old', 'somevalu');
601 INSERT INTO t1 VALUES ('other', 'anyvalue');
602
603 INSERT INTO t2 VALUES ('old');
604 INSERT INTO t2 VALUES ('other');
605
606 --error ER_FOREIGN_DUPLICATE_KEY_WITH_CHILD_INFO
607 UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
608
609 DROP TABLE t2;
610 DROP TABLE t1;
611
612 #
613 # Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
614 #
615 create table t1 (
616 c1 bigint not null,
617 c2 bigint not null,
618 primary key (c1),
619 unique key (c2)
620 ) engine=innodb;
621 #
622 create table t2 (
623 c1 bigint not null,
624 primary key (c1)
625 ) engine=innodb;
626 #
627 alter table t1 add constraint c2_fk foreign key (c2)
628 references t2(c1) on delete cascade;
629 show create table t1;
630 #
631 alter table t1 drop foreign key c2_fk;
632 show create table t1;
633 #
634 drop table t1, t2;
635
636 #
637 # Bug #14360: problem with intervals
638 #
639
640 create table t1(a date) engine=innodb;
641 create table t2(a date, key(a)) engine=innodb;
642 insert into t1 values('2005-10-01');
643 insert into t2 values('2005-10-01');
644 --sorted_result
645 select * from t1, t2
646 where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
647 drop table t1, t2;
648
649 create table t1 (id int not null, f_id int not null, f int not null,
650 primary key(f_id, id)) engine=innodb;
651 create table t2 (id int not null,s_id int not null,s varchar(200),
652 primary key(id)) engine=innodb;
653 INSERT INTO t1 VALUES (8, 1, 3);
654 INSERT INTO t1 VALUES (1, 2, 1);
655 INSERT INTO t2 VALUES (1, 0, '');
656 INSERT INTO t2 VALUES (8, 1, '');
657 commit;
658 DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
659 WHERE mm.id IS NULL;
660 select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
661 where mm.id is null lock in share mode;
662 drop table t1,t2;
663
664 #
665 # Test case where X-locks on unused rows should be released in a
666 # update (because READ COMMITTED isolation level)
667 #
668
669 connect (a,localhost,root,,);
670 connect (b,localhost,root,,);
671 connection a;
672 create table t1(a int not null, b int, primary key(a)) engine=innodb;
673 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
674 commit;
675 SET binlog_format='MIXED';
676 set autocommit = 0;
677 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
678 update t1 set b = 5 where b = 1;
679 connection b;
680 SET binlog_format='MIXED';
681 set autocommit = 0;
682 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
683 #
684 # X-lock to record (7,3) should be released in a update
685 #
686 --sorted_result
687 select * from t1 where a = 7 and b = 3 for update;
688 connection a;
689 commit;
690 connection b;
691 commit;
692 drop table t1;
693 connection default;
694 disconnect a;
695 disconnect b;
696
697 #
698 # Test case where no locks should be released (because we are not
699 # using READ COMMITTED isolation level)
700 #
701
702 connect (a,localhost,root,,);
703 connect (b,localhost,root,,);
704 connection a;
705 create table t1(a int not null, b int, primary key(a)) engine=innodb;
706 insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
707 commit;
708 set autocommit = 0;
709 --sorted_result
710 select * from t1 lock in share mode;
711 update t1 set b = 5 where b = 1;
712 connection b;
713 set autocommit = 0;
714 #
715 # S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
716 #
717 --error 1205
718 select * from t1 where a = 2 and b = 2 for update;
719 #
720 # X-lock to record (1,1),(3,1),(5,1) should not be released in a update
721 #
722 --error 1205
723 connection a;
724 commit;
725 connection b;
726 commit;
727 connection default;
728 disconnect a;
729 disconnect b;
730 drop table t1;
731
732 #
733 # Consistent read should be used in following selects
734 #
735 # 1) INSERT INTO ... SELECT
736 # 2) UPDATE ... = ( SELECT ...)
737 # 3) CREATE ... SELECT
738
739 connect (a,localhost,root,,);
740 connect (b,localhost,root,,);
741 connection a;
742 create table t1(a int not null, b int, primary key(a)) engine=innodb;
743 insert into t1 values (1,2),(5,3),(4,2);
744 create table t2(d int not null, e int, primary key(d)) engine=innodb;
745 insert into t2 values (8,6),(12,1),(3,1);
746 commit;
747 set autocommit = 0;
748 select * from t2 for update;
749 connection b;
750 SET binlog_format='MIXED';
751 set autocommit = 0;
752 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
753 insert into t1 select * from t2;
754 update t1 set b = (select e from t2 where a = d);
755 create table t3(d int not null, e int, primary key(d)) engine=innodb
756 select * from t2;
757 commit;
758 connection a;
759 commit;
760 connection default;
761 disconnect a;
762 disconnect b;
763 drop table t1, t2, t3;
764
765 #
766 # Consistent read should not be used if
767 #
768 # (a) isolation level is serializable OR
769 # (b) select ... lock in share mode OR
770 # (c) select ... for update
771 #
772 # in following queries:
773 #
774 # 1) INSERT INTO ... SELECT
775 # 2) UPDATE ... = ( SELECT ...)
776 # 3) CREATE ... SELECT
777
778 connect (a,localhost,root,,);
779 connect (b,localhost,root,,);
780 connect (c,localhost,root,,);
781 connect (d,localhost,root,,);
782 connect (e,localhost,root,,);
783 connect (f,localhost,root,,);
784 connect (g,localhost,root,,);
785 connect (h,localhost,root,,);
786 connect (i,localhost,root,,);
787 connect (j,localhost,root,,);
788 connection a;
789 create table t1(a int not null, b int, primary key(a)) engine=innodb;
790 insert into t1 values (1,2),(5,3),(4,2);
791 create table t2(a int not null, b int, primary key(a)) engine=innodb;
792 insert into t2 values (8,6),(12,1),(3,1);
793 create table t3(d int not null, b int, primary key(d)) engine=innodb;
794 insert into t3 values (8,6),(12,1),(3,1);
795 create table t5(a int not null, b int, primary key(a)) engine=innodb;
796 insert into t5 values (1,2),(5,3),(4,2);
797 create table t6(d int not null, e int, primary key(d)) engine=innodb;
798 insert into t6 values (8,6),(12,1),(3,1);
799 create table t8(a int not null, b int, primary key(a)) engine=innodb;
800 insert into t8 values (1,2),(5,3),(4,2);
801 create table t9(d int not null, e int, primary key(d)) engine=innodb;
802 insert into t9 values (8,6),(12,1),(3,1);
803 commit;
804 set autocommit = 0;
805 select * from t2 for update;
806 connection b;
807 SET binlog_format='MIXED';
808 set autocommit = 0;
809 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
810 --send
811 insert into t1 select * from t2;
812 connection c;
813 SET binlog_format='MIXED';
814 set autocommit = 0;
815 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
816 --send
817 update t3 set b = (select b from t2 where a = d);
818 connection d;
819 SET binlog_format='MIXED';
820 set autocommit = 0;
821 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
822 --send
823 create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
824 connection e;
825 SET binlog_format='MIXED';
826 set autocommit = 0;
827 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
828 --send
829 insert into t5 (select * from t2 lock in share mode);
830 connection f;
831 SET binlog_format='MIXED';
832 set autocommit = 0;
833 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
834 --send
835 update t6 set e = (select b from t2 where a = d lock in share mode);
836 connection g;
837 SET binlog_format='MIXED';
838 set autocommit = 0;
839 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
840 --send
841 create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
842 connection h;
843 SET binlog_format='MIXED';
844 set autocommit = 0;
845 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
846 --send
847 insert into t8 (select * from t2 for update);
848 connection i;
849 SET binlog_format='MIXED';
850 set autocommit = 0;
851 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
852 --send
853 update t9 set e = (select b from t2 where a = d for update);
854 connection j;
855 SET binlog_format='MIXED';
856 set autocommit = 0;
857 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
858 --send
859 create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
860
861 connection b;
862 --error 1205
863 reap;
864
865 connection c;
866 --error 1205
867 reap;
868
869 connection d;
870 --error 1205
871 reap;
872
873 connection e;
874 --error 1205
875 reap;
876
877 connection f;
878 --error 1205
879 reap;
880
881 connection g;
882 --error 1205
883 reap;
884
885 connection h;
886 --error 1205
887 reap;
888
889 connection i;
890 --error 1205
891 reap;
892
893 connection j;
894 --error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT
895 reap;
896
897 connection a;
898 commit;
899
900 connection default;
901 disconnect a;
902 disconnect b;
903 disconnect c;
904 disconnect d;
905 disconnect e;
906 disconnect f;
907 disconnect g;
908 disconnect h;
909 disconnect i;
910 disconnect j;
911 drop table t1, t2, t3, t5, t6, t8, t9;
912
913 # bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"
914 --error ER_WRONG_COLUMN_NAME
915 CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
916
917 #
918 # Bug #17152: Wrong result with BINARY comparison on aliased column
919 #
920
921 CREATE TABLE t1 (
922 a BIGINT(20) NOT NULL,
923 PRIMARY KEY (a)
924 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
925
926 CREATE TABLE t2 (
927 a BIGINT(20) NOT NULL,
928 b VARCHAR(128) NOT NULL,
929 c TEXT NOT NULL,
930 PRIMARY KEY (a,b),
931 KEY idx_t2_b_c (b,c(100)),
932 CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)
933 ON DELETE CASCADE
934 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
935
936 INSERT INTO t1 VALUES (1);
937 INSERT INTO t2 VALUES (1, 'bar', 'vbar');
938 INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
939 INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
940 INSERT INTO t2 VALUES (1, 'customer_over', '1');
941
942 SELECT * FROM t2 WHERE b = 'customer_over';
943 SELECT * FROM t2 WHERE BINARY b = 'customer_over';
944 SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
945 /* Bang: Empty result set, above was expected: */
946 SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
947 SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
948
949 drop table t2, t1;
950
951 #
952 # Test optimize on table with open transaction
953 #
954
955 CREATE TABLE t1 ( a int ) ENGINE=innodb;
956 BEGIN;
957 INSERT INTO t1 VALUES (1);
958 OPTIMIZE TABLE t1;
959 DROP TABLE t1;
960
961 #
962 # Bug #24741 (existing cascade clauses disappear when adding foreign keys)
963 #
964
965 CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
966
967 CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
968 CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
969 ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
970
971 ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
972 DELETE CASCADE ON UPDATE CASCADE;
973
974 SHOW CREATE TABLE t2;
975 DROP TABLE t2, t1;
976
977 #
978 # Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns
979 # for which there is a foreign key constraint ON ... SET NULL.
980 #
981
982 CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
983 CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
984 INSERT INTO t1 VALUES (1);
985 INSERT INTO t2 VALUES (1);
986 ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
987 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
988 --error ER_FK_COLUMN_NOT_NULL
989 ALTER TABLE t2 MODIFY a INT NOT NULL;
990 DELETE FROM t1;
991 DROP TABLE t2,t1;
992
993 #
994 # Bug #26835: table corruption after delete+insert
995 #
996
997 CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
998 ENGINE=InnoDB;
999 INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
1000 DELETE FROM t1;
1001 INSERT INTO t1 VALUES ('DDD');
1002 SELECT * FROM t1;
1003 DROP TABLE t1;
1004
1005 #
1006 # Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)
1007 # Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))
1008 #
1009
1010 CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
1011 AUTO_INCREMENT=42;
1012
1013 INSERT INTO t1 VALUES (0),(347),(0);
1014 SELECT * FROM t1;
1015
1016 SHOW CREATE TABLE t1;
1017
1018 CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
1019 INSERT INTO t2 VALUES(42),(347),(348);
1020 ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
1021 SHOW CREATE TABLE t1;
1022
1023 DROP TABLE t1,t2;
1024
1025 #
1026 # Bug #21101 (Prints wrong error message if max row size is too large)
1027 #
1028 SET innodb_strict_mode=ON;
1029 --replace_result 8126 {checked_valid} 4030 {checked_valid} 1982 {checked_valid}
1030 --error ER_TOO_BIG_ROWSIZE
1031 CREATE TABLE t1 (
1032 c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
1033 c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
1034 c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
1035 c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
1036 c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
1037 c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
1038 c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
1039 c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
1040 ) ENGINE = InnoDB charset latin1;
1041 SET innodb_strict_mode=OFF;
1042
1043 #
1044 # Bug #31860 InnoDB assumes AUTOINC values can only be positive.
1045 #
1046 DROP TABLE IF EXISTS t1;
1047 CREATE TABLE t1(
1048 id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
1049 ) ENGINE=InnoDB;
1050 INSERT INTO t1 VALUES(-10);
1051 SELECT * FROM t1;
1052 #
1053 # NOTE: The server really needs to be restarted at this point
1054 # for the test to be useful.
1055 #
1056 # Without the fix InnoDB would trip over an assertion here.
1057 INSERT INTO t1 VALUES(NULL);
1058 # The next value should be 1 and not -9 or a -ve number
1059 SELECT * FROM t1;
1060 DROP TABLE t1;
1061
1062 #
1063 # Bug #29157 UPDATE, changed rows incorrect
1064 #
1065 create table t1 (i int, j int) engine=innodb;
1066 insert into t1 (i, j) values (1, 1), (2, 2);
1067 --enable_info
1068 update t1 set j = 2;
1069 --disable_info
1070 drop table t1;
1071
1072 #
1073 # Bug #32440 InnoDB free space info does not appear in SHOW TABLE STATUS or
1074 # I_S
1075 #
1076 set @save_innodb_file_per_table= @@global.innodb_file_per_table;
1077 set @@global.innodb_file_per_table=0;
1078 create table t1 (id int) comment='this is a comment' engine=innodb;
1079 ANALYZE TABLE t1;
1080 select table_comment, data_free > 0 as data_free_is_set
1081 from information_schema.tables
1082 where table_schema='test' and table_name = 't1';
1083 drop table t1;
1084 set @@global.innodb_file_per_table= @save_innodb_file_per_table;
1085
1086 #
1087 # Bug 34920 test
1088 #
1089 CONNECTION default;
1090 CREATE TABLE t1 (
1091 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
1092 c2 VARCHAR(128) NOT NULL,
1093 PRIMARY KEY(c1)
1094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
1095
1096 CREATE TABLE t2 (
1097 c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
1098 c2 INT(10) UNSIGNED DEFAULT NULL,
1099 PRIMARY KEY(c1)
1100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
1101
1102 ANALYZE TABLE t2;
1103 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
1104 ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1);
1105 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2';
1106 DROP TABLE t2;
1107 DROP TABLE t1;
1108 # End 34920 test
1109 #
1110 # Bug #29507 TRUNCATE shows to many rows effected
1111 #
1112 CONNECTION default;
1113 CREATE TABLE t1 (c1 int default NULL,
1114 c2 int default NULL
1115 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1116
1117 --enable_info
1118 TRUNCATE TABLE t1;
1119
1120 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
1121 TRUNCATE TABLE t1;
1122
1123 --disable_info
1124 DROP TABLE t1;
1125 #
1126 # Bug#35537 Innodb doesn't increment handler_update and handler_delete.
1127 #
1128 -- disable_query_log
1129 -- disable_result_log
1130
1131 CONNECT (c1,localhost,root,,);
1132
1133 DROP TABLE IF EXISTS bug35537;
1134 CREATE TABLE bug35537 (
1135 c1 int
1136 ) ENGINE=InnoDB;
1137
1138 INSERT INTO bug35537 VALUES (1);
1139
1140 -- enable_result_log
1141
1142 SHOW SESSION STATUS LIKE 'Handler_update%';
1143 SHOW SESSION STATUS LIKE 'Handler_delete%';
1144
1145 UPDATE bug35537 SET c1 = 2 WHERE c1 = 1;
1146 DELETE FROM bug35537 WHERE c1 = 2;
1147
1148 SHOW SESSION STATUS LIKE 'Handler_update%';
1149 SHOW SESSION STATUS LIKE 'Handler_delete%';
1150
1151 DROP TABLE bug35537;
1152
1153 DISCONNECT c1;
1154 CONNECTION default;
1155
1156 SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig;
1157
1158 -- enable_query_log
1159
1160 --echo #
1161 --echo # Bug#27041288: ASSERTION FAILURE:
1162 --echo # ROW0SEL.CC:3955:PREBUILT->MYSQL_PREFIX_LEN <= RECORD_BUFFER..
1163 --echo #
1164 CREATE TABLE t(a INT PRIMARY KEY, b CHAR(0), c CHAR(1));
1165 INSERT INTO t VALUES (1, '', 'a'), (2, '', 'b');
1166 --sorted_result
1167 SELECT * FROM t;
1168 DROP TABLE t;
1169
1170 -- source suite/innodb/include/check_pfs_mem_other_is_zero.inc