Page MenuHomePhabricator

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

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)

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

@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?

@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 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 Normal.
Oetterer updated the task description. (Show Details)
Oetterer added a subscriber: Aklapper.