Problem with Sphinx resultset larger than 16 MB in MySQL

Question

Hello All,

I am accessing a large indexed text dataset using sphinxse via MySQL. The size of resultset is on the order of gigabytes. However, I have noticed that MySQL stops the query with following error whenever the dataset is larger than 16MB:

1430 (HY000): There was a problem processing the query on the foreign data source. Data source error: bad searchd response length (length=16777523)

length shows the length of resultset that offended MySQL. I have tried the same query with Sphinx's standalone search program. It works fine. I have tried all possible variables in both MySQL and Sphinx, but nothing is helping.

I am using Sphinx 0.9.9 rc-2 and MySQL 5.1.46.

Thanks

Answer 1

you probably need to increase max_allowed_packet from its default value of 16M:

From mysql's documentation

Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.

If you are using the mysql client program, its default max_allowed_packet variable is 16MB. To set a larger value, start mysql like this:

shell> mysql --max_allowed_packet=32M

That sets the packet size to 32MB.

Answer 2

I finally solved the problem. It turns out that the sphinx plugin for mysql (SphinxSE) hard-codes the 16 MB response limit on the resultset in the source code (bad bad bad source-code). I changed SPHINXSE_MAX_ALLOC to 1*1024*1024*1024 in file ha_sphinx.cc, and everything works fine now.