[MDEV-31093] "ON DUPLICATE KEY UPDATE" saves wrong data to the database Created: 2023-04-20  Updated: 2023-04-20

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.6.12
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Juanmi Taboada Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

Attachments: Zip Archive etc_mysql.zip    
Issue Links:
Problem/Incident
is caused by MDEV-371 Unique indexes for blobs Closed
Relates
relates to MDEV-30046 wrong row targeted with "insert ... o... Stalled

 Description   

Hello:

While developing a new version of our software, I found some issues during tests and couldn't figure out why.

I think this data produce a COLLISION when using the HASH engine for our INDEX.

I can reproduce this bug with the next code:

SET @c1=200193;
 
SET @c2='B2B';
 
 
 
DROP TABLE IF EXISTS `test`;
 
 
 
CREATE TABLE `test` (
 
    `intA` int(11) NOT NULL,
 
    `string` varchar(20) NOT NULL,
 
    `intB` int(11) NOT NULL,
 
    `intC` int(11) NOT NULL,
 
    `intD` int(11) NOT NULL,
 
    UNIQUE KEY `key` (`intA`,`string`,`intB`,`intC`) USING HASH
 
) ENGINE=InnoDB;
 
 
 
 
 
INSERT INTO `test` (`intA`, `string`, `intB`, `intC`, `intD`) VALUES
 
(@c1, @c2, 3, 2, 1234),
 
(@c1, @c2, 4, 0, 99999999);
 
 
 
SELECT * FROM test;
 
 
 
INSERT INTO test (intA,string,intB,intC,intD) VALUES (@c1, @c2, 4, 0, 999) ON DUPLICATE  KEY UPDATE intD = VALUES(intD);
 
 
 
SELECT * FROM test;
 
 
 
DROP TABLE test;

The result we get after executing this is:

intA    string  intB    intC    intD
 
200193  B2B     3       2       1234
 
200193  B2B     4       0       99999999
 
intA    string  intB    intC    intD
 
200193  B2B     3       2       999
 
200193  B2B     4       0       99999999

I think the expected behaviour from the database should be:

intA    string  intB    intC    intD
 
200193  B2B     3       2       1234
 
200193  B2B     4       0       99999999
 
intA    string  intB    intC    intD
 
200193  B2B     3       2       1234
 
200193  B2B     4       0       999

Thank you

 Comments   
Comment by Marko Mäkelä [ 2023-04-20 ]

Before MDEV-371, the HASH index type for InnoDB was silently interpreted as BTREE.

Comment by Juanmi Taboada [ 2023-04-20 ]

I don't have this problem when using BTREE. I switched to BTREE to avoid this bug until resolved.

Comment by Alice Sherepa [ 2023-04-20 ]

Could you please add you .cnf file? I tried to repeat on 10.6.12 and it worked as expected:

MariaDB [test]> CREATE TABLE `test` (
 
    ->     `intA` int(11) NOT NULL,
 
    ->     `string` varchar(20) NOT NULL,
 
    ->     `intB` int(11) NOT NULL,
 
    ->     `intC` int(11) NOT NULL,
 
    ->     `intD` int(11) NOT NULL,
 
    ->     UNIQUE KEY `key` (`intA`,`string`,`intB`,`intC`) USING HASH
 
    -> ) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0,039 sec)
 
 
MariaDB [test]> INSERT INTO `test` (`intA`, `string`, `intB`, `intC`, `intD`) VALUES
 
    -> (@c1, @c2, 3, 2, 1234),
 
    -> (@c1, @c2, 4, 0, 99999999);
 
Query OK, 2 rows affected (0,002 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT * FROM test;
 
+--------+--------+------+------+----------+
 
| intA   | string | intB | intC | intD     |
 
+--------+--------+------+------+----------+
 
| 200193 | B2B    |    3 |    2 |     1234 |
 
| 200193 | B2B    |    4 |    0 | 99999999 |
 
+--------+--------+------+------+----------+
 
2 rows in set (0,000 sec)
 
 
 
MariaDB [test]> INSERT INTO test (intA,string,intB,intC,intD) VALUES (@c1, @c2, 4, 0, 999) ON DUPLICATE  KEY UPDATE intD = VALUES(intD);
 
Query OK, 2 rows affected (0,002 sec)
 
 
MariaDB [test]> SELECT * FROM test;
 
+--------+--------+------+------+------+
 
| intA   | string | intB | intC | intD |
 
+--------+--------+------+------+------+
 
| 200193 | B2B    |    3 |    2 | 1234 |
 
| 200193 | B2B    |    4 |    0 |  999 |
 
+--------+--------+------+------+------+
 
2 rows in set (0,000 sec)
 
 
 
MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.6.12-MariaDB |
 
+-----------------+
 
1 row in set (0,000 sec)

Comment by Juanmi Taboada [ 2023-04-20 ]

So far I can reproduce this bug in:

  • Ubuntu:10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04
  • Debian: 10.5.19-MariaDB-1:10.5.19+maria~deb11 mariadb.org binary distribution

Configurations attached. etc_mysql.zip

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