#31144: MySQL unique constraints incorrectly limited to 255 char when it should
be
1000.
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL, MAriaDB, | Triage Stage:
Unique | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Steven Mapes):
Replying to [comment:6 felixxm]:
> I'm not a MySQL expert, but I think that MySQL doesn't raise any error
it'll just create an unique constraint for the first e.g. 191 characters
and ignores the remaining chars. So users will not be aware that DB
doesn't protect uniqueness.
As a longtime certificate MySQL Dev I can assure you that both MySQL and
MariaDB both do not act in this way and will raise error code 1071 (E.G
"Specified key was too long; max key length is 3072 bytes") if you try to
create an index that is too large.
Here is an example of creating indexes on a UTF-8 table that will not
error
{{{
CREATE DATABASE djangotest CHARACTER SET utf8 COLLATE utf8_general_ci;
USE djangotest;
CREATE TABLE `example_innodb` (
`example1` VARCHAR(1024) DEFAULT NULL,
`example2` VARCHAR(1024) DEFAULT NULL,
UNIQUE KEY `UQ_example1` (`example1`),
UNIQUE KEY `UQ_example2` (`example2`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
DROP TABLE example_innodb;
DROP DATABASE djangotest;
}}}
Here is an example of one where the Error Code: 1071 will be triggered
{{{
CREATE DATABASE djangotest2 CHARACTER SET utf8 COLLATE utf8_general_ci;
USE djangotest2;
CREATE TABLE `example_innodb` (
`example1` VARCHAR(2048) DEFAULT NULL,
`example2` VARCHAR(2048) DEFAULT NULL,
UNIQUE KEY `UQ_example1` (`example1`),
UNIQUE KEY `UQ_example2` (`example2`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
DROP TABLE example_innodb;
DROP DATABASE djangotest2;
}}}
For this you will receive the following error:
Error Code: 1071
Specified key was too long; max key length is 3072 bytes
Here is a 3rd example showing an UTF8MB4 table
{{{
CREATE DATABASE `example3`CHARACTER SET utf8mb4 COLLATE
utf8mb4_general_ci;
USE `example3`;
CREATE TABLE `example3`.`example` ( `column1` VARCHAR(3072) )
ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
ALTER TABLE `example3`.`example` ADD UNIQUE INDEX `test_unique`
(`column1`(3072));
}}}
This will again generate the an Error Code: 1071 - Specified key was too
long; max key length is 3072 bytes
Here is an example of the maximum UTF8MB4 index being created
{{{
ALTER TABLE `example3`.`example` ADD UNIQUE INDEX `test_unique2`
(`column1`(768));
}}}
Then simple try to index one more character and you will be over the byte
length
{{{
ALTER TABLE `example3`.`example` ADD UNIQUE INDEX `test_unique2`
(`column1`(769));
}}}
So the Error Code: 1071 will trigger again
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:7>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/069.d8ba3673f454c10ce9d7b6a82efb34e3%40djangoproject.com.
