Bug #23241 Unable to CREATE, DELETE, or SHOW Triggers
Submitted: 13 Oct 2006 1:07 Modified: 27 Jun 2007 7:54
Reporter: michael halligan Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.19 OS:Linux (SLES 9 SP3)
Assigned to: CPU Architecture:Any
Tags: MySQL Triggers

[13 Oct 2006 1:07] michael halligan 
Description:
Triggers in 5.0.19 appeaer to be seriously hosed. This trigger worked at one point, but then stopped after restarting MySQL.

How to repeat:
CREATE TRIGGER mediaAiTrig AFTER INSERT ON media FOR EACH ROW UPDATE profile SET activeOn = NOW() WHERE profile.id = NEW.owningProfile;

returns 

ERROR 1359 (HY000): Trigger already exists

mysql> DROP TRIGGER mediaAiTrig;

returns 

ERROR 1360 (HY000): Trigger does not exist

mysql> SHOW TRIGGERS

returns

Empty set (0.00 sec)
[13 Oct 2006 10:12] Sveta Smirnova 
Thank you for the report.

Please try using current 5.0.26 version and say us result.
[14 Nov 2006 0:00] Bugs System 
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".
[25 Apr 2007 15:37] Anca Dogaru 
I am having the same problem. 
I have upgraded to 5.0.33

mysql> select version()//
+-----------------+
| version()       |
+-----------------+
| 5.0.33-standard |
+-----------------+
1 row in set (0.01 sec)

mysql> drop trigger update_misc_fromcosts_ins //
ERROR 1360 (HY000): Trigger does not exist
mysql> create trigger update_misc_fromcosts_ins AFTER INSERT ON channel_cost
    -> FOR EACH ROW
    -> BEGIN
    -> SET @a =1;
    -> END //
ERROR 1359 (HY000): Trigger already exists

mysql> create trigger update_misc_fromcosts_ins_2222 AFTER INSERT ON channel_cost
    -> FOR EACH ROW
    -> BEGIN
    -> SET @a =1;
    -> END //
Query OK, 0 rows affected (0.10 sec)

Why i can't create a trigger with the name update_misc_fromcosts_ins ??
[26 Apr 2007 9:23] Valeriy Kravchuk 
All reporters:

Please, try to repeat with a newer version, 5.0.37, and inform about the results.
[8 May 2007 13:32] Petr Chloupek 
I've just upgraded to 5.0.37 and the problem stays on the place. I tried to dump database and then drop it, but I've got:

ERROR 1010 (HY000): Error dropping database (can't rmdir './xxxxxx', errno: 39)

I've found Bug #26703 - can't drop database with backticks and I'm curious if it could be related somehow, because the trigger, what started to make problems was created like this: 
CREATE TRIGGER tr_binsert_user BEFORE INSERT ON `user`

a haven't used backticked table name before this. 

When I dropped the database and recreated it from dump, everything works for now.
[26 May 2007 23:00] Bugs System 
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".
[27 May 2007 7:54] Valeriy Kravchuk 
If re-creration from dump solves the problem, looks like you had triggers created in some older 5.0.x version and the 5.0.37 just installed over. Please, check. There were some incompatible changes.
[27 May 2007 9:45] Petr Chloupek 
Yes, I'm unable to recreate bug in 5.0.37, so it's fixed for me.
[27 Jun 2007 23:00] Bugs System 
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".