Data Transmission Service:What can I do if data loss occurs in the destination database due to online DDL operations performed in a table merging task?

How it works

A table merging task consolidates tables from multiple source databases and synchronizes or migrates the data to a single destination table.

For example, if you configure a task to merge tables from two ApsaraDB RDS for MySQL databases (Database 1 and Database 2) into the destination database, DTS maps the data as follows:

• Table A1 (Database 1) → Table A (destination)

• Table A2 (Database 2) → Table A (destination)

If Table A1 has 100 records and Table A2 has 200 records, Table A in the destination database should contain 300 records after the merge.

Why online DDL operations cause data loss

Online DDL operations work by creating a temporary table, applying schema changes to it, and then using RENAME TABLE to swap the temporary table into place. The sequence of operations is:

1. Create a temporary table:

   CREATE TABLE tmp_table_table LIKE table_name

2. Change the schema of the temporary table:

   ALTER TABLE tmp_table_table XXXX

3. Copy historical data to the temporary table:

   INSERT IGNORE INTO tmp_table_table (SELECT %s FROM table_name FORCE INDEX (%s) WHERE xxx

4. Sync incremental data via binary logs:

   UPDATA/INSERT/DELETE tmp_table_name

5. Switch workloads to the temporary table:

   RENAME TABLE table_name to old_tmp_table_table, tmp_table_name to table_name

After these operations are performed, the data of Table A1 in Database 1 overwrites the data of Table A in the destination database. As a result, the data of Table A2 in Database 2 is lost.

Solution

To avoid data loss, use one of the following approaches: