[Bug 25503] Specified key was too long error in update.php script while altering categorylinks table which has myisam engine

2010-11-07 Thread bugzilla-daemon
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

2010-11-07 Thread bugzilla-daemon
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

2010-11-05 Thread bugzilla-daemon
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

2010-11-05 Thread bugzilla-daemon
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

2010-11-05 Thread bugzilla-daemon
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

2010-11-05 Thread bugzilla-daemon
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

2010-11-04 Thread bugzilla-daemon
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

2010-10-14 Thread bugzilla-daemon
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

2010-10-14 Thread bugzilla-daemon
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

2010-10-14 Thread bugzilla-daemon
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

2010-10-13 Thread bugzilla-daemon
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

2010-10-13 Thread bugzilla-daemon
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

2010-10-12 Thread bugzilla-daemon
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

2010-10-12 Thread bugzilla-daemon
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