Bug #104608 Improve error message for ALTER failing because concurrent Duplicate entry.
Submitted: 12 Aug 2021 22:31 Modified: 10 Nov 2021 13:24
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.7.35, 8.0.26 OS:Any
Assigned to: CPU Architecture:Any

[12 Aug 2021 22:31] Jean-François Gagné

in Bug#76895 and Bug#98600, we have an ALTER TABLE (and an OPTIMIZE TABLE) failing with a Duplicate entry error.  However, this error message is misleading.  The failure is during the log apply phase of an online ALTER and is documented in [1] as follow:

> When running an in-place online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. 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. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

[1]: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html

I think the error message should be something like "Online ALTER failed during log apply because of a concurrent Duplicate entry (or INSERT ... ON DUPLICATE KEY UPDATE)".  Also, maybe the error message could point in the direction of a solution, like using "LOCK=EXCLUSIVE" or "SET old_alter_table = ON".

Many thanks for looking into this,

Jean-François Gagné

How to repeat:
# Create a sandbox:
dbdeployer deploy single mysql_8.0.26

# Initialize the test environment:
./use <<< "
  CREATE TABLE test_jfg.t(
    v INT, v2 INT,
    UNIQUE KEY(v))"
# Make rows big to be able to quickly generate a big table (each row is close to 8 KB):
seq -f "ALTER TABLE t add column c%02.0f CHAR(255) DEFAULT '';" 1 30 | ./use test_jfg

# Make commit fast so loading is not too long:
./use <<< "set global sync_binlog = 0, global innodb_flush_log_at_trx_commit = 0"

# Fill the table with about 1 GB of data:
seq -f "INSERT INTO t (v) VALUES (%0.0f);" 1 $((1024/8*1024)) | ./use test_jfg

# Now generate the error:
( sleep 1; ./use test_jfg <<< "INSERT INTO t (v) VALUES (1)"; echo "INSERT $(date)"; ) & \
  ./use test_jfg <<< "ALTER TABLE t DROP COLUMN c01"; echo "ALTER $(date)"
[1] 9321
ERROR 1062 (23000) at line 1: Duplicate entry '1' for key 't.v'
INSERT Thu Aug 12 22:28:55 UTC 2021
ERROR 1062 (23000) at line 1: Duplicate entry '1' for key 't.v'
[1]+  Done                    ( sleep 1; ./use test_jfg <<< "INSERT INTO t (v) VALUES (1)"; echo "INSERT $(date)" )
ALTER Thu Aug 12 22:30:17 UTC 2021

# As you can see, both INSERT and ALTER failed with the same error message, which is confusing.

Suggested fix:
Improve the error message as suggested in the description.
[13 Aug 2021 4:27] MySQL Verification Team
Hello Jean-François,

Thank you for the feature request!

[15 Aug 2021 17:57] Jean-François Gagné
related: Bug#104626.
[10 Nov 2021 13:24] Jean-François Gagné
Fixing typo in title, from:

Improve error message for ATLER failing because concurrent Duplicate entry.


Improve error message for ALTER failing because concurrent Duplicate entry.