Opened 7 years ago

Closed 5 years ago

#9431 closed (fixed)

UNIQUE index on VARCHAR(256+) causes failure on MySQL 5.0 with InnoDB tables, a slightly higher threshold for MyISAM

Reported by: adamnelson Owned by: adamnelson
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords: mysql
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: UI/UX:

Description

Description

MySQL 5.0 can't handle 256 or more characters on a column with a UNIQUE index if the storage engine is InnoDB and the character set is utf8

Test

Shows 1 byte character set working:

mysql> create table foo ( x varchar(999), primary key (x)) character set
= 'latin1';
Query OK, 0 rows affected (0.00 sec)

Shows failure with larger character set (standard python utf8):

mysql> create table foo2 ( x varchar(999), primary key (x)) character
set = 'utf8';
ERROR 1071 (42000): Specified key was too long; max key length is 999 bytes

Shows success that works with utf8:

mysql> create table foo2 ( x varchar(255), primary key (x)) character
set = 'utf8';
Query OK, 0 rows affected (0.01 sec) 

Suggested Fix

./manage.py validate should throw a validation warning if the database is MySQL

Attachments (1)

untitled 21.diff (1.7 KB) - added by adamnelson 7 years ago.
django/django/db/backends/mysql/validation.py

Download all attachments as: .zip

Change History (12)

comment:1 Changed 7 years ago by russellm

./manage.py validate already throws a validation warning if the MySQL version is less than 5.0.3 (which is the version at which the character limit ceased to be a problem). I know it does this because I hit this warning just last week. The validation is done here.

Have you actually seen this problem somewhere with a system configuration that we're not catching? You don't provide a Django model or details on exactly what you are doing, so it's difficult to tell how you actually encountered this problem.

comment:2 Changed 7 years ago by mtredinnick

  • Component changed from Core framework to Database layer (models, ORM)
  • milestone post-1.0 deleted
  • Triage Stage changed from Unreviewed to Accepted

Russell, adding the unique index limits things further on MySQL (it avoids creating indexes over arbitrarily wide columns). See this thread on django-dev for details. We should add some extra validation for that case for MySQL, as well as add a note to the docs for CharField for people developing on other database backends who want their code to also be portable to MySQL.

comment:3 Changed 7 years ago by adamnelson

I already have the patch for it although what I'd like to do is to just make MySQL 5.0 a minimum requirement for Django 1.0 so that the check in validate.py only has to check for that one scenario of varchar > 255 AND unique on the same column rather than bothering with the varchar > 255 generally which is really only a problem for MySQL 4.1 and earlier.

On a side note, I was going to ignore the fact that MyISAM tables could possible handle 300 something bytes in a similar situation since the amount is determined by the size of the overall table structure - would be exceedingly difficult to test ahead of time.

comment:4 Changed 7 years ago by mtredinnick

You can't bump up the minimum MySQL version. There's lots of instances of MySQL out there in the wild and many of them are 4.x. It should be easy enough to write a patch similar to the current validation code that's there for this case without doing anything more intrusive.

comment:5 Changed 7 years ago by adamnelson

I agree although I can't find anywhere where there is a minimum version number for MySQL on Django in any of the official docs or even in the base object. After trudging through the lists, I see talk of deprecating MySQL 4.0 nearly 2 years ago but it seems to be a dead topic. Ostensibly MySQL 3.23 would even work in the current codebase since MySQLdb supports it. I honestly don't think that anybody could run Django 1.0 on MySQL 4.0 using many of the core Django apps currently out there without running into serious problems - mostly related to the lack of robust unicode support before 4.1.

MySQL 4.1 would be the only reasonable minimum but virtually nobody is using it which is why I think MySQL 5.0 is the realistic minimum. I'll send my previous email to the group as I can't imagine that anybody would be able to run Django 1.0 on MySQL 4.0 in a reliable fashion right now and MySQL 5.0 has been stable for 3 years.

comment:6 Changed 7 years ago by adamnelson

To be clear, I'll still write the patch without impacting support for MySQL 4.0. I already have the patch - just wanted to test it first before posting.

Changed 7 years ago by adamnelson

django/django/db/backends/mysql/validation.py

comment:7 Changed 7 years ago by adamnelson

  • Needs tests set

I did this patch a few days ago but it's been waiting while I could figure out how best to test it. I figured I would post so that if somebody wants to get this in and there are existing tests, it can be passed through sooner than I can figure out how to test this best. Although I know MySQL extremely well, I'm brand new to Django so I'll need some catchup time.

comment:8 Changed 7 years ago by mtredinnick

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

(In [9650]) Fixed #9431 -- Added extra validation for VARCHAR-based fields on MySQL.
max_length > 255 and unique=True is not permitted. Based on a patch from
adamnelson.

comment:9 Changed 7 years ago by mtredinnick

(In [9652]) [1.0.X] Fixed #9431 -- Added extra validation for VARCHAR-based fields on MySQL.
max_length > 255 and unique=True is not permitted. Based on a patch from
adamnelson.

Backport of r9470 from trunk.

comment:10 Changed 5 years ago by btimby

  • Resolution fixed deleted
  • Status changed from closed to reopened

OK, so why does Django block me from creating the following table...

python manage.py sql ftp
Error: One or more models did not validate:
ftp.path: "path": CharField cannot have a "max_length" greater than 255 when using "unique=True".

Even though I am using the latin1 charset? I can create the table with column length of 255, then boost it to the required 512.

show create table ftp_path;

CREATE TABLE ftp_path (

id int(11) NOT NULL AUTO_INCREMENT,
site_id int(11) DEFAULT NULL,
path varchar(512) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY path (path),
KEY site_id_refs_id_6224d7e1 (site_id),
CONSTRAINT site_id_refs_id_6224d7e1 FOREIGN KEY (site_id) REFERENCES ftp_site (id)

) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=latin1;

I would say this check as it stands is not quite sophisticated enough if it does not take the charset into account.

comment:11 Changed 5 years ago by ramiro

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

This ticket was closed by applying a fix to the problem taking in account the constraints described in the log above and related discussion in the django-dev list. I'm restoring the ticket status.

If you want to report the issue you are seeing a I'd like to suggest to open a new ticket, taking in account the following:

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