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

[29 May 2005 21:15] Omer Barnir
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.
[5 Aug 2005 15:00] Bugs System
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
[22 Jun 2006 12:21] Konstantin Osipov
Dmitri, please check the current patch and talk to Tomash how it should be fixed.
[27 Jun 2006 13:08] Bugs System
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
[28 Jun 2006 19:50] Bugs System
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
[13 Jul 2006 16:11] Konstantin Osipov
Pushed into 5.0.25 and 5.1.12
[24 Jul 2006 18:26] Paul DuBois
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.