diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/suite/innodb/r/foreign-keys.result | 175 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/foreign_key.result | 103 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/foreign_key_debug.result | 60 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/foreign-keys.test | 202 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/foreign_key.test | 130 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/foreign_key_debug.test | 78 |
6 files changed, 371 insertions, 377 deletions
diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result deleted file mode 100644 index d76f01f7095..00000000000 --- a/mysql-test/suite/innodb/r/foreign-keys.result +++ /dev/null @@ -1,175 +0,0 @@ -# -# Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE -# ADD FOREIGN KEY -# -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; -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; -connect con1,localhost,root; -delete from t1 where a=2; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE) -update t1 set a=10 where a=1; -connection default; -unlock tables; -connection con1; -connection default; -lock table t2 write; -connection con1; -delete from t1 where a=2; -connection default; -unlock tables; -connection con1; -ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE) -connection default; -unlock tables; -disconnect con1; -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; -c d -5 2 -4 10 -6 30 -drop table t2, t1; -drop user foo; -create table t1 (f1 int primary key) engine=innodb; -create table t2 (f2 int primary key) engine=innodb; -create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb; -insert into t1 values (1),(2),(3),(4),(5); -insert into t2 values (1),(2),(3),(4),(5); -insert into t3 values (1),(2),(3),(4),(5); -connect con1,localhost,root; -set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2'; -alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade; -connection default; -set debug_sync='before_execute_sql_command wait_for g1'; -update t1 set f1 = f1 + 100000 limit 2; -connect con2,localhost,root; -kill query UPDATE; -disconnect con2; -connection default; -ERROR 70100: Query execution was interrupted -set debug_sync='now signal g2'; -connection con1; -show create table t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `f2` int(11) NOT NULL, - PRIMARY KEY (`f2`), - CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -disconnect con1; -connection default; -select * from t2 where f2 not in (select f1 from t1); -f2 -select * from t3 where f3 not in (select f2 from t2); -f3 -drop table t3; -drop table t2; -drop table t1; -set debug_sync='reset'; -# -# MDEV-17595 - Server crashes in copy_data_between_tables or -# Assertion `thd->transaction.stmt.is_empty() || -# (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' -# fails in close_tables_for_reopen upon concurrent -# ALTER TABLE and FLUSH -# -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-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name) -# -CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB; -CREATE TABLE t2 LIKE t1; -FLUSH TABLES; -SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go'; -ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE; -connect con1, localhost, root; -SET debug_sync='now WAIT_FOR ready'; -SET lock_wait_timeout=1; -UPDATE t2 SET pk=10 WHERE pk=1; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction -PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1'; -DEALLOCATE PREPARE stmt; -FLUSH TABLE t2; -SET debug_sync='now SIGNAL go'; -connection default; -disconnect con1; -connection default; -SET debug_sync='reset'; -SHOW OPEN TABLES FROM test; -Database Table In_use Name_locked -DROP TABLE t1, t2; -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); -insert t2 values (10); -ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_pk`) REFERENCES `t1` (`pk`)) -flush tables; -flush status; -update t1 set data=10 where pk+1>10; -show status like '%opened_tab%'; -Variable_name Value -Opened_table_definitions 1 -Opened_tables 1 -flush tables; -flush status; -update t2 set t1_pk=11 where t1_pk+1>10; -show status like '%opened_tab%'; -Variable_name Value -Opened_table_definitions 1 -Opened_tables 1 -flush tables; -flush status; -lock tables t1 write; -show status like '%opened_tab%'; -Variable_name Value -Opened_table_definitions 2 -Opened_tables 2 -insert t1 values (4,4); -show status like '%opened_tab%'; -Variable_name Value -Opened_table_definitions 2 -Opened_tables 2 -unlock tables; -create function foo() returns int -begin -insert t1 values (5,5); -return 5; -end| -flush tables; -flush status; -select foo(); -foo() -5 -show status like '%opened_tab%'; -Variable_name Value -Opened_table_definitions 2 -Opened_tables 2 -drop function foo; -drop table t2, t1; diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index 7098faca0c9..378fe74e14b 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -130,3 +130,106 @@ ALTER TABLE t1 DROP f3; ALTER TABLE t1 CHANGE f f3 INT; DROP TABLE t1; SET FOREIGN_KEY_CHECKS=1; +# +# Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE +# ADD FOREIGN KEY +# +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; +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; +delete from t1 where a=2; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE) +update t1 set a=10 where a=1; +unlock tables; +lock table t2 write; +delete from t1 where a=2; +unlock tables; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE) +unlock tables; +create user foo; +grant select,update on test.t1 to foo; +update t1 set a=30 where a=3; +select * from t2; +c d +5 2 +4 10 +6 30 +drop table t2, t1; +drop user foo; +# +# MDEV-17595 - Server crashes in copy_data_between_tables or +# Assertion `thd->transaction.stmt.is_empty() || +# (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' +# fails in close_tables_for_reopen upon concurrent +# ALTER TABLE and FLUSH +# +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; +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); +insert t2 values (10); +ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_pk`) REFERENCES `t1` (`pk`)) +flush tables; +flush status; +update t1 set data=10 where pk+1>10; +show status like '%opened_tab%'; +Variable_name Value +Opened_table_definitions 1 +Opened_tables 1 +flush tables; +flush status; +update t2 set t1_pk=11 where t1_pk+1>10; +show status like '%opened_tab%'; +Variable_name Value +Opened_table_definitions 1 +Opened_tables 1 +flush tables; +flush status; +lock tables t1 write; +show status like '%opened_tab%'; +Variable_name Value +Opened_table_definitions 2 +Opened_tables 2 +insert t1 values (4,4); +show status like '%opened_tab%'; +Variable_name Value +Opened_table_definitions 2 +Opened_tables 2 +unlock tables; +create function foo() returns int +begin +insert t1 values (5,5); +return 5; +end| +flush tables; +flush status; +select foo(); +foo() +5 +show status like '%opened_tab%'; +Variable_name Value +Opened_table_definitions 2 +Opened_tables 2 +drop function foo; +drop table t2, t1; diff --git a/mysql-test/suite/innodb/r/foreign_key_debug.result b/mysql-test/suite/innodb/r/foreign_key_debug.result new file mode 100644 index 00000000000..741200b62b2 --- /dev/null +++ b/mysql-test/suite/innodb/r/foreign_key_debug.result @@ -0,0 +1,60 @@ +create table t1 (f1 int primary key) engine=innodb; +create table t2 (f2 int primary key) engine=innodb; +create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb; +insert into t1 values (1),(2),(3),(4),(5); +insert into t2 values (1),(2),(3),(4),(5); +insert into t3 values (1),(2),(3),(4),(5); +connect con1,localhost,root; +set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2'; +alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade; +connection default; +set debug_sync='before_execute_sql_command wait_for g1'; +update t1 set f1 = f1 + 100000 limit 2; +connect con2,localhost,root; +kill query UPDATE; +disconnect con2; +connection default; +ERROR 70100: Query execution was interrupted +set debug_sync='now signal g2'; +connection con1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f2` int(11) NOT NULL, + PRIMARY KEY (`f2`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +disconnect con1; +connection default; +select * from t2 where f2 not in (select f1 from t1); +f2 +select * from t3 where f3 not in (select f2 from t2); +f3 +drop table t3; +drop table t2; +drop table t1; +set debug_sync='reset'; +# +# MDEV-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name) +# +CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 LIKE t1; +FLUSH TABLES; +SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go'; +ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE; +connect con1, localhost, root; +SET debug_sync='now WAIT_FOR ready'; +SET lock_wait_timeout=1; +UPDATE t2 SET pk=10 WHERE pk=1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1'; +DEALLOCATE PREPARE stmt; +FLUSH TABLE t2; +SET debug_sync='now SIGNAL go'; +connection default; +disconnect con1; +connection default; +SET debug_sync='reset'; +SHOW OPEN TABLES FROM test; +Database Table In_use Name_locked +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test deleted file mode 100644 index 9f1622d74cd..00000000000 --- a/mysql-test/suite/innodb/t/foreign-keys.test +++ /dev/null @@ -1,202 +0,0 @@ ---source include/have_innodb.inc ---source include/have_debug.inc ---source include/have_debug_sync.inc - ---enable_connect_log - ---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; - -# -# MDEV-16465 Invalid (old?) table or database name or hang in ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys -# -create table t1 (f1 int primary key) engine=innodb; -create table t2 (f2 int primary key) engine=innodb; -create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb; -insert into t1 values (1),(2),(3),(4),(5); -insert into t2 values (1),(2),(3),(4),(5); -insert into t3 values (1),(2),(3),(4),(5); -connect con1,localhost,root; -set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2'; -send alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade; -connection default; -let $conn=`select connection_id()`; -set debug_sync='before_execute_sql_command wait_for g1'; -send update t1 set f1 = f1 + 100000 limit 2; -connect con2,localhost,root; -let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock' and info like 'update t1 %'; -source include/wait_condition.inc; ---replace_result $conn UPDATE -eval kill query $conn; -disconnect con2; -connection default; -error ER_QUERY_INTERRUPTED; -reap; -set debug_sync='now signal g2'; -connection con1; -reap; -show create table t2; -disconnect con1; -connection default; -select * from t2 where f2 not in (select f1 from t1); -select * from t3 where f3 not in (select f2 from t2); -drop table t3; -drop table t2; -drop table t1; -set debug_sync='reset'; - - ---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; - - ---echo # ---echo # MDEV-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name) ---echo # -CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB; -CREATE TABLE t2 LIKE t1; -FLUSH TABLES; - -SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go'; -send ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE; - -connect con1, localhost, root; -SET debug_sync='now WAIT_FOR ready'; -SET lock_wait_timeout=1; # change to 0 in 10.3 ---error ER_LOCK_WAIT_TIMEOUT -UPDATE t2 SET pk=10 WHERE pk=1; -PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1'; -DEALLOCATE PREPARE stmt; -FLUSH TABLE t2; -SET debug_sync='now SIGNAL go'; - -connection default; -reap; - -# Cleanup -disconnect con1; - -connection default; -SET debug_sync='reset'; -SHOW OPEN TABLES FROM test; -DROP TABLE t1, t2; - -# -# 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; 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 +# diff --git a/mysql-test/suite/innodb/t/foreign_key_debug.test b/mysql-test/suite/innodb/t/foreign_key_debug.test new file mode 100644 index 00000000000..75d05801b5f --- /dev/null +++ b/mysql-test/suite/innodb/t/foreign_key_debug.test @@ -0,0 +1,78 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +--enable_connect_log + +# +# MDEV-16465 Invalid (old?) table or database name or hang in ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys +# +create table t1 (f1 int primary key) engine=innodb; +create table t2 (f2 int primary key) engine=innodb; +create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb; +insert into t1 values (1),(2),(3),(4),(5); +insert into t2 values (1),(2),(3),(4),(5); +insert into t3 values (1),(2),(3),(4),(5); +connect con1,localhost,root; +set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2'; +send alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade; +connection default; +let $conn=`select connection_id()`; +set debug_sync='before_execute_sql_command wait_for g1'; +send update t1 set f1 = f1 + 100000 limit 2; +connect con2,localhost,root; +let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock' and info like 'update t1 %'; +source include/wait_condition.inc; +--replace_result $conn UPDATE +eval kill query $conn; +disconnect con2; +connection default; +error ER_QUERY_INTERRUPTED; +reap; +set debug_sync='now signal g2'; +connection con1; +reap; +show create table t2; +disconnect con1; +connection default; +select * from t2 where f2 not in (select f1 from t1); +select * from t3 where f3 not in (select f2 from t2); +drop table t3; +drop table t2; +drop table t1; +set debug_sync='reset'; + +--echo # +--echo # MDEV-16060 - InnoDB: Failing assertion: ut_strcmp(index->name, key->name) +--echo # +CREATE TABLE t1 (`pk` INT PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE t2 LIKE t1; +FLUSH TABLES; + +SET debug_sync='alter_table_intermediate_table_created SIGNAL ready WAIT_FOR go'; +send ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE; + +connect con1, localhost, root; +SET debug_sync='now WAIT_FOR ready'; +SET lock_wait_timeout=1; # change to 0 in 10.3 +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t2 SET pk=10 WHERE pk=1; +PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1'; +DEALLOCATE PREPARE stmt; +FLUSH TABLE t2; +SET debug_sync='now SIGNAL go'; + +connection default; +reap; + +# Cleanup +disconnect con1; + +connection default; +SET debug_sync='reset'; +SHOW OPEN TABLES FROM test; +DROP TABLE t1, t2; + +# +# End of 10.1 tests +# |