*Update on this:* I've figured out the original cause of the corruption and it's very simple to reproduce: When pointing to an InnoDB MySQL server, dropping a foreign key column in db.py will cause a SQL error because of the foreign key constraint MySQL automatically sets up. This is not web2py specific, you will get this error when trying to drop the column in phpmyadmin as well.
The answer is to manually drop the foreign key constraint in SQL (Process described here: Unable to drop a foreign key | DaniWeb<http://www.daniweb.com/web-development/databases/mysql/threads/405422/unable-to-drop-a-foreign-key> ). Of course it would be great if web2py's DAL could handle this. Right now, on OSX pointing to MySQL, dropping a foreign key in DB will get you the error described the first time, and hangs subsequent to that. A server restart is usually sufficient to clear the hangs, but for whatever reason my one app got stuck in a persistant hang that I could not shake and that required me to restart my machine. That particular scenario I haven't been able to reproduce. I can't believe I haven't bumped up against this before. At the very least it deserves documentation in the MySQL section of the DAL chapter in the book. On Tuesday, May 8, 2012 1:17:11 AM UTC-4, Yarin wrote: > > It all started with an a single column deletion on a single table in my > db.py: > <class 'gluon.contrib.pymysql.err.InternalError'> (1025, u"Error on rename > of './db_myapp/#sql-198_246f' to './db_myapp/note' (errno: 150)") > > Since then I've spent *hours* trying to reconstitute the database files > in any way possible. An earlier issue I had reported > here<https://groups.google.com/forum/?fromgroups#!topic/web2py/YUDE19OQC8M> > had been resolved by setting migrate=True, fake_migrate_all=True, but this > time around this doesn't do anything. The only time I can get the app > running is when migrate=False. Any time migrate=True, in *any*combination > with fake_migrate, my connection hangs, I get no errors, no db > files ever get built, and have to quit the server. I've even cleared out > the entire db.py file except for the auth table, and still, migrate=True > causes a hang. > > Since that first error I haven't been able to generate a single error, or > generate a single db file, in any conceivable combination. At this point > I'm at a complete dead end and can't imagine what the issue could be or > what more to try. > > I understand that the db definitions may be corrupted from time to time, > even though in this case I see no reason why they should have been. But my > real concern is that *there's no process I can fall back on to rebuild > the definitions from scratch*. Please help me with this. >
