Dropping mysql tables that have a foreign key contraint

1 post / 0 new
#1 Mon, 04/15/2013 - 18:11
fiesch

Dropping mysql tables that have a foreign key contraint

Hi all

I'm currently trying to write a install / uninstall script. The PHP based software is using mysql as it's database and has tables with foreign key constraints.

As the software is plugin-extendable, i can not be sure which tables relate to which other tables and in which way.

When uninstalling the Software, i match db tables against a prefix and try to drop the resulting tables which ends in "cannot delete or update a parent row: a foreign key constraint fails at /usr/libexec/webmin/web-lib-funcs.pl line 1361"

Because of the foreign key constraint i would need to call SET foreign_key_checks = 0 before the call and (for style's sake) SET foreign_key_checks = 1 afterwards.

I utilize the mysql::execute_sql_logged procedure.

However, since the SET foreign_key_check is session-based, each call to execute_sql_safe and it's derivates opens a private connection to the DB and combining multiple commands in one dbi command appears to be illegal this is quite a problem.

My question is: is there a smart way of doing this inside virtualmin / webmin without writing my own mysql lib file (or - worse - modify base). I've looked quite a bit but can't find a clean - less a simply working solution.

Any ideas?