Page MenuHomePhabricator
Create Task
Maniphest T193536

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

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 UNIQUE INDEX cargo_tables_main_table ON `cargo_tables` (main_table)

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

Related Objects
Search...

Event Timeline

hashar created this task.May 1 2018, 7:15 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 1 2018, 7:15 PM
Yaron_Koren subscribed.May 10 2018, 3:45 AM

@hashar - sorry for the long delay. I can think of three basic solutions:

  • Have the code manually set the encoding to be utf8 for any tables it creates.
  • Have the code check if the DB is using utf8mb4 encoding (or any other 4-byte encoding), and if so, set the default varchar size to be 191 or less.
  • Set the length of any index created to be 767 (or less), instead of always indexing the entire field.

I'm hardly an expert on DBs and encoding - do you, or anyone else, have any recommendation for which of these approaches is the best? Or is there maybe a better approach than any of these?

Yaron_Koren added a comment.May 22 2018, 7:20 PM

@hashar - I ended up doing something else; I created a new global variable, $wgCargoDefaultStringBytes, which is set to 300, but which can be overridden if 'utf8mb4' encoding is used, like this:

$wgCargoDefaultStringBytes = 191;

It would be great if you could try out the latest code, and add that setting, and see if it works for you.

• Vvjjkkii renamed this task from [Cargo] sql/Cargo.sql : Error: 1071 Specified key was too long; max key length is 767 bytes to bvdaaaaaaa.Jul 1 2018, 1:12 AM
• Vvjjkkii triaged this task as High priority.
• Vvjjkkii added projects: CheckUser, Connected-Open-Heritage-Batch-uploads (RAÄ-KMB_1_2017-02), Tamil-Sites, Gamepress, Hashtags, Jade, KartoEditor, Language-2018-Apr-June, New-Editor-Experiences, Mail, TCB-Team (now WMDE-TechWish).
• Vvjjkkii updated the task description. (Show Details)
• Vvjjkkii removed a subscriber: Aklapper.
Oetterer renamed this task from bvdaaaaaaa to [Cargo] sql/Cargo.sql : Error: 1071 Specified key was too long; max key length is 767 bytes.Jul 1 2018, 6:18 AM
Oetterer lowered the priority of this task from High to Medium.
Oetterer removed projects: TCB-Team (now WMDE-TechWish), Mail, New-Editor-Experiences, Language-2018-Apr-June, KartoEditor, Jade, Hashtags, Gamepress, Tamil-Sites, Connected-Open-Heritage-Batch-uploads (RAÄ-KMB_1_2017-02), CheckUser.
Oetterer updated the task description. (Show Details)
Oetterer added a subscriber: Aklapper.
hashar closed this task as Declined.Jun 10 2021, 1:52 PM

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.

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL