release/src/router/mysql/mysql-test/suite/innodb_plugin/t/innodb_misc1.test

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