ApsaraDB RDS:DDL unique key conflict optimization

How it works

Background: MySQL Online DDL allows concurrent writes, updates, and deletes on a target table during a DDL operation. These modifications are recorded in a row log. Just before the DDL finishes, the engine applies the row log to update the new data file. If the table has a unique index and a unique key conflict occurs in the changes generated during the DDL operation, row log application fails, and the entire DDL operation fails. Because row log application happens right before the DDL finishes, a failure at this stage is costly.

Overview: ApsaraDB RDS for MySQL optimizes this behavior. With this optimization enabled, unique key conflicts that occur during Online DDL do not cause the DDL operation to fail.

Usage notes

To enable the DDL unique key conflict optimization, your database instance must meet one of the following version requirements. If your instance does not meet these requirements, upgrade the major engine version or upgrade the minor engine version.

• MySQL 8.4

• MySQL 8.0 with a minor engine version of 20250531 or later

The following limitations apply when you use the DDL unique key conflict optimization:

• DDL operations that change the primary key are not supported.

• Conflicts on unique indexes created during the DDL operation are not supported.

• This feature does not support unique indexes that contain a virtual column or multi-valued unique indexes.

Parameter management

Verify the feature

Test procedure

Follow these steps to test the DDL execution behavior when the DDL unique key conflict optimization is enabled and disabled:

1. Create a table with a unique key and insert data.

   -- Create a table with a unique key
   CREATE DATABASE IF NOT EXISTS test_db;
   USE test_db;
   DROP TABLE IF EXISTS unique_test;
   CREATE TABLE unique_test (
       id         INT AUTO_INCREMENT PRIMARY KEY,
       user_id    VARCHAR(50) NOT NULL,
       name       VARCHAR(100),
       UNIQUE KEY uk_user_id (user_id)
   );
   
   -- Insert initial data (for example, 2,000,000 rows)
   DELIMITER $$
   DROP PROCEDURE IF EXISTS insert_initial_data $$
   CREATE PROCEDURE insert_initial_data(IN num_rows INT)
   BEGIN
       DECLARE i INT DEFAULT 1;
       START TRANSACTION;
       WHILE i <= num_rows DO
           SET @uid = CONCAT('user_', i);
           SET @name = CONCAT('Name_', i);
           INSERT INTO unique_test (user_id, name) VALUES (@uid, @name);
           SET i = i + 1;
       END WHILE;
       COMMIT;
   END $$
   DELIMITER ;
   
   CALL insert_initial_data(2000000);

2. Start a session and continuously insert duplicate values to trigger unique key conflicts.

   USE test_db;
   
   DELIMITER $$
   DROP PROCEDURE IF EXISTS insert_duplicate_loop $$
   CREATE PROCEDURE insert_duplicate_loop(
       IN loop_count INT
   )
   BEGIN
       DECLARE i INT DEFAULT 0;
       DECLARE CONTINUE HANDLER FOR 1062 BEGIN END; -- Ignore duplicate key errors
   
       WHILE i < loop_count DO
           -- Try to insert an existing user_id to trigger a unique key conflict
           INSERT INTO unique_test (user_id, name) VALUES ('user_1', 'ConflictUser');
           SET i = i + 1;
       END WHILE;
   END $$
   DELIMITER ;
   
    -- Loop 100,000 times to insert duplicates
   CALL insert_duplicate_loop(100000);

3. Start another session to test the DDL execution behavior with the DDL unique key conflict optimization enabled and disabled.

   ALTER TABLE test_db.unique_test ENGINE = InnoDB;

Test results

• When the DDL unique key conflict optimization is disabled, the DDL operation fails and returns an error.

  -- innodb_online_alter_ignore_dup_key_error_for_uk = OFF
  mysql> ALTER TABLE test_db.unique_test ENGINE = InnoDB;
  ERROR 1062 (23000): Duplicate entry 'user_1' for key 'unique_test.uk_user_id'

• When the DDL unique key conflict optimization is enabled, the DDL operation completes successfully.

  -- innodb_online_alter_ignore_dup_key_error_for_uk = ON
  mysql> ALTER TABLE test_db.unique_test ENGINE = InnoDB;
  Query OK, 0 rows affected (6.03 sec)
  Records: 0  Duplicates: 0  Warnings: 0