[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 --- Comment #14 from Aryeh Gregor simetrical+wikib...@gmail.com 2010-11-07 17:36:43 UTC --- Dmitriy, does it work now? -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 --- Comment #15 from Dmitriy c...@uniyar.ac.ru 2010-11-08 07:05:19 UTC --- Aryeh, yes, I've re-imported old 1.15 sql dump, updated local trunk, and re-run php update.php and it seems to work fine. Thank you, Roan. Next time, if I'll spot the bug, I'll open a new one instead of re-opening this one. I just didn't knew these new tables are belonging to ResourceLoader (now I do). -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 Roan Kattouw roan.katt...@gmail.com changed: What|Removed |Added CC||roan.katt...@gmail.com --- Comment #10 from Roan Kattouw roan.katt...@gmail.com 2010-11-05 20:10:33 UTC --- (In reply to comment #9) Please open a new bug, this is a separate issue. Product is MediaWiki, component is Resource Loader. Maybe there should be a primary key on (mrl_message) instead of a unique key on (mrl_resource, mrl_message), but perhaps you want duplicate mrl_messages sometimes, I don't know. Yes, that's the entire purpose of the table, you have (resource, message) pairs. It's an n-to-m relationship. Possibly the columns could just be shortened here too. I guess they could be. How short would they have to be? -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 --- Comment #11 from Aryeh Gregor simetrical+wikib...@gmail.com 2010-11-05 20:42:54 UTC --- It's 1000 bytes total at three bytes per characters, so you've got 333 characters total for the two columns. Alternatively, you could make them varbinary (or somehow force ASCII encoding for varchar?) so that they're one byte per character for everyone. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 --- Comment #12 from Roan Kattouw roan.katt...@gmail.com 2010-11-05 22:07:53 UTC --- (In reply to comment #11) It's 1000 bytes total at three bytes per characters, so you've got 333 characters total for the two columns. Alternatively, you could make them varbinary (or somehow force ASCII encoding for varchar?) so that they're one byte per character for everyone. varbinary sounds good, I'll do that -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 --- Comment #13 from Roan Kattouw roan.katt...@gmail.com 2010-11-05 22:16:36 UTC --- (In reply to comment #12) varbinary sounds good, I'll do that Done in r76150. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 Aryeh Gregor simetrical+wikib...@gmail.com changed: What|Removed |Added Status|REOPENED|RESOLVED Resolution||FIXED --- Comment #9 from Aryeh Gregor simetrical+wikib...@gmail.com 2010-11-04 21:12:08 UTC --- Please open a new bug, this is a separate issue. Product is MediaWiki, component is Resource Loader. Maybe there should be a primary key on (mrl_message) instead of a unique key on (mrl_resource, mrl_message), but perhaps you want duplicate mrl_messages sometimes, I don't know. Possibly the columns could just be shortened here too. I don't know what this table does offhand, so I can't say. Someone like Trevor or Roan will have to look at it. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 --- Comment #5 from Dmitriy c...@uniyar.ac.ru 2010-10-14 10:41:27 UTC --- Please post the output of SHOW CREATE TABLE wiki_categorylinks\G. I have a guess as to how this could happen, but would like to confirm it. Here's the output: CREATE TABLE `wiki_categorylinks` ( `cl_from` int(10) unsigned NOT NULL DEFAULT '0', `cl_to` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `cl_sortkey` varbinary(255) NOT NULL DEFAULT '', `cl_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `cl_sortkey_prefix` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `cl_collation` varbinary(32) NOT NULL DEFAULT '', `cl_type` enum('page','subcat','file') NOT NULL DEFAULT 'page', UNIQUE KEY `cl_from` (`cl_from`,`cl_to`), KEY `cl_timestamp` (`cl_to`,`cl_timestamp`), KEY `cl_collation` (`cl_collation`), KEY `cl_sortkey` (`cl_to`,`cl_type`,`cl_sortkey`(1),`cl_from`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 Then, I've tried field length of 231 utf8 chars: ALTER TABLE wiki_categorylinks CHANGE COLUMN cl_sortkey cl_sortkey varbinary(231) NOT NULL default ''; caused the same error when altering index, while 230 utf8 chars seems to be OK: ALTER TABLE wiki_categorylinks CHANGE COLUMN cl_sortkey cl_sortkey varbinary(230) NOT NULL default ''; fixed the error: mysql ALTER TABLE `wiki_categorylinks` DROP INDEX cl_sortkey, ADD INDEX cl_sortkey (cl_to, cl_type, cl_sortkey, cl_from); Query OK, 11510 rows affected (1.06 sec) Records: 11510 Duplicates: 0 Warnings: 0 No. That destroys the point of the index, you may as well just drop the index entirely if you do that. The field itself has to be shortened, not the index. The index has to cover the whole field to work correctly. (Although if your wiki is small enough, you might not notice the difference if the index isn't working.) Thanks for info, my mistake. This is not related to MyISAM, as far as I know -- it's probably related to the use of utf8 collation instead of binary. I've switched another wiki from 1.16 trunk (with InnoDB tables) to 1.17 trunk just few days before and there was no warnings or errors during execution of update.php. Has that patch-categorylinks-better-collation.sql already been incorporated into pre-release 1.16 ? It seems not - cannot find such patch in old backup. So I am not absolutely sure that it's unrelated to MyISAM (however this is another 1.15.4 wiki). -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 Aryeh Gregor simetrical+wikib...@gmail.com changed: What|Removed |Added Status|NEW |RESOLVED Resolution||FIXED --- Comment #6 from Aryeh Gregor simetrical+wikib...@gmail.com 2010-10-14 21:47:24 UTC --- Fixed in r74798. Thanks for the report and testing. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 --- Comment #7 from Aryeh Gregor simetrical+wikib...@gmail.com 2010-10-14 21:47:51 UTC --- (and thanks to Max for CC'ing me) -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 --- Comment #3 from Dmitriy c...@uniyar.ac.ru 2010-10-13 08:57:51 UTC --- Created attachment 7731 -- https://bugzilla.wikimedia.org/attachment.cgi?id=7731 Fixes index of categorylinks of 1.17 schema in myisam mode -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 Aryeh Gregor simetrical+wikib...@gmail.com changed: What|Removed |Added CC||simetrical+wikib...@gmail.c ||om --- Comment #4 from Aryeh Gregor simetrical+wikib...@gmail.com 2010-10-13 18:41:46 UTC --- (In reply to comment #0) I am trying to upgrade 1.15.4 to 1.17 trunk, while running update.php I've got the following error message: ...doing rev_id from 59003 to 59202 ...doing rev_id from 59203 to 59402 ...doing rev_id from 59403 to 59602 ...doing rev_id from 59603 to 59802 rev_len population complete ... 3701 rows changed (0 missing) Creating iwlinks table...ok ...iwl_prefix_title_from key already set on iwlinks table. Adding ul_value field to table updatelog...ok Adding iw_api field to table interwiki...ok ...iwl_prefix key doesn't exist. ...iwl_prefix_from_title key doesn't exist. Adding cl_collation field to table categorylinks...A database query syntax error has occurred. The last attempted database query was: ALTER TABLE `wiki_categorylinks` CHANGE COLUMN cl_sortkey cl_sortkey varbinary(255) NOT NULL default '', ADD COLUMN cl_sortkey_prefix varchar(255) binary NOT NULL default '', ADD COLUMN cl_collation varbinary(32) NOT NULL default '', ADD COLUMN cl_type ENUM('page', 'subcat', 'file') NOT NULL default 'page', ADD INDEX (cl_collation), DROP INDEX cl_sortkey, ADD INDEX cl_sortkey (cl_to, cl_type, cl_sortkey, cl_from) from within function DatabaseBase::sourceFile( /var/www/wiki/phase3/maintenance/archives/patch-categorylinks-better-collation.sql ). Database returned error 1071: Specified key was too long; max key length is 1000 bytes (localhost) Please post the output of SHOW CREATE TABLE wiki_categorylinks\G. I have a guess as to how this could happen, but would like to confirm it. Should I patch them temporarily and which key limitations would you suggest? Make the cl_sortkey column (*not* the index) shorter until it works. Offhand, I'd think ALTER TABLE wiki_categorylinks CHANGE COLUMN cl_sortkey cl_sortkey varbinary(230) NOT NULL default ''; should do it, because I'm guessing it's 255*3 = 765 bytes for cl_to in utf8, one byte for cl_type, four bytes for cl_from, 765 + 1 + 4 = 770, leaving 230 bytes for cl_sortkey. But I'd like that SHOW CREATE TABLE from you to confirm it. Ideally, please try different sizes and tell me the biggest that works. My first guess is 230 should work but 231 shouldn't, but try various sizes and tell me what you get. (In reply to comment #2) I've changed maintenance/patch-categorylinks-better-collation.sql like this (see an attachment) and the problem went away. Is it OK to use cl_sortkey(1) in cl_sortkey index? No. That destroys the point of the index, you may as well just drop the index entirely if you do that. The field itself has to be shortened, not the index. The index has to cover the whole field to work correctly. (Although if your wiki is small enough, you might not notice the difference if the index isn't working.) Maybe it would be a good idea for wikimedia to have a myisam-based setup at some of it's testing hosts? This is not related to MyISAM, as far as I know -- it's probably related to the use of utf8 collation instead of binary. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 --- Comment #1 from Dmitriy c...@uniyar.ac.ru 2010-10-12 18:30:36 UTC --- By the way, that wiki was happily going 1.12 to 1.14 then to 1.15 (in myisam mode) and I don't recall such errors while running update.php (hope my memory is not too short). -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine
https://bugzilla.wikimedia.org/show_bug.cgi?id=25503 Max Semenik maxsem.w...@gmail.com changed: What|Removed |Added AssignedTo|wikibug...@lists.wikimedia. |simetrical+wikib...@gmail.c |org |om -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email --- You are receiving this mail because: --- You are the assignee for the bug. You are on the CC list for the bug. ___ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l