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

I see that you use `implode()` but after imploding do you add a`;` at the end of `$query` to complete the SQL string? — Jay Blanchard, Jan 21 '15 at 13:55
I've tried with and without, no difference either way. I noticed that the sample code in a number of places didn't bother adding one at the end, so I believe it to be optional. — Ty W, Jan 21 '15 at 13:56
Have you considered using a prepared statement and simply iterating through the updates? My guess is that you would get better performance this way, as the query does not need to be re-planned each time. — Mike Brant, Jan 21 '15 at 14:55
remember this is sphinx (despite this question being tagged with mysql) - sphinx doesnt support protocol prepared statements. (only client emulated ones) — barryhunter, Jan 21 '15 at 16:48
Found this page: http://sphinxsearch.com/docs/current/sphinxql-multi-queries.html. Seems to suggest that while it's supported for some types of queries, UPDATE is not one of them. — Ty W, Jan 21 '15 at 18:13
You should add that as an answer, you can answer your own question, just not accept it (I think) — barryhunter, Jan 21 '15 at 18:18
looking into the API function you mentioned in the thread on the sphinx forums, and I'll update with the best approach I can come up with as an answer here. — Ty W, Jan 21 '15 at 22:06

barryhunter · Answer 1 · 2015-01-21T19:36:53.293

2

the error message you are getting, is from the searchd server, because it doesn't expect multiple command conncateted with ;

The query works in the CLI, because the mysql client itself splits it upto into separate queries and sends them one-by-one to the server (see the delimiter option). https://mariadb.com/kb/en/mariadb/documentation/clients-and-utilities/mysql-client/delimiters-in-the-mysql-client/

mysqli, has a similar query function http://php.net/manual/en/mysqli.multi-query.php which is hardcoded to use ; apparently. Turns out that sphinx doesnt support it for UPDATE queries, only for queries - see comment on the main question.

edited Jan 21 '15 at 19:36

answered Jan 21 '15 at 14:33

barryhunter

20,886
3
30
43

I'm not sure I understand the point you're trying to make regarding mysqli's multi-query function. are you saying it's capable of splitting the queries itself and sending them one at a time or just reiterating that it can't give sphinx what it expects? – Ty W Jan 21 '15 at 16:34
assuming sphinx can't handle the semicolon-separated queries, which does appear to be the case, is there a way of putting mysqli into a mode where it can quickly shoot over a bunch of queries for processing without waiting around for each round trip? If we execute these queries one by one it takes a very long time. – Ty W Jan 21 '15 at 16:36
I was mainly explainly WHY the CLI works. But also saying mysqli's multi_query SHOULD work. I can't explain why it doesnt work for you. But then again I've only ever used multi_query for doing multiple select statements, never multiple updates – barryhunter Jan 21 '15 at 16:54

score 1 · Accepted Answer · answered Jan 22 '15 at 11:59

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).

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

2 Answers2