Page MenuHomePhabricator

[AntiSpoof] sql/patch-antispoof.mysql.sql : Error: 1071 Specified key was too long; max key length is 767 bytes
Open, Needs TriagePublic

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 `spoofuser` (
 su_name VARCHAR(255),
 su_normalized VARCHAR(255),
 su_legal BOOL,
 su_error TEXT,
 PRIMARY KEY (su_name),
 KEY(su_normalized, su_name)
 ) ENGINE=InnoDB

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

Event Timeline

hashar created this task.May 1 2018, 7:10 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 1 2018, 7:10 PM

Change 429443 had a related patch set uploaded (by Hashar; owner: Hashar):
[mediawiki/extensions/AntiSpoof@master] Change primary key length to 191

https://gerrit.wikimedia.org/r/429443

Change 433950 had a related patch set uploaded (by Umherirrender; owner: Umherirrender):
[mediawiki/extensions/AntiSpoof@master] Rework database design

https://gerrit.wikimedia.org/r/433950

I would say, that T21164 could be merge into this? Sounds like the same problem with the locale

Change 433950 abandoned by Umherirrender:
Use binary collation in mysql for varchars

Reason:
Tests now passed by using binary as default collation

https://gerrit.wikimedia.org/r/433950

Vvjjkkii renamed this task from [AntiSpoof] sql/patch-antispoof.mysql.sql : Error: 1071 Specified key was too long; max key length is 767 bytes to fvdaaaaaaa.Jul 1 2018, 1:12 AM
Vvjjkkii triaged this task as High priority.
Vvjjkkii updated the task description. (Show Details)
Mainframe98 renamed this task from fvdaaaaaaa to [AntiSpoof] sql/patch-antispoof.mysql.sql : Error: 1071 Specified key was too long; max key length is 767 bytes.Jul 1 2018, 10:53 AM
Mainframe98 raised the priority of this task from High to Needs Triage.
Mainframe98 updated the task description. (Show Details)

Change 429443 abandoned by Hashar:
Change primary key length to 191

https://gerrit.wikimedia.org/r/429443