MySQL Bugs: #52750: renaming existing and inserting new triggers fail

Bug #52750	renaming existing and inserting new triggers fail
Submitted:	11 Apr 2010 21:38	Modified:	22 Jul 2010 9:23
Reporter:	Robert Nowotny	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
when You try to rename one trigger, and at the same time insert another new trigger, the syncronisation with source will fail, because at the SQL Commands created by workbench the "drop trigger" command is created after the "insert trigger" command for the renamed trigger. 

How to repeat:

see above  - just try to rename / insert triggers at the same time (for the same table)

Suggested fix:
when creating the SQL Commands, always create the "drop" sections before the "insert" sections - should be easy ...

For some reason I can not repeat this on Windows. Please, send the exact text of trigger you see when you use ALTER TABLE... menu item, then describe the exact changes to apply.

here the Models and SQL Code

Attachment: Desktop.rar (application/octet-stream, text), 10.16 KiB.

ok, I added the Models and SQL Code generated.
I commented the Errors I have seen in the SQL Text.

There seems also something wrong with the collations - (or at least I have not found were to set standard collation for a new database)
My standard Collation is always UTF8 ... not swedish ... 

1 - forward engineer test1.mwb (standard settings)
2 - try to synchronize Model --> source with  Model test2.mwb

the difference between test1.mwb and test2.mwb is just the Trigger name

I see followin Errors here : 
on Step 1 : 

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;  <-- where the hell comes latin1_swedish_ci from ...

on Step 2: 
ALTER TABLE `mydb`.`table1` COLLATE = utf8_general_ci ; # it was already utf8_general_ci before ... 

Create .... Drop  in the wrong order 

So the update will fail, because DROP Trigger is done AFTER Create Trigger (should have be done before).

Like other people in this forum, I see Workbench trying to update objects that have not been changed, especially triggers and collations.

greetings from vienna
yours sincerely
Ing. Robert Nowotny

Could you please try version 5.2.18 and comment the results here. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

The problem still persists in version 5.21rc

if You rename a trigger in workbench, it can not be added to the database, because the old trigger is dropped too late (after trying to add the new trigger).

And I am getting tired to get messages like "try again with version x.y"
The bug is so easy to reproduce, so please try yourself before You ask.

There are other problems when updating procedure or function code, what is not so easy to trace down, but I did not post it here until now - because it seems to be useless ... 

greetings from vienna
yours sincerely 
Ing. Robert Nowotny

I am not able to repeat this.

Here are my steps:

$ mysql < bug52750.sql

- open Workbench
- reverse engineer schema bug52750
- click onto table t
- choose trigger tab
- rename trigger_t to trigger_foo
- at line 11 (END $$) press enter and fill:

DELIMITER §
CREATE TRIGGER bug52750.trigger_t
BEFORE INSERT ON bug52750.tab
FOR EACH ROW
BEGIN
SET NEW.j=NEW.i + 7;
END§

- sync model
- ignore mydb

here is the result of syncing:

DELIMITER $$

USE `bug52750`$$
CREATE
DEFINER=`miracee`@`localhost`
TRIGGER `bug52750`.`trigger_foo`
BEFORE INSERT ON `bug52750`.`t`
FOR EACH ROW
BEGIN
SET NEW.j=NEW.i + 5;
END$$

USE `bug52750`$$
DROP TRIGGER IF EXISTS `bug52750`.`trigger_t` $$

USE `bug52750`$$
CREATE TRIGGER bug52750.trigger_t
BEFORE INSERT ON bug52750.tab
FOR EACH ROW
BEGIN
SET NEW.j=NEW.i + 7;
END$$

DELIMITER ;

Did I made something wrong?

my sql script

Attachment: bug52750.sql (text/x-sql), 308 bytes.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Dear Susannee, 
You need to rename at the same table - then it fails. In Your code You add or rename the trigger to another table - then it can work.

But since the DROP is comming too late, MySQL can not add the renamed trigger to the same table, because there is only one Trigger allowed on the "BEFORE INSERT" Event.

Hope that can make it more clear.
Just try to rename the Name of an "Before insert" Trigger - You will see that it fails .... 

Your code was : 

USE `bug52750`$$
CREATE
DEFINER=`miracee`@`localhost`
TRIGGER `bug52750`.`trigger_foo`
BEFORE INSERT ON `bug52750`.`t`   # you create trigger to table `bug52750`.`t` here 
FOR EACH ROW
BEGIN
SET NEW.j=NEW.i + 5;
END$$

USE `bug52750`$$
DROP TRIGGER IF EXISTS `bug52750`.`trigger_t` $$

USE `bug52750`$$
CREATE TRIGGER bug52750.trigger_t
BEFORE INSERT ON bug52750.tab   # you create trigger to table bug52750.tab here
FOR EACH ROW
BEGIN
SET NEW.j=NEW.i + 7;
END$$

Also with same table I get a proper result.

This looks like fixed by accident in Workbench 5.2.24.

Please try Workbench 5.2.24 RC and let us know if you still have the problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".