[MDEV-25862] BIGINT UNSIGNED treated as SIGNED Created: 2021-06-06  Updated: 2021-06-21

Status: Open
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.2.38, 10.5.10
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Dmitry Kustov Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

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

Issue Links:
Relates
relates to MDEV-25871 compare error bigint to varchar Closed

 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.

 Comments   
Comment by Sergei Golubchik [ 2021-06-06 ]

did you try not to quote numbers? Like in

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

instead of

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

Comment by Dmitry Kustov [ 2021-06-06 ]

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.

Comment by Dmitry Kustov [ 2021-06-06 ]

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;

Comment by Dmitry Kustov [ 2021-06-06 ]

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.

Comment by Dmitry Kustov [ 2021-06-06 ]

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.

Comment by Dmitry Kustov [ 2021-06-12 ]

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.

Comment by Dmitry Kustov [ 2021-06-12 ]

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

Comment by Dmitry Kustov [ 2021-06-12 ]

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.

Generated at Thu Feb 08 09:40:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.