Bug #80934 Replication fails as trigger exists in dumped database.
Submitted: 1 Apr 2016 14:53 Modified: 12 May 2016 9:34
Reporter: Jon Swain Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7.11 OS:Microsoft Windows (Server 2012 R2)
Assigned to: CPU Architecture:Any
Tags: mysqldump, replication, triggers

[1 Apr 2016 14:53] Jon Swain
Description:
Hi,

When trying to set up replication between a slave and master using mysqldump, replication fails as a trigger exists in the dumped database, even though it was recently deleted on the master.

How to repeat:
On the master....

[1] Create a single database called `trigger_test`

[2] Create a test table called `table_with_trigger` as below

CREATE TABLE `table_with_trigger` (
  `AutoID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Created` datetime NOT NULL,
  PRIMARY KEY (`AutoID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[3] Create a trigger called `table_with_trigger_before_insert` on the table `table_with_trigger` as below

CREATE DEFINER=`root`@`localhost` TRIGGER `table_with_trigger_before_insert` BEFORE INSERT ON `table_with_trigger` FOR EACH ROW 
BEGIN
    SET NEW.Created = CURRENT_TIMESTAMP;
END;

[4] Prepare an sql script 'delete_and_create_trigger.sql' as follows 

USE `trigger_test`;

DROP TRIGGER IF EXISTS `table_with_trigger_before_insert`;

SELECT SLEEP(10);

DELIMITER $$

CREATE TRIGGER `table_with_trigger_before_insert` BEFORE INSERT ON `table_with_trigger` FOR EACH ROW 
BEGIN
    SET NEW.Created = CURRENT_TIMESTAMP;
END$$

DELIMITER ;

On the slave....

[1] Start with a clean install of MySQL with no databases.

[2] Prepare a powershell script with the following (replacing the ips and credentials)

Write-Host "Exporting databases from master..."
& mysqldump.exe -u root -p <password> -h <masterip> -P <masterport> --all-databases --single-transaction --quick --triggers --routines --events --result-file="$env:TEMP\MySQL-Master-Export.sql"

Write-Host "Configuring replication settings..."
& mysql.exe -u root -p <password> -e "CHANGE MASTER TO MASTER_HOST='<masterip>', MASTER_PORT=<masterport>, MASTER_USER='root', MASTER_PASSWORD='<password>', MASTER_AUTO_POSITION=1 FOR CHANNEL '<channelname>';"

Write-Host "Importing databases, this could take a while..."
mysql.exe -u root -p <password> -e "SOURCE $env:TEMP\MySQL-Master-Export.sql"

Write-Host "Starting replication..."
& mysql.exe -u root -p <password> -e "START SLAVE"

TESTING....

We are no ready to test....

On the master...

Run the 'delete_and_create_trigger.sql' from the commmand line script as follows

mysql.exe -u root -p <password> -e "SOURCE delete_and_create_trigger.sql"

On the slave...

NOW quickly, whilst the script is running and is sleeping for 10 seconds, run the powershell script to start the dump of the database from the master (which should have no trigger as its been deleted..)

Results...

Once the powershell script finishes it will have imported the database from the master and started the slave. However, replication will fail with the following...

Last_SQL_Errno: 1359
Last_SQL_Error: Error 'Trigger already exists' on query. Default database: 'trigger_test'. Query: 'CREATE DEFINER=`root`@`localhost` TRIGGER `table_with_trigger_before_insert` BEFORE INSERT ON `table_with_trigger` FOR EACH ROW
BEGIN
    SET NEW.Created = CURRENT_TIMESTAMP;
END'

-----------------

If you examine the dump file you will notice that it contains the trigger even though we began the dump after the trigger had been deleted from the master.
[12 May 2016 9:22] Umesh Shastry
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.
[12 May 2016 9:34] Jon Swain
Could you explain to me how this is not a bug? 

If I am taking a dump of a database which has recently had a trigger deleted and the dump contains the trigger, surely this is a bug in MySQL Server as that is what mysqldump is connecting to?

By the look of the respsonse, it seems you have not even tried to recreate the problem and just dismissed it as not a bug.