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
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?
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
@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?
@Sorok7,
Please run:
nm -gC ha_sphinx.so
can you find sphinx_snippets there?