Skip to content

Loading…

Issues when adding a unique on two foreign keys (MySQL) #412

Open
skrivanos opened this Issue · 3 comments

3 participants

@skrivanos

Propel1 (rev 8ace328)
MySQL 5.5.15

When migrating from:

<table name="foos">
    <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
    <column name="user_id" type="integer" required="true"/>
    <column name="name" type="varchar" primaryString="true"/>

    <foreign-key foreignTable="users">
        <reference local="user_id" foreign="id"/>
    </foreign-key>
</table>

To:

<table name="foos">
    <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
    <column name="user_id" type="integer" required="true"/>
    <column name="bar_id" type="integer" required="false" />
    <column name="name" type="varchar" primaryString="true"/>

    <foreign-key foreignTable="users">
        <reference local="user_id" foreign="id"/>
    </foreign-key>

    <foreign-key foreignTable="bars">
        <reference local="bar_id" foreign="id"/>
    </foreign-key>

    <unique>
        <unique-column name="user_id"/>
        <unique-column name="bar_id"/>
    </unique>
</table>

Propel generates a migration like this:

SET FOREIGN_KEY_CHECKS = 0;

DROP INDEX `foos_FI_1` ON `foos`;

ALTER TABLE `foos` ADD
(
    `bar_id` INTEGER
);

CREATE INDEX `foos_FI_2` ON `foos` (`bar_id`);

CREATE UNIQUE INDEX `foos_U_1` ON `foos` (`user_id`,`bars_id`);

ALTER TABLE `foos` ADD CONSTRAINT `foos_FK_2`
    FOREIGN KEY (`bar_id`)
    REFERENCES `bars` (`id`);

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

Running this migration causes MySQL to go quite mad. The first statement removes an index used by a foreign key constraint, that in turn causes MySQL to REMOVE (or disable, not sure if it deletes the data) the table when executing the first ALTER statement. MySQL however leaves all constraints on the table which makes it a bit of a pain to recover from.

Edit:
Apparently the new column doesn't really matter, even when adding a unique on the two foreign keys by itself it'll try to drop the FK index.

Edit2:
The fix to this appears to be to put the DROP INDEX statement last, instead of first.

Edit3:
Appears to be unrelated to the fact that it is two FKs, it appears to happen as long as one of the columns in the unique is a FK.

@willdurand
Propel member

Thanks for your work on this issue, and all information you gave. Are you working on a fix?

@skrivanos

No, sorry, I am way too unfamiliar with Propel's code base to be able to fix this efficiently I think.

@smhg

(experiencing the same with MySQL 5.5.21:)

Related to Edit2 above: moving the order around only works because your old and new index have a different name.

The error you get has to do with the fact that the index used in the FK cannot be removed. This only works because of SET FOREIGN_KEY_CHECKS = 0;. Since this is basically applying a change that is not allowed, the results seem to be unpredictable (the deletion of a table is really worrying though).

A solution seems to be:
Group the DROP and CREATE INDEX statements that affect the FK into one statement. e.g.

ALTER TABLE `foo`
    DROP INDEX `foos_FI_1`,
    ADD UNIQUE `foos_U_1` ON `foo` (`user_id`);

(I took away the unrelated SQL from the example above)

Does Propel do this in other cases (statement grouping)? Otherwise it might be complicated to fix this?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.