Description:
Hi,
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 DATABASE test_jfg;
CREATE TABLE test_jfg.t(
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
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.
