MySQL trigger already exists but doesn't show

By ayates

So I just ran into an issue where I am trying to recreate a trigger, I drop the trigger and get an error telling me the trigger already exists e.g.

DROP TRIGGER IF EXISTS test_trigger;

I could overcome this error by specifying the table name e.g.

DROP TRIGGER IF EXISTS `my_table`.test_trigger;

However when I attempt to recreate the trigger I am still getting the "Trigger already exists" error, even though 'SHOW triggers;' doesn't return the trigger that is supposed to be removed.

This issue comes from a now fixed MySQL bug where you likely have a corrupt trigger, you can fix this by simply removing the corrupt trigger from your mysql data directory manually. In my case:

rm /usr/local/mysql-5.5.15-osx10.6-x86/data/my_table/test_trigger.TRN

Problem solved, MySQL now allows me to create the trigger.