Syncronizing MySQL where tables have triggers and foreign keys defined

On a recent consulting engagement, the PSCE team were charged with what can be considered a fairly common task of synchronising tables between master and slave in MySQL Replication. On this occasion the schema contained both foreign key constraints and triggers, this post describes how we avoided the potential problems related to such an operation.

The process to synchronise tables in MySQL is to first identify the differences between tables and then execute queries which bring those tables into a consistent state. The first part of the process can be handled by the pt-table-checksum tool, which steps through the table analysing sets of rows (chunks) and recording a checksum value. Then taking advantage of replication, the same process occurs on each of the slaves and the checksums can then be compared. Once the entire table has been processed, a second tool pt-table-sync can be used to generate queries which will correct any inconsistencies, executing queries that will result in NOOP on the master, yet effect change on the slave, bringing the master and slave into sync.

At the time of writing, the documentation for the current version of each tool was available here:

PSCE MySQL Table Synchronization Overview

There are a number of potential issues to which may arise whilst executing these procedures, however this post just looks at tables with foreign key definitions, and secondly the existence of triggers on the tables being synchronised. Specifically care must be taken with tables that have foreign key constraints with ON DELETE or ON UPDATE definitions since these might cause unintended changes on the child tables. Since the synchronisation process runs over replication, it is important to avoid the table triggers executing and modifying the updates being sent to synchronise the tables.

To avoid issues with foreign keys, we can configure the tool that synchronises the data to disable foreign key checks at the session level. For the triggers defined on the tables, it is then possible to exploit this session change to modify the trigger behaviour such that the trigger will fire but not execute any changes. This is achieved by adding a conditional to each of the triggers which checks the @@session.foreign_key_checks session variable. When @@session.foreign_key_checks is set to 0 and therefore foreign key checks are disabled the trigger action is also disabled.

CREATE <definer> TRIGGER <trigger_name> <> ON <table_name> FOR EACH ROW BEGIN
IF @@session.foreign_key_checks = 1 THEN
<trigger_action>;
END IF; END

In this particular case only a partial synchronisation was to be performed, meaning only a subset of tables need be check-summed and synchronised. Once we had a list of tables, we could query the information_schema to find out which triggers needed to be modified.

SELECT trigger_name, event_object_schema, event_object_table, event_manipulation
FROM information_schema.triggers t
WHERE
event_object_schema = <Schema name>
AND event_object_table IN (<List of tables>)

The triggers involved in this particular schema had definitions similar to the following.

BEFORE INSERT ON <table_name>
FOR EACH ROW
BEGIN
IF NEW.salary > 500 THEN
SET NEW.sover='Y';
ELSE
SET NEW.sover='N';
END IF;
END */;;
DELIMITER ;

This meant that the triggers could be modified in a uniform manner using a simple command line script.

mysqldump -u${mysql_user} --triggers --no-create-info --no-data --no-create-db --skip-opt ${schema_name} ${tables} > ${trigger_definitions_file}
sed -i 's/BEGIN/BEGINnrtIF @@session.foreign_key_checks = 1 THEN/g' ${trigger_definitions_file}
sed -i 's/END */;;/tEND IF;nrEND */;;/g' ${trigger_definitions_file}

The result being something similar to:

BEFORE INSERT ON <table_name>
FOR EACH ROW
BEGIN
IF @@session.foreign_key_checks = 1 THEN
IF NEW.salary > 500 THEN
SET NEW.sover='Y';
ELSE
SET NEW.sover='N';
END IF;
END IF;
END */;;
DELIMITER ;

Wrapping the entire body of the trigger seemed a little messy, so the final version of the triggers used the statement label syntax to create a quick exit using the LEAVE statement.

BEFORE INSERT ON <table_name>
FOR EACH ROW
BODY: BEGIN
IF @@session.foreign_key_checks = 0 THEN

LEAVE BODY;

END IF;

IF NEW.salary > 500 THEN
SET NEW.sover='Y';
ELSE
SET NEW.sover='N';
END IF;
END */;;
DELIMITER ;

Each trigger could then be dropped and recreated using the modified definition, meaning statements injected to the master by pt-table-sync will execute on the slave with @@session.foreign_key_checks automatically disabling the body of the trigger concerned.

So whilst pt-table-sync supports many options to configure it to the operating environment, sometimes its necessary to change the environment to allow the tool to achieve your goals.

Blog