Search: Incorrect sub part key
Imported from http://bugzilla.roxen.com/bugzilla/show_bug.cgi?id=6104
Reported by Marcus Wellhardh wellhard@roxen.com
Got this backtrace on a server that uses an old external MySQL server (4.0.27) that can't use indexes bigger then 255 characters:
big_query(): Query "
: create table IF NOT EXISTS queue (
8:42:49 : uri blob not null,
0d11h34m : uri_md5 char(32) not null default '',
: template varchar(255) not null default '',
: md5 char(32) not null default '',
: recurse tinyint not null,
: stage tinyint not null,
8:42:49 : UNIQUE(uri_md5),
0d11h34m : INDEX stage (stage),
: INDEX uri (uri(256))
: )
: " failed (Incorrect sub part key. The used key part isn't a string, the used length is longer than the key part or the table handler doesn'
t support unique sub keys)
: /space/local/backend/roxen/server-5.1.185/pike/lib/modules/Mysql.so:1: mysql(/*Localhost via UNIX socket*/)->big_query("\n create table IF N
OT EXISTS queue (\n uri blob not null,\n uri_md5 char(32) not null default '',\n\ttemplate varchar(255) not null default '',\n\tm
d5 char(32) not nu"+[151])
8:42:49 : /space/local/backend/roxen/server-5.1.185/pike/lib/modules/Sql.pmod/mysql.pike (e1bab684):728: mysql(/*Localhost via UNIX socket*/)->big_query(
"\n create table IF NOT EXISTS queue (\n uri blob not null,\n uri_md5 char(32) not null default '',\n\ttemplate varchar(255) n
ot null default '',\n\tmd5 "+[169],UNDEFINED,UNDEFINED)
0d11h34m : /space/local/backend/roxen/server-5.1.185/pike/lib/modules/Sql.pmod/Sql.pike (rev 1.100):547: Sql.mysql(/*Localhost via UNIX socket*/)->query("
\n create table IF NOT EXISTS queue (\n uri blob not null,\n uri_md5 char(32) not null default '',\n\ttemplate varchar(255) no
t null default '',\n\tmd5 char(32) not nu"+[151])
: /space/local/backend/roxen/server-5.1.185/pike/lib/modules/Search.pmod/Queue.pmod/MySQL.pike:50: Search.Queue.MySQL()->perhaps_create_table()
: /space/local/backend/roxen/server-5.1.185/pike/lib/modules/Search.pmod/Queue.pmod/MySQL.pike:32: Search.Queue.MySQL()->create(Web.Crawler.Stats
(),Web.Crawler.Policy(),"mysql://localhost/search_intranet2011_intranet2011","queue",UNDEFINED,UNDEFINED)
: modules/search/modules/search_sb_interface.pike (rev 1.353):2851: DatabaseProfile(1:intranet2011)->setup_queue()
: modules/search/modules/search_sb_interface.pike (rev 1.353):3025: DatabaseProfile(1:intranet2011)->crawl_url("sitebuilder://intranet2011/gemens
amt/kontor/vardomsorg/styrdokument-och-riktlinj/overgripande-styrdokument/ledningssystem-for-kvalit/Ledningssystem_slutversion-april-2011.pdf",UNDEFINED,UN
DEFINED,,,0)
8:42:49 : modules/search/modules/search_sb_interface.pike (rev 1.353):3052: DatabaseProfile(1:intranet2011)->crawl_sb_file("/gemensamt/kontor/vardomsorg/
styrdokument-och-riktlinj/overgripande-styrdokument/ledningssystem-for-kvalit/Ledningssystem_slutversion-april-2011.pdf",0)
0d11h34m : modules/search/modules/search_sb_interface.pike (rev 1.353):221: RoxenModule(intranet2011/search_sb_interface#0)->sb_index_file(Workarea(,Site(
intranet2011:/space/local/repository/intranet2011.sb/)),,,2)
: modules/search/modules/search_sb_interface.pike (rev 1.353):432: RoxenModule(intranet2011/search_sb_interface#0)->change_hook("file_change","ge
mensamt/kontor/vardomsorg/styrdokument-och-riktlinj/overgripande-styrdokument/ledningssystem-for-kvalit/Ledningssystem_slutversion-april-2011.pdf",0,"",,,0
)
: modules/search/modules/search_sb_interface.pike (rev 1.353):484: RoxenModule(intranet2011/search_sb_interface#0)->after_hook("file_change","gem
ensamt/kontor/vardomsorg/styrdokument-och-riktlinj/overgripande-styrdoku
diff --git a/lib/modules/Search.pmod/Queue.pmod/MySQL.pike b/lib/modules/Search.pmod/Queue.pmod/MySQL.pike
index 43bdeca..43a1922 100644 (file)
--- a/lib/modules/Search.pmod/Queue.pmod/MySQL.pike
+++ b/lib/modules/Search.pmod/Queue.pmod/MySQL.pike
@@ -44,9 +44,15 @@ static void perhaps_create_table( )
recurse tinyint not null,
stage tinyint not null,
UNIQUE(uri_md5),
- INDEX stage (stage)
+ INDEX stage (stage),
+ INDEX uri (uri(256))
)
");
+ if (!sizeof(db->query("SHOW INDEX FROM " + table +
+ " WHERE key_name = 'uri'"))) {
+ db->query("ALTER TABLE " + table +
+ " ADD INDEX uri (uri(256))");
+ }
}
Perhaps change "INDEX uri (uri(256)" to 255 instead?