Last modified: 2013-08-30 05:37:01 UTC
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)"
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_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_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?
@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.
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?
