Modify

Ticket #1076 (accepted defect)

Opened 2 years ago

Last modified 8 months ago

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

Reported by: paul.dubois@… Owned by: andrew
Priority: minor Milestone: 1.0
Component: migrations Version: 0.7.5
Keywords: MySQL delete_unique Cc:

Description

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.

Attachments

Change History

comment:1 Changed 2 years ago by andrew

  • Status changed from new to accepted
  • Version changed from unknown to 0.7.5
  • Milestone set to 1.0

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

comment:2 Changed 22 months ago by gordon@…

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

comment:3 Changed 8 months ago by nate@…

I ran into the same problem.

Mysql's delete_unique should also run a create_index after if the column is a foreign key. And the backwards would do a delete_index before in the same case. This worked for me:

    def forwards(self, orm):
        # Removing unique constraint on 'Table', fields ['other_table']
        db.delete_unique('my_table', ['other_table_id'])
+       db.create_index('my_table', ['other_table_id'])


    def backwards(self, orm):
        # Adding unique constraint on 'Table', fields ['other_table']
+       db.delete_index('my_table', ['other_table_id'])
        db.create_unique('my_table', ['other_table_id'])
View

Add a comment

Modify Ticket

Action
as accepted
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.