Opened 11 years ago

Closed 18 months 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: master
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 8 years ago.
Preliminary hack against Django 1.0.2final
hack-mysql-TextField-index.diff (1.4 KB) - added by mizatservercave 8 years ago.
Preliminary hack against Django 1.0.2final
2495-against-10914.diff (2.4 KB) - added by Honza Král 8 years ago.
Added tests and cleaned up the patch a bit
2495-against-13302.diff (8.2 KB) - added by Honza Král 7 years ago.
another approach to the problem, including rework of #12234
2495-against-15801.diff (7.8 KB) - added by Guilherme Salgado 6 years ago.
2495-against-15801.2.diff (7.9 KB) - added by Guilherme Salgado 6 years ago.
2495-doc.diff (649 bytes) - added by Tim Graham 18 months ago.

Download all attachments as: .zip

Change History (47)

comment:1 Changed 11 years ago by anonymous

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

comment:2 Changed 10 years ago by mico@…

have same error.
what to do?

comment:3 Changed 10 years ago by anonymous

Is there a known work around?

comment:4 Changed 10 years ago by anonymous

Same problem here. Any workaround in place?

comment:5 Changed 10 years ago by Simon G. <dev@…>

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 Changed 9 years ago by Simon Greenhill <dev@…>

#6336 was marked as a duplicate.

comment:8 Changed 9 years ago by Rob Hudson <treborhudson@…>

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 Changed 9 years ago by Malcolm Tredinnick

Keywords: qs-rf added
Version: queryset-refactorSVN

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

comment:10 Changed 9 years ago by Malcolm Tredinnick

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 Changed 9 years ago by Simon Greenhill

& #7750 was a duplicate

comment:12 Changed 8 years ago by martin

Cc: martin@… added

Changed 8 years ago by mizatservercave

Preliminary hack against Django 1.0.2final

Changed 8 years ago by mizatservercave

Preliminary hack against Django 1.0.2final

comment:13 Changed 8 years ago by mizatservercave

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 Changed 8 years ago by mizatservercave

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 Changed 8 years ago by Almad

Cc: Almad added

Changed 8 years ago by Honza Král

Attachment: 2495-against-10914.diff added

Added tests and cleaned up the patch a bit

comment:16 Changed 8 years ago by Honza Král

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 Changed 7 years ago by 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

comment:18 Changed 7 years ago by dannyman@…

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

-danny

comment:19 in reply to:  17 Changed 7 years ago by 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

comment:20 Changed 7 years ago by Honza Král

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 Changed 7 years ago by Russell Keith-Magee

milestone: 1.21.3

Not critical for 1.2

Changed 7 years ago by Honza Král

Attachment: 2495-against-13302.diff added

another approach to the problem, including rework of #12234

comment:22 Changed 6 years ago by Chris Chambers

Cc: Chris Chambers added

comment:23 Changed 6 years ago by 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

comment:24 Changed 6 years ago by EmilStenstrom

Cc: em@… added

comment:25 Changed 6 years ago by Łukasz Rekucki

Patch needs improvement: set

Latest patch doesn't apply cleanly anymore.

comment:26 Changed 6 years ago by anonymous

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

Changed 6 years ago by Guilherme Salgado

Attachment: 2495-against-15801.diff added

comment:27 Changed 6 years ago by anonymous

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

Changed 6 years ago by Guilherme Salgado

Attachment: 2495-against-15801.2.diff added

comment:28 Changed 6 years ago by Guilherme Salgado

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

comment:29 Changed 6 years ago by Guilherme Salgado

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 Changed 6 years ago by Łukasz Rekucki

Type: defectBug

comment:31 Changed 6 years ago by Łukasz Rekucki

Severity: normalNormal

comment:32 Changed 6 years ago by eigrad

Cc: eigrad added
Easy pickings: unset

comment:33 Changed 5 years ago by Jacob

milestone: 1.3

Milestone 1.3 deleted

comment:11 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:12 Changed 4 years ago by John Jacobsen

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

comment:13 Changed 3 years ago by 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.

comment:14 Changed 2 years ago by 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?

Changed 18 months ago by Tim Graham

Attachment: 2495-doc.diff added

comment:15 Changed 18 months ago by Tim Graham

Patch needs improvement: unset

Attached a patch to document the limitation.

comment:16 Changed 18 months ago by Tim Graham <timograham@…>

In e8cbc2b:

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

comment:17 Changed 18 months ago by Tim Graham <timograham@…>

In 4f83bfa:

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

Backport of e8cbc2b322d873a6fe39faca18d340ce93035087 from master

comment:18 Changed 18 months ago by Tim Graham

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