Cascading a data table and search table at the same time

Question

I keep hierarchies of file systems directories and files.

In the innodb table, I save the details of each directory / file and maintain a parent child relationship with a foreign key constraint that will be cascaded when deleted.

The myisam table is used to search these directories / files with a full text search. It contains the names and identifiers 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 should be reflected in the search table.

I am trying to find a better solution to maintain data consistency between two tables when deleting the parent directory. The innodb table is excellent. I delete the parent element, cascading deletion through the child elements until all of them are deleted. Removing hard rows from myisam table is difficult.

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

My second thought was related to the parent relationship of the children in the lookup table, but this is the myisam table to support full-text lookup, and therefore it does not support foreign key constraints.

I heard innodb now supports full-text searches, so I thought maybe I could change the search engine, but it is only available in the lab edition.

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

I resorted to programmatically removing all the children in the parent directory through a request loop, however I believe that there is a better option. Is there any other work that would be more effective? . At the moment, only two options that I can think of are waiting for one of the above approaches to be fixed or changed to another DBMS, such as PostgreSQL which supports triggers from cascading deletion.

Any other ideas would be greatly appreciated.

Answer 1

These headaches are exactly what made me leave mysql wherever possible.

... I feel that there should be a better option ...

Unfortunately, no . The simple problem is that you cannot delete the cascade and know mysql that it just deleted. Therefore, your only way is to find out what to remove it before it does it (this is the algorithm that you suggested at the end).

Since cascading will damage your data, you should not use the on update cascade key, so an attempt to delete the parent directory without deleting the child file will fail.

I would advise you to create a weightlifting (removal) procedure for you. This will prevent a lot of I / O between your application and the database, as it rejects all directories. Iy will also provide generic code for this if you ever access the same db through another application (or just want to do something manually).

As I said, I use postgresql mainly these days. This is one example of why.