Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.38, 10.5.10
-
None
-
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
- relates to
-
MDEV-25871 compare error bigint to varchar
-
- Closed
-
Activity
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.
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; |
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.
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.
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.
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
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.
did you try not to quote numbers? Like in
instead of