MySQL Online DDL Error

1. The Incident

A developer urgently reported that adding a column triggered:

ERROR 1062 (23000): Duplicate entry [...] for key [...]

‍

Initially suspecting a MySQL bug, I nearly blamed the developer—until logic failed:

​Red Flags:​​

• The error occurred during a new column addition (not a unique constraint)
• The duplicate field wasn’t the newly added one
• The reported "duplicate" value was actually unique

After gathering execution details, I reproduced this bizarre error:

2. Root Cause

​Online DDL Mechanics:​​
MySQL stores concurrent DML operations in a temporary log (innodb_online_alter_log_max_size [1]) during DDL execution. Post-DDL, it replays these changes.

​Official Explanation (Bug#76895):​​
This isn’t a bug but an expected behavior. If conflicting DMLs (e.g., duplicate inserts) occur during DDL, the error surfaces when applying the log.

3. Reproduction Steps

3.1 Table Structure

Requires both Primary and Unique Keys:

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `pad` (`pad`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

‍

3.2 Sample Data

SELECT * FROM sbtest1 LIMIT 2;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-----+
| id | k       | c                                                                                                                         | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-----+
|  1 | 2507307 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | h   |
|  2 | 2512400 | 13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289 | g   |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-----+

‍

3.3 Trigger the Error

1. Start DDL:

ALTER TABLE sbtest1 ADD COLUMN name VARCHAR(20);

‍

2. Concurrently insert duplicate unique key:

INSERT INTO sbtest1(k, pad) VALUES (1, 'h');  -- Error: Duplicate entry 'h' for key 'pad'

‍

3. The DDL then fails with the same error.

3.4 Odd Exceptions

• ​Primary key conflicts​ don’t fail DDL.
• Tables with only Unique Keys (no Primary) also avoid this issue.

4. Conclusion

​Conditions for the Error:​​

1. Table must have both Primary + Unique Keys.
2. Conflicting DML occurs during DDL execution.

​Why?​​
MySQL’s Online DDL replays logged DMLs post-operation. Conflicting entries (e.g., duplicate unique values) trigger errors during replay.

5. Open Question

Why does this only happen with Primary+Unique Key tables?
Share your theories in the comments!

​References:​​
[1] innodb_online_alter_log_max_size
[2] Bug#76895

‍