GitLab

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?