Opened 17 years ago
Closed 8 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)
Change History (47)
comment:1 Changed 17 years ago by
comment:5 Changed 17 years ago by
Keywords: | mysql TextField added |
---|---|
Triage Stage: | Unreviewed → 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:8 Changed 15 years ago by
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 15 years ago by
Keywords: | qs-rf added |
---|---|
Version: | queryset-refactor → SVN |
Ticket is not specific to queryset-refactor. Fixing the version.
comment:10 Changed 15 years ago by
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:12 Changed 15 years ago by
Cc: | martin@… added |
---|
Changed 15 years ago by
Attachment: | hack-mysql-TextField-index.patch added |
---|
Preliminary hack against Django 1.0.2final
Changed 15 years ago by
Attachment: | hack-mysql-TextField-index.diff added |
---|
Preliminary hack against Django 1.0.2final
comment:13 Changed 15 years ago by
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 15 years ago by
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 14 years ago by
Cc: | Almad added |
---|
Changed 14 years ago by
Attachment: | 2495-against-10914.diff added |
---|
Added tests and cleaned up the patch a bit
comment:16 Changed 14 years ago by
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 TextField
s
Please let me know it the approach taken in the patch is not welcome and I will update it.
comment:17 follow-up: 19 Changed 14 years ago by
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:19 Changed 14 years ago by
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 14 years ago by
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.
Changed 13 years ago by
Attachment: | 2495-against-13302.diff added |
---|
another approach to the problem, including rework of #12234
comment:22 Changed 13 years ago by
Cc: | Chris Chambers added |
---|
comment:23 Changed 13 years ago by
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.
comment:24 Changed 13 years ago by
Cc: | em@… added |
---|
comment:25 Changed 13 years ago by
Patch needs improvement: | set |
---|
Latest patch doesn't apply cleanly anymore.
comment:26 Changed 13 years ago by
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 13 years ago by
Attachment: | 2495-against-15801.diff added |
---|
comment:27 Changed 13 years ago by
Actually, the patch I just attached is missing a __init.py
on the newly created directory; attaching a new one shortly
Changed 13 years ago by
Attachment: | 2495-against-15801.2.diff added |
---|
comment:28 Changed 13 years ago by
Just noticed the test doesn't fail when I revert the changes, so the patch needs more work
comment:29 Changed 13 years ago by
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 12 years ago by
Type: | defect → Bug |
---|
comment:31 Changed 12 years ago by
Severity: | normal → Normal |
---|
comment:32 Changed 12 years ago by
Cc: | eigrad added |
---|---|
Easy pickings: | unset |
comment:13 Changed 10 years ago by
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 9 years ago by
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 8 years ago by
Attachment: | 2495-doc.diff added |
---|
comment:15 Changed 8 years ago by
Patch needs improvement: | unset |
---|
Attached a patch to document the limitation.
comment:18 Changed 8 years ago by
Resolution: | → wontfix |
---|---|
Status: | new → closed |
errr... to be clear, the SQL I quoted is the working syntax, not what django generates.