So, we spent an embarrassing amount of time here at AF HQ recently wresting with this issue – what seemed like a migration bug in easy-thumbnails (a Django image thumbnailing app to replace the now-basically-defunct sorl-thumbnail).
The symptom was a failing migration (we use South for schema migrations, and easy-thumbnails provides the migration scripts for itself). More than halfway through (migration #10 of 13 I think), we got an error about not being able to drop a foreign key:
db.drop_foreign_key(‘easy_thumbnails_source’, ‘storage_new_id’)
[...] raise ValueError(“Cannot find a FOREIGN KEY constraint on table %s, column %s” % (table_name, column))
ValueError: Cannot find a FOREIGN KEY constraint on table easy_thumbnails_source, column storage_new_id
The answer, which the title of this post sort of gave away, is that MyISAM doesn’t support foreign keys, and this particular database happened to have a few (and the server was set to create MyISAM tables by default… because it’s a per-server, not a per-database setting). Still, why did it take so long to track down the particular problem here? Because the error didn’t point to the ADD FOREIGN KEY statement, which would have made the problem fairly obvious, but rather the DROP FOREIGN KEY statement, which didn’t exactly illuminate the problem.
According to the docs:
“For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.”
So the migrations blissfully buzzed through the foreign key creation, but died on the DROP. I’m sure there was a good reason at the time for this “we’ll just silently ignore your ADD FOREIGN KEY statement” behavior, but it makes debugging things like this pretty damn hard.
Moral #1: Just don’t use MyISAM.
Moral #2: See Moral #1. OK, that’s a bit rash; there are, of course, specific cases where MyISAM outperforms InnoDB; but InnoDB should really be the default, and it’s a damn shame that on many systems it isn’t.