Data Transmission Service:Synchronize tables without primary keys or UNIQUE constraints

Limits

• The Exactly-Once write feature is supported only for full and incremental data synchronization in specific two-way data synchronization instances. For more information, see the Supported databases section of this topic.

• The source database must contain tables that do not have primary keys or UNIQUE constraints and the tables must meet the following requirements:

  • If the Database Type of the source database is MySQL, and the Access Method is not set to Alibaba Cloud Instance, you must enable the global transaction identifier (GTID) mode and set the storage engine of the tables to be synchronized as InnoDB.

  • The size of a single table to be synchronized cannot exceed 200 GB.

  • You cannot perform DDL operations to change data by using the Exactly-Once write feature.

Supported databases

Usage notes

Take note of the following items for a two-way synchronization instance that the Exactly-Once write feature is enabled:

• If the Database Type of the source database is MySQL or PolarDB for MySQL, tables that do not have primary keys or UNIQUE constraints in the source database are temporarily locked during data synchronization.

• The DDL operations that are performed to delete data in the source database may cause data loss, especially when the incremental write module is restarted after which the operations. Make sure that the data in the source and destination databases is consistent.

• During full or incremental data synchronization, DTS creates a database or schema named dts in the destination instance and creates multiple transaction tables in the database or schema. Do not perform DML operations on the transaction tables when the DTS task is running.

• By default, the setting of Enable Exactly-Once Write for a reverse synchronization task is the same as that for the forward synchronization task and cannot be modified.

• DTS performs a full table scan on a table that does not have primary keys or UNIQUE constraints and generates a table snapshot during full data synchronization.

Procedure

1. Go to the Configure Objects step of a forward synchronization task.

   Note

   • If you use the previous version of the configuration page, go to the Configure Objects and Advanced Settings step.

   • For more information, see the topics listed in Overview of data synchronization scenarios.

2. Set the Enable Exactly-Once Write parameter to Yes.

3. Complete the configurations based on your needs.