MySQL Bugs: #19240: Trigger missing; can't create it, getting "Table doesn't exist" when dropping it

Bug #19240	Trigger missing; can't create it, getting "Table doesn't exist" when dropping it
Submitted:	21 Apr 2006 2:30	Modified:	22 Apr 2006 11:44
Reporter:	Robin Daugherty	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.19-standard	OS:	apple-darwin8.2.1
Assigned to:		CPU Architecture:	Any

Description:
This has happened to me a few times now.  Running
mysqladmin  Ver 8.41 Distrib 5.0.19, for apple-darwin8.2.1 on powerpc
Server version          5.0.19-standard
Protocol version        10

This also happened with 5.0.18-standard.

I go to drop a trigger, and get an error that the table doesn't exist.  The table exists, but the trigger does not--it has disappeared.  The table is an InnoDB table.  The table has not been dropped and recreated.

The only handling for this seems to be to interrupt service and drop the database.  However, dropping it always causes an error:

mysql> DROP DATABASE wwdc_prod;
ERROR 1010 (HY000): Error dropping database (can't rmdir './wwdc_prod', errno: 66)

So I have to manually delete the folder.

How to repeat:
Don't know how create the problem, but here is what it looks like:

mysql> DROP TRIGGER TaskInsert;
ERROR 1146 (42S02): Table 'wwdc_prod.Tasks' doesn't exist

mysql> show create table Tasks;
*** table design clipped, but it's there ***

Trying to create the trigger that can't be dropped, I get
ERROR 1359 (HY000) at line 837: Trigger already exists

When I dropped the database and it couldn't delete the folder, I found the triggers in the database folder:
/usr/local/mysql/data/wwdc# ls -l
-rw-rw----   1 mysql  wheel  40 Apr 20 13:10 SessionsDelete.TRN
-rw-rw----   1 mysql  wheel  40 Apr 20 13:10 SessionsInsert.TRN
-rw-rw----   1 mysql  wheel  40 Apr 20 13:10 SessionsUpdate.TRN

Perhaps in the future, if I delete these files instead of dropping the database, I will be able to recreate them without dropping the database.  However, I still have no explanation for the absence of these triggers in the database itself.

Found the following in the error log, for each time I got the "table doesn't exist" error:

060420 20:10:17060420 20:10:17 [ERROR] Cannot find table wwdc_prod/Sessions from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.

Of course, I haven't dropped the table, and I'm able to pull data from it.  No file operations have taken place at all.

On my previous comment, deleting a TRN file on a trigger with this problem fixes the issue.  I can again use the CREATE TRIGGER for a trigger of that name, and it appears on the SHOW TRIGGERS list.

So the issues here are: 
 - Why are triggers being "forgotten" while the corresponding TRN files still exist?
 - Why is InnoDB reporting at the same time that the table doesn't exist when it obviously does?

Thank you for a problem report. Have you performed any ALTER TABLE statements? If yes, there is a know bug that answers the following your question:

"- Why are triggers being "forgotten" while the corresponding TRN files still exist?"

Anyway, we need a repeatable set of steps that demonstrates the problem each and every time (test case). Please, also check you test case with a newer version, 5.0.20.

Yes, an ALTER did take place.  Please mark this a dup of Bug #18153.

Duplicate of bug #18153. Fixed in 5.0.20.