Cannot repair database - all options tried

[Yarin]

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  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.

[Johann Spies]

On 8 May 2012 07:17, Yarin <ykes...@gmail.com> wrote:

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.

What is happening in the database itself: Can you browse the data in mysql outside web2py?

If so, you can try the following:

* Backup your mysql database (mysqldump)
* Export the mysql-data to csv-file(s).
* Drop the tables
* Fix your models.
* delete all the files in web2py/applications<yourapp>/databases
* start the app again (try "python web2py.py -M -N -S <yourapp>").  This will recreate the tables.
* from appadmin: export the empty tables to get the correct csv-file headings.
* adapt your previously exported csv-files to fit the exported headings.
* try and import it.

Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

[Michele Comitini]

I would add to make a backup copy of the databases folder under your app.
then delete the databases folder content, put db(...., fake_migrate_all=True)
python web2py.py -M -S <your app> [you can exit from the python shell then]

If you keep having troubles you must have some problem in the table
related to foreign keys, see:

https://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

"""If MySQL reports an error number 1005 from a CREATE TABLE
statement, and the error message refers to error 150, table creation
failed because a foreign key constraint was not correctly formed.
Similarly, if an ALTER TABLE fails and it refers to error 150, that
means a foreign key definition would be incorrectly formed for the
altered table. To display a detailed explanation of the most recent
InnoDB foreign key error in the server, issue SHOW ENGINE INNODB
STATUS.
Important"""

mic

2012/5/8 Johann Spies <johann...@gmail.com>:

[Yarin]

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).

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.