Bug #67357 Wrong error reported attempting to create an already existant trigger
Submitted: 24 Oct 2012 11:53 Modified: 18 Jan 2013 17:45
Reporter: Ondřej Bouda Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.6.7-rc OS:Windows (Windows 7)
Assigned to: CPU Architecture:Any
Tags: create trigger, error, existing

[24 Oct 2012 11:53] Ondřej Bouda 
Description:
I issued a CREATE TRIGGER query attempting to create an already existant trigger.

I expected to receive error #1359 (Trigger already exists)

However, I received error #1235 with message: "This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'".

I consider this a bug, since I was not trying to create another trigger with the same action time and event for the table. Instead, MySQL should have found out that the trigger already exists and issue a proper error - #1359.

It also happens in MySQL Server versions 5.1.41 and 5.5.24.

How to repeat:
CREATE TABLE `t` (`a` INT);

CREATE TRIGGER `tr` BEFORE INSERT ON `t` FOR EACH ROW
SET NEW.a = 0;

CREATE TRIGGER `tr` BEFORE INSERT ON `t` FOR EACH ROW
SET NEW.a = 0;

Suggested fix:
Let MySQL report error #1359 (Trigger already exists) sooner than error #1235.
[24 Oct 2012 12:27] MySQL Verification Team 
Thank you for the bug report.
[18 Jan 2013 17:45] Paul DuBois 
Noted in 5.7.1 changelog.

Attempts to create a trigger for which a trigger with the same action
time and event already existed resulted in an ER_NOT_SUPPORTED_YET
error rather than an ER_TRG_ALREADY_EXISTS error.