Page MenuHomePhabricator

Specified key was too long
Closed, DeclinedPublic


Author: obasigeorge

OS: Windows XP Media Center Edition - SP2 [All patches to date applied]
Server: Apache 2.0.55
PHP: 5.1.1
MySQL: 5.0.17

The installation of the application fails due to the specified key being too
long for the categories table. All tables have also been changed from InnoDB to
MyISAM for testing, as my hosting provider does not support InnoDB.

I have included the generated message.

+ Attached Installation Message

MediaWiki 1.5.4 installation

Please include all of the lines below when reporting installation problems.
Checking environment...

  • PHP 5.1.1: ok
  • PHP server API is apache2handler; ok, using pretty URLs (index.php/Page_Title)
  • Have XML / Latin1-UTF-8 conversion support.
  • PHP is configured with no memory_limit.
  • Have zlib support; enabling output compression.
  • Neither Turck MMCache nor eAccelerator are installed, can't use object

caching functions

  • GNU diff3 not found.
  • Found GD graphics library built-in, image thumbnailing will be enabled if

you enable uploads.

  • Installation directory: D:\WWWData\wwwroot\kurabu\wiki
  • Script URI path: /wiki
  • Warning: $wgSecretKey key is insecure, generated with mt_rand(). Consider

changing it manually.

  • Trying to connect to MySQL on localhost as root... o MySQL error 1045: Access denied for user 'root'@'localhost' (using

password: NO)

  • Trying regular user... ok.
  • Connected to 5.0.17-nt-log; enabling MySQL 4 enhancements
  • Warning: $wgSecretKey key is insecure, generated with mt_rand(). Consider

changing it manually.

  • Database obasi_blog exists
  • Creating tables... using MySQL 3/4 table defs...Query "CREATE TABLE

kurabu_mw_categorylinks ( cl_from int(8) unsigned NOT NULL default '0', cl_to
varchar(255) binary NOT NULL default '', cl_sortkey varchar(86) binary NOT NULL
default '', cl_timestamp timestamp NOT NULL, UNIQUE KEY cl_from(cl_from,cl_to),
KEY cl_sortkey(cl_to,cl_sortkey), KEY cl_timestamp(cl_to,cl_timestamp) )
TYPE=MyISAM" failed with error code "Specified key was too long; max key length
is 1000 bytes".

Version: 1.10.x
Severity: normal
OS: Linux
Platform: PC



Event Timeline

bzimport raised the priority of this task from to Medium.Nov 21 2014, 9:01 PM
bzimport set Reference to bz4445.
bzimport added a subscriber: Unknown Object (MLST).

Try one of the following:

  • Use InnoDB tables
  • Set the default character set to Latin-1 for this database
  • edit maintenance/tables.sql and shorten that key from 86 characters to 78 or fewer until it fits

marcus wrote:

*** Bug 5123 has been marked as a duplicate of this bug. ***

mediawiki wrote:

This also happens with the 'jobs' table when UTF-8 is used, see for more information. There appears to be
no workaround.

jiri.dlouhy wrote:

It looks that in the tables.sql there is to ambitious definition of table "job":
if I want to use utf-8:

PRIMARY KEY job_id (job_id), KEY (job_cmd, job_namespace, job_title)

The key is 2 * (255 + 255 +8) =1032 - this is too much for mysql

can be job_id or job_cmd little bit shorter??

yvesf wrote:

i manually create job:
(note (128) )
CREATE TABLE tgwiki_job (
job_cmd VARCHAR( 128 ) NOT NULL DEFAULT '',
job_namespace INT NOT NULL ,
job_title VARCHAR( 128 ) BINARY NOT NULL ,
job_params BLOB NOT NULL DEFAULT '',
PRIMARY KEY job_id( job_id ) ,
KEY ( job_cmd, job_namespace, job_title )

at this time it seems to work good. But i dont know anything about the consequences.

norman wrote:

*** Bug 5489 has been marked as a duplicate of this bug. ***

gruen0aermel wrote:

In regards to comment #5, I made a similar change, but instead of shortening the
fields, I only shortened the keys generated off those fields.

Here is what I changed in maintenance/tables.sql and
maintenance/archives/patch-job.sql (I had to change both for the config script
to listen to me.) (This was with mediawiki-1.6.7) That change I made was in
the second to last line, adding both the "(128)".

  • Jobs performed by parallel apache threads or a command-line daemon

CREATE TABLE /*$wgDBprefix*/job (

job_id int(9) unsigned NOT NULL auto_increment,

-- Command name, currently only refreshLinks is defined
job_cmd varchar(255) NOT NULL default '',

-- Namespace and title to act on
-- Should be 0 and '' if the command does not operate on a title
job_namespace int NOT NULL,
job_title varchar(255) binary NOT NULL,

-- Any other parameters to the command
-- Presently unused, format undefined
job_params blob NOT NULL default '',

PRIMARY KEY job_id (job_id),
KEY (job_cmd(128), job_namespace, job_title(128))

) TYPE=InnoDB;

oxyge_007 wrote:

I had the same problem and did the workaround mentioned in . This helped executing
the update script successfully. Everything works fine, except doing rollbacks.
Then it shows the following error: "1196: Warning: Some non-transactional
changed tables couldn't be rolled back" did anyone experience the same problem?
any solutions?

janrei.g wrote:

I subscribe to wish for a solution to this. I didn't find a workaround yet that
works for me...

jakob wrote:

This bug still irks me, now with the upgrade to 10.0, for an other table;

Creating page_restrictions table...Query "CREATE TABLE wikit_page_restrictions (
pr_page int(8) NOT NULL,
pr_type varchar(255) NOT NULL,
pr_level varchar(255) NOT NULL,
pr_cascade tinyint(4) NOT NULL,
pr_user int(8) NULL,
pr_expiry char(14) binary NULL,
PRIMARY KEY pr_pagetype (pr_page,pr_type),
KEY pr_page (pr_page),
KEY pr_typelevel (pr_type,pr_level),
KEY pr_level (pr_level),
KEY pr_cascade (pr_cascade)
" failed with error code "Specified key was too long. Max key length is 500 (localhost)".

(In reply to comment #11)

pr_type varchar(255) NOT NULL,
pr_level varchar(255) NOT NULL,
KEY pr_typelevel (pr_type,pr_level),

That's your problem right there. sizeof(pr_type)+sizeof(pr_level) == 510, which is larger than 500. I have no idea why pr_type and pr_level are so insanely large (type is usually "edit" or "move", and level is usually "sysop"), but dropping a few chars from either of these shouldn't cause any trouble.

jon.1234 wrote:

I have had a similar problem with the change to the categorylinks index made on the upgrade from 1.10.1 to 1.11.0. I worked round it by changing the size of the key. Will my change have any long-term adverse effects? See Cheers!

robchur wrote:

*** Bug 11309 has been marked as a duplicate of this bug. ***

  • Bug 9350 has been marked as a duplicate of this bug. ***

There's two issues here: one is that for older versions of MyISAM, the key length was too short. The second issue is that the field was needlessly long.

The latter issue was fixed per bug 9350 comment 6. The former issue can be solved by upgrading or using InnoDB. Marking this WFM.