CharField cannot have a "max_length" greater than 255 when using "unique=True"

[germ]

my platform: django 1.1.1, mysql 5.0.67, python 2.6.2

why does syncdb complain
CharField cannot have a "max_length" greater than 255 when using
"unique=True"

when mysql 5.0.67 does allow greater tan 255? is there a way to
override or ignore this error?

this works in mysql
mysql> create table mbg_test_1 (id int(5), url varchar(760) not null,
primary key (id), unique (url));
Query OK, 0 rows affected (0.00 sec)

mysql> desc mbg_test_1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(5) | NO | PRI | 0 | |
| url | varchar(760) | NO | UNI | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

but a Model with
url = models.CharField('Full URL', max_length=760, unique=True)
yields an error from syncdb:
$ manage.py syncdb
Error: One or more models did not validate:
threats.threat: "url": CharField cannot have a "max_length" greater
than 255 when using "unique=True".
$

not sure why django hits this error since the true max length is 767,
not 255:

mysql> create table mbg_test_1 (url varchar(999) not null, unique
(url));
ERROR 1071 (42000): Specified key was too long; max key length is 767
bytes
mysql>

[bruno desthuilliers]

On 7 déc, 23:46, germ <mitch.german...@gmail.com> wrote:
> my platform: django 1.1.1, mysql 5.0.67, python 2.6.2
>
> why does syncdb complain
>  CharField cannot have a "max_length" greater than 255 when using
> "unique=True"
>
> when mysql 5.0.67 does allow greater tan 255? is there a way to
> override or ignore this error?
>
> this works in mysql
> mysql> create table mbg_test_1 (id int(5), url varchar(760) not null,
> primary key (id), unique (url));
> Query OK, 0 rows affected (0.00 sec)
>
>

> not sure why django hits this error since the true max length is 767,
> not 255:
>
> mysql> create table mbg_test_1 (url varchar(999) not null, unique (url));
> ERROR 1071 (42000): Specified key was too long; max key length is 767
> bytes

Are you sure you're using the same encoding (charset) in both cases ?
Note that the above message says "767 *bytes*", not 767 *characters*

Here is what I have (mysql 5.0.75):

mysql> create table mbg_test_1 (url varchar(767) not null, unique
(url)) CHARACTER SET utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 1000
bytes

The point is that utf8 can use up to 3 bytes per character...

[germ]

> The point is that utf8 can use up to 3 bytes per character...

i appreciate your reply. interesting distinction between bytes and
chars. i think i still need some more guidance. still not sure how to
resolve with your suggestion to consdier the character set.

i am able to create the django model class defining the table manually
in mysql without any error. i was also able to create that same model
with django 1.0.2 using syncdb. however, with django 1.1.1 i get the
max_length error (as originally posted). it seems like the django
syncdb code is detecting a size threshold, outputting the error, and
exiting without ever really attempting the mysql table create.

here's the mysql table created by django 1.0.2 syncdb without any
errors (some portions redacted) as output with 'show create table t':

CREATE TABLE `t` (
`id` int(11) NOT NULL auto_increment,
`url` varchar(760) NOT NULL,
...
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`),
...
) ENGINE=InnoDB AUTO_INCREMENT=1374 DEFAULT CHARSET=latin1

and here it is as i manually created it in mysql:

REATE TABLE `mbg_t` (
`id` int(11) NOT NULL auto_increment,
`url` varchar(760) NOT NULL,
...
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

the corresponding model class in models.py has not changed from django
1.0.2 to 1.1.1:

url = models.CharField('Full URL', max_length=760, unique=True)

when we first implemented this a year ago for django 1.0.2 we tried
using a TextField; however, we switched to CharField due to
http://code.djangoproject.com/ticket/2495 which still seems
unresolved.

just looking for a simple way to specify a url field in models.py that
will allow creation with django 1.1.1 syncdb and mysql 5.0.67. want
this url text/char field to be as large as permitted and unique.

thanks all for your time and assistance!

[Karen Tracey]

On Tue, Dec 8, 2009 at 1:21 PM, germ <mitch.g...@gmail.com> wrote:

> The point is that utf8 can use up to 3 bytes per character...

i appreciate your reply. interesting distinction between bytes and
chars. i think i still need some more guidance. still not sure how to
resolve with your suggestion to consdier the character set.

i am able to create the django model class defining the table manually
in mysql without any error. i was also able to create that same model
with django 1.0.2 using syncdb. however, with django 1.1.1 i get the
max_length error (as originally posted). it seems like the django
syncdb code is detecting a size threshold, outputting the error, and
exiting without ever really attempting the mysql table create.

Yes, I believe that change was made in response to this thread:

http://groups.google.com/group/django-developers/browse_thread/thread/edf3312c4fa1d2d4

reporting validation did not detect table creations that MySQL would reject. The ticket to look at for details of the change is #9431 (http://code.djangoproject.com/ticket/9431).  It appears to be rather difficult to determine ahead of time what, exactly, is the limit MySQL will apply. You show it reporting 767 bytes, that thread shows it reporting 999 bytes, the ticket notes it's a different limit for InnoDB compared to MyISAM, the table charset is a factor, etc. 

I think that a limit of 255 was put into Django due to difficulty (impossibility?) of figuring out ahead of time what the limit really is for the table about to be created, and since 255 would definitely not trigger an error, and probably having a unique index on a field longer than that is likely not a great idea.  But I'm not sure about all the reasons, I'm just guessing based on what I read on the thread/ticket.

Karen

[bruno desthuilliers]

On 8 déc, 19:21, germ <mitch.german...@gmail.com> wrote:
> > The point is that utf8 can use up to 3 bytes per character...
>
> i appreciate your reply. interesting distinction between bytes and
> chars.

Been here, done that :-/

> i think i still need some more guidance. still not sure how to
> resolve with your suggestion to consdier the character set.

Well, my suggestion wrt/ charset is to use utf-8 everywhere (os,
shells, source codes, templates and all text files, databases, default
encoding for Apache, etc) - but this won't obviously solve your
problem !-)

> just looking for a simple way to specify a url field in models.py that
> will allow creation with django 1.1.1 syncdb and mysql 5.0.67. want
> this url text/char field to be as large as permitted and unique.

IE won't accept urls longer than 2083 characters IIRC - and a 2083
characters urls is neither very practical nor something you meet
everyday. Also, as Karen said, a unique index on a 2000+ characters
field might not be such a good idea - at least depending on how you're
using it.

Anyway... There's at least one workaround I can think of that imply a
little denormalization: using a text field for the url - without
uniqueness constraint -, and storing the hash() value (or some md5 sum
or like) of the url in another field, this one with a unique
constraint. The hash or md5 would be computed in the model save()
method. This is not totally dry and not as robust as a real unique
constraint on the url field itself, but that's the better solution I
can come with.

[germ]

for my needs i will patch django/db/backends/mysql/validation.py
producing my own locally used django rpm to allow more than 255. in my
configuration this works fine. thanks all for your time and guidance!