sphinx, sphinx_snippets() & MySQL 5.5

I’ve written a patch which completes Sphinx’s integration with MySQL 5.5.

Up until a couple months ago, Sphinx would not compile with MySQL 5.5 at all. This is, thankfully, resolved as of Sphinx 2.0.3.

However, to my disdain, I’ve found out that it only partially work: the sphinx_snippets() user defined function is not included within the plugin library. After some quick poking I discovered that it was not added to the build, and when added, would not compile.

I rely on sphinx_snippets() quite a lot, and like it. Eventually I wrote the fix to the snippets_udf.cc which allows it to run in a MySQL 5.5 server.

Here are the changes for the 2.0.4 version of Sphinx:

• snippets_udf.cc
• CMakeLists.txt

Replace your 2.0.4 files with these two and get on compiling your MySQL server.

Compilation guide

For completeness, here’s how to compile Percona Server 5.5 with Sphinx 2.0.4 including the above patches:

Get Percona Server source code and Sphinx Search source code.

I’ll be using Percona Server 5.5.21-25.0. I use /data/tmp/mysql as compilation path, and install MySQL on /usr/local/mysql55.

mkdir -p /data/tmp/mysql
cd /data/tmp/mysql
tar xzfv Percona-Server-5.5.21-rel25.0.tar.gz
tar xzfv sphinx-2.0.4-release.tar.gz
cd Percona-Server-5.5.21-rel25.0/
cp -R /data/tmp/mysql/sphinx-2.0.4-release/mysqlse storage/sphinx

Overwrite with patched files included in this post:

cp /tmp/CMakeLists.txt storage/sphinx/CMakeLists.txt
cp /tmp/snippets_udf.cc storage/sphinx/snippets_udf.cc

Build MySQL:

sh BUILD/autorun.sh
./configure --with-plugin-sphinx --prefix=/usr/local/mysql55
make
sudo make install

Install the mysql55 service:

cd /usr/local/mysql55
sudo cp support-files/mysql.server /etc/init.d/mysql55

In /etc/bash.bashrc, add:

export PATH=/usr/local/mysql55/bin:${PATH}

Start MySQL:

sudo service mysql55 start

Login to MySQL as an administrato (typically root) and install Sphinx:

mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';
mysql> CREATE FUNCTION sphinx_snippets RETURNS STRING SONAME 'ha_sphinx.so';

Notes

See also http://sphinxsearch.com/bugs/view.php?id=1090 and http://sphinxsearch.com/forum/view.html?id=8982