Topic on Project:Support desk

Searchindex table in wrong language

6
Sb61408 (talkcontribs)

Hi,

Looking for some help as I feel I have exhausted my hunting skills and found no answer to my problem. I have stood up a new site using MediaWiki 1.27 LTS on Windows server 2016 with MSSQL, IIS7 and PHP 7.2.5 CGI. The site is mostly operational with a few issues, but the biggest obstacle is that the search feature is not returning any results unless you specify an exact page name in full. In digging into this issue I have tracked it as far as the SQL table the search query is hitting with a full text search statement, but the table values for title and text are stored in what looks like Chinese rather than English. I've looked through all the settings for language I can find in both my LocalSettings.php and php.ini files and everything configurable is set to "en". I have also run the rebuildtextindex and rebuildall maintenance scripts successfully but still have this problem. There are 4 users of this site all with either English selected as their preferred language or no preference selected and all of the content built to date is in English as well. I'm out of ideas on where to look and would appreciate some help.

Thanks,

Stephen

90.187.68.5 (talkcontribs)

See "MSSQL: Fulltext search doesn’t work"

Sb61408 (talkcontribs)
Sb61408 (talkcontribs)

As a work around I created a trigger on this table that seems to work, although it is ugly:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:  Stephen Bradley

-- Create date: 6/21/2018

-- Description: Band-aide to get the searchindex table results in English not some double byte language

-- =============================================

CREATE TRIGGER [dbo].[trg_searchindex_A_IUP]

ON [dbo].[searchindex]

AFTER INSERT, UPDATE

AS

 BEGIN

  SET NOCOUNT ON;

  UPDATE [searchindex]

  SET    [si_title] = [p].[page_title]

    , [si_text] = [t].[old_text]

  FROM   [inserted] [i]

      INNER JOIN [page] [p]

       ON [i].[si_page] = [p].[page_id]

      INNER JOIN [revision] [r]

       ON [p].[page_latest] = [r].[rev_id]

      INNER JOIN [text] [t]

       ON [r].[rev_text_id] = [t].[old_id]

  WHERE  [searchindex].[si_page] = [i].[si_page]

 END

GO

ALTER TABLE [dbo].[searchindex] ENABLE TRIGGER [trg_searchindex_A_IUP]

GO
Kevin71246 (talkcontribs)

https://phabricator.wikimedia.org/T166133 Also mentions this. In the code it mentions the "chinese" is expected, but in the end it doesnt work.


Code Fix:

D:\Websites\wiki\includes\search\SearchMssql.php (line:187) update()

Comment this line:

//$sql .= "INSERT INTO $table (si_page, si_title, si_text) VALUES ($id, $si_title, $si_text)";

Add:

$text = str_replace('\'', '', $text); //remove apostrophes

$sql .= "INSERT INTO $table (si_page, si_title, si_text) VALUES ($id, '" . $title . "', '" . $text . "')";


Should probably fix this too:

D:\Websites\wiki\includes\search\SearchMssql.php (line:208) updateTitle()

Comment this line:

//$sql .= "INSERT INTO $table (si_page, si_title, si_text) VALUES ($id, $si_title, 0x00)";

Add:

$sql .= "INSERT INTO $table (si_page, si_title, si_text) VALUES ($id, '" . $title . "', 0x00)";


Run this to fix index after making code change:

"C:\Program Files\PHP\php.exe" rebuildtextindex.php

Kevin71246 (talkcontribs)

Actually I couldnt run the rebuildtextindex at first, but here was the fix: (SQL Script)

update revision set rev_page=1 where rev_page=0 --was 0

Reply to "Searchindex table in wrong language"