MySQL: delete_unique() fails if the index "supports" a FK

[paul.dubois@…]

I'll paraphrase a mail I recently sent to the transifex-devel list

In (at least) MySQL 5.0.x and 5.1.x, foreign keys need to be "supported" by an index. If the user explicitly adds an index, MySQL will use that
one. MySQL will not allow the index to be dropped. This is a problem for the following automatically-generated migration:

(Ref: ​https://github.com/transifex/transifex/blob/1.2/transifex/addons/webhooks/migrations/0002_allow_many_hooks_to_a_project.py)

def forwards(self, orm):
    # Removing unique constraint on 'WebHook', fields ['project']
    db.delete_unique('webhooks_webhook', ['project_id'])

    # Changing field 'WebHook.project'
    db.alter_column('webhooks_webhook', 'project_id', self.gf('django.db.models.fields.related.ForeignKey')(to=orm['projects.Project']))

The call to delete_unique() causes a mysterious MySQL error (I believe they have changed it to something more user-friendly in later releases):

  _mysql_exceptions.OperationalError: (1025, "Error on rename of '.\\tfex\\#sql-27f0_16' to '.\\tfex\\webhooks_webhook' (errno: 150)")

One fix for transifex is to hand-modify the migration like so:

    def forwards(self, orm):
       
        # Removing unique constraint on 'WebHook', fields ['project']
+       # Remove the foreign key to avoid problems with MySQL; it'll get recreated.
+       db.delete_foreign_key('webhooks_webhook', 'project_id')
        db.delete_unique('webhooks_webhook', ['project_id'])

        # Changing field 'WebHook.project'
        db.alter_column('webhooks_webhook', 'project_id', self.gf('django.db.models.fields.related.ForeignKey')(to=orm['projects.Project']))

I suppose another possiblity is for MySQL's delete_unique to get a little smarter.

[andrew]

Yep, I'd say in this case that delete_unique is what needs improving, as conflating indexes and uniques is a MySQL-specific thing.

[gordon@…]

I tried this and the foreign_key constraint didn't get recreated