There is a serious bug with the sphinx storage engine, introduced in 0.9.9-RC2 (and which has not been fixed in latest revisions, as yet – last checked with rev 2006).
I would usually just revert to an older version (0.9.9-RC1 does not contain this bug), but for the reason that RC2 introduces an important feature: the sphinx_snippets() function, which allows for creation of snippets from within MySQL, and which makes the sphinx integration with MySQL complete, as far as the application is concerned.
The bug
The bug is described here and here (and see further discussions). Though it’s claimed to have been fixed, it’s been re-reported, and I’ve tried quite a few revisions and verified it has not been fixed (tested on Debian/Ubuntu x64). Essentially, the bug does not allow you to set filters on a query issued from within the SphinxSE. For example, the following queries fail:
SELECT ... FROM ... WHERE query='python;mode=any;sort=relevance;limit=200;range=myUnixTimestamp,1249506000,1252184400;' SELECT ... FROM ... WHERE query='python;mode=any;sort=relevance;limit=200;filter=my_field,1;'
While the following query succeeds:
SELECT ... FROM ... WHERE query='python;mode=any;sort=relevance;limit=200;'
The error message is this:
ERROR 1430 (HY000): There was a problem processing the query on the foreign data source. Data source error: searchd error: invalid or truncated request
I see this as a serious bug in the SphinxSE: it renders it useless; searching without the ability to filter is not something I can live with.
The motivation
Sphinx does not store the actual text content. To get search results with snippets, you need to:
- Ask sphinx for the documents ids
- Get the content for those documents
- Ask sphinx for snippets based on the provided content and search phrase.
With the introduction of the sphinx_snippets() function, this can all be done with a single query, like this:
SELECT my_docs.my_docs_id, my_docs.publish_time, CONVERT(sphinx_snippets(my_docs.id.content, 'my_docs_index', 'python') USING utf8) AS snippet FROM tets.my_docs INNER JOIN test.my_docs_sphinx USING(my_docs_id) WHERE query='python;mode=any;sort=relevance;limit=200;range=publish_time_unix,1249506000,1252184400;';
This is really a life saver; without this function, you need to get the resutls back to your application, then send the data again to MySQL, in which case you might altogether discard the SphinxSE and talk to sphinx directly. But with a single query you get to ask the results just as if you were asking for any result set from your database (with extra syntax).
The workaround
My setup is Percona’s mysql-5.1.34-xtradb5 source, on Ubuntu server 8.04 amd64. The trick is to first compile MySQL with sphinx 0.9.9-RC2, in order to produce the sphinx.so file (where the sphinx_snippets() function is found), backup the sphinx.so file, then recompile everything with sphinx 0.9.9-RC1. The steps being:
Compile MySQL with sphinx 0.9.9-Rc2 (I choose to install MySQL on /usr/local/mysql51):
tar xzfv mysql-5.1.34-xtradb5.tar.gz cd mysql-5.1.34-xtradb5 cp -R /tmp/resources/sphinx-0.9.9-rc2/mysqlse storage/sphinx sh BUILD/autorun.sh ./configure --with-plugins=innobase,sphinx --prefix=/usr/local/mysql51 make
This produces the sphinx.so, sphinx.so.0, sphinx.so.0.0.0 files. Back them up!
Next, recompile with sphinx 0.9.9-RC1. I’ve found that simple copying and recompiling doesn’t work well. So just cleanup everything and start afresh:
cd .. rm -rf mysql-5.1.34-xtradb5 tar xzfv mysql-5.1.34-xtradb5.tar.gz cd mysql-5.1.34-xtradb5 cp -R /tmp/resources/sphinx-0.9.9-rc1/mysqlse storage/sphinx sh BUILD/autorun.sh ./configure --with-plugins=innobase,sphinx --prefix=/usr/local/mysql51 make sudo make install
Copy the sphinx.so files into the MySQL plugin directory (/usr/local/mysql51/lib/mysql/plugin in our case).
Then build sphinx (you must have MySQL includes for sphinx to compile, so this must be the second step):
cd /tmp/resources/sphinx-0.9.9-rc1/ ./configure --prefix=/usr/local/sphinx --with-mysql=/usr/local/mysql51 make sudo make install
Essentially, we’re working now with 0.9.9-RC1, but the sphinx_snippets() function is from the 0.9.9-RC2 version, and happily no one bothers about this mix.
I hope this helps.