id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc,branch,changelog,apichanges,internalchanges 12878,Max total index size in MySQL shold be 3072 bytes,Jun Omae,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 [source:/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.",defect,closed,normal,1.0.17,database backend,1.0.15,normal,fixed,mysql,,,"Max total index size in MySQL is 3072 bytes, rather than 1000 bytes.",,