Page MenuHomePhabricator

[3D] sql/create_counts.sql Error: 1071 Specified key was too long; max key length is 767 bytes
Closed, DeclinedPublic

Description

When running MySQL/MariaDB with charset utf8mb4, a character is 4 bytes. Hence a key created on a VARCHAR(255) takes 255 characters * 4 bytes/characters = 1020 bytes. However Innodb defaults to a maximum of 767 bytes for an index.

The extension fails to install on Debian Stretch which uses utf8mb4 as the default charset:

Query: create table if not exists `betafeatures_user_counts` (
 `feature`          varchar(255) primary key not null,
 `number`           integer not null default 0
 ) ENGINE=InnoDB

Function: Wikimedia\Rdbms\Database::sourceFile( /workspace/src/extensions/BetaFeatures/includes/../sql/create_counts.sql )
Error: 1071 Specified key was too long; max key length is 767 bytes (localhost:/tmp/quibble-mysql-8g9g9lae/socket)

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald Transcript
Vvjjkkii renamed this task from [3D] sql/create_counts.sql Error: 1071 Specified key was too long; max key length is 767 bytes to kvdaaaaaaa.Jul 1 2018, 1:13 AM
Vvjjkkii triaged this task as High priority.
Vvjjkkii updated the task description. (Show Details)
Vvjjkkii removed subscribers: gerritbot, Aklapper.
Mainframe98 renamed this task from kvdaaaaaaa to [3D] sql/create_counts.sql Error: 1071 Specified key was too long; max key length is 767 bytes.Jul 1 2018, 10:50 AM
Mainframe98 raised the priority of this task from High to Needs Triage.
Mainframe98 updated the task description. (Show Details)
Mainframe98 added subscribers: Aklapper, gerritbot.

The issue was that MediaWiki did not define a default charset it thus used whatever the MySQL server has. On Debian Jessie it used to be Latin1, on Debian Stretch that got changed to utf8mb4.

Eventually that got addressed by https://gerrit.wikimedia.org/r/c/mediawiki/core/+/461279 which now defaults to a binary charset:

--- a/includes/DefaultSettings.php
+++ b/includes/DefaultSettings.php
@@ -1931,7 +1931,7 @@ $wgDBprefix = '';
 /**
  * MySQL table options to use during installation or update
  */
-$wgDBTableOptions = 'ENGINE=InnoDB';
+$wgDBTableOptions = 'ENGINE=InnoDB, DEFAULT CHARSET=binary';

See T193222#4247329 and following comments for more details.