[#MDEV-32871] A table is missing inserted entries from concurrent processes

[MDEV-32871] A table is missing inserted entries from concurrent processes Created: 2023-11-24 Updated: 2023-12-04
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Data Manipulation - Insert
Affects Version/s:	11.0.2, 10.11.5, 11.1.2, 11.2.2
Fix Version/s:	10.6, 10.11, 11.0, 11.1, 11.2

Type:

Bug

Priority:

Critical

Reporter:

Jiri Slaby

Assignee:

Sergei Golubchik

Resolution:

Unresolved

Votes:

Labels:

None

Environment:

openSUSE Tubleweed, x86_64

Attachments:

cr.sql

db.c

Description

I create a db and table using the attached cr.sql.

Then from my c code, using connector-c, I do:

SET autocommit=0

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 the c code (it creates 10 parallel children), 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).

Comments

Comment by Jiri Slaby [ 2023-11-27 ]

Forgot to note, running with libmariadb3-3.3.7-1.1.x86_64.

Comment by Jiri Slaby [ 2023-11-30 ]

Running mysqld 8.2.0 from mysql:latest docker image:

select count(id), substr(src,5,6) as sss from sources group by sss;

+-----------+--------+

| count(id) | sss    |

+-----------+--------+

|        50 | 013324 |

|        50 | 013325 |

|        50 | 013326 |

|        50 | 013327 |

|        50 | 013328 |

|        50 | 013329 |

|        50 | 013330 |

|        50 | 013331 |

|        50 | 013332 |

|        50 | 013333 |

+-----------+--------+

I.e. everything seemingly as expected. But mysql does not support UNIQUE on VARCHAR(1024).

Comment by Jiri Slaby [ 2023-11-30 ]

Dockers with

are broken the same way. (It does not reproduce with VARCHAR(256) column.)

Comment by Jiri Slaby [ 2023-12-01 ]

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;

~~Should that error out?~~ Looks like the support for more than 3072 bytes from ~~MDEV-371~~ has bugs?

Generated at Thu Feb 08 10:34:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.