www.otton.org

ERROR 1116 (HY000): Too many tables; MySQL can only use 61 tables in a join

If you ever run into this MySQL error, something has gone badly wrong with your database schema. In this case, the database had no foreign key constraints, and I was trying to build a DELETE query that cascaded across ~230 tables. Not pretty at all.

It didn’t help that there was no documentation and no consistency in key names - id, clientid, client, fkClient, they all referred to the same key in different tables.

After a few false starts, I gave up on doing the deletes by hand; instead I went through the database creating the foreign key constraints that should have been there in the first place. It’s the same amount of work, I end up with a slightly saner database (always leave the codebase better than you found it), and doing the delete becomes a simple “delete clientid”, which cascades through the rest of the tables removing related data.