MySQL Bugs: #51409: Triggers not updating

Bug #51409	Triggers not updating
Submitted:	23 Feb 2010 3:49	Modified:	27 May 2010 16:39
Reporter:	Barry Galbraith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.16, 5.2.17, 5.2.19	OS:	Windows (XP Pro SP3)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	synchronize model, triggers

Description:
Changing triggers in a model, and then synchronising those changes to the source still shows differences between model and source when re-synchronizing.

How to repeat:
Add triggers to a table in a test db with mysql commandline client.
Reverse engineer database to a model.
Confirm the triggers on the test table.
Change 1 or more triggers.
Save the model.
Synchronize model.
Check that changes to triggers are shown.
Select update source.
Select next.
Check the SQL statements are correct.
Complete the changes.
Check the triggers in test db with commandline to ensure that the changes were successfully loaded.
Synchronize the model again.
Check that the triggers changes still show as differences, even though source and model are equal.

Suggested fix:
Fix triggers display status so that when model and source agree, no differences are reported.

Many thanks for writing a bug report.

Unfortunately, I am not able to repeat this.

Here is my test case:

1)

On MySQL CLI:

DROP SCHEMA IF EXISTS bug51409;
CREATE SCHEMA bug51409;
USE bug51409;

CREATE TABLE t(i INTEGER, j INTEGER, PRIMARY KEY(i));
CREATE TABLE t2(i INTEGER, j INTEGER, k INTEGER DEFAULT 5, PRIMARY KEY(i));

DELIMITER §

CREATE TRIGGER tr BEFORE INSERT ON t
FOR EACH ROW
BEGIN
INSERT INTO t2 SET i=NEW.i, j=NEW.j;
END §

DELIMITER ;

2) On Workbench:

a) Reverse engineer schema bug51409
b) edit trigger, change before into after
c) sync model

The script will drop the trigger and recreate it

3) On MySQL cli:

SHOW CREATE TRIGGER tr\G

CREATE DEFINER=`myuser`@`localhost` TRIGGER `bug51409`.`tr`
AFTER INSERT ON `bug51409`.`t`
FOR EACH ROW
BEGIN
INSERT INTO t2 SET i=NEW.i, j=NEW.j;
END

Did I made something wrong?

After the steps you took, resynch your model and source, and the your model will show changes to the trigger still waiting to be sent to the source.
It looks like the "Create Trigger" code written by Workbench doesn't match what SHOW CREATE TRIGGER generates, so the resynch will always show changes waiting to be sent to source.

I loaded multiple triggers on a table, AFTER INSERT, AFTER UPDATE, AFTER DELETE using mysql commandline.

In model, the three triggers "run together".
Edit one (or two) and then synchronize.

Verify that the change was loaded, and then synchronize again. The change is still waiting!

Also, I just tried to insert two triggers on the one table with model.
As soon as you add the second code, it disappears!
I can't add a second trigger with Model.

Add 3 triggers to the table.
Synchronise, and verify the triggers are in the db.
Open and edit all 3 triggers.
Synchronize again, and verify the triggers updated in db.
Synchronize again, and triggers 2 and 3 show changes waiting, even though they've already been sent to db.
Synchronize and bring triggers from source to model.
Check triggers again. Nothing has changed in the editor, but now now changes are shown pending.

I can confirm the behaviour also on 5.2.17 - SOME triggers are considered as different between Model and Source - and are updated over and over on each synchronisation between model and source.

Thank you for the feedback.

Verified as described: after adding second trigger WB decides there are new changes forever.

Simple test case: open sakila.film and press Apply without changing anything.

fix confirmed in repository

An entry has been added to the 5.2.22 changelog:

When synchronizing to a live database, triggers showed as requiring updating, even when the model and live database were identical.