diff --git a/mysql-test/suite/innodb/r/bug117395.result b/mysql-test/suite/innodb/r/bug117395.result new file mode 100644 index 00000000000..8aba638a2f0 --- /dev/null +++ b/mysql-test/suite/innodb/r/bug117395.result @@ -0,0 +1,70 @@ +use test; +CREATE TABLE `t1` ( +`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, +`a` int(11) DEFAULT NULL UNIQUE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE `t2` ( +`pk` int(11) NOT NULL PRIMARY KEY, +`b` int(11) DEFAULT NULL UNIQUE, +CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE `t3` ( +`pk` int(11) NOT NULL PRIMARY KEY, +`b` int(11) DEFAULT NULL UNIQUE, +CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`pk`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +INSERT INTO t1 (a) VALUES (1); +INSERT INTO t1 (a) VALUES (2); +INSERT INTO t1 (a) VALUES (3); +set debug_sync='before_update_access_dml WAIT_FOR s1'; +# Sending: +UPDATE t1 SET a = 6 where a = 1;; +set debug_sync = 'ref_table_referenced_set_is_clear_and_not_insert WAIT_FOR s2'; +# Sending: +optimize table t3;; +set debug_sync = "now signal s1"; +# Reaping: UPDATE t1 SET a = 6 where a = 1; +set debug_sync = "now signal s2"; +# Reaping: optimize table t3; +Table Op Msg_type Msg_text +test.t3 optimize note Table does not support optimize, doing recreate + analyze instead +test.t3 optimize status OK +drop TABLE t3; +drop TABLE t2; +drop TABLE t1; +CREATE TABLE `t1` ( +`pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, +`a` int(11) DEFAULT NULL UNIQUE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE `t2` ( +`pk` int(11) NOT NULL PRIMARY KEY, +`b` int(11) DEFAULT NULL UNIQUE, +CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE `t3` ( +`pk` int(11) NOT NULL PRIMARY KEY, +`b` int(11) DEFAULT NULL UNIQUE, +CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`pk`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +INSERT INTO t1 (a) VALUES (1); +INSERT INTO t1 (a) VALUES (2); +INSERT INTO t1 (a) VALUES (3); +set debug_sync='before_update_access_dml WAIT_FOR s1'; +# Sending: +UPDATE t1 SET a = 6 where a = 1;; +SELECT OBJECT_NAME, LOCK_TYPE from performance_schema.metadata_locks where OBJECT_NAME = 't3'; +OBJECT_NAME LOCK_TYPE +t3 SHARED_WRITE +set debug_sync = 'ref_table_referenced_set_is_clear_and_not_insert WAIT_FOR s2'; +# Sending: +optimize table t3;; +set debug_sync = "now signal s1"; +# Reaping: UPDATE t1 SET a = 6 where a = 1; +set debug_sync = "now signal s2"; +# Reaping: optimize table t3; +Table Op Msg_type Msg_text +test.t3 optimize note Table does not support optimize, doing recreate + analyze instead +test.t3 optimize status OK +drop TABLE t3; +drop TABLE t2; +drop TABLE t1; diff --git a/mysql-test/suite/innodb/t/bug117395.test b/mysql-test/suite/innodb/t/bug117395.test new file mode 100644 index 00000000000..5538de14e81 --- /dev/null +++ b/mysql-test/suite/innodb/t/bug117395.test @@ -0,0 +1,124 @@ +#--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +--connect (con1,localhost,root,,test) +--connect (con2,localhost,root,,test) +--connect (con3,localhost,root,,test) + +use test; + +#------------------------------------------------------------------------------ +# Bug #117395 +#------------------------------------------------------------------------------ +--disable_warnings +CREATE TABLE `t1` ( + `pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, + `a` int(11) DEFAULT NULL UNIQUE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `t2` ( + `pk` int(11) NOT NULL PRIMARY KEY, + `b` int(11) DEFAULT NULL UNIQUE, + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `t3` ( + `pk` int(11) NOT NULL PRIMARY KEY, + `b` int(11) DEFAULT NULL UNIQUE, + CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`pk`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +--enable_warnings + +INSERT INTO t1 (a) VALUES (1); +INSERT INTO t1 (a) VALUES (2); +INSERT INTO t1 (a) VALUES (3); + +--connection con1 +set debug_sync='before_update_access_dml WAIT_FOR s1'; +--echo # Sending: +--send UPDATE t1 SET a = 6 where a = 1; + +--connection con2 +set debug_sync = 'ref_table_referenced_set_is_clear_and_not_insert WAIT_FOR s2'; +--echo # Sending: +--send optimize table t3; +--sleep 2 + + +--connection con3 +set debug_sync = "now signal s1"; + +--connection con1 +--echo # Reaping: UPDATE t1 SET a = 6 where a = 1; +--reap + +--connection con3 +set debug_sync = "now signal s2"; + +--connection con2 +--echo # Reaping: optimize table t3; +--reap + +drop TABLE t3; +drop TABLE t2; +drop TABLE t1; + +#------------------------------------------------------------------------------ +# FTS with FK and update casecade +#------------------------------------------------------------------------------ +--disable_warnings +CREATE TABLE `t1` ( + `pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, + `a` int(11) DEFAULT NULL UNIQUE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `t2` ( + `pk` int(11) NOT NULL PRIMARY KEY, + `b` int(11) DEFAULT NULL UNIQUE, + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `t3` ( + `pk` int(11) NOT NULL PRIMARY KEY, + `b` int(11) DEFAULT NULL UNIQUE, + CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`pk`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +--enable_warnings + +INSERT INTO t1 (a) VALUES (1); +INSERT INTO t1 (a) VALUES (2); +INSERT INTO t1 (a) VALUES (3); + +--connection con1 +set debug_sync='before_update_access_dml WAIT_FOR s1'; +--echo # Sending: +--send UPDATE t1 SET a = 6 where a = 1; + +--connection con3 +SELECT OBJECT_NAME, LOCK_TYPE from performance_schema.metadata_locks where OBJECT_NAME = 't3'; + +--connection con2 +set debug_sync = 'ref_table_referenced_set_is_clear_and_not_insert WAIT_FOR s2'; +--echo # Sending: +--send optimize table t3; +--sleep 2 + + +--connection con3 +set debug_sync = "now signal s1"; + +--connection con1 +--echo # Reaping: UPDATE t1 SET a = 6 where a = 1; +--reap + +--connection con3 +set debug_sync = "now signal s2"; + +--connection con2 +--echo # Reaping: optimize table t3; +--reap + +drop TABLE t3; +drop TABLE t2; +drop TABLE t1; \ No newline at end of file diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc index 7a21aebf5b0..013c9deed4f 100644 --- a/storage/innobase/dict/dict0dict.cc +++ b/storage/innobase/dict/dict0dict.cc @@ -3519,6 +3519,8 @@ dberr_t dict_foreign_add_to_cache(dict_foreign_t *foreign, for_in_cache->referenced_table = ref_table; for_in_cache->referenced_index = index; + DEBUG_SYNC_C("ref_table_referenced_set_is_clear_and_not_insert"); + std::pair ret = ref_table->referenced_set.insert(for_in_cache); diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc index 5d92d9748c9..e9d9f9c1111 100644 --- a/storage/innobase/row/row0mysql.cc +++ b/storage/innobase/row/row0mysql.cc @@ -1882,6 +1882,21 @@ static void init_fts_doc_id_for_ref( it != table->referenced_set.end(); ++it) { foreign = *it; + /* For non-cascading update/delete scenarios, there is no need to recurse + further since fts_init_doc_id is only used for cascading operations. For + cascading update and delete scenarios, concurrent access safety is + guaranteed because open_and_process_routine acquires a SHARED_WRITE lock + on the child table during table opening. */ + if (!(foreign->type & DICT_FOREIGN_ON_DELETE_CASCADE) && + !(foreign->type & DICT_FOREIGN_ON_UPDATE_CASCADE)) + continue; + +#ifdef UNIV_DEBUG + if (!strncmp(foreign->foreign_table->name.m_name, "test/t3", 7)) { + DEBUG_SYNC_C("before_update_access_dml"); + } +#endif /* UNIV_DEBUG */ + ut_ad(foreign->foreign_table != nullptr); if (foreign->foreign_table->fts != nullptr) {