sphinx, sphinx_snippets() & MySQL 5.5

MySQL

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:

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

8 thoughts on “sphinx, sphinx_snippets() & MySQL 5.5”

  1. Pingback: Thoughts on using MySQL plugins | code.openark.org

  2. I got it:
    [root@fedoraconf lib]# service mysql55 start
    Starting MySQL (Percona Server)….. ERROR! The server quit without updating PID file (/var/lib/mysql/fedoraconf.pid).
    [root@fedoraconf lib]# /etc/init.d/mysql55 start
    Starting MySQL (Percona Server)….. ERROR! The server quit without updating PID file (/var/lib/mysql/fedoraconf.pid).
    [root@fedoraconf lib]#tail -20 /var/log/mysqld.log
    120423 3:12:41 InnoDB: The InnoDB memory heap is disabled
    120423 3:12:41 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    120423 3:12:41 InnoDB: Compressed tables use zlib 1.2.3
    120423 3:12:41 InnoDB: Initializing buffer pool, size = 512.0M
    120423 3:12:41 InnoDB: Completed initialization of buffer pool
    120423 3:12:41 InnoDB: highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 50037
    120423 3:12:41 InnoDB: Database was not shut down normally!
    InnoDB: Starting crash recovery.
    InnoDB: Reading tablespace information from the .ibd files…
    InnoDB: Restoring possible half-written data pages from the doublewrite
    InnoDB: buffer…
    InnoDB: Doing recovery: scanned up to log sequence number 1597945
    120423 3:12:41 InnoDB: Starting an apply batch of log records to the database…
    InnoDB: Progress in percents: 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
    InnoDB: Apply batch completed
    120423 3:12:42 InnoDB: Waiting for the background threads to start
    120423 3:12:43 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 1597945
    120423 3:12:43 [ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist
    120423 03:12:43 mysqld_safe mysqld from pid file /var/lib/mysql/fedoraconf.pid ended

    How I can fix it?

  3. Fix it with install db.
    But got this
    mysql> CREATE FUNCTION sphinx_snippets RETURNS STRING SONAME ‘ha_sphinx.so’;
    ERROR 1127 (HY000): Can’t find symbol ‘sphinx_snippets’ in library

  4. @Sorok7,
    As per first comment: you are right: need to install db. The method above works for existing database as well.
    May also need upgrade db.

    As per second problem, that’s interesting, and indicates you may have failed to follow the guidelines: sphinx_snippets is not in the library, which is exactly what this post solves. Did you override the two files with the files attached in this post?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.