Question

the maintenance service reports an error while updating the search index

Case FC1960403

Answer 1

This could be corruption of the Bug table. Try repairing the table.

If that didn't work, it could be the result of a giant case. If the error message gives you a case number, such as "Type: Bug ID: 17558" Try the following:

1. First reset the search index in Admin -> Site -> Search

2. If that doesn't fix it, run the following SQL on your database:

   SELECT ixBug, ixBugEvent, LENGTH(s)
   FROM BugEvent
   ORDER BY LENGTH(s) DESC
   LIMIT 20;
   

   or, for SQL Server:

   SELECT TOP 20 ixBug, ixBugEvent, DATALENGTH(s)
   FROM BugEvent
   ORDER BY DATALENGTH(s) DESC
   

This will help you see if there's a huge bug event that might be choking your indexer.

Answer 2

In fact lately we discovered some very large cases (due to received emails with very large body) in our MySQL database causing FogBugz 8.0.26 to never complete indexing. In addition we couldn't open the cases in FogBugz (System.OutOfMemoryException).

We decided to copy them to another self defined table and to truncate the data in the original table.

Here are our SQL statements (WITHOUT ANY WARRANTY!) if someone is interested in:

// custom user defined table for large events
// only create if it doesn't exists already
CREATE TABLE IF NOT EXISTS bugeventlarge LIKE bugevent;

// inserting (copying) very large cases from bugevent to bugeventlarge
INSERT IGNORE INTO bugeventlarge
  SELECT * FROM bugevent WHERE LENGTH(s) > 10000000; // 10 MB

// truncate very large data and html emails of closed cases
// in table bugevent and attach a hint in the text
UPDATE bugevent
  SET
    s = CASE WHEN LENGTH(s) > 100000 THEN CONCAT( LEFT(s, 100000), CHAR(13), CHAR(10), CHAR(13), CHAR(10), '[Truncated - Original in Table bugeventlarge]') ELSE s END,
    sHTML = CASE WHEN LENGTH(s) > 100000 THEN CONCAT( LEFT(sHTML, 100000), CHAR(13), CHAR(10), CHAR(13), CHAR(10), '[Truncated - Original in Table bugeventlarge]') ELSE sHTML END
WHERE
  ixBugEvent IN (
    SELECT bugeventlarge.ixBugEvent FROM bugeventlarge
    JOIN bug ON bug.ixBug = bugeventlarge.ixBug
    WHERE 
      bug.fOpen = 0
  );

// list all open cases with large data as comma separated value
SELECT GROUP_CONCAT(DISTINCT bugevent.ixBug) FROM bugevent
JOIN bug ON bug.ixBug = bugevent.ixBug
WHERE 
  bugevent.ixBugEvent IN (SELECT ixBugEvent FROM bugeventlarge)
AND
  LENGTH(bugevent.s) > 10000000
AND
  bug.fOpen = 1;

Hint: Alle theses statements can take a good amount of time.

Disclaimer: USE AT YOUR OWN RISK! Please understand that you modify the content of FogBugz' internal data tables and that nobody can be held responsible for any data loss. Please make reliable backups first!

I REPEAT: USE AT YOUR OWN RISK!