MySQL unique constraints incorrectly limited to 255 char when it should be 1000.

[Steven Mapes]

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 testCHARACTER 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

[Mariusz Felisiak]

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.

[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.

[Adam Johnson]

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?

[Mariusz Felisiak]

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.

[Steven Mapes]

Replying to 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

[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 :)

[Steven Mapes]

Replying to 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

[Mariusz Felisiak]

That's definitely not an "easy picking".

[Adam 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.

[Ashutosh Chauhan]

I would like to try to do it.

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 2695ac8e:

Fixed #31144 -- Relaxed system check for max_length of CharFields on MySQL/MariaDB by turning into a warning.