NDB Cluster 似乎忽略了 foreign_key_checks=OFF

根據本地變數的MySQL文件foreign_key_checks

從 MySQL Cluster NDB 7.3.2 開始，設置此變數對 NDB 表的影響與對 InnoDB 表的效果相同——以前，該設置被忽略並強制執行所有此類檢查（錯誤 #14095855）。

我應該能夠使用外鍵導入無序的 InnoDB 轉儲文件。

我的 NDB 版本

# ndbd --version
MySQL distrib mysql-5.6.21 ndb-7.3.7, for Linux (x86_64)

但是當我試圖添加一個以

SET foreign_key_checks=OFF;

--
-- Table structure for table `aclAktion`
--

DROP TABLE IF EXISTS `aclAktion`;
CREATE TABLE `aclAktion` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `editMask` set('read','write','create','delete','super') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'read,write,create,delete',
 PRIMARY KEY (`id`),
 UNIQUE KEY `aclAktion_uIdx_name` (`name`)
) ENGINE=NDBCLUSTER ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `aclGroup`
--

DROP TABLE IF EXISTS `aclGroup`;

SHOW VARIABLES LIKE 'foreign_key_checks';
CREATE TABLE `aclGroup` (  -- this is line #63 in the dumpfile
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `aclRole_id` enum('gast','kunde','owner','owner_boss','red_ext','red_ext_boss','red_int','red_int_boss','verlag_boss','sysop','root') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'gast',
 `numUsers` int(11) NOT NULL DEFAULT '0',
 `name` varchar(64) NOT NULL DEFAULT '',
 `aktiv` tinyint(1) NOT NULL DEFAULT '0',
 `login` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `aclGroup_uIdx_name` (`name`),
 KEY `aclGroup_idx_aclRole_id` (`aclRole_id`),
 CONSTRAINT `aclGroup_fk_aclRole_id` FOREIGN KEY (`aclRole_id`) REFERENCES `aclRole` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=NDBCLUSTER ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `aclRole`
--

DROP TABLE IF EXISTS `aclRole`;
CREATE TABLE `aclRole` (
 `id` enum('gast','kunde','owner','owner_boss','red_ext','red_ext_boss','red_int','red_int_boss','verlag_boss','sysop','root') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'gast',
 `numGroups` int(11) NOT NULL DEFAULT '0',
 `login` tinyint(1) NOT NULL DEFAULT '0',
 `checkNumLogins` tinyint(1) NOT NULL DEFAULT '0',
 `defaultPrivileges` set('read','write','create','delete','super') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'read',
 `beschreibung` varchar(192) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`)
) ENGINE=NDBCLUSTER ROW_FORMAT=DYNAMIC;

我收到一個錯誤：

ERROR 1215 (HY000) at line 63: Cannot add foreign key constraint

API 節點上的錯誤日誌顯示 NDB 實際上嘗試正確處理外鍵

2014-11-03 17:50:05 [NdbApi] INFO     -- Flushing incomplete GCI:s < 10865/8
2014-11-03 17:50:05 [NdbApi] INFO     -- Flushing incomplete GCI:s < 10865/8
2014-11-03 17:50:05 22485 [Note] NDB Binlog: starting log at epoch 10865/8
2014-11-03 17:50:05 22485 [Note] NDB Binlog: ndb tables writable
2014-11-03 17:50:05 22485 [Note] NDB Binlog: Node: 2, subscribe from node 4, Subscriber bitmask 010
2014-11-03 17:50:05 22485 [Note] NDB Binlog: Node: 3, subscribe from node 4, Subscriber bitmask 010
2014-11-03 17:50:08 22485 [Note] NDB Binlog: Node: 2, unsubscribe from node 4, Subscriber bitmask 00
2014-11-03 17:50:08 22485 [Note] NDB Binlog: Node: 3, unsubscribe from node 4, Subscriber bitmask 00
2014-11-03 17:50:16 22485 [Note] NDB Binlog: Node: 2, subscribe from node 4, Subscriber bitmask 010
2014-11-03 17:50:16 22485 [Note] NDB Binlog: Node: 3, subscribe from node 4, Subscriber bitmask 010
2014-11-03 18:01:29 22485 [Note] NDB Binlog: CREATE TABLE Event: REPL$database/aclAktion
2014-11-03 18:01:29 22485 [Note] NDB Binlog: logging ./database/aclAktion (UPDATED,USE_WRITE)
2014-11-03 18:01:29 22485 [Note] NDB FK: Created mock table 'NDB$FKM_20_0_aclRole' referenced by 'aclGroup'

ndb 節點上沒有錯誤

看來，變數foreign_key_checks = off在這裡被忽略了。有什麼提示嗎？

模擬表是很久以前在舊版本的 MySQL 中載入 mysqldump 的方式

存在這樣的問題

• 如果 dest 已經有表，則載入 mysqldump 腳本可能會失敗並出現錯誤 1215
• 外鍵：mysqldump 應該尊重外鍵

看到最後一行

2014-11-03 18:01:29 22485 [Note] NDB FK: Created mock table 'NDB$FKM_20_0_aclRole' referenced by 'aclGroup'

由於 mysqldumps 預設按字母順序排列，因此在某些情況下，外鍵關係的父表會出現在子表之後而不是之前。因此，創建模擬表是為了讓父級採用 aclGroup 表。

由於一個子節點在同一列上不能對同一父節點有多個外鍵引用，因此 NDB 忽略它並仍在嘗試強制執行foreign_key_checks=OFF

在幕後，這是 NDB 儲存引擎應該做的事情CREATE TABLE aclGroup

ALTER TABLE aclGroup DROP CONSTRAINT aclGroup_fk_aclRole_id;
DROP TABLE `NDB$FKM_20_0_aclRole`;
DROP TABLE IF NOT EXISTS aclRole;
CREATE TABLE aclRole (...) ENGINE=NDBCLUSTER;
ALTER TABLE aclGroup ADD CONSTRAINT aclGroup_fk_aclRole_id
FOREIGN KEY (`aclRole_id`) REFERENCES `aclRole` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;    

如果您查看Dump of foreign key constraint with mysqldump not sorted，您會注意到這是很久以前對 InnoDB 的功能請求。NDB 中可能沒有正確實現相同的功能。我敢肯定，如果您在腳本中更改為NDBCLUSTER並InnoDB執行它，它將毫無意外地工作。

所以，是的，foreign_key_checks=OFF被忽略了。

引用自：https://dba.stackexchange.com/questions/81743