Propel2: Adding an index to a foreign-keyed MySQL column corrupts the entire table

Building this schema on MySQL:

<?xml version="1.0" encoding="UTF-8"?>
<database name="exampledb">
    <table name="foo">
        <column name="bar_id" type="integer"/>
        <column name="other_column" type="integer"/>
        <foreign-key foreignTable="bar">
            <reference local="bar_id" foreign="id"/>
        </foreign-key>
    </table>
    <table name="bar">
        <column name="id" type="integer" primaryKey="true"/>
    </table>
</database>

… and then modifying it by adding an explicit key that includes bar_id, along with some other change that will cause an ALTER TABLE statement to be generated:

<?xml version="1.0" encoding="UTF-8"?>
<database name="exampledb">
    <table name="foo">
        <column name="bar_id" type="integer"/>
        <column name="other_column" type="integer"/>
        <column name="new_column" type="integer"/>
        <foreign-key foreignTable="bar">
            <reference local="bar_id" foreign="id"/>
        </foreign-key>
        <index>
            <index-column name="bar_id"/>
            <index-column name="other_column"/>
        </index>
    </table>
    <table name="bar">
        <column name="id" type="integer" primaryKey="true"/>
    </table>
</database>

… makes everything go boom, leaving the database schema in a corrupted state:

mark@lunchbox:~$ sudo Propel2/bin/propel diff
2 tables found in all databases.
Comparing models...
Structure of database was modified in datasource "exampledb": 1 modified tables
"/home/mark/generated-migrations/PropelMigration_1454198980.php" file successfully created.
Please review the generated SQL statements, and add data migration code if necessary.
Once the migration class is valid, call the "migrate" task to execute it.
mark@lunchbox:~$ sudo Propel2/bin/propel migrate
Executing migration PropelMigration_1454198980 up


  [Propel\Runtime\Exception\RuntimeException]                            
  Failed to execute SQL "ALTER TABLE `foo`                               
    ADD `new_column` INTEGER AFTER `other_column`". Aborting migration.  



  [PDOException]                                                                                                       
  SQLSTATE[HY000]: General error: 1025 Error on rename of './exampledb/#sql-483_31' to './exampledb/foo' (errno: 150)  


migration:migrate [--platform PLATFORM] [--config-dir CONFIG-DIR] [--recursive] [--output-dir OUTPUT-DIR] [--migration-table MIGRATION-TABLE] [--connection CONNECTION] [--fake] [--force]

The problem is that, due to the order of the statements, the generated migration tries to do an ALTER TABLE while foo has a foreign key that isn’t backed by any index. This fails with the error above - even though FOREIGN_KEY_CHECKS is 0 - and makes the table sort-of disappear (although MySQL still won’t let you recreate it). Here are the statements that get generated and run:

# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

DROP INDEX `foo_fi_426410` ON `foo`;

ALTER TABLE `foo`

  ADD `new_column` INTEGER AFTER `other_column`;

CREATE INDEX `foo_i_565df3` ON `foo` (`bar_id`, `other_column`);

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;

This has been reported long ago.

One obvious-looking strategy to fix this is to reorder the operations in getModifyTableDDL such that this does not occur. However, this is tricky. We’d need to do index dropping and index creation at the same time, prior to making other changes… but you can’t do that either, because index creation has to come after column creation to avoid trying to add an index to a non-existent column. Nightmare! We’d need some sort of dependency-tree-resolution algorithm if we went down this path.

I suspect there’s a neater solution, perhaps involving collapsing all the changes down into a single ALTER TABLE statement. I will play around tomorrow.