Page MenuHomePhabricator

On install - Database returned error 1071: Specified key was too long;
Closed, InvalidPublic

Description

Author: urbanvintner

Description:
Running MediaWiki 1.16.0
MySQL 5.1.50
Collation set to utf8-unicode-ci

Suggestions?

Full error output follows

Creating thread table...A database query syntax error has occurred.
The last attempted database query was:
"CREATE TABLE thread (
thread_id int(8) unsigned NOT NULL auto_increment,
thread_root int(8) unsigned UNIQUE NOT NULL,
thread_ancestor int(8) unsigned NOT NULL,
thread_parent int(8) unsigned NULL,
thread_summary_page int(8) unsigned NULL,
thread_subject varchar(255) NULL,
thread_author_id int unsigned NULL,
thread_author_name varchar(255) NULL,
thread_modified char(14) binary NOT NULL default '',
thread_created char(14) binary NOT NULL default '',
thread_editedness int(1) NOT NULL default 0,
thread_article_namespace int NOT NULL,
thread_article_title varchar(255) binary NOT NULL,
thread_article_id int(8) unsigned NOT NULL,
thread_type int(4) unsigned NOT NULL default 0,
thread_sortkey varchar(255) NOT NULL default '',
thread_replies int(8) DEFAULT -1,
thread_signature TINYBLOB NULL,
PRIMARY KEY thread_id (thread_id),
UNIQUE INDEX thread_root_page (thread_root),
INDEX thread_ancestor (thread_ancestor, thread_parent),
INDEX thread_article_title (thread_article_namespace, thread_article_title, thread_sortkey),
INDEX thread_article (thread_article_id, thread_sortkey),
INDEX thread_modified (thread_modified),
INDEX thread_created (thread_created),
INDEX thread_summary_page (thread_summary_page),
INDEX (thread_author_id,thread_author_name),
INDEX (thread_sortkey)
) ENGINE=InnoDB, DEFAULT CHARSET=utf8
"
from within function "DatabaseBase::sourceStream".
Database returned error "1071: Specified key was too long; max key length is 1000 bytes (localhost)"


Version: REL1_19-branch
Severity: critical

Details

Reference
bz26517

Event Timeline

bzimport raised the priority of this task from to Normal.Nov 21 2014, 11:22 PM
bzimport set Reference to bz26517.
bzimport added a subscriber: Unknown Object (MLST).
bzimport created this task.Dec 31 2010, 1:48 AM
Reedy added a comment.Dec 31 2010, 5:32 AM

mysql> CREATE TABLE thread (

->  thread_id int(8) unsigned NOT NULL auto_increment,
->  thread_root int(8) unsigned UNIQUE NOT NULL,
->  thread_ancestor int(8) unsigned NOT NULL,
->  thread_parent int(8) unsigned NULL,
->  thread_summary_page int(8) unsigned NULL,
->  thread_subject varchar(255) NULL,
->  thread_author_id int unsigned NULL,
->  thread_author_name varchar(255) NULL,
->  thread_modified char(14) binary NOT NULL default '',
->  thread_created char(14) binary NOT NULL default '',
->  thread_editedness int(1) NOT NULL default 0,
->  thread_article_namespace int NOT NULL,
->  thread_article_title varchar(255) binary NOT NULL,
->  thread_article_id int(8) unsigned NOT NULL,
->  thread_type int(4) unsigned NOT NULL default 0,
->  thread_sortkey varchar(255) NOT NULL default '',
->  thread_replies int(8) DEFAULT -1,
->  thread_signature TINYBLOB NULL,
->  PRIMARY KEY thread_id (thread_id),
->  UNIQUE INDEX thread_root_page (thread_root),
->  INDEX thread_ancestor (thread_ancestor, thread_parent),
->  INDEX thread_article_title (thread_article_namespace, thread_article_title,
-> thread_sortkey),
->  INDEX thread_article (thread_article_id, thread_sortkey),
->  INDEX thread_modified (thread_modified),
->  INDEX thread_created (thread_created),
->  INDEX thread_summary_page (thread_summary_page),
->  INDEX (thread_author_id,thread_author_name),
->  INDEX (thread_sortkey)
->  ) ENGINE=InnoDB, DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.14 sec)

mysql> ^CCtrl-C -- exit!
Aborted
reedy@ubuntu64-esxi:~$ mysql --version
mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1
reedy@ubuntu64-esxi:~$

WFM...

Seemingly, an "error" with that collation - As per the error... It's as that collation uses multiple bytes per character

mysql> SHOW VARIABLES LIKE '%collation%';
+----------------------+-------------------+

Variable_nameValue

+----------------------+-------------------+

collation_connectionlatin1_swedish_ci
collation_databaselatin1_swedish_ci
collation_serverlatin1_swedish_ci

+----------------------+-------------------+

is mine (and the toolserver), and that's fine

->  INDEX thread_article_title (thread_article_namespace, thread_article_title,
-> thread_sortkey),

2 fields are 255 chars.. I'm guessing that they're to blame...

I thought s/varchar/varbinary/ was the way to "fix" this?

urbanvintner wrote:

@Reedy, Changing to a Latin Collation seems to have to effect. The database charset is Unicode too though, which I need it to be incidentally

@Roan - Not sure what you mean? Can you explain further

(In reply to comment #3)

@Roan - Not sure what you mean? Can you explain further

My question was directed to the MySQL experts around here. IIRC changing varchar to varbinary in the table definition was previously used to fix a similar case.

What problems would changing the s/varchar/varbinary/ introduce? Is there anyone who has tested this with varbinary?

(In reply to comment #5)

What problems would changing the s/varchar/varbinary/ introduce? Is there
anyone who has tested this with varbinary?

Should be fine, but I'm rewriting the LiquidThreads schema as we speak, so it's not necessarily high priority.

Should be fine

So is there a reason to retain varbinary instead of varchar? (I ask b/c if your rewrite includes changing varchar to varbinary, I'd like to close this bug.)

(In reply to comment #7)

Should be fine

So is there a reason to retain varbinary instead of varchar? (I ask b/c if your
rewrite includes changing varchar to varbinary, I'd like to close this bug.)

This is confusing. If you mean retaining varchar instead of varbinary, then no, there's no need to keep it as a varchar.

sorry for the confusion: glad you saw through it :)

closing as FIXED since Andrew is rewriting and aware of this problem.

Cavila added a comment.Jul 4 2013, 7:25 PM

Andrew may be aware : ) but nothing was fixed, I'm afraid. See http://www.mediawiki.org/wiki/Thread:Extension_talk:LiquidThreads/Database_error

Resetting "assigned to" since that complete rewrite was abandoned AFAIK and Andrew may not be in the project anymore

This bug is definitely not low priority; however, it's not clear if there is someone willing to work on 1.19 issues for LQT (there is no maintainer so nobody can decide if it's supported or not, sorry).

With what releases this is confirmed to happen?

Jorm removed a subscriber: Jorm.Dec 26 2015, 7:29 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 26 2015, 7:29 PM
Jdforrester-WMF lowered the priority of this task from Normal to Lowest.Aug 4 2016, 11:35 PM
Jdforrester-WMF added a subscriber: Jdforrester-WMF.

LiquidThreads has been replaced by StructuredDiscussions on all Wikimedia production wikis (except one, which will be done soon). It is no longer under active development or maintenance, so I'm re-classifying all open LQT tasks as "Lowest" priority.

Zoranzoki21 closed this task as Invalid.Sep 29 2018, 10:14 PM
Zoranzoki21 added a subscriber: Zoranzoki21.

The version mentoined here is unsupported. So, closing this task as invalid.