All Products
Search
Document Center

Configure synchronization when triggers exist

Last Updated: May 28, 2019

The real-time synchronization feature provided by DTS is not perfectly compatible with triggers. When the object to be synchronized is an entire database and the database contains a trigger that updates the synchronized table, the synchronized data may be inconsistent.

For example, the synchronization database is jiangliu_trigger_test, and the database contains two tables, namely parent and child. A trigger is in the parent table. When a row is inserted into the parent table, a row is inserted into the child table. Under such circumstances, the synchronization initialization migrates these two tables and the trigger to the target instance. During the synchronization, if an insert operation is performed on the parent table in the source instance, data in the child table is inconsistent between the source and target instance.

Assume that the parent table is defined as follows:

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

The child table is defined as follows:

  1. CREATE TABLE `child` (
  2. `sys_child_id` int(11) NOT NULL AUTO_INCREMENT,
  3. `user_vs_id` int(11) DEFAULT NULL,
  4. `name` varchar(30) DEFAULT NULL,
  5. PRIMARY KEY (`sys_child_id`)

The trigger on the parent table is as follows:

  1. CREATE DEFINER= `dts`@`%` trigger data_check after
  2. insert on parent for each row begin
  3. insert into child(user_vs_id, name) values(new.user_vs_id, new.name) ;end

To solve this problem, we must delete the data_check trigger in the target instance. Data in the child table is synchronized from the source instance. This topic describes how to configure the synchronization link when a trigger exists to avoid data inconsistency.

The steps for configuring the synchronization link when a trigger exists are as follows.

Object schema migration

Log on to the DTS console and create a migration task for object schema migration only and migrate all the object schemas of the objects to be synchronized to the target database. Follow these steps:

  1. Log on to the DTS Console.

    Log on to the DTS console, click Create Migration Task in the upper-right corner, and then create an object schema migration task.

  2. Configure the instance connection.

    Configure the migration task name and the connection information of the source and target instances. Specifically:

    Select RDS instance for both the source and target instance type.

  3. Select a migration type and a migration object.

    Select only object schema migration as the migration type. Do not select full migration and incremental migration, because we only need to migrate the object schema to the target RDS instance.

    Select migration type

  4. Perform a pre-check.

    After the pre-check is complete, start the object schema migration.

Delete the trigger for the target instance

After the schema migration is complete, manually delete the trigger migrated to the target RDS instance by executing the following SQL statements:

  1. show triggers;
  2. drop trigger data_check;

The data_check is the trigger to be deleted.

After the trigger is deleted from the target instance, you can start to create a synchronization link.

Because the schema has been migrated, you should select full data initialization for the synchronization initialization.

Configure the synchronization link as follows:

Enter the data synchronization page, and click Create Synchronization Task in the upper-right corner to purchase a synchronization link. After the synchronization link is ready, return to the DTS Console, and click Configure Synchronization Link on the right side of the link to configure it.

  1. Configure instance information.

    Set the migration task name and configure the connection of the source and target instances. Specifically:

    • Task name

    By default, DTS generates a name for every task automatically. The task name is not required to be unique. You can modify the name. We recommend that you use an informative name that is easy to identify.

    • Connection information of the source instance

      • Instance type: Select RDS Instance.
      • RDS instance ID: Configure the ID of the RDS instance
      • Database account: The account for accessing the RDS instance
      • Database password: The password of the preceding database account
    • Target instance connection

      • Instance type: Select RDS Instance.
      • RDS instance ID: Configure the ID of the RDS instance
      • Database account: The account for accessing the RDS instance
      • Database password: The password of the preceding database account
  2. Select a synchronization object

    Select a synchronization object in this step. The synchronization object selected must be consistent with the schema migration object above.

  3. Initialize the synchronization

    Configure the initialization setting of the synchronization

    Note: Only check full data initialization here. Do not check schema initialization, because after you complete the preceding schema migration, the schema definition of the synchronization object has been migrated to the target RDS instance.

  4. Pre-check

    After the pre-check succeeds, you can start the synchronization task.

    You have configured the entire synchronization link.

Data consistency

  1. Insert a record in the parent table of the source database.

    1. insert into parent values(1,'jiangliu');
  2. Verify whether the data of the child table is consistent between the source and target databases.

    Query data in the child table in source and target instances separately.

    1. select * from child;

    The result is as follows:

    • Source database:

    Source database

    • Target database:

    Target database

    We can see that the synchronization data is consistent.