MySQL Bugs: #28117: problem deleting a trigger

Bug #28117	problem deleting a trigger
Submitted:	26 Apr 2007 9:19	Modified:	26 Apr 2007 19:50
Reporter:	Anca Dogaru	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.33	OS:	Linux (centos)
Assigned to:		CPU Architecture:	Any

Description:
I am having problems deleting a trigger after some time it has been used.
This bug is related to 

http://bugs.mysql.com/bug.php?id=23241

It happend on 5.0.24 and on 5.0.33 too

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 ??

How to repeat:
I cannot reproduce the bug with other trigger, but i can;t fix this situation either.

Thank you for a problem report. Please, try to repeat with a newert version, 5.0.37, and inform about the results. I was not able to repeat the behaviour described with latest 5.0.42:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.42 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tt (c1 int);
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter //
mysql> create trigger update_tt after insert on tt for each row set @a=1//
Query OK, 0 rows affected (0.03 sec)

mysql> drop trigger update_tt//
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger update_tt after insert on tt for each row set @a=1//
Query OK, 0 rows affected (0.01 sec)

If your complete(!) test case is different, please, send it.

After some research work i think this is a duplicate of
http://bugs.mysql.com/bug.php?id=18333

since before upgrading mysql version we used 5.0.19 too

i can see now the *.TRN files are still there and that is the reason i cannot delete the trigger (they probably got corrupted when and update was added).

Duplicate of bug #18153.