Opened 19 years ago
Closed 14 years ago
#1820 closed defect (fixed)
Autogenerated ManyToMany fields can generate too long identifiers for mysql to handle
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | normal | Keywords: | mysql |
Cc: | remco@…, crucialfelix@…, bronger@…, nabucosound@…, ville@… | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
Having a model as part of an application called: front_booking:
class AccomodationUnit(Model): Facility = ManyToManyField(Facility, verbose_name = 'faciliteiten')
Generates an "Identifier name is too long" exception when doing a syncdb on mysql 5.0.x. Mysql is only able to store identifiers of 64 bytes max.
_mysql_exceptions.OperationalError: (1059, "Identifier name 'AccomodationUnit_id_referencing_front_booking_accomodationunit_id' is too long")
Possible solution would be to allow a user to somehow influence the naming of these identifiers or use identifier names of 64 bytes max. in size
Change History (18)
comment:1 by , 19 years ago
comment:2 by , 18 years ago
I got the same thing when using models.ForeignKey(). It generated the following identifier:
corporationID_id_referencing_baseline_crpnpccorporation_corporationID
Seems like the Django framework could catch the exception this throws and offer an alternate name (perhaps a pure ASCII hash?) back to the mysql layer.
comment:3 by , 18 years ago
Keywords: | mysql added |
---|---|
Triage Stage: | Unreviewed → Design decision needed |
Version: | magic-removal → SVN |
from django.db import models class This_Is_A_Really_Long_Model_Name(models.Model): var1 = models.CharField(maxlength=20) class And_This_Is_Another_ReallyLong_Model_Name_Because_MySQL_Cant_Take_Long_Identifiers(models.Model): var2 = models.ForeignKey(This_Is_A_Really_Long_Model_Name)
Raises:
_mysql_exceptions.ProgrammingError: (1103, "Incorrect table name 't1820_and_this_is_another_reallylong_model_name_because_mysql_cant_take_long_identifiers'")
which looks related.
Not sure what the best solution is - do we just want to document this?
comment:4 by , 18 years ago
Triage Stage: | Design decision needed → Accepted |
---|
The whole auto-name-generation stuff is a bit fragile in this respect. We spent ages last year fixing up index lengths. Oracle has similar problems, so I keep meaning to look at the boulder-oracle-sprint branch and steal some of those ideas. Need to talk over with Jacob how he wants to merge all that stuff in, because in the medium-term (pre-1.0?) we should abstract a lot of that into the backend.
For now, documenting it is a reasonable band-aid, but it's one of things I want to look at prior to 1.0; just not sure if we'll have time (it's going to be a slightly backwards-incompatible change, which is why I have that target in mind).
comment:5 by , 18 years ago
Hmm.. I don't think it'll be as easy as chopping the generated constraint at position 63, since this will probably make it easier to get clashes. Whilst it's nice to have a human readable name for the constraints, how useful is it really? you can get this info from the database anyway (SHOW INDEX... on mysql).
So - why not just take an md5 of the generated constraint, this way you're guaranteed to get a 32 char constraint, and it'll be unique across the tables/constraints.
comment:6 by , 18 years ago
We've more or less solved the constraint problem already (we try to keep things a little bit readable to help with debugging, but there is a hash involved). This particular ticket is talking about and auto-generated table name, by the looks of it (which is a case we didn't previously look at). I'm not in favour of just using hashes everywhere, because then we are needlessly punishing people who use databases which don't have unreasonably ridiculous identifier limitations and making their debugging and manual work harder. Hence the plan to move a lot of the identifier generation stuff into the respective backends so that databases like MySQL and Oracle can be handled specially.
comment:7 by , 18 years ago
See also #3607 (closed as a dupe of this ticket) for a similar problem with PostgreSQL.
comment:8 by , 17 years ago
Is there any advances with this issue? I'd like to help here. I posted ticket 3607
comment:9 by , 17 years ago
Cc: | added |
---|
Tried to see what the latest version of the 5.1 mysql branch did. But 5.1.23 still bharfs on this. Not sure either what the best thing to do is, it is actually a mysql problem but django users are running into this.
comment:10 by , 16 years ago
Cc: | added |
---|
Just ran into this. The hash may have worked well for the constraint name, but they seem to vary depending on ... (?)
ubuntu:
ALTER TABLE website_releasesnewsletter_additional_recipients
ADD CONSTRAINT releasesnewsletter_id_refs_abstractmailing_ptr_id_14a73856b276acd4 FOREIGN KEY (releasesnewsletter_id
) REFERENCES website_releasesnewsletter
(abstractmailing_ptr_id
);
mac:
ALTER TABLE website_artistnewsletter_additional_recipients
ADD CONSTRAINT artistnewsletter_id_refs_abstractmailing_ptr_id_4326e0be FOREIGN KEY (artistnewsletter_id
) REFERENCES website_artistnewsletter
(abstractmailing_ptr_id
);
ubuntu failed due to long-arsh identifier.
easiest solution is to use shorter class names.
it would be ideal to have db_table used for all related fields and inheritance relationships.
example: even though AbstractMailing uses a db_table of AbsMail
CREATE TABLE AbsMail
(
id
integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
subject
varchar(100) NOT NULL,
from_email
varchar(75) NOT NULL,
status
varchar(32) NOT NULL,
created_on
datetime NOT NULL,
created_by_id
integer NOT NULL,
sent_on
datetime NULL,
content_type_id
integer NULL
)
the subclasses still use the class name:
CREATE TABLE mailings_contactmailing
(
abstractmailing_ptr_id
integer NOT NULL PRIMARY KEY,
body
longtext NOT NULL
);
ALTER TABLE mailings_contactmailing
ADD CONSTRAINT abstractmailing_ptr_id_refs_id_5468a0a0 FOREIGN KEY (abstractmailing_ptr_id
) REFERENCES AbsMail
(id
);
comment:11 by , 16 years ago
In reference to the previous comment: using a consistent length hash is reasonable. Changing the way the column names are created is not worth it. There's no difference whether the table or model name is used at a technical level (they're both just strings), so making the smallest change necessary is preferable.
comment:12 by , 16 years ago
Cc: | added |
---|
comment:13 by , 16 years ago
Hi everybody,
I run into this issue, any updates about it? It is specially frustrating to find your code fail to syncdb in a MySQL production server installation, after months of smooth development using SQLite.
If the use of shorter class names is gonna be the resolution for this ticket, I'd recommend to document it somewhere in the documentation. I am not aware whether or not I can go straight to do it myself or wait for someone to authorise it...
comment:14 by , 16 years ago
Cc: | added |
---|
comment:15 by , 15 years ago
I had a problem with a long constraint name, and got around it by munging Django's output with a Perl one-liner:
./manage.py sqlreset core | perl -pe 's{DROP TABLE}{DROP TABLE IF EXISTS}; s{(?<=CONSTRAINT )(\S{47})\S*?(\S{16})(?= )}{$1_$2}' | mysql
comment:16 by , 15 years ago
Cc: | added |
---|
comment:18 by , 14 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
Have tried this in the current trunk it is no longer a problem
accomodationunit_id_refs_id_6c62fa96
This is now what is being generated.
This is not necessarily the case for ManyToMany fields, but hold for all autogenerated fields. My solution was to just rename the models as to make sure that the generated Identifier names for these models would be < 64 bytes.