All Products
Search
Document Center

Data Transmission Service:Configure a data synchronization task for a source database that contains a trigger

Last Updated:Feb 29, 2024

If you select a database as the object to be synchronized and the database contains a trigger that updates a table, data inconsistency may occur. This topic describes how to configure a data synchronization task for a source database that contains a trigger.

Important

If you configure a data synchronization task by using the Data Transmission Service (DTS) console of the new version and the source and destination databases are of the supported types, you can configure the Method to Migrate Triggers in Source Database parameter in the Configure Objects and Advanced Settings step. For more information, see Synchronize or migrate triggers from the source database.

Case

A database named triggertestdata contains the parent and child tables. The parent table contains a trigger. If a data record is inserted into the parent table, the trigger inserts the data record into the child table.

Note

The following table describes the definitions of the tables and trigger.

Object type

Name

Definition

Table

parent

CREATE TABLE `parent` (
  `user_vs_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`user_vs_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8

Table

child

CREATE TABLE `child` (
  `sys_child_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_vs_id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`sys_child_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8

Trigger

data_check

CREATE  TRIGGER `triggertestdata`.`data_check`
AFTER INSERT  ON triggertestdata.parent
 FOR EACH ROW
begin

insert into child(user_vs_id, name) values(new.user_vs_id, new.name) ;

end;

In this case, if an INSERT operation is performed on the parent table during data synchronization, the data in the source child table is inconsistent with the data in the destination child table. To solve this issue, you must delete the trigger in the destination database.

Implementation process

  1. Create a data migration task to migrate data from the source database to the destination database. For more information, see the relevant topic.

    Important
    • In the new version of the DTS console, select Schema Migration for the Migration Types parameter.

    • In the previous version of the DTS console, select Schema Migration for the Migration Types parameter.

  2. Log on to the destination database and delete the trigger that is migrated from the source database.

  3. Create a data synchronization task to synchronize data from the source database to the destination database. For more information, see the relevant topic.

    Important
    • In the new version of the DTS console, Incremental Data Synchronization is selected for the Synchronization Types parameter by default. You need to only select Full Data Synchronization for the Synchronization Types parameter.

    • In the previous version of the DTS console, select Initial Full Date Synchronization for the Initial Synchronization parameter.

Procedure

In this example, the source database is a self-managed MySQL database and the destination database is an ApsaraDB RDS for MySQL instance. For more information about configuration examples, see Overview of data synchronization scenarios.

  1. Create a data migration task to migrate the schema from the source database to the destination database. For more information, see Migrate data from a self-managed MySQL database to an ApsaraDB RDS for MySQL instance. In this example, only Schema Migration is selected for the Migration Types parameter.

    Note

    In the Configure Migration Types and Objects step, select only Schema Migration for the Migration Types parameter, and then select databases and tables to be migrated.

    仅选择结构迁移

  2. During schema migration, DTS migrates triggers to the destination database. After data migration is complete, you must log on to the destination database and execute the following statements to delete the migrated triggers.

    drop trigger <Trigger name>;

    Example:

    drop trigger data_check;
  3. Create a data synchronization task to synchronize data from the source database to the destination database. For more information, see Synchronize data from a self-managed MySQL database hosted on ECS to an ApsaraDB RDS for MySQL instance. In this example, only Initial Full Data Synchronization is selected for the Initial Synchronization parameter.

    Note
    • When you create the data synchronization task, you must select the same objects as those you selected in Step 1.

    • Schema migration is complete. Therefore, you need to select only Initial Full Data Synchronization for the Initial Synchronization parameter.

    仅选择全量数据初始化

Check data consistency

  1. Log on to the source database and insert a data record into the parent table.

    insert into parent values(1,'testname');

    After a data record is inserted, the trigger inserts the data record into the source child table.

  2. Log on to the source and destination databases. Query the data of the source child table and the destination child table. Check whether the data is consistent between the two tables.

    • Query result of the source child table查询结果

    • Query result of the destination child table查询结果

    The results show that the data in the source child table is consistent with the data in the destination child table.