multi_query not working in mysqli to update realtime sphinx index via sphinxql

Question

We're using Sphinx realtime indexes and talking to them via PHP's mysqli driver. Single queries work fine, but we're trying to implement a batch update process using multi_query.

Queries look like this:

UPDATE rt SET rank = 1 WHERE id = 881523;
UPDATE rt SET rank = 2 WHERE id = 881727;
UPDATE rt ...

(this is after imploding with the semicolon, linebreaks added for ease of reading)

And the error we get is:

sphinxql: syntax error, unexpected ';', expecting $end near ';UPDATE rt SET rank = 2 WHERE id = 881727'

If I copy/paste the query into the mysql CLI client, no problem:

mysql> UPDATE rt SET rank = 1 WHERE id = 881523;UPDATE rt SET rank = 2 WHERE id = 882884;UPDATE rt SET rank = 3 WHERE id = 881727;
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Here's the PHP code. $conn is an instance of the Connection class from http://foolcode.github.io/SphinxQL-Query-Builder/, but we're working with the underlying mysqli connection directly by using $conn->getConnection():

$query = implode(';', $queries);
if($conn->getConnection()->multi_query($query)) {
        do { }
        while($conn->getConnection()->next_result());
}
if($conn->getConnection()->error) {
        error_log("query: " . $query);
        error_log($conn->getConnection()->error);
        die();
}

I've tried using the multiQuery() function from the SphinxQL Query Builder connection class with the same results.

The error message claiming the semicolon is unexpected seems the strangest part to me. If it wants $end, does that mean it's not supporting multiple queries? Why wouldn't it, if it's willing to take the same query via the mysql client?

$php -v
PHP 5.5.20 (cli) (built: Dec 29 2014 18:02:29) 
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.5.0, Copyright (c) 1998-2014 Zend Technologies
    with Zend OPcache v7.0.4-dev, Copyright (c) 1999-2014, by Zend Technologies

Answer 1

So at this time (version 2.2.7), the docs (http://sphinxsearch.com/docs/current/sphinxql-multi-queries.html) indicate that multi query support is only for SELECT, SHOW WARNINGS, SHOW STATUS, and SHOW META queries. This explains why sphinx expects the end of the query and not a ';' for UPDATE queries.

Barry suggested to me on the sphinx forums (http://sphinxsearch.com/forum/view.html?id=13201) to use the sphinx API's UpdateAttributes (http://sphinxsearch.com/docs/current.html#api-func-updateatttributes) function to handle the bulk updates. This worked very well. We're seeing 5k updates/second for two different indexes (same data indexed two different ways) on a single thread of our EC2 hardware (r3.xlarge, indexes on ephemeral SSD).