MySQL Bugs: #71868: triggers with lower case text don't show up

Bug #71868	triggers with lower case text don't show up
Submitted:	27 Feb 2014 17:02	Modified:	14 May 2014 22:33
Reporter:	mike coutinho	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	anything after 6.06	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	trigger

Description:
I had some triggers in a table saved in my model.  The triggers looked like this:
CREATE TRIGGER `trg_tech_before_insert` before INSERT ON `tech` FOR EACH ROW

There is also a BEFORE INSERT action.  In later versions of workbench, these before triggers where they have lowercase letters do not show up.  I lost data and had to revert to previous versions because they aren't registering correctly.

How to repeat:
Create a trigger with a before in lower case.

Thank you for the bug report. This happens modeling or server alive?. Thanks.

it was happening in the server environment and the design modeling area from what I can tell.

I found this because I was looking to add a before trigger event on a table. When I went into my model, i didn't see one, which I thought odd since I thought there would have been one there, so I created a new one.  The new one uses uppercase for the first line.  I kept on rolling.  

I tested it in my dev environment then moved it to production.  no problems.  But then my code started backfiring and I couldn't figure out why.  Then I noticed I was missing code from my table in the before insert trigger.  When I went into all of my environments, prod, test, qa, and test 2, I couldn't see the before action on anything except for the new one I just created.  I even pulled a backup from 5 months ago and restored it to make sure I could find the code.  It wasn't until I went back 4-5 versions of workbench that I noticed that I could see the code again.  The workbench interface would display the before INSERT and the BEFORE INSERT.   Then I figured out that by adding in the BEFORE INSERT code that I wiped out the before INSERT code that existed.  

To fix this I went through every table I had in my model, over 200 tables, and noted which ones had any trigger definitions with lowercase letters.  I modified all of them to have uppercase letters.  I then opened it up in the new 6.1 beta workbench and could see my beautiful triggers.

So the issue is fixed on 6.1?.

i first found the issue on 6.1, so no it is not fixed there.  I believe i had to go back to 6.0.5 to find a version that worked and I could see my triggers that had lowercase text in them. I will try the new 6.1.2 beta that came out today and see if it works or not.

Thank you for the feedback. If still the issue exists please provide a test case
with table/trigger create statements. Thanks.

This should (hopefully without any modifications) show you what I am talking about.  Create the testdb database.  Create the table.  Add a trigger with the BEFORE in all caps.  

Don't run the whole thing in a script.  

Perform an alter on the table and look at the trigger and you should see it.  

Now run the alter trigger that is at the end that changes the BEFORE to before.  

Perform an alter on the table and look at the trigger.  You won't be able to see it.

I just did this in the 6.1.2 beta and it did not show up.  So this is still not working in this version.  What is odd though is that on the side in the table navigation at the left, it showed the trigger with both BEFORE and before.  I have some screenshots to upload if I can.

CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

CREATE TABLE `testdb`.`testtable` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `col1` VARCHAR(45) NULL,
  `col2` VARCHAR(45) NULL,
  `col3` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

USE `testdb`;

DELIMITER $$

DROP TRIGGER IF EXISTS testdb.testtable_BINS$$
USE `testdb`$$
CREATE TRIGGER `testtable_BINS` BEFORE INSERT ON `testtable` FOR EACH ROW
begin
	set new.col1 = now();
end

$$
DELIMITER ;

USE `testdb`;

DELIMITER $$

DROP TRIGGER IF EXISTS testdb.testtable_BINS$$
USE `testdb`$$
CREATE DEFINER=`coutinho`@`%` TRIGGER `testtable_BINS` before INSERT ON `testtable` FOR EACH ROW
begin
	set new.col1 = now();
end$$
DELIMITER ;

screenshot of workbench

Attachment: Screen Shot 2014-03-05 at 4.43.58 PM.png (image/png, text), 52.63 KiB.

screenshot of workbench

Attachment: Screen Shot 2014-03-05 at 4.44.07 PM.png (image/png, text), 16.30 KiB.

screenshot of workbench

Attachment: Screen Shot 2014-03-05 at 4.44.40 PM.png (image/png, text), 37.94 KiB.

screenshot of workbench

Attachment: Screen Shot 2014-03-05 at 4.44.53 PM.png (image/png, text), 15.46 KiB.

screenshot of workbench

Attachment: Screen Shot 2014-03-05 at 4.45.00 PM.png (image/png, text), 123.12 KiB.

Thank you for the feedback.

Fixed as of the upcoming MySQL Workbench 6.1.6 release, and here's the changelog entry:

Mixed case and lower cased triggers were not properly displayed in the
"Alter Table" tab, causing them to not be visible or editable.

Thank you for the extremely detailed bug report.

I'm using Version 5.2.47, Rev. 10398.  I have this problem too.  I have a table called RFQs, and the default trigger name is RFQs_BINS.  When I added the trigger, it wouldn't show up, much in the manner described here.  However, it showed in the schema list on the left.  I did a SHOW TRIGGERS, and the trigger was in the database.

It was a small trigger, so I deleted it and added it again, changing the name to RFQS_BINS, and the problem was corrected.