Bug #110175 unique key has duplicate data for mysqludmp

• Submitted: 23 Feb 2023 3:48
• Modified: 23 Feb 2023 14:23
• Reporter: nycle chen
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.7.34-log
• OS: CentOS (CentOS (CentOS Linux release 7.6.1810 (Core)))
• CPU Architecture: Any
• Tags: unique key has duplicate data.

[23 Feb 2023 3:48] nycle chen

Description:
I has a table, it likes:
create table t1
(
id bigint not null auto_increment,
code bigint,
name varchar(64),
unique key(code),
primary key(id)
)engine=innodb;

1.When I execute query for condition code column(code=1) , then retuen one row, but use mysqldump the full table, it retuens the same code(code=1) for two rows, and the code column has a nuique index. It like this:
  select * from t1 where code=1; 
  Then it return one row the code=1.

  mysqldump -hxxx -Pxxx  -uxxx -pxxx  --single-transaction --set-gtid-purged=off --databases db1  --tables t1 > ./t1.sql   
  The the t1.sql file has two rows for code=1.

2.When I use the sql(group by) to query. The the same code has two rows, but I restart the instance the same code has one row. 

select code,count(*) cnt from t1
group by code having cnt > 1;
It return many rows. It is the same code has many rows. But the code column has unique key. It like this:
code cnt
---- ----
1    2
2    2

When I restart the instance, it returns one row for the same code, that is  mysqldump and the query(for group) is ok. It like this:
select code,count(*) cnt from t1
group by code having cnt > 1;
code cnt
---- ----
1    1
2    1

But it can not report the case. If I restart the instance, then I can not find any code(for mysqludmp and group by query) has two rows.

How to repeat:
Can not report. If I restart the instance, then the unique key has only one rows.

[23 Feb 2023 14:23] MySQL Verification Team

Hi,

You have already submitted this bug once in:

https://bugs.mysql.com/bug.php?id=110092

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.