MySQL Bugs: #116142: Add another uk to a field that already has a uk results in a duplicate key error

Bug #116142	Add another uk to a field that already has a uk results in a duplicate key error
Submitted:	18 Sep 2024 11:34	Modified:	19 Sep 2024 11:06
Reporter:	linus luo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DDL, unique secondary index

Description:
Under delete-insert write load, adding another unique secondary index to a field that already has a unique secondary index will result in a duplicate key error.
However, since the uniqueness constraints of the two unique indexes are the same, the existence of the first unique index should allow the second unique index to be successfully constructed.

How to repeat:
1、Prepare a table in the following format:
CREATE TABLE `sbtest1` (
  `id` bigint NOT NULL,
  `k` bigint NOT NULL,
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `tran_time` varchar(20) NOT NULL,
  PRIMARY KEY (`id`,`tran_time`),
  UNIQUE KEY `i_global_k` (`k`),
  KEY `i_global_pad` (`pad`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2、Insert 1,000,000 rows of data.

3、Execute the sysbench oltp_write_only script (retaining only the delete_inserts section) with 64 concurrency.

4、Execute the following DDL statement:
ALTER TABLE sbtest1 ADD UNIQUE KEY i_global_uk1(k);

5、There is a high probability of encountering a duplicate key error; if it does not occur, trying twice more will definitely result in one.

HI Mr. Luo,

Thank you for your bug report.

However, first of all, you are using an old release.

Much more important then this fact is that we require a full test case. This is a forum only for reports containing all SQL commands (or sysbench commands) that are required to repeat the behaviour reported.

You can use sysbench for both filling data and the LUA script that would show the result.  

We need a fully repeatable test case, including data and the exact LUA script.

Do be noted that we shall be using our latest release of our official binaries, so we might fail to reproduce yoru results.

Can't repeat.

script1: oltp_read_write_sp_globalindex_columns_2260_no_part

Attachment: oltp_read_write_sp_globalindex_columns_2260_no_part.lua (application/octet-stream, text), 1.94 KiB.

script2: oltp_common_ddl_no_part

Attachment: oltp_common_ddl_no_part.lua (application/octet-stream, text), 14.39 KiB.

Not only is there a problem with repeatedly adding uk to the same field, but there is also an issue with adding uks with the same restrictions to a table.

Detailed reproduction steps have been added, and two scripts have been uploaded:

1、Execute the following command: sysbench oltp_common_ddl_no_part.lua --mysql-socket=/path/to/socket --mysql-db=test --mysql-user=username --mysql-password=password --tables=1 --table-size=1000000 --threads=10 prepare

2、Execute the following command: sysbench oltp_read_write_sp_globalindex_columns_2260_no_part.lua --mysql-socket=/path/to/socket --mysql-user=username --mysql-password=password --mysql-db=test --tables=1 --table-size=1000000 --threads=64 --range_selects=on --skip_trx=off --report-interval=5 --time=360 --mysql-ignore-errors=all run

3、Execute the following SQL in the MySQL client: ALTER TABLE sbtest1 ADD UNIQUE KEY i_global_uk1(k); / ALTER TABLE sbtest1 ADD UNIQUE KEY i_global_uk1(k1);

4、There is a high probability of encountering a duplicate key error; if it does not occur, trying twice more will definitely result in one.

Note: The reason that adding uk on k1 can also trigger is that in our script, the values of k and k1 always remain consistent, so the uk restrictions on k and k1 are the same.

Hi Mr. luo,

Thank you for your contribution.

We ran all the tests and got the error on the first attempt:

ERROR 1061 (42000) on line 3: Duplicate key name 'i_global_uk1'.

This bug is now fully verified for the version 8.0 and all higher versions.

are you sure *Duplicate key name* is the problem here,  and not this?

https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-limitations.html

"When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. "

There's a difference between adding the key twice:

mysql> ALTER TABLE sbtest1 ADD UNIQUE KEY i_global_uk1(k);
Query OK, 0 rows affected, 1 warning (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> ALTER TABLE sbtest1 ADD UNIQUE KEY i_global_uk1(k);
ERROR 1061 (42000): Duplicate key name 'i_global_uk1'

Versus adding it once but getting:

mysql> ALTER TABLE sbtest1 ADD UNIQUE KEY i_global_uk1(k);
ERROR 1062 (23000): Duplicate entry '639' for key 'sbtest1.i_global_uk1'

Hi all,
I suspect that this bug shares the same root cause as Bug #117237.

Hi Mr. Song,

Actually , these are different bugs with different internal causes.