Wikimedia Bugzilla is closed!

Use MySQL 4.0 if possible; 4.1 is not fully backwards compatible.

If you must use 4.1 (and your PHP installation is properly configured for 
it!) then make sure the database is created with latin-1 as the default 
character set, not UTF-8. MySQL 4.1's character set support will 
otherwise do strange things, including complaining about keys being too 
long.

Also you may experience data corrupting with MySQL 4.1 when doing 
backups and restores with mysqldump unless you are careful to match 
the latin-1 charset setting, as the automatic conversion between "latin
-1" and utf-8 is lossy to the utf-8 and binary data which is stored in 
some fields.

Thanks a bunch, I'll try that. (MySQL is configured to use a default charset of
UTF-8 indeed.) Is this a bug in MySQL that should be reported, BTW, or is it
just MediaWiki that's not entirely compatible with MySQL here? It sure sounds
like the former's the case, given the strange and apparently unrelated error
message.

I believe the index maximum length limits are in bytes; having fields 
marked as Unicode probably makes them eat up more bytes for a given 
character length, and one of the indexes which fits at 8 bits per 
'character' doesn't fit at 16 (or 32 or however they store it).

At some point we'll want to make some changes to how fields are laid 
out and used to make better use of MySQL 4.1 and higher's Unicode 
support, but we need to maintain compatibility with the widely-used 
older versions too. Historically it's been necessary to let MySQL think it's 
working in 8-bit latin-1 and just shove UTF-8-encoded data in there.

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

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

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

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

Had the same problem with 1.5alpha1 released 2005-05-03 on MySql 3.23.58. Fixed
by changing cl_to and cl_sortkey to varchar(250) in the source. Will this have
any adverse effects?

See the following MySQL bug:
http://bugs.mysql.com/bug.php?id=4541

Yes, do these keys really have to be so long?  Has anyone profiled the performance gains or 
losses, or whether the values ever approach that length?

Long keys can make selects faster, but they can make inserts a LOT slower.   I suggest 
looking at whether the keys really need to be this wide.   In my installation, no value even 
comes close to using the maximum length of this index.  

I can say nothing about whether such long key is a req.
But I can add my quick resolution for this problem (so far looks like it works):

CREATE TABLE `mw_categorylinks` (
  `cl_from` int(8) unsigned NOT NULL default '0',
  `cl_to` varchar(255) character set utf8 collate utf8_bin NOT NULL default '',
  `cl_sortkey` varchar(255) character set utf8 collate utf8_bin NOT NULL default '',
  `cl_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
  UNIQUE KEY `cl_from` (`cl_from`,`cl_to`),
  KEY `cl_sortkey` (`cl_to`,`cl_sortkey`(78)),
  KEY `cl_timestamp` (`cl_to`,`cl_timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The idea is to use shorter key length (`cl_sortkey`(78)).
As a result composite key total size is <= 333.

Changed table definition in CVS HEAD for 1.5; cl_sortkey is now limited to 86 characters 
rather than 255, so the key length fits within the 1024-byte limit when utf8 is selected. 
It also should stay within the 500 byte limit on MySQL 3.x.

(Incidentally, that it's 3 bytes per character indicates a serious bug in MySQL; it 
apparently doesn't support full UTF-8 but only a partial subset. Depending on how it's 
implemented this could introduce data corruption for text containing characters outside 
the BMP.)

Since the index now covers the entire field, the actual sorting also no longer triggers a 
filesort (according to EXPLAIN).

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

cl_sortkey is still too long for me - 78 is the upper limit on my server's
installation of MySQL 4.1, for some reason. I doubt this helps, but may as well:
$ mysql -V
mysql  Ver 14.7 Distrib 4.1.8, for pc-linux (i686)

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

This bug is NOT fixed in 1.6.3.

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

PHP 4.3.4 installed 
PHP server API is apache2handler; ok, using pretty URLs (index.php/Page_Title) 
Have XML / Latin1-UTF-8 conversion support. 
PHP's memory_limit is 8M. If this is too low, installation may fail! Attempting to raise limit 
to 20M... ok. 
Have zlib support; enabling output compression. 
Neither Turck MMCache nor eAccelerator are installed, can't use object caching functions 
Found GNU diff3: /usr/bin/diff3. 
Found GD graphics library built-in, image thumbnailing will be enabled if you enable uploads. 
Installation directory: /srv/www/htdocs/wiki 
Script URI path: /wiki 
Environment checked. You can install MediaWiki. 
Generating configuration file...

Database type: mysql 
PHP is linked with old MySQL client libraries. If you are using a MySQL 4.1 server and have 
problems connecting to the database, see http://dev.mysql.com/doc/mysql/en/old-client.html for 
help. 
Attempting to connect to database server as root...success. 
Connected to 4.0.18 
Database wikidb exists 
There are already MediaWiki tables in this database. Checking if updates are needed... 
Granting user permissions... 
...hitcounter table already exists.
...querycache table already exists.
...objectcache table already exists.
...categorylinks table already exists.
...logging table already exists.
...validate table already exists.
...user_newtalk table already exists.
...transcache table already exists.
...trackbacks table already exists.
...externallinks table already exists.
Creating job table...Query "CREATE TABLE `job` (
 job_id int(9) unsigned NOT NULL auto_increment,
 job_cmd varchar(255) NOT NULL default '',
 job_namespace int NOT NULL,
 job_title varchar(255) binary NOT NULL,
 job_params blob NOT NULL default '',
 PRIMARY KEY job_id (job_id),
 KEY (job_cmd, job_namespace, job_title)
 ) ENGINE=InnoDB
" failed with error code "Specified key was too long. Max key length is 500 (localhost)".
 

I propose this patch :

--- tables.sql.orig     2006-04-13 08:00:16.000000000 -0400
+++ tables.sql  2006-04-13 10:30:22.000000000 -0400
@@ -924,5 +924,5 @@
   job_params blob NOT NULL default '',

   PRIMARY KEY job_id (job_id),
-  KEY (job_cmd, job_namespace, job_title)
+  KEY (job_cmd(160), job_namespace, job_title(160))
 ) TYPE=InnoDB;

FYI: #17 works for me. Hopes this doesn't fsck up the database later on :-)

FWIW: #17 only fixes the problem for new installations. I encountered it on an upgrade from 1.5.4 to 1.6.5 and 
applied the patch to maintenance/archives/patch-job.sql which does the same trick.

Thank you, Martin Rohrbach - that was indeed the procedure I had to perform in
order to make it work in my place. My situation is thus the same as #19, and it
worked nicely. Two big wikis are now running nicely on this solution.

Maybe someone will find this usefull, (Sergei Golubchik = MySQL Dev)
http://bugs.mysql.com/bug.php?id=2130

I just found this after upgrading to 1.6.5 where the problem still persists (for job_title and job_cmd). I set both 
to 250 instead 255 (max_key is 500 here) and added a default latin1 to the patch-job.sql file. Still, as a 
general question, how dangerous is playing with these field lengths? Did I break my database? What with the 
next upgrade scripts? I am not an sql-expert at all... TIA+CU Lars.

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

#17 worked fine with 1.6.7, too. Thanks a lot, Thomas. Sorry for creating
duplicate 6269.

I have just done a clean install of 1.6.7, hit this bug, applied suggestion #17.
 Can't this #17 patch be applied to the source code to stop everyone else
hitting this problem?

I have just done a clean install of 1.6.7, hit this bug, applied suggestion #17.
 Can't this #17 patch be applied to the source code to stop everyone else
hitting this problem?

Just tried installing 1.6.7 & ran into the same error for the table 'job' on
Windows Server 2003 Standard Edition with Apache 2.0.55 & MySQL 5.0.15-nt, found
the following worked (as specified above #17, with the ENGINE=InnoDB added);

CREATE TABLE `job` (
job_id int(9) unsigned NOT NULL auto_increment,
job_cmd varchar(255) NOT NULL default '',
job_namespace int NOT NULL,
job_title varchar(255) binary NOT NULL,
job_params blob NOT NULL default '',
PRIMARY KEY job_id (job_id),
KEY (job_cmd(160), job_namespace, job_title(160))
) ENGINE=InnoDB;

I had the same problem and did the workaround mentioned in
http://bugzilla.wikimedia.org/show_bug.cgi?id=1322#c19 . 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?

MediaWiki 1.7.1 Installation
Don't forget security updates! Keep an eye on the low-traffic release
announcements mailing list. 
Checking environment...
Please include all of the lines below when reporting installation problems.

PHP 5.1.4 installed 
Found database drivers for: MySQL 
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 nor APC 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: C:\xampplite\htdocs\wiki 
Script URI path: /wiki 
Environment checked. You can install MediaWiki. 
Warning: $wgSecretKey key is insecure, generated with mt_rand(). Consider
changing it manually. 
Generating configuration file...

Database type: MySQL 
Loading class: DatabaseMysql
Attempting to connect to database server as root...success. 
Connected to 5.0.21-community 
Database wikidb exists 
There are already MediaWiki tables in this database. Checking if updates are
needed... 
...hitcounter table already exists.
...querycache table already exists.
...objectcache table already exists.
Creating categorylinks table...Query "CREATE TABLE `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=InnoDB
" failed with error code "Specified key was too long; max key length is 1000
bytes (localhost)".

this seems crazy. happens to me with mediawiki-1.7.1, php-5.1.6, mysql-4.1.21:
===
Creating tables... using MySQL 5 table defs...Query "CREATE TABLE
`mw_karate_job` ( job_id int(9) unsigned NOT NULL auto_increment, job_cmd
varchar(255) NOT NULL default '', job_namespace int NOT NULL, job_title
varchar(255) binary NOT NULL, job_params blob NOT NULL default '', PRIMARY KEY
job_id (job_id), KEY (job_cmd, job_namespace, job_title) ) TYPE=InnoDB, DEFAULT
CHARSET=utf8 " failed with error code "Specified key was too long; max key
length is 1024 bytes (localhost)".
===
going to try #17...

pls fix this, such things really should not happen!

Created attachment 2478 [details]
patch (job table)

Patch changes index for job.job_cmd to only use the first 66 characters
(floor((1000-9*4-3*255)/3)) of job_cmd. Shouldn't be a big deal, as names for
job queue commands will probably stay under 66 chars anyways.

(In reply to comment #31)
> Created an attachment (id=2478) [edit]
> patch (job table)
> 
> Patch changes index for job.job_cmd to only use the first 66 characters
> (floor((1000-9*4-3*255)/3)) of job_cmd. Shouldn't be a big deal, as names for
> job queue commands will probably stay under 66 chars anyways.

I applied this patch and it had no effect.

I did find this workaround which seems to help:

Modify maintenance/archives/patch-job.sql, add the following lines:

DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci

after the “) TYPE=xxxxDB” line.

(In reply to comment #32)
> I applied this patch and it had no effect.

Meaning? Where does the install script hang?

By the way, I've successfully tested the patch on 1.8-svn, php 5.1.6, MySQL
4.1.21-standard.

Decreasing priority and severity.

It's not a trivial bug. It doesn't have a particularly high priority, but it's
not trivial.

Severity = how bad the bug is for those who experience it, priority = whether
we're actually do anything about it.

http://bugzilla.wikimedia.org/page.cgi?id=fields.html#bug_severity

Tried new install of mediawiki 1.10.0 and encountered the same errors with UTF-8 database. Used the workarounds in comment #11, comment #12, and comment #17, here are all changes I made to maintenance/tables.sql to get the tables created:


464c464
<   -- needs cut to be smaller than 1024 bytes (at 3 bytes per char).
---
>   -- needs cut to be smaller than 1000 bytes (at 3 bytes per char).
467c467
<   cl_sortkey varchar(86) binary NOT NULL default '',
---
>   cl_sortkey varchar(78) binary NOT NULL default '',
1061c1061
<   KEY (job_cmd, job_namespace, job_title)
---
>   KEY (job_cmd(160), job_namespace, job_title(160))
1124c1124
<   pr_level varchar(255) NOT NULL,
---
>   pr_level varchar(78) NOT NULL,

I get a similar problem for 10.0, but the patch above does not seem to work for me (a different table which fails);

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)
 ) TYPE=InnoDB
" failed with error code "Specified key was too long. Max key length is 500 (localhost)".

Wasn't this fixed by Tim in trunk (for the 1000-byte limit anyway)?

Looks like it. In trunk, it's pr_type varbinary(60) NOT NULL and pr_level varbinary(60) NOT NULL.

These fixes should at some point be backported to 1.10.1, I think, unless it's decided it's not worth it when 1.11 comes out so soon, which seems reasonable.  Until then, users of 1.10.0 should be able to apply r23239 and r23247 to their local installations (they may not apply completely cleanly).

Fixed in r23239, r23247 by Tim Starling.

This still occurs in the following scenario :

php-5.0.3-1
MySQL-server-4.0.20-0

Upgrading from MediaWiki 1.10.1 to 1.12.0


Content-type: text/html
X-Powered-By: PHP/5.0.3

MediaWiki 1.12.0 Updater

Going to run database updates for wikidb
Depending on the size of your database this may take a while!
Abort with control-c in the next five seconds...0
...have ipb_id field in ipblocks table.
...have ipb_expiry field in ipblocks table.
...already have interwiki table
...indexes seem up to 20031107 standards
...hitcounter table already exists.
...have rc_type field in recentchanges table.
...have user_real_name field in user table.
...querycache table already exists.
...objectcache table already exists.
...categorylinks table already exists.
Already have pagelinks; skipping old links table updates.
...have rc_ip field in recentchanges table.
...image primary key already set.
...have rc_id field in recentchanges table.
...have rc_patrolled field in recentchanges table.
...logging table already exists.
...have user_token field in user table.
The watchlist table is already set up for email notification.
...watchlist talk page rows already present
...user table does not contain old email authentication field.
...page table already exists.
...have log_params field in logging table.
Logging table has correct title encoding.
...have ar_rev_id field in archive table.
...have page_len field in page table.
revision timestamp indexes already up to 2005-03-13
...rev_text_id already in place.
...have rev_deleted field in revision table.
...have img_width field in image table.
...have img_metadata field in image table.
...have user_email_token field in user table.
...have ar_text_id field in archive table.
...page_namespace is already a full int (int(11)).
...ar_namespace is already a full int (int(11)).
...rc_namespace is already a full int (int(11)).
...wl_namespace is already a full int (int(11)).
...qc_namespace is already a full int (int(11)).
...log_namespace is already a full int (int(11)).
...have img_media_type field in image table.
...already have pagelinks table.
No img_type field in image table; Good.
Already have unique user_name index.
...user_groups table already exists.
...user_groups is in current format.
...have ss_total_pages field in site_stats table.
...user_newtalk table already exists.
...transcache table already exists.
...have iw_trans field in interwiki table.
...trackbacks table already exists.
...wl_notificationtimestamp is already nullable.
...timestamp key on logging already exists.
...have ipb_range_start field in ipblocks table.
Setting page_random to a random value on rows where it equals 0...changed 0 rows
...have user_registration field in user table.
...templatelinks table already exists
...externallinks table already exists.
...job table already exists.
...have ss_images field in site_stats table.
...langlinks table already exists.
...querycache_info table already exists.
...filearchive table already exists.
...have ipb_anon_only field in ipblocks table.
Checking for additional recent changes indices...
...index `rc_ns_usertext` seems ok.
...index `rc_user_text` seems ok.
...have user_newpass_time field in user table.
...redirect table already exists.
...querycachetwo table already exists.
...have ipb_enable_autoblock field in ipblocks table.
Checking for backlinking indices...
Checking if pagelinks index pl_namespace includes field pl_from...
...index pl_namespace on table pagelinks seems to be ok
Checking if templatelinks index tl_namespace includes field tl_from...
...index tl_namespace on table templatelinks seems to be ok
Checking if imagelinks index il_to includes field il_from...
...index il_to on table imagelinks seems to be ok
...have rc_old_len field in recentchanges table.
...have user_editcount field in user table.
...page_restrictions table already exists.
...have log_id field in logging table.
...have rev_parent_id field in revision table.
...have pr_id field in page_restrictions table.
...have rev_len field in revision table.
...have rc_deleted field in recentchanges table.
...have log_deleted field in logging table.
...have ar_deleted field in archive table.
...have ipb_deleted field in ipblocks table.
...have fa_deleted field in filearchive table.
...have ar_len field in archive table.
Adding ipb_block_email field to table ipblocks...ok
Checking for categorylinks indices...
Checking if categorylinks index cl_sortkey includes field cl_from...
...index cl_sortkey on table categorylinks has no field cl_from; adding
Query "ALTER TABLE `categorylinks`
 DROP INDEX cl_sortkey,
 ADD INDEX cl_sortkey(cl_to, cl_sortkey, cl_from)
" failed with error code "Specified key was too long. Max key length is 500 (localhost)".



Here is the fix I had to apply to get this to work :

I modified the file :
/var/www/html/wiki/maintenance/archives/patch-categorylinksindex.sql

and changed the line :

   ADD INDEX cl_sortkey(cl_to, cl_sortkey, cl_from);

to

   ADD INDEX cl_sortkey(cl_to(160), cl_sortkey(160), cl_from);

after which point, re-running the upgrade completed successuflly

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