缺少插入的行

June 15, 2021

當事務中的一組查詢（在 MariaDB 10.2.38 中）以某種方式導致後來失去行時，我遇到了一個奇怪的 heisenbug。我的意思是事務中插入了一些行，但是當事務結束時，它們在表中失去了。

例如，程式碼執行一組這樣的查詢：

INSERT INTO `ffxiv__freecompany` (
                   `freecompanyid`, `name`, `serverid`, `formed`, `registered`, `updated`, `deleted`, `grandcompanyid`, `tag`, `crest`,
                   `rank`, `slogan`, `activeid`, `recruitment`, `communityid`, `estate_zone`, `estateid`, `estate_message`, `Role-playing`, `Leveling`, `Casual`, `Hardcore`, `Dungeons`,
                   `Guildhests`, `Trials`, `Raids`, `PvP`, `Tank`, `Healer`, `DPS`, `Crafter`, `Gatherer`
               )
               VALUES (
                   '9236179148295239722', 'The Foundation', (SELECT `serverid` FROM `ffxiv__server` WHERE `server`='Balmung'), '2021-01-09', UTC_DATE(), UTC_TIMESTAMP(), NULL,
       (SELECT `gcrankid` FROM `ffxiv__grandcompany_rank` WHERE `gc_name`='Maelstrom' ORDER BY `gcrankid` LIMIT 1), 'Ruina', NULL, '15', NULL,
       (SELECT `activeid` FROM `ffxiv__timeactive` WHERE `active`=NULL AND `active` IS NOT NULL LIMIT 1), '0', NULL, NULL,
       (SELECT `estateid` FROM `ffxiv__estate` WHERE CONCAT('Plot ', `plot`, ', ', `ward`, ' Ward, ', `area`, ' (', CASE WHEN `size` = 1 THEN 'Small' WHEN `size` = 2 THEN 'Medium' WHEN `size` = 3 THEN 'Large' END, ')')=NULL LIMIT 1),
       NULL, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'
               )
               ON DUPLICATE KEY UPDATE
                   `name`='The Foundation',
                   `serverid`=(SELECT `serverid` FROM `ffxiv__server` WHERE `server`='Balmung'),
                   `updated`=UTC_TIMESTAMP(), `deleted`=NULL, `tag`='Ruina', `crest`=COALESCE(NULL, `crest`), `rank`='15', `slogan`=NULL,
                   `activeid`=(SELECT `activeid` FROM `ffxiv__timeactive` WHERE `active`=NULL AND `active` IS NOT NULL LIMIT 1),
                   `recruitment`='0', `communityid`=NULL, `estate_zone`=NULL,
                   `estateid`=(SELECT `estateid` FROM `ffxiv__estate` WHERE CONCAT('Plot ', `plot`, ', ', `ward`, ' Ward, ', `area`, ' (', CASE WHEN `size` = 1 THEN 'Small' WHEN `size` = 2 THEN 'Medium' WHEN `size` = 3 THEN 'Large' END, ')')=NULL LIMIT 1),
                   `estate_message`=NULL, `Role-playing`='0', `Leveling`='0', `Casual`='0', `Hardcore`='0', `Dungeons`='0', `Guildhests`='0', `Trials`='0', `Raids`='0', `PvP`='0', `Tank`='0', `Healer`='0', `DPS`='0', `Crafter`='0', `Gatherer`='0';
UPDATE `name`=`name`;

INSERT INTO `ffxiv__freecompany_names`(`freecompanyid`, `name`)
   VALUES ('9236179148295239722', 'The Foundation')
   ON DUPLICATE KEY UPDATE `name`=`name`;

INSERT IGNORE INTO `ffxiv__freecompany_x_character`
   (`characterid`, `freecompanyid`)
   SELECT `ffxiv__freecompany_character`.`characterid`, `ffxiv__freecompany_character`.`freecompanyid`
       FROM `ffxiv__freecompany_character`
       WHERE `ffxiv__freecompany_character`.`freecompanyid`='9236179148295239722'
         AND `ffxiv__freecompany_character`.`characterid` NOT IN ('9829366','33753934','30047770','35007019','21443691');

DELETE FROM `ffxiv__freecompany_character`
      WHERE `freecompanyid`='9236179148295239722'
        AND `ffxiv__freecompany_character`.`characterid` NOT IN ('9829366','33753934','30047770','35007019','21443691');

INSERT INTO `ffxiv__freecompany_rank` (`freecompanyid`, `rankid`, `rankname`) VALUE ('9236179148295239722', '0', 'Leader') ON DUPLICATE KEY UPDATE `rankname`='Leader'

INSERT INTO `ffxiv__freecompany_character` (`characterid`, `freecompanyid`, `join`, `rankid`) VALUES ('9829366', '9236179148295239722', UTC_DATE(), '0') ON DUPLICATE KEY UPDATE `rankid`='0';

INSERT INTO `ffxiv__freecompany_rank` (`freecompanyid`, `rankid`, `rankname`) VALUE ('9236179148295239722', '1', 'Founder') ON DUPLICATE KEY UPDATE `rankname`='Founder'

INSERT INTO `ffxiv__freecompany_character` (`characterid`, `freecompanyid`, `join`, `rankid`) VALUES ('33753934', '9236179148295239722', UTC_DATE(), '1') ON DUPLICATE KEY UPDATE `rankid`='1';

INSERT INTO `ffxiv__freecompany_rank` (`freecompanyid`, `rankid`, `rankname`) VALUE ('9236179148295239722', '3', 'Officer') ON DUPLICATE KEY UPDATE `rankname`='Officer'

INSERT INTO `ffxiv__freecompany_character` (`characterid`, `freecompanyid`, `join`, `rankid`) VALUES ('30047770', '9236179148295239722', UTC_DATE(), '3') ON DUPLICATE KEY UPDATE `rankid`='3';

INSERT INTO `ffxiv__freecompany_rank` (`freecompanyid`, `rankid`, `rankname`) VALUE ('9236179148295239722', '3', 'Officer') ON DUPLICATE KEY UPDATE `rankname`='Officer'

INSERT INTO `ffxiv__freecompany_character` (`characterid`, `freecompanyid`, `join`, `rankid`) VALUES ('35007019', '9236179148295239722', UTC_DATE(), '3') ON DUPLICATE KEY UPDATE `rankid`='3';

INSERT INTO `ffxiv__freecompany_rank` (`freecompanyid`, `rankid`, `rankname`) VALUE ('9236179148295239722', '3', 'Officer') ON DUPLICATE KEY UPDATE `rankname`='Officer'

INSERT INTO `ffxiv__freecompany_character` (`characterid`, `freecompanyid`, `join`, `rankid`) VALUES ('21443691', '9236179148295239722', UTC_DATE(), '3') ON DUPLICATE KEY UPDATE `rankid`='3';

完成後，我希望在中有相應的新行ffxiv__freecompany_character，但是……它們不見了。然而，沒有報告錯誤（PHP 程式碼會跟踪它們，並且確實會按預期擷取事務中的失敗）。

在試圖以某種方式捕捉到這一點時，我嘗試執行類似的命令

INSERT INTO `ffxiv__freecompany_character`
   (`characterid`, `freecompanyid`, `join`, `rankid`)
   VALUES ('21443691', '9236179148295239722', UTC_DATE(), '3')
   ON DUPLICATE KEY UPDATE `rankid`='3';
SELECT * FROM `ffxiv__freecompany_character`
   WHERE `characterid`='21443691';

手動一次（而且只有一次！）我確實經歷過這種情況，執行這 2 個命令導致返回 0 行，即使INSERT報告了 1 個新行。現在，在PROD

上，有幾個程序同時執行這樣的查詢集（具有不同的 ID），所以我認為這可能與隔離級別（READ COMMITTED看看為什麼這會導致這種行為，除非我主動刪除行，然後重新插入（事實並非如此）。

由於與 DB 的所有通信都是通過庫完成的，因此我嘗試記錄它呼叫的所有查詢，但即便如此 - 沒有發現隨機 DELETE 或類似的東西，這可能會刪除行。此外，它似乎是隨機的，因為在 1 種情況下它按預期工作，但隨後有幾組像這樣失敗。

因此，在嘗試擷取錯誤並出於可能的原因/解決方案進行Google搜尋近 5 天后，我現在不知所措，因此我正在聯繫更多知識淵博的 DBA：什麼會導致這種行為？這可能是隔離級別問題嗎？或者可能是隔離級別的一些設置？還是完全不同的東西？

謝謝你。

我已經啟用general log以查看發生了什麼。而且…它顯示了與管理數據庫連接的 PHP 程式碼日誌相同的內容：

210605 19:05:57   2102 Query    SELECT `characterid` FROM `ffxiv__character` WHERE `characterid` IN ('23038801','18185027')
         2102 Query    START TRANSACTION
         2102 Query    INSERT INTO `ffxiv__freecompany` (
                   `freecompanyid`, `name`, `serverid`, `formed`, `registered`, `updated`, `deleted`, `grandcompanyid`, `tag`, `crest`, `rank`, `slogan`, `activeid`, `recruitment`, `communityid`, `estate_zone`, `estateid`, `estate_message`, `Role-playing`, `Leveling`, `Casual`, `Hardcore`, `Dungeons`, `Guildhests`, `Trials`, `Raids`, `PvP`, `Tank`, `Healer`, `DPS`, `Crafter`, `Gatherer`
               )
               VALUES (
                   '9226608999087051355', 'Oztroja', (SELECT `serverid` FROM `ffxiv__server` WHERE `server`='Asura'), '2018-11-07', UTC_DATE(), UTC_TIMESTAMP(), NULL, (SELECT `gcrankid` FROM `ffxiv__grandcompany_rank` WHERE `gc_name`='Immortal Flames' ORDER BY `gcrankid` LIMIT 1), 'oz', '5977392b16d00d3f45f82d92bdfa081732210e2b2e7bff2062e3b4256da47638', '29', NULL, (SELECT `activeid` FROM `ffxiv__timeactive` WHERE `active`='Always' AND `active` IS NOT NULL LIMIT 1), '1', NULL, 'oz18-41', (SELECT `estateid` FROM `ffxiv__estate` WHERE CONCAT('Plot ', `plot`, ', ', `ward`, ' Ward, ', `area`, ' (', CASE WHEN `size` = 1 THEN 'Small' WHEN `size` = 2 THEN 'Medium' WHEN `size` = 3 THEN 'Large' END, ')')='Plot 28, 20 Ward, The Lavender Beds (Large)' LIMIT 1), NULL, '1', '1', '1', '0', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1'
               )
               ON DUPLICATE KEY UPDATE
                   `name`='Oztroja', `serverid`=(SELECT `serverid` FROM `ffxiv__server` WHERE `server`='Asura'), `updated`=UTC_TIMESTAMP(), `deleted`=NULL, `tag`='oz', `crest`=COALESCE('5977392b16d00d3f45f82d92bdfa081732210e2b2e7bff2062e3b4256da47638', `crest`), `rank`='29', `slogan`=NULL, `activeid`=(SELECT `activeid` FROM `ffxiv__timeactive` WHERE `active`='Always' AND `active` IS NOT NULL LIMIT 1), `recruitment`='1', `communityid`=NULL, `estate_zone`='oz18-41', `estateid`=(SELECT `estateid` FROM `ffxiv__estate` WHERE CONCAT('Plot ', `plot`, ', ', `ward`, ' Ward, ', `area`, ' (', CASE WHEN `size` = 1 THEN 'Small' WHEN `size` = 2 THEN 'Medium' WHEN `size` = 3 THEN 'Large' END, ')')='Plot 28, 20 Ward, The Lavender Beds (Large)' LIMIT 1), `estate_message`=NULL, `Role-playing`='1', `Leveling`='1', `Casual`='1', `Hardcore`='0', `Dungeons`='1', `Guildhests`='1', `Trials`='1', `Raids`='1', `PvP`='1', `Tank`='1', `Healer`='1', `DPS`='1', `Crafter`='1', `Gatherer`='1'
         2102 Query    INSERT INTO `ffxiv__freecompany_names`(`freecompanyid`, `name`) VALUES ('9226608999087051355', 'Oztroja') ON DUPLICATE KEY UPDATE `name`=`name`
         2102 Query    INSERT IGNORE INTO `ffxiv__freecompany_x_character` (`characterid`, `freecompanyid`) SELECT `ffxiv__freecompany_character`.`characterid`, `ffxiv__freecompany_character`.`freecompanyid` FROM `ffxiv__freecompany_character` WHERE `ffxiv__freecompany_character`.`freecompanyid`='9226608999087051355' AND `ffxiv__freecompany_character`.`characterid` NOT IN ('23038801','18185027')
         2102 Query    DELETE FROM `ffxiv__freecompany_character` WHERE `freecompanyid`='9226608999087051355' AND `ffxiv__freecompany_character`.`characterid` NOT IN ('23038801','18185027')
         2102 Query    INSERT INTO `ffxiv__freecompany_rank` (`freecompanyid`, `rankid`, `rankname`) VALUE ('9226608999087051355', '0', 'マスター') ON DUPLICATE KEY UPDATE `rankname`='マスター'
         2102 Query    INSERT INTO `ffxiv__freecompany_character` (`characterid`, `freecompanyid`, `join`, `rankid`) VALUES ('23038801', '9226608999087051355', UTC_DATE(), '0') ON DUPLICATE KEY UPDATE `rankid`='0'
         2102 Query    INSERT INTO `ffxiv__freecompany_rank` (`freecompanyid`, `rankid`, `rankname`) VALUE ('9226608999087051355', '1', 'リカバリーガール') ON DUPLICATE KEY UPDATE `rankname`='リカバリーガール'
         2102 Query    INSERT INTO `ffxiv__freecompany_character` (`characterid`, `freecompanyid`, `join`, `rankid`) VALUES ('18185027', '9226608999087051355', UTC_DATE(), '1') ON DUPLICATE KEY UPDATE `rankid`='1'
         2102 Query    COMMIT
         2102 Query    DELETE FROM `cron__schedule` WHERE `task`='ffentityupdate' AND `arguments`='[\"9226608999087051355\",\"freecompany\"]'

之後，沒有提及9226608999087051355（公司 ID）和 2 個字元（23038801, 18185027）中的任何一個。

不管我怎麼看，這對我來說沒有任何意義。

我可能唯一的另一個想法是插入的字元 idffxiv__freecompany_character是字元串，而實際表使用 INT，但它會始終影響所有公司（因為使用相同的程式碼）並且不會在另一次嘗試中正確插入行。

相關表格的結構：

CREATE TABLE `ffxiv__freecompany` (
 `freecompanyid` bigint(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)',
 `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Free Company name',
 `serverid` tinyint(2) UNSIGNED NOT NULL COMMENT 'ID of the server Free Company resides on',
 `formed` date NOT NULL DEFAULT current_timestamp() COMMENT 'Free Company formation day as seen on Lodestone',
 `registered` date NOT NULL DEFAULT current_timestamp() COMMENT 'When Free Company was initially added to tracker',
 `updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'When Free Company was last updated on the tracker',
 `deleted` date DEFAULT NULL COMMENT 'Date when Free Company was marked as deleted',
 `grandcompanyid` tinyint(2) UNSIGNED NOT NULL COMMENT 'ID of Grand Company affiliated with the Free Company',
 `tag` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Short name of Free Company',
 `crest` char(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name (hash) of image representing merged crest for the company (generated on each company update from 1 to 3 images on Lodestone)',
 `rank` tinyint(2) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Company level',
 `slogan` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Public message shown on company board as seen on Lodestone',
 `activeid` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'ID of active time as registered on tracker',
 `recruitment` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company is recruiting or not',
 `communityid` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Community ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/community_finder/communityid/)',
 `estate_zone` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Name of estate',
 `estateid` smallint(5) UNSIGNED DEFAULT NULL COMMENT 'Estate ID as registered by the tracker',
 `estate_message` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Greeting on estate board as shown on Lodestone',
 `Role-playing` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company participates in role-playing',
 `Leveling` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company participates in leveling',
 `Casual` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company participates in casual activities',
 `Hardcore` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company participates in hardcore activities',
 `Dungeons` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company participates in dungeons',
 `Guildhests` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company participates in guildhests',
 `Trials` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company participates in trials',
 `Raids` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company participates in raids',
 `PvP` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company participates in PvP',
 `Tank` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company is looking for tanks',
 `Healer` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company is looking for healers',
 `DPS` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company is looking for DPSs',
 `Crafter` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company is looking for crafters',
 `Gatherer` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Whether company is looking for gatherers'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;

CREATE TABLE `ffxiv__freecompany_character` (
 `characterid` int(10) UNSIGNED NOT NULL COMMENT 'Character ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/character/characterid/)',
 `freecompanyid` bigint(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)',
 `join` date NOT NULL DEFAULT current_timestamp() COMMENT 'Date when character joined company as identified by tracker',
 `rankid` tinyint(2) UNSIGNED DEFAULT NULL COMMENT 'ID calculated based on rank icon on Lodestone'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;

CREATE TABLE `ffxiv__freecompany_names` (
 `freecompanyid` bigint(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)',
 `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Previous name of the company'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;

CREATE TABLE `ffxiv__freecompany_rank` (
 `freecompanyid` bigint(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)',
 `rankid` tinyint(2) UNSIGNED NOT NULL COMMENT 'ID calculated based on rank icon on Lodestone',
 `rankname` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the rank as reported by Lodestone'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;

CREATE TABLE `ffxiv__freecompany_x_character` (
 `characterid` int(10) UNSIGNED NOT NULL COMMENT 'Character ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/character/characterid/)',
 `freecompanyid` bigint(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;


ALTER TABLE `ffxiv__freecompany`
 ADD PRIMARY KEY (`freecompanyid`) USING BTREE,
 ADD KEY `serverid_fc` (`serverid`),
 ADD KEY `grandcompanyid` (`grandcompanyid`),
 ADD KEY `estateid` (`estateid`),
 ADD KEY `activeid` (`activeid`),
 ADD KEY `registered` (`registered`),
 ADD KEY `updated` (`updated`),
 ADD KEY `deleted` (`deleted`),
 ADD KEY `communityid` (`communityid`),
 ADD KEY `name_order` (`name`);
ALTER TABLE `ffxiv__freecompany` ADD FULLTEXT KEY `name` (`name`,`tag`,`slogan`,`estate_zone`,`estate_message`);

ALTER TABLE `ffxiv__freecompany_character`
 ADD PRIMARY KEY (`characterid`) USING BTREE,
 ADD KEY `fc_char_fcid` (`freecompanyid`),
 ADD KEY `fc_char_rankid` (`rankid`);

ALTER TABLE `ffxiv__freecompany_names`
 ADD PRIMARY KEY (`freecompanyid`, `name`);

ALTER TABLE `ffxiv__freecompany_rank`
 ADD PRIMARY KEY (`freecompanyid`, `rankid`),
 ADD KEY `rankid` (`rankid`);

ALTER TABLE `ffxiv__freecompany_x_character`
 ADD PRIMARY KEY (`characterid`,`freecompanyid`),
 ADD KEY `fc_xchar_fc` (`freecompanyid`);


ALTER TABLE `ffxiv__freecompany`
 ADD CONSTRAINT `activeid` FOREIGN KEY (`activeid`) REFERENCES `ffxiv__timeactive` (`activeid`) ON DELETE SET NULL ON UPDATE SET NULL,
 ADD CONSTRAINT `estateid` FOREIGN KEY (`estateid`) REFERENCES `ffxiv__estate` (`estateid`) ON DELETE SET NULL ON UPDATE SET NULL,
 ADD CONSTRAINT `grandcompanyid` FOREIGN KEY (`grandcompanyid`) REFERENCES `ffxiv__grandcompany_rank` (`gcrankid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 ADD CONSTRAINT `serverid_fc` FOREIGN KEY (`serverid`) REFERENCES `ffxiv__server` (`serverid`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ffxiv__freecompany_character`
 ADD CONSTRAINT `fc_char_charid` FOREIGN KEY (`characterid`) REFERENCES `ffxiv__character` (`characterid`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `fc_char_fcid` FOREIGN KEY (`freecompanyid`) REFERENCES `ffxiv__freecompany` (`freecompanyid`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `fc_char_rankid` FOREIGN KEY (`rankid`) REFERENCES `ffxiv__freecompany_rank` (`rankid`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ffxiv__freecompany_names`
 ADD CONSTRAINT `fc_names_id` FOREIGN KEY (`freecompanyid`) REFERENCES `ffxiv__freecompany` (`freecompanyid`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ffxiv__freecompany_rank`
 ADD CONSTRAINT `fcranks_freecompany` FOREIGN KEY (`freecompanyid`) REFERENCES `ffxiv__freecompany` (`freecompanyid`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ffxiv__freecompany_x_character`
 ADD CONSTRAINT `fc_xchar_fc` FOREIGN KEY (`freecompanyid`) REFERENCES `ffxiv__freecompany` (`freecompanyid`) ON DELETE CASCADE ON UPDATE CASCADE,
 ADD CONSTRAINT `fc_xchar_id` FOREIGN KEY (`characterid`) REFERENCES `ffxiv__character` (`characterid`) ON DELETE CASCADE ON UPDATE CASCADE;

雖然我將嘗試以某種一致的方式複制此問題，但我已在https://jira.mariadb.org/browse/MDEV-25862上報告了此錯誤

VARCHAR 沒有幫助。並且轉換回 BIGINT 失敗，Duplicate entry '9223372036854775807' for key 'PRIMARY'暗示 SQL 忽略 UNSIGNED 標誌。

總結和結束（至少現在）這個問題：
原始問題源於這樣一個事實，即 MySQL 不保證 BIGINT 與字元串（VARCHAR 或非 VARCHAR）的有效和一致比較，如https://bugs.mysql.com/bug.php?id=103992中所確認
PHP 目前不支持 BIGINT。即使獲取字元串這樣的值是可以接受的（至少有時是這樣），但不能將它們綁定為實際的 BIGINT。這在上面的 MySQL 錯誤報告和https://bugs.php.net/bug.php?id=81143中都突出顯示
在我解決問題的測試期間，我確實遇到了一些情況，在 PHP 和 DB 中使用字元串仍然會導致數據失去。我不是 100% 肯定，但看起來我有一種競爭條件，在某些情況下可能會發生。調整邏輯似乎有所幫助。
將 VARCHAR 值轉換回 BIGINT UNSIGNED 的問題純粹是 MariaDB 之一 - https://jira.mariadb.org/browse/MDEV-25862

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

缺少插入的行

相關問答

查找數據庫中任何表的最後更新/插入/刪除的實用方法？

MySQL/MariaDB：SELECT 查詢可以阻止 INSERT 查詢嗎？

如何在 MariaDB 表中查找未使用的列

Zabbix MariaDB 將項目數據複製到另一個項目

MariaDB InnoDB 不會在超時時回滾

如何在 MySQL/MariaDB 中使用 FIRST_VALUE 視窗函式來獲取每組一行？