Page MenuHomePhabricator

"1071: Specified key was too long" (MySQL error) during installation
Closed, ResolvedPublic

Description

Author: bugzilla.wikimedia

Description:
Hi,

I just tried installing mediawiki 1.4beta4 for the first time for testing
purposes (on Windows XP SP 2 / Apache 2.0.52 / PHP 5.0.3 / MySQL 4.1.8), and got
the following error message (a few linebreaks were added for clarity). FYI, I'm
filing this as "Version: unspecified", since 1.4beta4 is not yet available for
selection. (shame shame! ;))

  • Cut here ---

Checking environment...

  • PHP 5.0.3: 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.
  • Turck MMCache not installed, can't use object caching functions
  • Found GD graphics library built-in, image thumbnailing will be enabled if

you enable uploads.

  • Installation directory: C:\Programme\Apache

Group\Apache2\htdocs\mediawiki-1.4beta4

  • Script URI path: /mediawiki-1.4beta4
  • Warning: $wgProxyKey is insecure
  • Connected as root (automatic)
  • Connected to database... 4.1.8-nt; enabling MySQL 4 enhancements
  • Warning: $wgProxyKey is insecure
  • Created database wikidb
  • Creating tables...A database error has occurred

Query: CREATE TABLE mw_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) )
Function: Error: 1071 Specified key was too long; max key length is 1024 bytes
(localhost)
Backtrace: Database.php line 345 calls wfDebugDieBacktrace()
Database.php line 297 calls Database::reportQueryError()
install-utils.inc line 118 calls Database::query()
index.php line 516 calls dbsource()

  • Cut here ---

Any ideas? Maybe it's something I did, but if that's the case, buggered if I
know what it is.


Version: unspecified
Severity: normal
Platform: PC

Details

Reference
bz1322
ReferenceSource BranchDest BranchAuthorTitle
repos/data-engineering/airflow-dags!361T332216-set-archive-retries-to-zeromainxcollazowmf_airflow_common: Force HDFSArchiveOperator to have retries=0.
repos/phabricator/phabricator!2work/other-assigneeswmf/stablebrennenworkboards: Initialize $other_assignees array
repos/releng/cli!382docker-mw-mwscriptmainaddshoreAdd mediawiki mwscript
Customize query in GitLab

Revisions and Commits

Related Objects

Event Timeline

bzimport raised the priority of this task from to Low.Nov 21 2014, 8:08 PM
bzimport set Reference to bz1322.
bzimport added a subscriber: Unknown Object (MLST).

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.

bugzilla.wikimedia wrote:

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. ***

zigger wrote:

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

zigger wrote:

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

Michael.Keppler wrote:

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

gamaiel wrote:

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?

carltonb wrote:

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.

mmitya wrote:

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).

zigger wrote:

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

lowzl wrote:

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)

zigger wrote:

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

henry.cocozzoli wrote:

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)".

tom.noel+wikimedia wrote:

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;

jakob.grimstveit wrote:

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

Martin.Rohrbach wrote:

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.

jakob.grimstveit wrote:

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.

der.scotty wrote:

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

grobe wrote:

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.

robchur wrote:

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

alex wrote:

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

mediazilla wrote:

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?

mediazilla wrote:

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?

tavernerj wrote:

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;

oxyge_007 wrote:

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?

tom_a_sparks wrote:

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)".

niki.waibel wrote:

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!

dto wrote:

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.

Attached:

wintermute_77 wrote:

(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.

dto wrote:

(In reply to comment #32)

I applied this patch and it had no effect.

Meaning? Where does the install script hang?

dto wrote:

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.

robchur wrote:

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

ayg wrote:

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

hlai2openssh wrote:

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,

jakob wrote:

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)".

ayg wrote:

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

dto wrote:

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

ayg wrote:

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.

bugzilla.wikimedia.org wrote:

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. ***

epriestley added a commit: Unknown Object (Diffusion Commit).Mar 4 2015, 8:21 AM