db.models.TextField cannot be marked unique when using mysql backend

[anonymous]

When I used a field like this:

text     = models.TextField(maxlength=2048, unique=True)

it results in the following sql error when the admin app goes to make the table

_mysql_exceptions.OperationalError: (1170, "BLOB/TEXT column 'text' used in key specification without a key length")

After a bit of investigation, it turns out that mysql refuses to use unique with the column unless it is only for an indexed part of the text field:

CREATE TABLE `quotes` ( `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,     `text` longtext NOT NULL ,     `submitTS` datetime NOT NULL,     `submitIP` char(15) NOT NULL,     `approved` bool NOT NULL, unique (text(1000))); 

Of course 1000 is just an arbitrary number I chose, it happens to be the maximum my database would allow.

Not entirely sure how this can be fixed, but I figured it was worth mentioning.

[anonymous]

errr... to be clear, the SQL I quoted is the working syntax, not what django generates.

[mico@…]

have same error.
what to do?

[anonymous]

Is there a known work around?

[anonymous]

Same problem here. Any workaround in place?

[Simon G. <dev@…>]

A workaround is to leave the unique=true requirement off when you create the table, and run an ALTER TABLE query to create the required index, before re-adding unique=true to the model.

Unfortunately a patch for this is not as easy as I thought it would be - you can't just find the CREATE line in management.py and change the UNIQUE line when the backend is mysql, and the field type is a blog/longtext. As far as I know there's no way to create a UNIQUE index with a blob in this way - this has to be added later to the create table command after the field specifications as a KEY constraint, or run later as an ALTER TABLE command.

[Simon Greenhill <dev@…>]

#6336 was marked as a duplicate.

[Rob Hudson <treborhudson@…>]

This is true for qs_rf as well... perhaps we can fix it there so when it's merged this will be fixed?

Note: Jacob's jellyroll project exposes this bug since it uses a TextField with an index. I believe this was change from an IntegerField to a TextField when Flickr surpassed 32-bit integer IDs for their photos.

[Malcolm Tredinnick]

Ticket is not specific to queryset-refactor. Fixing the version.

[Malcolm Tredinnick]

This doesn't really have anything to do with qs-rf, so removing the keyword. It's not a blocker for that branch.

[Simon Greenhill]

& #7750 was a duplicate

[mizatservercave]

Preliminary hack against Django 1.0.2final

[mizatservercave]

Preliminary hack against Django 1.0.2final

[mizatservercave]

Patch(es) added. For some reason Trac is reluctant to show the patch summary.

This is a quick-n-dirty 'first-cut' at making models.TextField index-enabled when using MySQL.

It has had only very basic testing done (syncdb works and MySQL shows an index installed).

I suspect the method used in this patch can probably be improved upon greatly, and so I refer to this patch as a 'hack'.

Tests are encouraged.

[mizatservercave]

For the record, I asked Django to install an index for me by setting a model.TextField like so:

name = models.TextField(max_length=256, db_index=True)

This made syncdb complain, so I hushed it. :)

[Honza Král]

Added tests and cleaned up the patch a bit

[Honza Král]

Added a new patch that have tests (failing without the patch) and more updated magic number. After discussing with jacob on #django-dev I only fixed the db_index property and not unique or unique_together since that doesn't make much sense for TextFields

Please let me know it the approach taken in the patch is not welcome and I will update it.

[dannyman@…]

Hello,

How do I replace the constraint with ALTER TABLE?

My class boils down to:

class Tag(models.Model):
 name = models.TextField(max_length=32, primary_key=True)
 # Needed for syncdb.  See: http://code.djangoproject.com/ticket/2495
 #name = models.TextField(max_length=32)

So, I ran syncdb using the latter line, and it ran . . . now I'm in mysql . . .

mysql> describe events_tag;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | longtext | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> alter table events_tag add primary key(name);
ERROR 1170 (42000): BLOB/TEXT column 'name' used in key specification without a key length
mysql> alter table events_tag add unique key(name);
ERROR 1170 (42000): BLOB/TEXT column 'name' used in key specification without a key length
mysql> alter table events_tag add unique index(name);
ERROR 1170 (42000): BLOB/TEXT column 'name' used in key specification without a key length

My main concern is that the tag name be unique. Do I have to set that in MySQL, or will Django enforce this for me, or do I have to implement this check within the class? (I already have a save method which does some sanity checking before a new tag can be added, so throwing a duplicate check in there aint a big deal . . .)

Thanks!

Sincerely,
-daniel

[dannyman@…]

A work-around is to use CharField instead of TextField.

-danny

[Honza Král]

Replying to dannyman@toldme.com:

Hello,

How do I replace the constraint with ALTER TABLE?
mysql> alter table events_tag add primary key(name);
ERROR 1170 (42000): BLOB/TEXT column 'name' used in key specification without a key length

well, specify a key length:
mysql> alter table events_tag add primary key(name(255));
should work

[Honza Král]

Marking for 1.2 to get some attention. I am happy to rewrite the patch/tests if something is missing on inadequate.

[Russell Keith-Magee]

Not critical for 1.2

[Honza Král]

another approach to the problem, including rework of #12234

[Adam Nelson]

We up the patch from making the index on the first 255 bytes to 767 bytes if we marked this ticket as an enhancement for MySQL systems from 4.1.2 and up and left behind MySQL 4.0 (version 4.1 came out 6 years ago). IMHO this wouldn't break backwards compatibility since this never worked for anybody anyway and this code is only run when creating or altering the models.

​MySQL 4.1 Documentation

[Łukasz Rekucki]

Latest patch doesn't apply cleanly anymore.

[anonymous]

The two lines below in django/db/backends/postgresql/creation.py seem to be what changed and caused the patch to not apply cleanly anymore.

13363 russellm 2010-06-21 07:48:45 -0400 (Mon, 21 Jun 2010) style.SQL_TABLE(qn(truncate_name(index_name,self.connection.ops.max_name_length()))) + ' ' +
[...]
13451 russellm 2010-07-29 22:54:47 -0400 (Thu, 29 Jul 2010) db_type = f.db_type(connection=self.connection)

I'll attach an updated patch shortly

[anonymous]

Actually, the patch I just attached is missing a __init.py on the newly created directory; attaching a new one shortly

[Guilherme Salgado]

Just noticed the test doesn't fail when I revert the changes, so the patch needs more work

[Guilherme Salgado]

The patch from Honza_Kral doesn't actually fix the problem if you have unique=True, and after talking to andrewgodwin it turns out the fix for that is not trivial, so I'm leaving this for now.

[Jacob]

Milestone 1.3 deleted

[Aymeric Augustin]

Change UI/UX from NULL to False.

[John Jacobsen]

Just got bit by this, so here's my +1...

[ANUBHAV JOSHI]

I think that this is a problem of MYSQL and not Django. MySQL does not stores the key length for longtext types. So its not possible to assign a fey specification before that. And that seems to be a bug of MySQL.

[Aymeric Augustin]

After eight years, and considering that the fix is far from easy, could we file this under "use a better database" and close the ticket?

[Tim Graham]

Attached a patch to document the limitation.

[Tim Graham <timograham@…>]

In e8cbc2b:

Refs #2495 -- Documented that MySQL cannot have TextField(unique=True).

[Tim Graham <timograham@…>]

In 4f83bfa:

[1.8.x] Refs #2495 -- Documented that MySQL cannot have TextField(unique=True).

Backport of e8cbc2b322d873a6fe39faca18d340ce93035087 from master