Why Does Adding a Column Trigger Duplicate Entry Errors in MySQL?

1. What happened?

A developer reported an

ERROR 1062 (23000): Duplicate entry … key …

when trying to add a column, suspecting a MySQL problem. The DBA points out that the error is likely caused by adding a column with a unique constraint that contains duplicate data.

Question: If the column is newly added, why would a duplicate‑entry error appear (there is no unique index on the new column)?

The DBA asks for the connection info, the exact SQL, and the full error message, then reproduces the issue.

This is a normal add‑column operation.

The column being added is different from the column that reported the duplicate value.

Seeing “duplicate key”, the first instinct is to check whether the column has duplicate values. After investigation, the value turned out to be unique…

2. Why does it happen?

MySQL’s Online DDL stores DML statements that occur during DDL execution in a temporary log file whose size is controlled by the innodb_online_alter_log_max_size parameter. After the DDL finishes, the logged DML is applied to the table.

A bug record (not considered a bug by MySQL) describes this as an Online DDL characteristic.

In short, if two DML statements insert the same data while a DDL is running, a duplicate entry error can be raised.

3. Reproducing the issue

3.1 Create the table

<code>mysql> show create table sbtest1\G
...
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 AUTO_INCREMENT=5000004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
...</code>

3.2 View table data

<code>mysql> select * from sbtest1 limit 2;
+----+---------+------------------------------------------------------+-----+
| id | k       | c                                                    | pad |
+----+---------+------------------------------------------------------+-----+
| 1  | 2507307 | 68487932199-...                                      | h   |
| 2  | 2512400 | 13241531885-...                                      | g   |
+----+---------+------------------------------------------------------+-----+
</code>

3.3 Add a new column

<code>mysql> alter table sbtest1 add column name varchar(20);
</code>

3.4 Insert data that conflicts with the unique key

<code>mysql> insert into sbtest1(k, pad) values (1, 'h');
ERROR 1062 (23000): Duplicate entry 'h' for key 'pad';
</code>

3.5 Adding another column also triggers the same error

<code>mysql> alter table sbtest1 add column name varchar(20);
ERROR 1062 (23000): Duplicate entry 'h' for key 'pad';
</code>

3.6 Observation

If a primary‑key conflict occurs, the DDL does not fail. If only a unique key exists without a primary key, the DDL also does not fail.

4. Summary

MySQL will report a duplicate entry error during DDL when the following conditions are met:

Conditions

The table must have both a primary key and a unique key.

During DDL execution, DML that inserts a value conflicting with the unique key is logged.

Root cause

Online DDL stores concurrent DML in a log and applies it after the DDL finishes; if the logged DML contains duplicate values for a unique key, the application of those statements triggers the duplicate‑entry error.

5. Final question

Why must the table contain both a primary key and a unique key for the duplicate‑entry error to appear?

References

innodb_online_alter_log_max_size: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_online_alter_log_max_size

Bug #76895: https://bugs.mysql.com/bug.php?id=76895