Opened 18 years ago

Closed 9 years ago

#2495 closed Bug (wontfix)

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

Reported by: anonymous Owned by: Honza Král
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: mysql TextField
Cc: treborhudson@…, martin@…, django-ticket-2495@…, Almad, Chris Chambers, em@…, eigrad Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (7)

hack-mysql-TextField-index.patch (1.3 KB ) - added by mizatservercave 15 years ago.
Preliminary hack against Django 1.0.2final
hack-mysql-TextField-index.diff (1.4 KB ) - added by mizatservercave 15 years ago.
Preliminary hack against Django 1.0.2final
2495-against-10914.diff (2.4 KB ) - added by Honza Král 15 years ago.
Added tests and cleaned up the patch a bit
2495-against-13302.diff (8.2 KB ) - added by Honza Král 14 years ago.
another approach to the problem, including rework of #12234
2495-against-15801.diff (7.8 KB ) - added by Guilherme Salgado 13 years ago.
2495-against-15801.2.diff (7.9 KB ) - added by Guilherme Salgado 13 years ago.
2495-doc.diff (649 bytes ) - added by Tim Graham 9 years ago.

Download all attachments as: .zip

Change History (47)

comment:1 by anonymous, 18 years ago

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

comment:2 by mico@…, 18 years ago

have same error.
what to do?

comment:3 by anonymous, 18 years ago

Is there a known work around?

comment:4 by anonymous, 17 years ago

Same problem here. Any workaround in place?

comment:5 by Simon G. <dev@…>, 17 years ago

Keywords: mysql TextField added
Triage Stage: UnreviewedAccepted

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.

comment:7 by Simon Greenhill <dev@…>, 16 years ago

#6336 was marked as a duplicate.

comment:8 by Rob Hudson <treborhudson@…>, 16 years ago

Cc: treborhudson@… added
Version: queryset-refactor

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.

comment:9 by Malcolm Tredinnick, 16 years ago

Keywords: qs-rf added
Version: queryset-refactorSVN

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

comment:10 by Malcolm Tredinnick, 16 years ago

Keywords: qs-rf removed

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

comment:11 by Simon Greenhill, 16 years ago

& #7750 was a duplicate

comment:12 by martin, 15 years ago

Cc: martin@… added

by mizatservercave, 15 years ago

Preliminary hack against Django 1.0.2final

by mizatservercave, 15 years ago

Preliminary hack against Django 1.0.2final

comment:13 by mizatservercave, 15 years ago

Has patch: set
Needs tests: set
Patch needs improvement: set

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.

comment:14 by mizatservercave, 15 years ago

Cc: django-ticket-2495@… added

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. :)

comment:15 by Almad, 15 years ago

Cc: Almad added

by Honza Král, 15 years ago

Attachment: 2495-against-10914.diff added

Added tests and cleaned up the patch a bit

comment:16 by Honza Král, 15 years ago

Needs tests: unset
Owner: changed from nobody to Honza Král
Patch needs improvement: unset

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.

comment:17 by dannyman@…, 14 years ago

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

comment:18 by dannyman@…, 14 years ago

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

-danny

in reply to:  17 comment:19 by Honza Král, 14 years ago

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

comment:20 by Honza Král, 14 years ago

milestone: 1.2

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

comment:21 by Russell Keith-Magee, 14 years ago

milestone: 1.21.3

Not critical for 1.2

by Honza Král, 14 years ago

Attachment: 2495-against-13302.diff added

another approach to the problem, including rework of #12234

comment:22 by Chris Chambers, 13 years ago

Cc: Chris Chambers added

comment:23 by Adam Nelson, 13 years ago

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

comment:24 by EmilStenstrom, 13 years ago

Cc: em@… added

comment:25 by Łukasz Rekucki, 13 years ago

Patch needs improvement: set

Latest patch doesn't apply cleanly anymore.

comment:26 by anonymous, 13 years ago

Patch needs improvement: unset

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

by Guilherme Salgado, 13 years ago

Attachment: 2495-against-15801.diff added

comment:27 by anonymous, 13 years ago

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

by Guilherme Salgado, 13 years ago

Attachment: 2495-against-15801.2.diff added

comment:28 by Guilherme Salgado, 13 years ago

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

comment:29 by Guilherme Salgado, 13 years ago

Patch needs improvement: set

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.

comment:30 by Łukasz Rekucki, 13 years ago

Type: defectBug

comment:31 by Łukasz Rekucki, 13 years ago

Severity: normalNormal

comment:32 by eigrad, 13 years ago

Cc: eigrad added
Easy pickings: unset

comment:33 by Jacob, 13 years ago

milestone: 1.3

Milestone 1.3 deleted

comment:11 by Aymeric Augustin, 12 years ago

UI/UX: unset

Change UI/UX from NULL to False.

comment:12 by John Jacobsen, 11 years ago

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

comment:13 by ANUBHAV JOSHI, 10 years ago

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.

comment:14 by Aymeric Augustin, 10 years ago

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?

by Tim Graham, 9 years ago

Attachment: 2495-doc.diff added

comment:15 by Tim Graham, 9 years ago

Patch needs improvement: unset

Attached a patch to document the limitation.

comment:16 by Tim Graham <timograham@…>, 9 years ago

In e8cbc2b:

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

comment:17 by Tim Graham <timograham@…>, 9 years ago

In 4f83bfa:

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

Backport of e8cbc2b322d873a6fe39faca18d340ce93035087 from master

comment:18 by Tim Graham, 9 years ago

Resolution: wontfix
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top