[debian-mysql] Bug#1015293: wrong row targeted with "insert ... on duplicate" and "replace", leading to data corruption

[Frank Heckenbach]

Package: mariadb-server-core-10.5
Version: 1:10.5.15-0+deb11u1
Severity: important

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.

-- System Information:
Debian Release: 11.4
  APT prefers stable-updates
  APT policy: (500, 'stable-updates'), (500, 'stable-security'), (500, 'stable-debug'), (500, 'proposed-updates-debug'), (500, 'proposed-updates'), (500, 'stable')
Architecture: amd64 (x86_64)

Kernel: Linux 5.10.0-14-amd64 (SMP w/24 CPU threads)
Kernel taint flags: TAINT_WARN
Locale: LANG=de_DE, LC_CTYPE=de_DE (charmap=ISO-8859-1), LANGUAGE not set
Shell: /bin/sh linked to /usr/bin/dash
Init: systemd (via /run/systemd/system)
LSM: AppArmor: enabled

Versions of packages mariadb-server-core-10.5 depends on:
ii  libaio1         0.3.112-9
ii  libc6           2.31-13+deb11u3
ii  libcrypt1       1:4.4.18-4
ii  liblz4-1        1.9.3-2
ii  libpcre2-8-0    10.36-2
ii  libsnappy1v5    1.1.8-1
ii  libssl1.1       1.1.1n-0+deb11u3
ii  libstdc++6      10.2.1-6
ii  libsystemd0     247.3-7
ii  mariadb-common  1:10.5.15-0+deb11u1
ii  zlib1g          1:1.2.11.dfsg-2+deb11u1

mariadb-server-core-10.5 recommends no packages.

mariadb-server-core-10.5 suggests no packages.

-- no debconf information