MySQL will never migrate this way (not alter table problem)

[Diogo Munaro]

Hey guys, I'm trying to do CI (continuous integration) on web2py application.

I really need automatic database migration and web2py is really good with it, but using MySQL I notice that we have some caveats:

I can live without more than one alter table on migrations, but I can't without this other caveat that is not specified by web2py but is on mysql manual:

Setting foreign_key_checks to 0 can also be useful for ignoring foreign key constraints during LOAD DATA and ALTER TABLE operations. However, even if foreign_key_checks = 0, MySQL does not permit the creation of a foreign key constraint where a column references a nonmatching column type. Also, if a table has foreign key constraints, ALTER TABLE cannot be used to alter the table to use another storage engine. To change the storage engine, you must drop any foreign key constraints first.

So, I can't drop a column without dropping it's foreign key constraints first, and I always get error 150 if I try.

Now I'm migrating my database to Postgres, because web2py REALLY support migrations on it. Just look this citation inside postgres manual:

This form drops a column from a table. Indexes and table constraints involving the column will be automatically dropped as well. You will need to say CASCADE if anything outside the table depends on the column, for example, foreign key references or views. If IF EXISTS is specified and the column does not exist, no error is thrown. In this case a notice is issued instead.

What should we do to solve this issue with MySQL?  A Stackoverflow question said that we should create a table replica, apply migrations on it and than migrate data.

I don't know any other migrations solutions that do it on python. I think South have same problem because new Django 1.7 migration feature South-like have a lot of caveats: https://docs.djangoproject.com/en/dev/topics/migrations/

What do you think about make migrations as stackoverflow question suggest? (http://stackoverflow.com/questions/338978/mysql-innodb-need-to-delete-column-and-accompanying-foreign-key-constraint-a)

Is there another way to do that with MySQL?

 

 Regards

[Diogo Munaro]

Summary: We can't make some database migrates on MySQL because we can't DROP column with foreign key. Suggestions?