Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2.38, 10.5.10
    • None
    • Data types
    • None
    • For 10.2.38: Linux cpanel8.c.fozzy.com 3.10.0-962.3.2.lve1.5.49.el7.x86_64 #1 SMP Thu Mar 4 05:39:46 EST 2021 x86_64 x86_64 x86_64 GNU/Linux
      For 10.5.10: Windows 10.0.19042.985 x64

    Description

      I have encountered a strange behavior/inconsistency with how MariaDB is working with large values of BIGINT UNSIGNED. It appears that if BIGINT UNSIGNED is used and a value is larger than 9223372036854775807 (max value of BIGINT SIGNED) insert of rows with such values can fail under unknown conditions. It plagues transactions mainly, but I did have 1 case outside of a transaction as well.
      Code samples and more details can be checked in https://dba.stackexchange.com/questions/293791/missing-an-inserted-row
      I will be trying to get a consistent way to replicate this and update both StackExchange and this bug report as soon as I find any new info.

      Attachments

        Issue Links

          Activity

            did you try not to quote numbers? Like in

            INSERT INTO ... VALUES (9236179148295239722, ...) ...
            

            instead of

            INSERT INTO ... VALUES ('9236179148295239722', ...) ...
            

            serg Sergei Golubchik added a comment - did you try not to quote numbers? Like in INSERT INTO ... VALUES (9236179148295239722, ...) ... instead of INSERT INTO ... VALUES ( '9236179148295239722' , ...) ...
            Simbiat Dmitry Kustov added a comment -

            The code is ran from PHP using PDO and arguments binding. Unfortunately in this case it seems to be required to use bind BIGINT as strings, because otherwise it fails if I try to bind it as INT, since for PHP this value will be too large for an INT.
            But this is a valid point, I will include this while trying to simulate the issue consistently.

            Simbiat Dmitry Kustov added a comment - The code is ran from PHP using PDO and arguments binding. Unfortunately in this case it seems to be required to use bind BIGINT as strings, because otherwise it fails if I try to bind it as INT, since for PHP this value will be too large for an INT. But this is a valid point, I will include this while trying to simulate the issue consistently.
            Simbiat Dmitry Kustov added a comment -

            I am unable to replicate this behavior manually. I've tried doing below (with varied different INSERTs in transaction), but no luck: rows are inserted normally. Currently my guess is that either there is some issue with PDO (which does not explain how I was able to replicate this once in manual mode using actual tables) or there is some magic going on due to several (up to 4) transactions being started at the same time, which I am not sure how to replicate in a given instance (like a fraction of a second or, at least, a second). Any ideas, what else I can try?

            CREATE TABLE `simbiatr_simbiat`.`bigIntTest` ( `testfield` BIGINT(20) UNSIGNED NOT NULL ) ENGINE = InnoDB;
            ALTER TABLE `bigIntTest` ADD PRIMARY(`testfield`);
            CREATE TABLE `simbiatr_simbiat`.`bigIntTest2` ( `testfield` BIGINT(20) UNSIGNED NOT NULL , `forindex` INT(10) UNSIGNED NOT NULL ) ENGINE = InnoDB;
            ALTER TABLE `bigIntTest2` ADD CONSTRAINT `bigIntTest` FOREIGN KEY (`testfield`) REFERENCES `bigIntTest`(`testfield`) ON DELETE CASCADE ON UPDATE CASCADE;
            ALTER TABLE `bigIntTest2` ADD PRIMARY KEY(`testfield`, `forindex`);
            START TRANSACTION;
            INSERT INTO `bigIntTest`(`testfield`) VALUES ('9226608999087051356') ON DUPLICATE KEY UPDATE `testfield`=`testfield`;
            DELETE FROM `bigIntTest2` WHERE `testfield`='9226608999087051356';
            INSERT INTO `bigIntTest2` (`testfield`,`forindex`) VALUES ('9226608999087051356', '1234567890') ON DUPLICATE KEY UPDATE `forindex`=`forindex`;
            COMMIT;
            

            Simbiat Dmitry Kustov added a comment - I am unable to replicate this behavior manually. I've tried doing below (with varied different INSERTs in transaction), but no luck: rows are inserted normally. Currently my guess is that either there is some issue with PDO (which does not explain how I was able to replicate this once in manual mode using actual tables) or there is some magic going on due to several (up to 4) transactions being started at the same time, which I am not sure how to replicate in a given instance (like a fraction of a second or, at least, a second). Any ideas, what else I can try? CREATE TABLE `simbiatr_simbiat`.`bigIntTest` ( `testfield` BIGINT (20) UNSIGNED NOT NULL ) ENGINE = InnoDB; ALTER TABLE `bigIntTest` ADD PRIMARY (`testfield`); CREATE TABLE `simbiatr_simbiat`.`bigIntTest2` ( `testfield` BIGINT (20) UNSIGNED NOT NULL , `forindex` INT (10) UNSIGNED NOT NULL ) ENGINE = InnoDB; ALTER TABLE `bigIntTest2` ADD CONSTRAINT `bigIntTest` FOREIGN KEY (`testfield`) REFERENCES `bigIntTest`(`testfield`) ON DELETE CASCADE ON UPDATE CASCADE ; ALTER TABLE `bigIntTest2` ADD PRIMARY KEY (`testfield`, `forindex`); START TRANSACTION ; INSERT INTO `bigIntTest`(`testfield`) VALUES ( '9226608999087051356' ) ON DUPLICATE KEY UPDATE `testfield`=`testfield`; DELETE FROM `bigIntTest2` WHERE `testfield`= '9226608999087051356' ; INSERT INTO `bigIntTest2` (`testfield`,`forindex`) VALUES ( '9226608999087051356' , '1234567890' ) ON DUPLICATE KEY UPDATE `forindex`=`forindex`; COMMIT ;
            Simbiat Dmitry Kustov added a comment -

            I converted BIGINT to VARCHAR and... It did not resolve this at all. Meaning, that the issue lies somewhere else, but so far I fail to see where exactly. I can only describe the process some more:
            I have a CRON job called every minute, which calls a PHP script (https://github.com/Simbiat/Cron) which gets a list of tasks and processes them.
            Up to 4 jobs like that are allowed to run at the same time.
            After I've noticed an issue with data, I had to populate the tasks manually crating ~100k tasks with differ only in `freecompanyid`.
            The above implies, that there is a chance that up to 4 jobs can start working with the table(s) at once (but with different `freecompanyid`).
            I check for "bad" companies using SELECT like this:

            SELECT `freecompanyid`, `updated` FROM `ffxiv__freecompany` WHERE `deleted` IS NULL AND `freecompanyid` NOT IN (SELECT `freecompanyid` FROM `ffxiv__freecompany_character`) ORDER BY `updated` DESC;
            

            I can see that the issue comes in "waves": for several minutes (maybe up to an hour even) - everything is processed fine, rows are added and then - BOOM - couple of dozens of new updated entries, that have no `characters` in `ffxiv__freecompany_character` despite characters being present on respective source pages (https://eu.finalfantasyxiv.com/lodestone/freecompany/%freecompanyid%/).
            Obviously first idea was, that the data grabbed was missing the characters - it did not.
            Then that queries are not being sent - they were (properly registered in general_log), no errors.

            Should I try to "READ UNCOMMITTED" or something?.. I mean, in my understanding, it should not be relevant considering the original set of queries, but I am out of ideas.

            Simbiat Dmitry Kustov added a comment - I converted BIGINT to VARCHAR and... It did not resolve this at all. Meaning, that the issue lies somewhere else, but so far I fail to see where exactly. I can only describe the process some more: I have a CRON job called every minute, which calls a PHP script ( https://github.com/Simbiat/Cron ) which gets a list of tasks and processes them. Up to 4 jobs like that are allowed to run at the same time. After I've noticed an issue with data, I had to populate the tasks manually crating ~100k tasks with differ only in `freecompanyid`. The above implies, that there is a chance that up to 4 jobs can start working with the table(s) at once (but with different `freecompanyid`). I check for "bad" companies using SELECT like this: SELECT `freecompanyid`, `updated` FROM `ffxiv__freecompany` WHERE `deleted` IS NULL AND `freecompanyid` NOT IN (SELECT `freecompanyid` FROM `ffxiv__freecompany_character`) ORDER BY `updated` DESC; I can see that the issue comes in "waves": for several minutes (maybe up to an hour even) - everything is processed fine, rows are added and then - BOOM - couple of dozens of new updated entries, that have no `characters` in `ffxiv__freecompany_character` despite characters being present on respective source pages ( https://eu.finalfantasyxiv.com/lodestone/freecompany/%freecompanyid%/ ). Obviously first idea was, that the data grabbed was missing the characters - it did not. Then that queries are not being sent - they were (properly registered in general_log), no errors. Should I try to "READ UNCOMMITTED" or something?.. I mean, in my understanding, it should not be relevant considering the original set of queries, but I am out of ideas.
            Simbiat Dmitry Kustov added a comment - - edited

            Interesting thing: when trying to convert BACK to BIGINT using

            SET FOREIGN_KEY_CHECKS=0;
            ALTER TABLE `ffxiv__freecompany` CHANGE `freecompanyid` `freecompanyid` BIGINT(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)';
            ALTER TABLE `ffxiv__freecompany_character` CHANGE `freecompanyid` `freecompanyid` BIGINT(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)';
            ALTER TABLE `ffxiv__freecompany_names` CHANGE `freecompanyid` `freecompanyid` BIGINT(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)';
            ALTER TABLE `ffxiv__freecompany_rank` CHANGE `freecompanyid` `freecompanyid` BIGINT(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)';
            ALTER TABLE `ffxiv__freecompany_ranking` CHANGE `freecompanyid` `freecompanyid` BIGINT(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)';
            ALTER TABLE `ffxiv__freecompany_x_character` CHANGE `freecompanyid` `freecompanyid` BIGINT(20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)';
            SET FOREIGN_KEY_CHECKS=1;
            

            I get...

            #1062 - Duplicate entry '9223372036854775807' for key 'PRIMARY'
            

            And 9223372036854775807 is the MAX value for... BIGINT SIGNED.

            UPDATE: doing the same on the test table (previously mentioned) with 2 entries valid for BIGINT UNSIGNED also fails with same error. This implies that there is an issue with how BIGINT UNSIGNED is treated.
            And if MySQL (or InnoDB?) does some type conversion for strings that look like BIGINT that could also explain why VARCHAR did not help. Although, there is something else in here.

            Simbiat Dmitry Kustov added a comment - - edited Interesting thing: when trying to convert BACK to BIGINT using SET FOREIGN_KEY_CHECKS=0; ALTER TABLE `ffxiv__freecompany` CHANGE `freecompanyid` `freecompanyid` BIGINT (20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)' ; ALTER TABLE `ffxiv__freecompany_character` CHANGE `freecompanyid` `freecompanyid` BIGINT (20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)' ; ALTER TABLE `ffxiv__freecompany_names` CHANGE `freecompanyid` `freecompanyid` BIGINT (20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)' ; ALTER TABLE `ffxiv__freecompany_rank` CHANGE `freecompanyid` `freecompanyid` BIGINT (20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)' ; ALTER TABLE `ffxiv__freecompany_ranking` CHANGE `freecompanyid` `freecompanyid` BIGINT (20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)' ; ALTER TABLE `ffxiv__freecompany_x_character` CHANGE `freecompanyid` `freecompanyid` BIGINT (20) UNSIGNED NOT NULL COMMENT 'Free Company ID taken from Lodestone URL (https://eu.finalfantasyxiv.com/lodestone/freecompany/freecompanyid/)' ; SET FOREIGN_KEY_CHECKS=1; I get... #1062 - Duplicate entry '9223372036854775807' for key 'PRIMARY' And 9223372036854775807 is the MAX value for... BIGINT SIGNED. UPDATE : doing the same on the test table (previously mentioned) with 2 entries valid for BIGINT UNSIGNED also fails with same error. This implies that there is an issue with how BIGINT UNSIGNED is treated. And if MySQL (or InnoDB?) does some type conversion for strings that look like BIGINT that could also explain why VARCHAR did not help. Although, there is something else in here.
            Simbiat Dmitry Kustov added a comment -

            Another interesting side effect I've encountered while trying to find a workaround (and after converting respective BIGINT columns to VARCHAR):
            If I run

            SELECT `freecompanyid` FROM `ffxiv__freecompany` WHERE `freecompanyid`=9236038410806755490
            

            It returns following data set:

            9236038410806755330
            9236038410806755331
            9236038410806755337
            9236038410806755342
            9236038410806755348
            9236038410806755354
            9236038410806755374
            9236038410806755375
            9236038410806755377
            9236038410806755389
            9236038410806755393
            9236038410806755398
            9236038410806755399
            9236038410806755402
            9236038410806755407
            9236038410806755411
            9236038410806755412
            9236038410806755415
            9236038410806755416
            9236038410806755420
            9236038410806755426
            9236038410806755431
            9236038410806755440
            9236038410806755447
            9236038410806755464
            9236038410806755469
            9236038410806755478
            9236038410806755487
            9236038410806755490
            9236038410806755860
            9236038410806755868
            9236038410806755872
            9236038410806755878
            9236038410806755884
            9236038410806755885
            9236038410806755888
            9236038410806755889
            9236038410806755892
            9236038410806755899
            9236038410806755910
            9236038410806755918
            9236038410806755946
            9236038410806755947
            9236038410806755948
            9236038410806755962
            9236038410806755983
            9236038410806756328
            9236038410806756329
            9236038410806756339
            9236038410806756346
            

            Which looked, as if result is for

            SELECT `freecompanyid` FROM `ffxiv__freecompany` WHERE `freecompanyid` LIKE '923603841080675%'
            

            But if I run it with LIKE, I get more results:

            9236038410806755330
            9236038410806755331
            9236038410806755337
            9236038410806755342
            9236038410806755348
            9236038410806755354
            9236038410806755374
            9236038410806755375
            9236038410806755377
            9236038410806755389
            9236038410806755393
            9236038410806755398
            9236038410806755399
            9236038410806755402
            9236038410806755407
            9236038410806755411
            9236038410806755412
            9236038410806755415
            9236038410806755416
            9236038410806755420
            9236038410806755426
            9236038410806755431
            9236038410806755440
            9236038410806755447
            9236038410806755464
            9236038410806755469
            9236038410806755478
            9236038410806755487
            9236038410806755490
            9236038410806755860
            9236038410806755868
            9236038410806755872
            9236038410806755878
            9236038410806755884
            9236038410806755885
            9236038410806755888
            9236038410806755889
            9236038410806755892
            9236038410806755899
            9236038410806755910
            9236038410806755918
            9236038410806755946
            9236038410806755947
            9236038410806755948
            9236038410806755962
            9236038410806755983
            9236038410806756328
            9236038410806756329
            9236038410806756339
            9236038410806756346
            9236038410806756364
            9236038410806756365
            9236038410806756852
            9236038410806756854
            9236038410806757328
            9236038410806757338
            9236038410806757341
            9236038410806757369
            9236038410806757389
            9236038410806757391
            9236038410806757409
            9236038410806757429
            9236038410806757430
            9236038410806758343
            9236038410806758345
            9236038410806758362
            9236038410806758371
            9236038410806758393
            9236038410806758401
            9236038410806758433
            9236038410806758450
            9236038410806758470
            9236038410806759393
            9236038410806759398
            9236038410806759829
            9236038410806759871
            

            Which is strange in square, since original query should have returned only 1 value, but even if it was somehow internally converted to LIKE, it returned less, than it should have.

            Simbiat Dmitry Kustov added a comment - Another interesting side effect I've encountered while trying to find a workaround (and after converting respective BIGINT columns to VARCHAR): If I run SELECT `freecompanyid` FROM `ffxiv__freecompany` WHERE `freecompanyid`=9236038410806755490 It returns following data set: 9236038410806755330 9236038410806755331 9236038410806755337 9236038410806755342 9236038410806755348 9236038410806755354 9236038410806755374 9236038410806755375 9236038410806755377 9236038410806755389 9236038410806755393 9236038410806755398 9236038410806755399 9236038410806755402 9236038410806755407 9236038410806755411 9236038410806755412 9236038410806755415 9236038410806755416 9236038410806755420 9236038410806755426 9236038410806755431 9236038410806755440 9236038410806755447 9236038410806755464 9236038410806755469 9236038410806755478 9236038410806755487 9236038410806755490 9236038410806755860 9236038410806755868 9236038410806755872 9236038410806755878 9236038410806755884 9236038410806755885 9236038410806755888 9236038410806755889 9236038410806755892 9236038410806755899 9236038410806755910 9236038410806755918 9236038410806755946 9236038410806755947 9236038410806755948 9236038410806755962 9236038410806755983 9236038410806756328 9236038410806756329 9236038410806756339 9236038410806756346 Which looked, as if result is for SELECT `freecompanyid` FROM `ffxiv__freecompany` WHERE `freecompanyid` LIKE '923603841080675%' But if I run it with LIKE, I get more results: 9236038410806755330 9236038410806755331 9236038410806755337 9236038410806755342 9236038410806755348 9236038410806755354 9236038410806755374 9236038410806755375 9236038410806755377 9236038410806755389 9236038410806755393 9236038410806755398 9236038410806755399 9236038410806755402 9236038410806755407 9236038410806755411 9236038410806755412 9236038410806755415 9236038410806755416 9236038410806755420 9236038410806755426 9236038410806755431 9236038410806755440 9236038410806755447 9236038410806755464 9236038410806755469 9236038410806755478 9236038410806755487 9236038410806755490 9236038410806755860 9236038410806755868 9236038410806755872 9236038410806755878 9236038410806755884 9236038410806755885 9236038410806755888 9236038410806755889 9236038410806755892 9236038410806755899 9236038410806755910 9236038410806755918 9236038410806755946 9236038410806755947 9236038410806755948 9236038410806755962 9236038410806755983 9236038410806756328 9236038410806756329 9236038410806756339 9236038410806756346 9236038410806756364 9236038410806756365 9236038410806756852 9236038410806756854 9236038410806757328 9236038410806757338 9236038410806757341 9236038410806757369 9236038410806757389 9236038410806757391 9236038410806757409 9236038410806757429 9236038410806757430 9236038410806758343 9236038410806758345 9236038410806758362 9236038410806758371 9236038410806758393 9236038410806758401 9236038410806758433 9236038410806758450 9236038410806758470 9236038410806759393 9236038410806759398 9236038410806759829 9236038410806759871 Which is strange in square, since original query should have returned only 1 value, but even if it was somehow internally converted to LIKE, it returned less, than it should have.
            Simbiat Dmitry Kustov added a comment -

            Looks like some issues with BIGINT were reported in PHP bug tracker as well: https://bugs.php.net/bug.php?id=80653 which resulted in https://bugs.mysql.com/bug.php?id=102338
            The above mentioned behavior is true with regular statement as well, though.

            Regardless, I also reported this in MySQL, since this is most likely an issue in it as well: https://bugs.mysql.com/bug.php?id=103992

            Simbiat Dmitry Kustov added a comment - Looks like some issues with BIGINT were reported in PHP bug tracker as well: https://bugs.php.net/bug.php?id=80653 which resulted in https://bugs.mysql.com/bug.php?id=102338 The above mentioned behavior is true with regular statement as well, though. Regardless, I also reported this in MySQL, since this is most likely an issue in it as well: https://bugs.mysql.com/bug.php?id=103992
            Simbiat Dmitry Kustov added a comment -

            Issue with VARCHAR -> BIGINT UNSIGNED is purely MariaDB's, MySQL 8.0.0.25 does not have it. Issue with SELECT is present in MySQL, though.

            Simbiat Dmitry Kustov added a comment - Issue with VARCHAR -> BIGINT UNSIGNED is purely MariaDB's, MySQL 8.0.0.25 does not have it. Issue with SELECT is present in MySQL, though.

            People

              Unassigned Unassigned
              Simbiat Dmitry Kustov
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.