MySQL Bugs: #10946: Confusing error messeges in the case of duplicate trigger definition

Bug #10946	Confusing error messeges in the case of duplicate trigger definition
Submitted:	29 May 2005 21:15	Modified:	24 Jul 2006 18:26
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	506-beta-log	OS:	Linux (Linux Suse 9.3)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
There are two cases where the system consideres a trigger to be a duplicate of an existing trigger:
1) When a trigger on the same table already exists with the same name.
   for example:
mysql> use test;
Database changed
mysql> create table t1 (f1 integer);
Query OK, 0 rows affected (0.01 sec)
mysql> create trigger tr1 before insert on t1 for each row set new.f1=5;
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger tr1 before update on t1 for each row set new.f1=5;
ERROR 1359 (HY000): Trigger already exists

2) When a trigger with a different name but with the same action/timing is created
   on the same table. For example:
mysql> use test;
Database changed
mysql> create table t1 (f1 integer);
Query OK, 0 rows affected (0.01 sec)
mysql> create trigger tr1 before insert on t1 for each row set new.f1=5;
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger tr2 before insert on t1 for each row set new.f1=5;
ERROR 1359 (HY000): Trigger already exists

Note: In both cases the same error is generated although the problem is different and this is confusing. 
Furthermor, in scenario #2 above, when trying to drop the 'duplicate' trigger tr2, the following error is displayed:

mysql> drop trigger t1.tr2;
ERROR 1360 (HY000): Trigger does not exist
which is confusing even more (and will be for customenrs) .....

The two errors should result in different error messages.

How to repeat:
see above description

Suggested fix:
The error in the second case should be different and point to the fact that this is
not a case where the trigger already exists but a case where a trigger on the
action/timing specified exists for the table.

If it in not possible to change the code at this point in the release, 
at least the above behavior should be documented clearly.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27926

Dmitri, please check the current patch and talk to Tomash how it should be fixed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8316

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8434

Pushed into 5.0.25 and 5.1.12

Noted in 5.0.25, 5.1.12 changelogs.

The same trigger error message was produced under two conditions: The
trigger duplicated an existing trigger name, or the trigger
duplicated an existing combination of action and event. Now different
messages are produced for the two conditions so as to be more
informative.