Autogenerated ManyToMany fields can generate too long identifiers for mysql to handle

[remco@…]

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

[remco@…]

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.

[b.nuttall@…]

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.

[Simon G. <dev@…>]

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?

[Malcolm Tredinnick]

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).

[Simon G. <dev@…>]

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.

[Malcolm Tredinnick]

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.

[Malcolm Tredinnick]

See also #3607 (closed as a dupe of this ticket) for a similar problem with PostgreSQL.

[MarioGonzalez <gonzalemario @…>]

Is there any advances with this issue? I'd like to help here. I posted ticket 3607

[Remco Wendt]

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.

[crucialfelix@…]

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);

[Malcolm Tredinnick]

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.

[nabucosound]

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...

[anonymous]

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

[Ramiro Morales]

(reformatted description)

[None]

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.