MySQL Bugs: #42670: Sync does not update triggers

Bug #42670	Sync does not update triggers
Submitted:	7 Feb 2009 8:57	Modified:	20 Apr 2018 20:23
Reporter:	Kevin Peno	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.0.29	OS:	Windows
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	CHECKED, sync, trigger

Description:
When performing database syncro, triggers are not updated if changed after the first load.

How to repeat:
Create a table with a trigger:

CREATE  TABLE IF NOT EXISTS `test`.`test` (
  `added` TIMESTAMP NOT NULL ,
);

DELIMITER //

CREATE TRIGGER trigTestAdded BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
    SET NEW.added=CURRENT_TIMESTAMP;
END;//

Syncronize with live DB and trigger is added.

#2 Edit trigger definition and resync. Changes are not applied.

Suggested fix:
Replace or update triggers on sync

Thank you for the bug report. I couldn't repeat, please provide your model file to try to test on our side?. Thanks in advance.

I've attached a known problem file as requested. There is a trigger under the table `files` which alerted me to the problem. After you sync for the first time, do something as simple as changing NEW.added to NEW.tested and you will see that the trigger is never syncronized.

Thanks,

Many thanks for pointing this out.

Generally you can't change a trigger in SQL. You need to drop and recreate it.

Maybe that is the reason for this behaviour.

I have to clarify this with development.

Hello,

Yes, I understand that it must be dropped to update. However, when you reported that you could not duplicate the issue I tried amking another project file to duplicate as well. I was also unable to duplicate the issue. When I tried the same process in a new project file, it ALWAYS performed the DROP before adding the "changed" trigger. Something with this file is preventing that from happening however.

Thanks,

This only hits Workbench SE and the direct communication with the database.

When you export as SQL the trigger will be updated but not in Forward Engineering.

The implementation of the synchronization feature cannot simply compare the code of objects like triggers, SPs or views, as they might be modified by the server. Hence we cannot detect changes in this DDL code or we might end up in re-synch'ing such objects even though they did not really change. We will attack this problem in MySQL Workbench 5.2.

At version 6.3 in 2015 this is still an issue.
Can anyone fix this?

As a help to the person who will try to reproduce/fix the issue:

 - for me only the AFTER UPDATE triggers were not always synced;
 - only if the model file is used as an intermediary: sync the model with a "source" database to take over the changes into the model, then (without editing the model itself) sync it to a "destination" database;
 - editing the trigger in the model itself makes the sync process to detect the changes 

Steps to reproduce:

1. Create schema in "source" database

-- MySQL Script generated by MySQL Workbench
-- 06/24/15 10:09:20
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`test`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`test` (
  `added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
  PRIMARY KEY (`added`)  COMMENT '')
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

-- -----------------------------------------------------
-- Table `mydb`.`test2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`test2` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `val` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '')
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

USE `mydb`;

DELIMITER $$
USE `mydb`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `mydb`.`test_AFTER_UPDATE`
AFTER UPDATE ON `mydb`.`test`
FOR EACH ROW
BEGIN
	update test2 set val = val + 1;
END$$

DELIMITER ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

2. Make a new model file

3. Sync the model from the "source" database, so that the model will contain all the tables and the trigger

4. Sync the model into a "destination" database (I've used another remote server)

5. Alter the trigger in the "source" database, lets say change it to:
    update test2 set val = val + 2; 

6. Sync the model from "source" database, take over to the model the changes made earlier.

7. Try to sync the model into "destination" database and it won't detect the change you've made on the trigger.

The expected result would be that the sync process detects the changes...

Hope this helps.
Any feedback is appreciated.

Thanks for the details. They will certainly help to find a solution.

Posted by developer:
 
Bug was fixed in an undefined version. No changelog entry needed.