Opened 4 years ago

Last modified 4 years ago

#31144 closed Bug

MySQL unique constraints incorrectly limited to 255 char when it should be 1000 — at Version 1

Reported by: Steven Mapes Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: MySQL, MariaDB, Indexes, Unique Keys
Cc: Claude Paroz, Adam Johnson Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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

Change History (1)

comment:1 by Steven Mapes, 4 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top