Bug #98600 Optimize table fails with duplicate entry on UNIQUE KEY
Submitted: 14 Feb 2020 11:48 Modified: 14 Feb 2020 12:23
Reporter: Jericho Rivera Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7,8.0, 5.7.29, 8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[14 Feb 2020 11:48] Jericho Rivera
Description:
An optimize table or its equivalent ALTER TABLE can fail with duplicate entry on a UNIQUE KEY.
+--------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                          |
+--------------+----------+----------+-------------------------------------------------------------------+
| test.testddl | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.testddl | optimize | error    | Duplicate entry '100' for key 'testddl.name'                      |
| test.testddl | optimize | status   | Operation failed                                                  |
+--------------+----------+----------+-------------------------------------------------------------------+

This is easily reproducible following the how-to-repeat procedure. Note that the dowhile() procedure is ongoing or can be ran for a short while then execute the insert on T2 even if it is continuously failing with Duplicate entry. If T2 loop is cancelled the optimize table (T3) will not fail in succeeding loops.

Failures can happen more with 5.7 but still can be reproduced in 8.0 latest as well.

We're aware of online DDL limitations but the ease of reproducing the optimize table failure looks like a bug.

How to repeat:
Terminal 1:
CREATE TABLE `testddl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`empid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
Unique key(name)
) ENGINE=InnoDB;

delimiter //
CREATE PROCEDURE dowhile()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= 2000000) DO
INSERT INTO `testddl` (id, name, empid) values (i, i, i);
SET i = i+1;
END WHILE;
END
//
delimiter ;

call dowhile();

Terminal 2:
while true; do mysql -uroot -e "insert into test.testddl (name,empid) values (100,100)"; sleep 1; done;

Terminal 3:
while true; do mysql -uroot -e "optimize table test.testddl"; sleep 1; done
[14 Feb 2020 12:23] MySQL Verification Team
Hello Jericho,

Thank you for the report and test case.
Verified as described with 8.0.19 build.

regards,
Umesh
[2 Mar 2020 21:01] Sveta Smirnova
See also analysis at https://jira.percona.com/browse/PS-6858?focusedCommentId=250975&page=com.atlassian.jira.pl...