cleanup: foreign-keys.test vs foreign_key.testmariadb-10.1.45

1 files changed, 130 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index afaeaa073a2..b33fbf60675 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -158,3 +158,133 @@ ALTER TABLE t1 CHANGE f f3 INT;
 DROP TABLE t1;
 
 SET FOREIGN_KEY_CHECKS=1;
+
+--echo #
+--echo # Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE
+--echo # ADD FOREIGN KEY
+--echo #
+
+CREATE TABLE `department` (`department_id` INT, `department_people_fk` INT,
+PRIMARY KEY (`department_id`)) engine=innodb;
+
+CREATE TABLE `title` (`title_id` INT, `title_manager_fk` INT,
+`title_reporter_fk` INT, PRIMARY KEY (`title_id`)) engine=innodb;
+
+CREATE TABLE `people` (`people_id` INT, PRIMARY KEY (`people_id`)) engine=innodb;
+
+ALTER TABLE `department` ADD FOREIGN KEY (`department_people_fk`) REFERENCES
+`people` (`people_id`);
+
+ALTER TABLE `title` ADD FOREIGN KEY (`title_manager_fk`) REFERENCES `people`
+(`people_id`);
+
+ALTER TABLE `title` ADD FOREIGN KEY (`title_reporter_fk`) REFERENCES `people`
+(`people_id`);
+
+drop table title, department, people;
+
+#
+# FK and prelocking:
+# child table accesses (reads and writes) wait for locks.
+#
+create table t1 (a int primary key, b int) engine=innodb;
+create table t2 (c int primary key, d int,
+  foreign key (d) references t1 (a) on update cascade) engine=innodb;
+insert t1 values (1,1),(2,2),(3,3);
+insert t2 values (4,1),(5,2),(6,3);
+flush table t2 with read lock; # this takes MDL_SHARED_NO_WRITE
+connect (con1,localhost,root);
+--error ER_ROW_IS_REFERENCED_2
+delete from t1 where a=2;
+send update t1 set a=10 where a=1;
+connection default;
+let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock';
+source include/wait_condition.inc;
+unlock tables;
+connection con1;
+reap;
+connection default;
+lock table t2 write; # this takes MDL_SHARED_NO_READ_WRITE
+connection con1;
+send delete from t1 where a=2;
+connection default;
+let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock';
+source include/wait_condition.inc;
+unlock tables;
+connection con1;
+--error ER_ROW_IS_REFERENCED_2
+reap;
+connection default;
+unlock tables;
+disconnect con1;
+
+# but privileges should not be checked
+create user foo;
+grant select,update on test.t1 to foo;
+connect(foo,localhost,foo);
+update t1 set a=30 where a=3;
+disconnect foo;
+connection default;
+select * from t2;
+drop table t2, t1;
+drop user foo;
+
+--echo #
+--echo # MDEV-17595 - Server crashes in copy_data_between_tables or
+--echo #              Assertion `thd->transaction.stmt.is_empty() ||
+--echo #              (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)'
+--echo #              fails in close_tables_for_reopen upon concurrent
+--echo #              ALTER TABLE and FLUSH
+--echo #
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(1),(2);
+CREATE TABLE t2 (b INT, KEY(b)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES(2);
+ALTER TABLE t2 ADD FOREIGN KEY(b) REFERENCES t1(a), LOCK=EXCLUSIVE;
+DROP TABLE t2, t1;
+
+#
+# MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys
+#
+
+create table t1 (pk int primary key, data int) engine=innodb;
+insert t1 values (1,1),(2,2),(3,3);
+create table t2 (t1_pk int, foreign key (t1_pk) references t1 (pk)) engine=innodb;
+insert t2 values (1),(2);
+error ER_NO_REFERENCED_ROW_2;
+insert t2 values (10);
+flush tables;
+flush status;
+# with ON UPDATE RESTRICT child tables are not opened
+update t1 set data=10 where pk+1>10;
+show status like '%opened_tab%';
+flush tables;
+flush status;
+# neither are parent tables
+update t2 set t1_pk=11 where t1_pk+1>10;
+show status like '%opened_tab%';
+# under LOCK TABLES
+flush tables;
+flush status;
+lock tables t1 write;
+show status like '%opened_tab%';
+insert t1 values (4,4);
+show status like '%opened_tab%';
+unlock tables;
+delimiter |;
+create function foo() returns int
+begin
+  insert t1 values (5,5);
+  return 5;
+end|
+delimiter ;|
+flush tables;
+flush status;
+select foo();
+show status like '%opened_tab%';
+drop function foo;
+drop table t2, t1;
+
+#
+# End of 10.1 tests
+#