[MDEV-25862] BIGINT UNSIGNED treated as SIGNED - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.2.38, 10.5.10
Fix Version/s: None
Component/s: Data types
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

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

Ascending order - Click to sort in descending order

Sergei Golubchik added a comment - 2021-06-06 08:04

did you try not to quote numbers? Like in

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

instead of

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

Sergei Golubchik added a comment - 2021-06-06 08:04 did you try not to quote numbers? Like in INSERT INTO ... VALUES (9236179148295239722, ...) ... instead of INSERT INTO ... VALUES ( '9236179148295239722' , ...) ...

Dmitry Kustov added a comment - 2021-06-06 09:25

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.

Dmitry Kustov added a comment - 2021-06-06 09:25 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.

Dmitry Kustov added a comment - 2021-06-06 16:23

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;

Dmitry Kustov added a comment - 2021-06-06 16:23 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 ;

Dmitry Kustov added a comment - 2021-06-06 17:51

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.

Dmitry Kustov added a comment - 2021-06-06 17:51 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.

Dmitry Kustov added a comment - 2021-06-06 17:57 - 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.

Dmitry Kustov added a comment - 2021-06-06 17:57 - 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.

Dmitry Kustov added a comment - 2021-06-12 15:54

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.

Dmitry Kustov added a comment - 2021-06-12 15:54 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.

Dmitry Kustov added a comment - 2021-06-12 16:29

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

Dmitry Kustov added a comment - 2021-06-12 16:29 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

Dmitry Kustov added a comment - 2021-06-12 19:26

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.

Dmitry Kustov added a comment - 2021-06-12 19:26 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

Assignee:: Unassigned

Reporter:: Dmitry Kustov

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2021-06-06 06:46

Updated:: 2021-06-21 07:58

Git Integration

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

MariaDB Server