Code

Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#2257 closed defect (fixed)

[patch] syncdb should generate unique constraint names with hashes

Reported by: mir@… Owned by: mtredinnick
Component: Core (Management commands) Version:
Severity: normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Take a look at how syncdb names constraints:

backend.quote_name('%s_referencing_%s_%s' % (r_col, table, col))

In some circumstances, syncdb generates the same constraint name (for foreign keys= for different tables. Now, using both table names in the name can lead to too long names, so this isn't a solution. I propose to use a hash of both table names + the two attribute names. A quick, easy and dirty version is:

backend.quote_name('%s_referencing_%s_%x' % (r_col, col, hash((r_table, table))))

Here's an example.

class Man(models.Model):
  father = ForeignKey(Man, null=True)

class Woman(models.Model):
  father = ForeignKey(Man, null=True)

These will (currently) both create the constraint 'father_referencing_man_id' (resulting in incomprehensible error message in case of mysql)

Attachments (1)

2257_fullname_1.patch (1.2 KB) - added by bill@… 8 years ago.
supplies db wide unique fk names (for mysql)

Download all attachments as: .zip

Change History (7)

comment:1 Changed 8 years ago by mtredinnick

  • Owner changed from adrian to mtredinnick

comment:2 Changed 8 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

The auto-closer doesn't seem to want to pick this up (in [3373]):

Fixed #2257 -- MySQL wants constraint names to be unique per-database, so fixed
the SQL generation to ensure this.

comment:3 Changed 8 years ago by mtredinnick

  • Resolution fixed deleted
  • Status changed from closed to reopened

David Avraamides pointed out (politely) that I suck. The solution in [3373] only works when all references are generated at the same time. I'll write a better fix shortly; reopening for now, since people seem to be hitting this about once a day.

Changed 8 years ago by bill@…

supplies db wide unique fk names (for mysql)

comment:4 Changed 8 years ago by bill@…

  • Summary changed from syncdb should generate unique constraint names with hashes to [patch] syncdb should generate unique constraint names with hashes

patch produces unique fk names. This one uses the full table names in the fk name (I have another that uses the col names and hashes the two tablenames at the end if you want it). The memo dict reference_names has been taken out.

comment:5 Changed 8 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from reopened to closed

(In [3512]) Fixed #2257 -- make constraint names unique across all table/column
combinations (for the benefit of MySQL).

comment:6 Changed 8 years ago by mtredinnick

Thanks for the patch, Bill, but I ended up going with a hash of the table names at the end, just to save a few characters.

For people reading this in the future, the reason I am not just punting this and using no name so that MySQL can make on up is because it makes debugging a little easier to see a constraint name that makes sense when you violate it. It's worth a little pain to try and get that right.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


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

 
Note: See TracTickets for help on using tickets.