Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Incomplete
-
11.0.2, 10.11.5, 11.1.2, 11.2.2
-
None
-
openSUSE Tubleweed, x86_64
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).
Attachments
Activity
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).
Dockers with
- mariadb:latest (11.2.2)
- mariadb:11.1.3
- mariadb:11.1.2
are broken the same way. (It does not reproduce with VARCHAR(256) column.)
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?
Have you (@Sergei Golubchik) fixed this in MDEV-29954? Or maybe some other issue. I cannot repro with 11.2.3 (unlike with 11.2.2). But I also changed HW in the meantime.
It's unlikely hardware dependent. But indeed there were few MDEV-371 related bug fixes between 11.2.2 and 11.2.3, one of them could've fixed your case too.
We'll close in a while as not repeatable anymore. But, please, add a comment if you'll see it happening again.
Forgot to note, running with libmariadb3-3.3.7-1.1.x86_64.