[MDEV-39846] UNIQUE BLOB contains duplicate records after ALTER IGNORE with READ COMMITTED - Jira

XML

Word

Printable

--source include/have_innodb.inc

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

CREATE TABLE t (pk INT PRIMARY KEY, b BLOB) ENGINE=InnoDB;

INSERT INTO t VALUES (1,'foo'),(2,'foo');

ALTER IGNORE TABLE t ADD UNIQUE (b);

SELECT * FROM t;

DROP TABLE t;

10.11 7423b8e4c2b4166cfe463be5f189dbf66d66cda7
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
CREATE TABLE t (pk INT PRIMARY KEY, b BLOB) ENGINE=InnoDB;
INSERT INTO t VALUES (1,'foo'),(2,'foo');
ALTER IGNORE TABLE t ADD UNIQUE (b);
SELECT * FROM t;
pk b
1 foo
2 foo
DROP TABLE t;

The failure started happening after this commit in 10.6.23 / 10.11.14:

commit 9703c90712f33014b9f222c08e272e898937a1bb

Author: Sergei Golubchik

Date:   Fri Jul 11 15:49:53 2025 +0200

    MDEV-37199 UNIQUE KEY USING HASH accepting duplicate records

11.4 and up are not affected.

Also, none of the versions, even those which drop the duplicate record upon ALTER IGNORE, produce any warnings while doing so.

is caused by

MDEV-37199 UNIQUE KEY USING HASH accepting duplicate records

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