[Django] #31144: MySQL unique constraints incorrectly limited to 255 char when it should be 1000
Django
1000
-------------------------------------+-------------------------------------
Reporter: Steven | Owner: nobody
Mapes |
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) | Keywords: MySQL, MAriaDB,
Severity: Normal | Unique
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
There is a bug within db.backends.mysql.validation.check_field_type where
the maximum unique constraints is still being limited to 255 characters
even though MySQL supports unique keys of up to 1000 characters by default
and InnoDB supports 3072 bytes for InnoDB tables that use DYNAMIC or
COMPRESSED row format and 767 bytes for InnoDB tables that use the
REDUNDANT or COMPACT row format.
Reference -
InnoDB - https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html
MyISAM - https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-
engine.html
Example
CREATE DATABASE `test`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test;
{{{
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 test;
}}}
It just needs the max length to be increased on line 38 of
db.backends.mysql.validation
--
Ticket URL: <https://code.djangoproject.com/ticket/31144>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
1000
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.0
(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
-------------------------------------+-------------------------------------
Old description:
> There is a bug within db.backends.mysql.validation.check_field_type where
> the maximum unique constraints is still being limited to 255 characters
> even though MySQL supports unique keys of up to 1000 characters by
> default and InnoDB supports 3072 bytes for InnoDB tables that use DYNAMIC
> or COMPRESSED row format and 767 bytes for InnoDB tables that use the
> REDUNDANT or COMPACT row format.
>
> Reference -
> InnoDB - https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html
>
> MyISAM - https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-
> engine.html
>
> Example
>
> CREATE DATABASE `test`CHARACTER SET utf8 COLLATE utf8_general_ci;
> USE test;
> {{{
> 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 test;
> }}}
>
> It just needs the max length to be increased on line 38 of
> db.backends.mysql.validation
New description:
There is a bug within db.backends.mysql.validation.check_field_type where
the maximum unique constraints is still being limited to 255 characters
even though MySQL supports unique keys of up to 1000 characters by default
and InnoDB supports 3072 bytes for InnoDB tables that use DYNAMIC or
COMPRESSED row format and 767 bytes for InnoDB tables that use the
REDUNDANT or COMPACT row format.
MySQL supports VARCHARs of up to 65,535 length. It's only CHAR that is
restricted to 255 and as Django creates VARVCHARs there is no need to
enforce that limit.
Reference -
1. InnoDB - https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html
2. MyISAM - https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-
engine.html
3. CHAR and VARCHAR Types
-https://dev.mysql.com/doc/refman/8.0/en/char.html
Example
CREATE DATABASE `test`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test;
{{{
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 test;
}}}
It just needs the max length to be increased on line 38 of
db.backends.mysql.validation
This is across all branches
--
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: MySQL, MAriaDB, | Triage Stage:
Unique | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Claude Paroz (added)
* status: new => closed
* version: 3.0 => master
* resolution: => wontfix
Comment:
Thanks for this ticket, however Django supports MySQL 5.6+. Moreover this
restriction depends on character set, database version, database engine,
and parameters (see related ticket #28661), so it's quite complicated to
determine the real upper bound. Even with MySQL 8.0 we still have the
following restriction:
> InnoDB tables that use DYNAMIC or COMPRESSED row format and 767 bytes
for InnoDB tables that use the REDUNDANT or COMPACT row format.
which means 255 characters with `utf8` and 191 characters with `utf8mb4`.
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: MySQL, MAriaDB, | Triage Stage:
Unique | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Adam (Chainz) Johnson (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: MySQL, MAriaDB, | Triage Stage:
Unique | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Steven Mapes):
So why not raise a warning or, better yet, let the database itself handle
whether or not there is an error applying the index and thus the migration
will not apply anyway just as if you use an index_together or
unique_together that is over the max length. These are handled by MySQL as
Django lets them pass and is a work around on the {{{unique=True}}} issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Unique | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: wontfix =>
Comment:
I agree with Steven here. MariaDB also ships many storage engines other
than InnoDB with different limits. It might be better to "translate"
errors coming out of the database to add a bit of django context e.g.
which model is causing the problem?
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL, MAriaDB, | Triage Stage:
Unique | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL, MAriaDB, | Triage Stage:
Unique | Unreviewed
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
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Indexes, Unique Keys | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Changes (by Steven Mapes):
* keywords: MySQL, MAriaDB, Unique => MySQL, MariaDB, Indexes, Unique Keys
* easy: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL, MariaDB, | Triage Stage:
Indexes, Unique Keys | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by SameeranB):
Hi! I would like to take up this issue if no one is already working on it.
I'm new to this codebase, so I might need a little help if that's okay :)
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL, MariaDB, | Triage Stage:
Indexes, Unique Keys | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by Steven Mapes):
Replying to [comment:9 Sameeran Bandishti]:
> Hi! I would like to take up this issue if no one is already working on
it. I'm new to this codebase, so I might need a little help if that's okay
:)
That would be great from my point of view. I would just make the change
and publish a PR for it myself but I'm unsure on how best to write the
unit tests without them being actual integration tests other than using
mocks to raise the error or not but then it seems rather pointless of a
test.
For me the change is either
1. Remove lines 37 through 45 of
```db.backends.mysql.validation.check_field_type``` as they are simply not
needed as the DB itself will trigger an error.
2. Change ```checks.Error``` on line 40 to ```checks.Warning``` and update
the error message to be one that is more useful. E.G to instruct the
developer to check the documentation and for the limits based on the
character set they are using for the database/table/column.
The docs should also be updated to remove any reference to MySQL having a
255 char limit on VARCHAR as its not true
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL, MariaDB, | Triage Stage:
Indexes, Unique Keys | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* easy: 1 => 0
Comment:
That's definitely not an "easy picking".
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL, MariaDB, | Triage Stage:
Indexes, Unique Keys | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Adam (Chainz) Johnson):
> Change checks.Error on line 40 to checks.Warning and update the
error message to be one that is more useful. E.G to instruct the developer
to check the documentation and for the limits based on the character set
they are using for the database/table/column.
Given what I currently know of general ecosystem and which table types are
ore common, I think it's probably more favourable to move to a warning
than remove the check entirely.
> The docs should also be updated to remove any reference to MySQL having
a 255 char limit on VARCHAR as its not true
I think it's still useful to mention briefly as a potential stumbling
block, with links to MySQL and MariaDB docs around the issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Indexes, Unique Keys |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL, MariaDB, | Triage Stage: Accepted
Indexes, Unique Keys |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Ashutosh Chauhan):
I would like to try to do it.
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: Rohit Jha
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL, MariaDB, | Triage Stage: Accepted
Indexes, Unique Keys |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Rohit Jha
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: Rohit Jha
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: MySQL, MariaDB, | Triage Stage: Accepted
Indexes, Unique Keys |
Needs tests: 1 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
* needs_tests: 0 => 1
Comment:
[https://github.com/django/django/pull/12459 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:16>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: Rohit Jha
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Indexes, Unique Keys | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
* needs_tests: 1 => 0
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:17>
Django
-------------------------------------+-------------------------------------
Reporter: Steven Mapes | Owner: Rohit Jha
Component: Database layer | Version: master
(models, ORM) |
Keywords: MySQL, MariaDB, | Triage Stage: Ready for
Indexes, Unique Keys | checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"2695ac8e0441b4d7e5460eac3bb7ea315164a6bf" 2695ac8e]:
{{{
#!CommitTicketReference repository=""
revision="2695ac8e0441b4d7e5460eac3bb7ea315164a6bf"
Fixed #31144 -- Relaxed system check for max_length of CharFields on
MySQL/MariaDB by turning into a warning.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31144#comment:18>