Edgewall Software
Modify

Opened 7 years ago

Closed 7 years ago

Last modified 6 years ago

#12878 closed defect (fixed)

Max total index size in MySQL shold be 3072 bytes

Reported by: Jun Omae Owned by: Jun Omae
Priority: normal Milestone: 1.0.17
Component: database backend Version: 1.0.15
Severity: normal Keywords: mysql
Cc: Branch:
Release Notes:

Max total index size in MySQL is 3072 bytes, rather than 1000 bytes.

API Changes:
Internal Changes:

Description

In MySQL, primary key of attachment table is (`type`(83),`id`(83),`filename`(83)). It is calculated to fit 1000 bytes.

83 chars * 4 bytes (utf8mb4 size) * 3 columns = 996 bytes ≦ 1000 bytes

The limitation is calculated at tags/trac-1.0.15/trac/db/mysql_backend.py@:146,151#L142.

mysql> show create table attachment\G
*************************** 1. row ***************************
       Table: attachment
Create Table: CREATE TABLE `attachment` (
  `type` text COLLATE utf8mb4_bin NOT NULL,
  `id` text COLLATE utf8mb4_bin NOT NULL,
  `filename` text COLLATE utf8mb4_bin NOT NULL,
  `size` int(11) DEFAULT NULL,
  `time` bigint(20) DEFAULT NULL,
  `description` text COLLATE utf8mb4_bin,
  `author` text COLLATE utf8mb4_bin,
  `ipnr` text COLLATE utf8mb4_bin,
  PRIMARY KEY (`type`(83),`id`(83),`filename`(83))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

However, index over 1000 bytes can be actually created.

mysql> CREATE TABLE `attachment_test` (
    ->   `type` text COLLATE utf8mb4_bin NOT NULL,
    ->   `id` text COLLATE utf8mb4_bin NOT NULL,
    ->   `filename` text COLLATE utf8mb4_bin NOT NULL,
    ->   `size` int(11) DEFAULT NULL,
    ->   `time` bigint(20) DEFAULT NULL,
    ->   `description` text COLLATE utf8mb4_bin,
    ->   `author` text COLLATE utf8mb4_bin,
    ->   `ipnr` text COLLATE utf8mb4_bin,
    ->   PRIMARY KEY (`type`(191),`id`(191),`filename`(191))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
    -> ;
Query OK, 0 rows affected (0.19 sec)

The 1000 bytes limitation is for MyISAM, not InnoDB.

mysql> create table test_myisam (
    ->   id int primary key,
    ->   name0 text, name1 text, name2 text, name3 text, name4 text,
    ->   name5 text, name6 text, name7 text, name8 text, name9 text)
    -> ENGINE=MyISAM CHARSET=utf8mb4;
Query OK, 0 rows affected (0.13 sec)

mysql> create unique index idx_test on test_myisam (name1(191), name2(60));
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
mysql> create unique index idx_test on test_myisam (name1(191), name2(59));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table test_innodb (
    ->   id int primary key,
    ->   name0 text, name1 text, name2 text, name3 text, name4 text,
    ->   name5 text, name6 text, name7 text, name8 text, name9 text)
    -> ENGINE=InnoDB CHARSET=utf8mb4;
Query OK, 0 rows affected (0.14 sec)

mysql> create unique index idx_test on test_innodb (
    ->   name1(191), name2(191), name3(191), name4(191), name5(5));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create unique index idx_test on test_innodb (
    ->   name1(191), name2(191), name3(191), name4(191), name5(4));
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

Trac requires InnoDB storage engine. We should use 3072 bytes for the limitation.

Attachments (0)

Change History (5)

comment:1 by Jun Omae, 7 years ago

Owner: set to Jun Omae
Status: newassigned

Proposed changes in [eaf2d5131/jomae.git].

comment:2 by Ryan J Ollos, 7 years ago

I don't know MySQL very well, but looks logical to me. I ran tests and all passed:

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.19, for osx10.12 (x86_64) using  EditLine wrapper

comment:3 by Jun Omae, 7 years ago

Thanks for the reviewing and testing!

I just verified unit tests pass on the following environments:

Version Charset mysqld --version
MySQL 5.0.95 utf8 mysqld Ver 5.0.95 for redhat-linux-gnu on i386 (Source distribution)
MySQL 5.1.73 utf8 mysqld Ver 5.1.73 for redhat-linux-gnu on x86_64 (Source distribution)
MySQL 5.5.55 utf8, utf8mb4 mysqld Ver 5.5.55-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))
MySQL 5.6.37 utf8, utf8mb4 mysqld Ver 5.6.37 for Linux on x86_64 (MySQL Community Server (GPL))
MySQL 5.7.19 utf8, utf8mb4 mysqld Ver 5.7.19 for Linux on x86_64 (MySQL Community Server (GPL))
MariaDB 10.2.7 utf8, utf8mb4 mysqld Ver 10.2.7-MariaDB-10.2.7+maria~jessie for debian-linux-gnu on x86_64 (mariadb.org binary distribution)

comment:4 by Jun Omae, 7 years ago

Release Notes: modified (diff)
Resolution: fixed
Status: assignedclosed

Committed in [16183] and merged in [16184-16185].

comment:5 by Ryan J Ollos, 6 years ago

Milestone: 1.0.161.0.17

Milestone renamed

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Jun Omae to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.