1

I am having a strange heisenbug when a set of queries in a transaction (in MariaDB 10.2.38) somehow results in missing rows afterward. I mean that there are rows inserted within the transaction, but when the transaction ends, they are missing in the table.
For example, code runs a set of queries like this:

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';

After it's done I expect to have respective new rows in ffxiv__freecompany_character, but... They are missing. And yet, no errors are reported (PHP code tracks them and it does catch failure in transactions as expected).
While trying to catch this somehow I've tried running commands like

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';

manually and one time (and only one!) I did experience the case, that running these 2 commands resulted in 0 rows being returned, even though INSERT reported 1 new row.
Now, on PROD there are several processes running sets of queries like that (with different IDs) simultaneously, so I think this may be related to isolation level (READ COMMITTED), but even after re-reading explanations about isolation levels several times, I do not see why this would result in such behavior unless I was actively removing the rows, that I was then reinserting (which is not the case).
Since all communication with DB is done through a library, I tried logging all queries it called, but even then - no random DELETEs or anything like that was identified, that could remove the rows. Besides, it seems random, because in 1 case it works as expected, but then several sets like this fail.

So, I am at a loss now after almost 5 days trying to catch the bug and googling for possible reasons/solution, thus am reaching out to more knowledgeable DBAs: what can result in such behavior? Can this be an isolation level issue? Or perhaps some settings for the isolation level? Or something else entirely?
Thank you.

I've enabled general log to see what's going on. And... It showed the same thing as log for the PHP code, that manages database connections:

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\"]'

After that, there were no mentions of neither 9226608999087051355 (company ID) nor any of the 2 characters (23038801, 18185027).
No matter how I look at it, it does not make any sense to me.
The only other idea I may have is that character id being inserted to ffxiv__freecompany_character are strings, while the actual table uses INT, but then it would affect all companies always (since using same code) and will not proper INSERT the rows on another try.

Structre of the tables in question:

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;

While I will be trying to replicate this issue in some consistent way, I've reported this bug on https://jira.mariadb.org/browse/MDEV-25862

VARCHAR did not help. And conversion to back to BIGINT fails with Duplicate entry '9223372036854775807' for key 'PRIMARY' implying SQL ignoring UNSIGNED flag.

Improve this question
1
  • @RickJames, I am more interested in ffxiv__freecompany_characters (can't validate names with the source, since I do not know whether name actually has changed and thus something new needs to be added), but regardless I've added structure of all the tables in question to the question.
    – Simbiat
    Jun 5, 2021 at 19:40

2 Answers 2

Reset to default
2

To summarize and close (at least for now) this question:

  1. Original issue stems from the fact, that MySQL does not guarantee valid and consistent comparison of BIGINT against strings (VARCHAR or not) as confirmed in https://bugs.mysql.com/bug.php?id=103992
  2. PHP currently does not support BIGINT. And even if getting such values as strings can be acceptable (at least at times), inability to bind them as actual BIGINTs is not. This was highlighted in MySQL bug report above and also in https://bugs.php.net/bug.php?id=81143
  3. During my tests to solve the issue, I did face cases, when using strings both in PHP and in DB still resulted in loss of data. I am not 100% sure, but it looks I had a sort of race condition, that could occur under some circumstances. Adjusting the logic seems to have helped.
  4. Issue with converting of VARCHAR values back to BIGINT UNSIGNED is purely MariaDB one - https://jira.mariadb.org/browse/MDEV-25862
Improve this answer
1 
SET autocommit = ON;
INSERT ...
pause
SELECT ...

During the 'pause' some other thread could DELETE or UPDATE that row. In this case, the SELECT would not find it.

BEGIN;
INSERT ...
pause
SELECT ...
COMMIT;

Now, the INSERT and SELECT are part of the same transaction. No other thread can mess with row in question. The SELECT will find the row. (There is still a chance that some other connection could DELETE the row after that COMMIT.)

Another way to debug this is to turn on the "general log". That log will show all the queries from all connections in chronological order. (Be sure to turn off the general log when finished; it is a disk hog.)

9226608999087051355 = 2^63-1 is the max value for BIGINT UNSIGNED. I don't see where exactly it is happening, but I think the value is indicative of hitting the limit and getting stuck there.

Check for errors and warnings after each statement.

Workaround?

Change that column to VARCHAR(25) and don't use BIGINT anywhere near that column. And be sure not to tell Json conversion to treat it as numeric.

Improve this answer
1
  • 1
    Comments are not for extended discussion; this conversation has been moved to chat.
    – Hannah Vernon ♦
    Jun 9, 2021 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.