Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#2188 closed defect (fixed)

[patch] Older versions of MySQL cannot handle varchars > 255 characters in length.

Reported by: Fraser Nevett <mail@…> Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version: master
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

The problem

Versions of MySQL below 5.0.3 only support varchar fields with a maximum length of 255 characters (see the MySQL docs for details). When Django is generating the create SQL for a CharField, CommaSeparatedIntegerField or SlugField it uses the maxlength attribute to specify the varchar length. This clearly has the potential to produce SQL which is not accepted by older (yet still very popular and prevalent) versions of MySQL.

Possible solutions

As I see it, there are three options to address this:

  1. document this as a known issue and leave it up to developers to deal with the problem manually;
  2. detect the problem when validating the model and display an error to the user telling them they can't have a maxlength > 255; or
  3. detect the problem when creating the database and use the text data type (instead of varchar) when maxlength > 255.

Pros and cons of the solutions

I think option one would be the minimum, but really isn't that helpful and doesn't deal with the problem itself. I don't think this is the way to go.

Option two would stop the problem from occurring, but is obviously restrictive and wouldn't allow you the freedom to easily switch between database backends.

The use of the text data type in option three pretty much removes any restrictions on the maxlength of a field, but may have undesired performance issues -- does MySQL treat varchars and texts differently? Also, since text does not have a length specified, MySQL would not perform length validation when inserting or updating data.

What now?

I attach patches for options two and three, and will leave it to your judgement as to which would be more appropriate and in keeping with the Django ethos.

Attachments (2)

Option 2.diff (3.2 KB) - added by Fraser Nevett <mail@…> 10 years ago.
Triggers an error when the model is validated.
Option 3.diff (4.4 KB) - added by Fraser Nevett <mail@…> 10 years ago.
Uses text instead of varchar when necessary

Download all attachments as: .zip

Change History (6)

Changed 10 years ago by Fraser Nevett <mail@…>

Attachment: Option 2.diff added

Triggers an error when the model is validated.

Changed 10 years ago by Fraser Nevett <mail@…>

Attachment: Option 3.diff added

Uses text instead of varchar when necessary

comment:1 Changed 10 years ago by Adrian Holovaty

Status: newassigned

I'd be most comfortable with Option 2.

comment:2 Changed 10 years ago by Malcolm Tredinnick

Resolution: fixed
Status: assignedclosed

(In [3855]) Fixed #2188 -- Raise an error when using long CharFields in combination with
older MySQL versions. Thanks, Fraser Nevett <mail@…> .

comment:3 Changed 10 years ago by sean

The patch breaks python 2.3 compatibility.

comment:4 Changed 10 years ago by Malcolm Tredinnick

(In [3872]) Reintroduced the changes from [3855] with more flexible handling of version
strings. Refs #2188, #2827.

Note: See TracTickets for help on using tickets.
Back to Top