Max total index size in MySQL shold be 3072 bytes

[Jun Omae]

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.

[Jun Omae]

Proposed changes in [eaf2d5131/jomae.git].

[Ryan J Ollos]

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

[Jun Omae]

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)

[Jun Omae]

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

[Ryan J Ollos]

Milestone renamed