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

[Adam Nelson]

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

[Russell Keith-Magee]

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

[Malcolm Tredinnick]

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.

[Adam Nelson]

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.

[Malcolm Tredinnick]

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.

[Adam Nelson]

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.

[Adam Nelson]

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.

[Adam Nelson]

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

[Adam Nelson]

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.

[Malcolm Tredinnick]

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

[Malcolm Tredinnick]

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

[btimby]

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.

[Ramiro Morales]

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:

• Use the preview button before posting to make sure the formatting of the description is readable.
• Read (al least?) the following two threads: ​http://groups.google.com/group/django-developers/browse_thread/thread/edf3312c4fa1d2d4 and ​http://groups.google.com/group/django-users/browse_thread/thread/ad168ccea8412e0f/96d9339acfe85906 . Note there is some suggestion it isn't easy for the syncdb Django code to know in advance which exact field length limit will be in effect in the DB side.
• If your have experience with MySQL try to suggest a solution of how that limit could be detected or tracked in a robust way for combinations of supported MySQL versions and possible database/table charsets.