Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5, 10.6, 10.5.15, 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
None
-
Linux 5.10.0-14-amd64, Debian 11.5
Description
(Also reported here: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1015293)
Using the MySQL interface, these statements:
DROP TABLE IF EXISTS t; |
CREATE TABLE t (s BLOB, n INT, UNIQUE (s)); |
INSERT INTO t VALUES ('Hrecvx_0004ln-00',1), ('Hrecvx_0004mm-00',1); |
INSERT INTO t VALUES ('Hrecvx_0004mm-00',2) ON DUPLICATE KEY UPDATE n = VALUES (n); |
SELECT * FROM t; |
produce this output:
s n
Hrecvx_0004ln-00 2
Hrecvx_0004mm-00 1
So the latter "INSERT" updates the wrong row.
This happens whether the first column is "BLOB" or "TEXT", but only
with specific values. (In my actual use case with ~1 million rows,
it happened a few dozen times, which might be consistent e.g. with
collisions of a 32 bit hash or so.)
Likewise, these statements:
DROP TABLE IF EXISTS t; |
CREATE TABLE t (s BLOB, n INT, UNIQUE (s)); |
INSERT INTO t VALUES ('Hrecvx_0004ln-00',1), ('Hrecvx_0004mm-00',1); |
REPLACE INTO t VALUES ('Hrecvx_0004mm-00',2); |
SELECT * FROM t; |
give the error:
ERROR 1062 (23000) at line 4: Duplicate entry 'Hrecvx_0004mm-00' for key 's'
In my understanding, this error should actually be impossible with
"REPLACE INTO".
It might be the same issue, i.e. it tries to delete the wrong row
before inserting the new one, so it's still duplicate.
Attachments
Issue Links
- is duplicated by
-
MDEV-30588 Failed to update duplicate data when running insert... on duplicate key update.
- Closed
- relates to
-
MDEV-371 Unique indexes for blobs
- Closed
-
MDEV-17395 REPLACE/INSERT ODKU: support WITHOUT OVERLAPS
- Stalled
-
MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table
- Closed
-
MDEV-31093 "ON DUPLICATE KEY UPDATE" saves wrong data to the database
- Open
- split to
-
MDEV-34091 Refactor write_record and generalize the code with replication
- Stalled