Mysql Server timing out on specific locate queries

Question

Im programming a search with ZF3 and the DB module. Everytime i use more than 1 short keyword - like "49" and "am" or "1" and "is" i get this error:

Statement could not be executed (HY000 - 2006 - MySQL server has gone away)

Using longer keywords works perfectly fine as long as i dont use 2 or more short keywords. The problem only occurs on the live server its working fine on the local test server.

The project table has ~2200 rows with all kind of data the project_search table has 17000 rows with multiple entries for each project , each looking like:

id, projectid, searchtext

The searchtext Column is fulltext. Here the relevant part of the php code:

$sql = new Sql($this->db);    
$select = $sql->select(['p'=>'projects']);
if(isset($filter['search'])) {
        $keywords = preg_split('/\s+/', trim($filter['search']));
        $join = $sql->select('project_search');
        $join->columns(['projectid' => new Expression('DISTINCT(projectid)')]);
        $join->group("projectid");
        foreach($keywords as $keyword) {
            $join->having(["LOCATE('$keyword', GROUP_CONCAT(searchtext))"]);

        }
        $select->join(
            ["m" => $join],
            "m.projectid = p.id",
            ['projectid'],
            \Zend\Db\Sql\Select::JOIN_RIGHT
        );
    }

Here the resulting Query:

SELECT p.*, m.projectid FROM projects AS p INNER JOIN ( SELECT projectid FROM project_search GROUP BY projectid HAVING LOCATE('am', GROUP_CONCAT(searchtext)) AND LOCATE('49', GROUP_CONCAT(searchtext)) ) AS m ON m.projectid = p.id GROUP BY p.id ORDER BY createdAt DESC

I rewrote the query using "MATCH(searchtext) AGAINST('$keyword)" and "searchtext LIKE '%keyword%' with the same result.

The problem seems to be with the live mysql server how can i debug this ?

[EDIT]

After noticing that the error only occured in a special view which had other search related queries - each using multiple joins (1 join / keyword) - i merged those queries and the error was gone. The amount of queries seemed to kill the server.

Answer 1

As stated in my EDIT the problem wasnt the query from the original Question, but some other queries using the search - parameter as well. Every query had a part like follows :

if(isset($filter['search'])) {
        $keywords = preg_split('/\s+/', trim($filter['search']));
        $field = 1;
        foreach($keywords as $keyword) {
            $join = $sql->select('project_search');
            $join->columns(["pid$field" => 'projectid']);
            $join->where(["LOCATE('$keyword', searchtext)"]);
            $join->group("projectid");
            $select->join(
                ["m$field" => $join],
                "m$field.pid$field = p.id"
            );
            $field++;
        }
    }

This resulted in alot of queries with alot of resultrows killing the mysql server eventually. I merged those Queries into the first and the error was gone.

Answer 2

Try refactoring your inner query like so.

     SELECT a.projectid 
       FROM (
              SELECT DISTINCT projectid
                FROM projectsearch
               WHERE searchtext LIKE '%am%'
            ) a
       JOIN (
              SELECT DISTINCT projectid
                FROM projectsearch
               WHERE searchtext LIKE '%49%'
            ) b ON a.projectid = b.projectid

It should give you back the same set of projectid values as your inner query. It gives each projectid value that has matching searchtext for both search terms, even if those terms show up in different rows of project_search. That's what your query does by searching GROUP_CONCAT() output.

Try creating an index on (searchtext, projectid). The use of column LIKE '%sample' means you won't be able to random-access that index, but the two queries in the join may still be able to scan the index, which is faster than scanning the table. To add that index use this command.

 ALTER TABLE project_search ADD INDEX project_search_text (searchtext, projectid); 

Try to do this in a MySQL client program (phpmyadmin for example) rather than directly from your php program.

Then, using the MySQL client, test the inner query. See how long it takes. Use EXPLAIN SELECT .... to get an explanation of how MySQL is handling the query.

It's possible your short keywords are returning a ridiculously high number of matches, and somehow overwhelming your system. In that case you can put a LIMIT 1000 clause or some such thing at the end of your inner query. That's not likely, though. 17 kilorows is not a large number.

If that doesn't help your production MySQL server is likely misconfigured or corrupt. If I were you I would call your hosting service tech support, somehow get past the front-line support agent (who won't know anything except "reboot your computer" and other such foolishness), and tell them the exact times you got the "gone away" message. They'll be able to check the logs.

Pro tip: I'm sure you know the pitfalls of using LIKE '%text%' as a search term. It's not scalable because it's not sargable: it can't random access an index. If you can possibly redesign your system, it's worth your time and effort.

Answer 3

You could TRY / CATCH to check if you get a more concrete error:

BEGIN TRY
    BEGIN TRANSACTION
        --Insert Your Queries Here--
    COMMIT
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();


    IF @@TRANCOUNT > 0
    ROLLBACK 

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );

END CATCH

Although because you are talking about short words and fulltext it seems to me it must be related to StopWords.

Try running this query from both your dev server and production server and check if there are any differences:

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

Also check in my.ini (if that is the config file) text file if these are set to:

ft_stopword_file = ""

ft_min_word_len = 1