MediaWiki Installation Help

I've recently been trying to get a Wiki set up on this website. While running the setup, MedaWiki returned the following errror;

Specified key was too long; max key length is 1000 bytes

when it tried it tried to create the categoryLinks table in my mySQL database. It took me quite a while to figure out the solution.

There are a couple things that need to be understood in order to understand why this error is happening.

• UTF-8 encoding uses 3 bytes per character. Thus, the above problem will only occur when a database's encoding is set to UTF-8. This is unfortunate, because not all people will have an option to choose another encoding type for their mediaWiki installation.
• In our case, there is a 1000 byte limit on keys.

Let's take a look at mediaWiki's SQL CREATE statement for the categoryLinks table so that we can pinpoint the problem.

CREATE TABLE wiki_categorylinks (
cl_from int(8) unsigned NOT NULL default '0',
cl_to varchar(255) binary NOT NULL default '',
cl_sortkey varchar(255) binary NOT NULL default '',
cl_timestamp timestamp NOT NULL,
UNIQUE KEY cl_from(cl_from,cl_to),
KEY cl_sortkey(cl_to,cl_sortkey(128)),
KEY cl_timestamp(cl_to,cl_timestamp)
)

At first glance, it looks like a perfectly legal SQL statement that shouldn't cause any problems. So where is our problem coming from?

If we look closer at the statement KEY cl_sortkey(cl_to,cl_sortkey(128)) we see that we are trying to create a key using cl_to and cl_sortkey. In this case, mediaWiki is only indexing the first 128 characters of sortkey. So how many bytes are we using for this key?

If we do the math, remembering that UTF-8 encoding uses 3 bytes per character we arrive at the the following calculation:

255+128=383
3*383 = 1149 bytes

So there's our problem. We're trying to create a key that is 1149 bytes long when we only have 1000 bytes to use. No wonder we're getting an error.

My solution was to change the CREATE statement in such a way as to bring this number down below 1000. Here's what I changed it to:

KEY cl_sortkey(cl_to(200),cl_sortkey(128))

This way, we're only taking the first 200 characters of cl_to to use in a our key. This may have adverse affects on MediaWiki, but I think the chances are pretty small. 200 characters is still quite a bit.

Let's check our math:

200+128=328
3*328=984

Since 984 is less than 1000, we should be fine. It worked for me, give it a shot. If it doesn't fix your problems, you can always drop the table and recreate it using a different workaround.

Disclaimer: This is not an official MediaWiki bug fix, as such, I will not be held responsible for any damages resulting from its use. I am in no way associated with the WikiMedia Foundation or the MediaWiki content tool.