Opened 9 years ago

Closed 8 days ago

#2495 closed Bug (wontfix)

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

Reported by: anonymous Owned by: Honza_Kral
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: mysql TextField
Cc: treborhudson@…, martin@…, django-ticket-2495@…, Almad, chrischambers, 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 7 years ago.
Preliminary hack against Django 1.0.2final
hack-mysql-TextField-index.diff (1.4 KB) - added by mizatservercave 7 years ago.
Preliminary hack against Django 1.0.2final
2495-against-10914.diff (2.4 KB) - added by Honza_Kral 6 years ago.
Added tests and cleaned up the patch a bit
2495-against-13302.diff (8.2 KB) - added by Honza_Kral 5 years ago.
another approach to the problem, including rework of #12234
2495-against-15801.diff (7.8 KB) - added by salgado 4 years ago.
2495-against-15801.2.diff (7.9 KB) - added by salgado 4 years ago.
2495-doc.diff (649 bytes) - added by timgraham 11 days ago.

Download all attachments as: .zip

Change History (47)

comment:1 Changed 9 years ago by anonymous

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

comment:2 Changed 9 years ago by mico@…

have same error.
what to do?

comment:3 Changed 9 years ago by anonymous

Is there a known work around?

comment:4 Changed 9 years ago by anonymous

Same problem here. Any workaround in place?

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

  • Keywords mysql TextField added
  • Triage Stage changed from Unreviewed to Accepted

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

#6336 was marked as a duplicate.

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

  • Cc treborhudson@… added
  • Version set to 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 7 years ago by mtredinnick

  • Keywords qs-rf added
  • Version changed from queryset-refactor to SVN

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

comment:10 Changed 7 years ago by mtredinnick

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

& #7750 was a duplicate

comment:12 Changed 7 years ago by martin

  • Cc martin@… added

Changed 7 years ago by mizatservercave

Preliminary hack against Django 1.0.2final

Changed 7 years ago by mizatservercave

Preliminary hack against Django 1.0.2final

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

  • Cc Almad added

Changed 6 years ago by Honza_Kral

Added tests and cleaned up the patch a bit

comment:16 Changed 6 years ago by Honza_Kral

  • Needs tests unset
  • Owner changed from nobody to Honza_Kral
  • 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 follow-up: Changed 6 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 6 years ago by dannyman@…

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

-danny

comment:19 in reply to: ↑ 17 Changed 6 years ago by Honza_Kral

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 6 years ago by Honza_Kral

  • milestone set to 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 5 years ago by russellm

  • milestone changed from 1.2 to 1.3

Not critical for 1.2

Changed 5 years ago by Honza_Kral

another approach to the problem, including rework of #12234

comment:22 Changed 5 years ago by chrischambers

  • Cc chrischambers added

comment:23 Changed 5 years ago by adamnelson

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 5 years ago by EmilStenstrom

  • Cc em@… added

comment:25 Changed 5 years ago by lrekucki

  • Patch needs improvement set

Latest patch doesn't apply cleanly anymore.

comment:26 Changed 4 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 4 years ago by salgado

comment:27 Changed 4 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 4 years ago by salgado

comment:28 Changed 4 years ago by salgado

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

comment:29 Changed 4 years ago by 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 4 years ago by lrekucki

  • Type changed from defect to Bug

comment:31 Changed 4 years ago by lrekucki

  • Severity changed from normal to Normal

comment:32 Changed 4 years ago by eigrad

  • Cc eigrad added
  • Easy pickings unset

comment:33 Changed 4 years ago by jacob

  • milestone 1.3 deleted

Milestone 1.3 deleted

comment:11 Changed 4 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:12 Changed 2 years ago by eigenhombre

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

comment:13 Changed 21 months ago by anubhav9042

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 12 months ago by aaugustin

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 11 days ago by timgraham

comment:15 Changed 11 days ago by timgraham

  • Patch needs improvement unset

Attached a patch to document the limitation.

comment:16 Changed 8 days ago by Tim Graham <timograham@…>

In e8cbc2b:

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

comment:17 follow-up: Changed 8 days 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 8 days ago by timgraham

  • Resolution set to wontfix
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.
Back to Top