Bug #117395 FTS checks table with FK constraints without acquiring MDL

• Submitted: 6 Feb 1:56
• Modified: 6 Feb 7:53
• Reporter: Jingyuan Sun
• Status: Analyzing
• Category: MySQL Server: FULLTEXT search
• Severity: S1 (Critical)
• Version: 8.0
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any
• Tags: foreign key, fulltext

[6 Feb 1:56] Jingyuan Sun

Description:
Before row update on a table, FTS initializes doc id for any table that directly or indirectly references current table by foreign key constraint in case of cascade update/delete. FTS accomplishes this by recursively going through table definition and look for FK references (init_fts_doc_id_for_ref). However, this initialization is also performed on table linked by FKs with no referential action, for which MDL lock may not have been acquired. This unnecessary check results in unsafe access to table definition and may lead to server crash in case of concurrent DDL on related tables.

e.g. table t1-t5 with following FK reference relation:
t1 <-(FK w/ cascade)- t2 <-(FK w/o cascade)- t3 <-(FK)- t4
row update on t1 acquires MDL on t1, t2, and t3, but not t4 or t5. However, FTS will attempt to initialize doc id for t4 and look for FKs in its table definition.

How to repeat:
see below patch

From d368e1b302f5548f1fc9c1164d020a4fc799e63c Mon Sep 17 00:00:00 2001
From: sunjingyuan <[e-mail]>
Date: Thu, 6 Feb 2025 09:49:41 +0800
Subject: [PATCH] FTS FK unsafe access demo

---
 mysql-test/suite/innodb/t/fk-master.opt |  1 +
 mysql-test/suite/innodb/t/fk.test       | 61 +++++++++++++++++++++++++
 storage/innobase/dict/dict0dict.cc      |  1 +
 storage/innobase/row/row0mysql.cc       |  3 ++
 4 files changed, 66 insertions(+)
 create mode 100644 mysql-test/suite/innodb/t/fk-master.opt
 create mode 100644 mysql-test/suite/innodb/t/fk.test

diff --git a/mysql-test/suite/innodb/t/fk-master.opt b/mysql-test/suite/innodb/t/fk-master.opt
new file mode 100644
index 00000000000..a631c117e8c
--- /dev/null
+++ b/mysql-test/suite/innodb/t/fk-master.opt
@@ -0,0 +1 @@
+--debug-sync-timeout=3600
\ No newline at end of file
diff --git a/mysql-test/suite/innodb/t/fk.test b/mysql-test/suite/innodb/t/fk.test
new file mode 100644
index 00000000000..a137f04a9ae
--- /dev/null
+++ b/mysql-test/suite/innodb/t/fk.test
@@ -0,0 +1,61 @@
+#--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;
+ 
+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);
+ 
+--connection con1
+set debug_sync='fts_init_doc_id_for_ref_2  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;
\ No newline at end of file
diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc
index ff66dc32792..51859c31fb9 100644
--- a/storage/innobase/dict/dict0dict.cc
+++ b/storage/innobase/dict/dict0dict.cc
@@ -3518,6 +3518,7 @@ 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<dict_foreign_set::iterator, bool> ret =
         ref_table->referenced_set.insert(for_in_cache);
diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc
index 5cf4ff3bf5c..5712bfc34e0 100644
--- a/storage/innobase/row/row0mysql.cc
+++ b/storage/innobase/row/row0mysql.cc
@@ -1883,6 +1883,9 @@ static void init_fts_doc_id_for_ref(
     foreign = *it;
 
     ut_ad(foreign->foreign_table != nullptr);
+    if(!strncmp(foreign->foreign_table->name.m_name,"test/t3",7)) {
+      DEBUG_SYNC_C("fts_init_doc_id_for_ref_2");
+    }
 
     if (foreign->foreign_table->fts != nullptr) {
       fts_init_doc_id(foreign->foreign_table);
-- 
2.43.0

Suggested fix:
in init_fts_doc_id_for_ref, check FK type and do not follow FK with no referential action