[MDEV-31093] "ON DUPLICATE KEY UPDATE" saves wrong data to the database - Jira

XML

Word

Printable

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

is caused by

MDEV-371 Unique indexes for blobs

relates to

MDEV-30046 wrong row targeted with "insert ... on duplicate" and "replace", leading to data corruption

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.