[Bug 1217688] New: mariadb: a table is missing inserted entries from concurrent processes
https://bugzilla.suse.com/show_bug.cgi?id=1217688 Bug ID: 1217688 Summary: mariadb: a table is missing inserted entries from concurrent processes Classification: openSUSE Product: openSUSE Tumbleweed Version: Current Hardware: Other URL: https://jira.mariadb.org/browse/MDEV-32871 OS: Other Status: NEW Severity: Normal Priority: P5 - None Component: Other Assignee: danilo.spinella@suse.com Reporter: jslaby@suse.com QA Contact: qa-bugs@suse.de Target Milestone: --- Found By: --- Blocker: --- Created attachment 871082 --> https://bugzilla.suse.com/attachment.cgi?id=871082&action=edit db inserter With mariadb: 11.0.2, 11.1.2 and 11.1.3 (from server:database): From my c++ code, using connector-c, I do: SET autocommit=0 CREATE TABLE IF NOT EXISTS sources(id INTEGER AUTO_INCREMENT PRIMARY KEY, src VARCHAR(1024) NOT NULL UNIQUE) INSERT IGNORE INTO sources(src) VALUES (?) START TRANSACTION And then a lot of: INSERT IGNORE INTO sources(src) VALUES ('src-003431-00001') src is composed of pid and values from 0 to 49. So they are all unique. Finally, I do: COMMIT If I run 10 instances in parallel like: for aa in {1..10} ; do ./db & done ; wait I see: select count(id), substr(src,5,6) as sss from sources group by sss; +-----------+--------+ | count(id) | sss | +-----------+--------+ | 50 | 003430 | | 50 | 003431 | | 50 | 003432 | | 40 | 003433 | | 50 | 003434 | | 50 | 003435 | | 50 | 003436 | | 50 | 003437 | | 50 | 003438 | | 50 | 003439 | +-----------+--------+ I.e. Some of pids stored only last 40 values, numbers 0..9 are missing in the table: select src from sources where src like '%003433%' order by src ; +------------------+ | src | +------------------+ | src-003433-00010 | | src-003433-00011 | | src-003433-00012 | | src-003433-00013 | | src-003433-00014 | | src-003433-00015 | | src-003433-00016 | | src-003433-00017 | | src-003433-00018 | | src-003433-00019 | | src-003433-00020 | | src-003433-00021 | | src-003433-00022 | | src-003433-00023 | | src-003433-00024 | | src-003433-00025 | | src-003433-00026 | | src-003433-00027 | | src-003433-00028 | | src-003433-00029 | | src-003433-00030 | | src-003433-00031 | | src-003433-00032 | | src-003433-00033 | | src-003433-00034 | | src-003433-00035 | | src-003433-00036 | | src-003433-00037 | | src-003433-00038 | | src-003433-00039 | | src-003433-00040 | | src-003433-00041 | | src-003433-00042 | | src-003433-00043 | | src-003433-00044 | | src-003433-00045 | | src-003433-00046 | | src-003433-00047 | | src-003433-00048 | | src-003433-00049 | +------------------+ Is this a bug in my program (attached) or the db? If I look into the general log, I see the inserts which apparently did not get into the table: grep src-003433 /tmp/glog 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00000') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00000') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00000') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00000') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00000') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00001') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00002') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00003') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00004') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00005') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00006') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00007') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00008') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00009') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00010') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00010') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00011') 24 Execute INSERT IGNORE INTO sources(src) VALUES ('src-003433-00012') Note also the repeated inserts. They come from a deadlock, so I repeat the exec in a loop (see the code). Dockers with mariadb:latest (11.2.2) mariadb:11.1.3 mariadb:11.1.2 all run FINE too. -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c1 --- Comment #1 from Jiri Slaby <jslaby@suse.com> --- Disabling LTO does not help. -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c2 --- Comment #2 from Danilo Spinella <danilo.spinella@suse.com> --- Hello Jiri, thank you for the detailed report with a reproducer. By my understanding, this is an issue that only happens with mariadb on TW. Any version of the official mariadb images works fine. Can you please confirm? -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c3 --- Comment #3 from Jiri Slaby <jslaby@suse.com> --- (In reply to Danilo Spinella from comment #2)
By my understanding, this is an issue that only happens with mariadb on TW.
Yeah, I haven't tried anything else than TW so far.
Any version of the official mariadb images works fine. Can you please confirm?
Concur. I wonder, if we build mariadb with some different flags. But I failed to find what is used for building the docker images (it's apparently mariadb on Ubuntu 22.04). -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c4 --- Comment #4 from Danilo Spinella <danilo.spinella@suse.com> --- (In reply to Jiri Slaby from comment #3)
(In reply to Danilo Spinella from comment #2)
By my understanding, this is an issue that only happens with mariadb on TW.
Yeah, I haven't tried anything else than TW so far.
Any version of the official mariadb images works fine. Can you please confirm?
Concur. I wonder, if we build mariadb with some different flags. But I failed to find what is used for building the docker images (it's apparently mariadb on Ubuntu 22.04).
One flag that usually causes problem is -DFORTIFY_SOURCE=2. lto is also often the cause of many issues, but it looks like it's not the problem here. -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c5 --- Comment #5 from Jiri Slaby <jslaby@suse.com> --- (In reply to Jiri Slaby from comment #3)
(In reply to Danilo Spinella from comment #2)
By my understanding, this is an issue that only happens with mariadb on TW.
Yeah, I haven't tried anything else than TW so far.
Any version of the official mariadb images works fine. Can you please confirm?
Concur. I wonder, if we build mariadb with some different flags. But I failed to find what is used for building the docker images (it's apparently mariadb on Ubuntu 22.04).
Scratch this. I temporarily dropped the column size from varchar(1024) to varchar(256) in the docker images (when testing mysql-community where 1024 is not allowed for unique columns). When varchar(1024) is used, it's broken also in the docker. Let me update the upstream issue. -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c6 --- Comment #6 from Jiri Slaby <jslaby@suse.com> --- Created attachment 871086 --> https://bugzilla.suse.com/attachment.cgi?id=871086&action=edit c reproducer, incl. fork() -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c7 --- Comment #7 from Jiri Slaby <jslaby@suse.com> --- Created attachment 871087 --> https://bugzilla.suse.com/attachment.cgi?id=871087&action=edit cr.sql to prepare the db, user, and the table -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 Jiri Slaby <jslaby@suse.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #871082|0 |1 is obsolete| | -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c8 --- Comment #8 from Jiri Slaby <jslaby@suse.com> --- It happens in 15sp6 too: $ grep VERSION= /etc/os-release VERSION="15-SP6" $ rpm -q mariadb mariadb-10.11.5-150600.2.6.x86_64 +-----------+--------+ | count(id) | sss | +-----------+--------+ | 50 | 001656 | | 50 | 001657 | | 50 | 001658 | | 30 | 001659 | | 50 | 001660 | | 46 | 001661 | | 50 | 001662 | | 50 | 001663 | | 50 | 001664 | | 50 | 001665 | +-----------+--------+ -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c9 --- Comment #9 from Jiri Slaby <jslaby@suse.com> --- In mysql:
mysql> ALTER TABLE sources CHANGE COLUMN src src VARCHAR(769) NOT NULL UNIQUE; ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
The maximum length of UNIQUE column in mysql is 3072 bytes, i.e. 768 utf8mb4 chars. So if I do this in mariadb:
MariaDB [structs_66]> ALTER TABLE sources CHANGE COLUMN src src VARCHAR(768) NOT NULL UNIQUE; everything works. If I enlarge to 769, it breaks: MariaDB [structs_66]> ALTER TABLE sources CHANGE COLUMN src src VARCHAR(769) NOT NULL UNIQUE; -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c10 --- Comment #10 from Jiri Slaby <jslaby@suse.com> --- So it might be that the only problem is that no error (max key length is 3072 bytes) is emitted and the table is miscreated. -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c11 --- Comment #11 from Jiri Slaby <jslaby@suse.com> --- (In reply to Jiri Slaby from comment #10)
So it might be that the only problem is that no error (max key length is 3072 bytes) is emitted and the table is miscreated.
No, support for > 3072 was added to mariadb in 10.5 via: https://jira.mariadb.org/browse/MDEV-371 So it's likely a bug in the hashing over the virtual table. -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 Danilo Spinella <danilo.spinella@suse.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|danilo.spinella@suse.com |antonio.teixeira@suse.com -- You are receiving this mail because: You are on the CC list for the bug.
https://bugzilla.suse.com/show_bug.cgi?id=1217688 https://bugzilla.suse.com/show_bug.cgi?id=1217688#c12 Jiri Slaby <jslaby@suse.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://jira.mariadb.org/br | |owse/MDEV-32871 --- Comment #12 from Jiri Slaby <jslaby@suse.com> --- BTW, the upstream issue: https://jira.mariadb.org/browse/MDEV-32871 FWIW this was fixed with 11.2.2->11.2.3 update a a by-product by some other change. -- You are receiving this mail because: You are on the CC list for the bug.
participants (1)
-
bugzilla_noreply＠suse.com