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.
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.