Cascade delete of data table and search table at same time

Question

I'm storing file system hierarchies of directories and files.

In an innodb table, I store the details of each directory/file and maintain the parent child relationship with a foreign key constraint that will cascade on delete.

A myisam table is used to search these directories/files with a full text search. It contains the names and id's of each row.

Any rows in the data table (innodb table) will have a corresponding row in the search table (myisam table) and adding or removing rows from the data table must be reflected in the search table.

I'm trying to find the best solution to maintain data consistency between the two tables when deleting a parent directory. The innodb table is fine. I delete the parent, the delete cascades through the children until they are all deleted. Deleting the corresponding rows from the myisam table is more difficult.

My first thought was to use an on-delete trigger on the innodb table. When a row is deleted, it deletes the corresponding row from myisam table. However, since MySQL does not activate triggers during a cascade delete (a known bug for 7 years that was fixed by mentioning the lack of support in the manual), that is not an option.

My second thought was put a parent child relationship in the search table, but it is a myisam table to support the full text search functionality, and so it does not support foreign key constraints.

I had heard that innodb now supports full text searches, so I thought maybe I could change the search table engine, but its only available in the lab release.

My last thought was to abandon foreign key constraints and use only triggers to maintain data consistency. On delete, delete from both innodb and myisam table where parent = OLD.id. However, to prevent endless loops that could corrupt all data in the table, MySQL does not support manipulating the data in the same table that activated the trigger.

I have resorted to programmatically retrieving all children under the parent directory through a loop of requests, however, I feel there has got to be a better option. Is there any other work around that would be more efficient? At this point, the only two options I can think of are waiting for one of the above approaches to be fixed or changing to a different RDBMS like PostgreSQL that does support firing triggers from a cascade delete.

Any other ideas would be greatly appreciated.

Answer 1

These sort of headaches are exactly the thing that made me move away from mysql where possible.

... I feel there has got to be a better option ...

Sadly there isn't. The simple problem is that you can't delete cascade and have mysql know what it just deleted. Therefor your only option is to find out what its about to delete before it does (this is the algorithm you suggested at the end).

Since cascading will break your data you should not use an on update cascade key so that attempting to delete a parent directory without deleting the child will fail.

I would advise that you create a procedure to do the heavy lifting (deleting) for you. This will prevent a large IO between your app and the DB as it recuses through all the directories. Iy will also provide common code for doing so if you ever access the same db through a different app (or you just want to do something manually).

As I stated first, I use postgresql mostly these days. This is one example of why.