Install MySQL with Sphinx Search Engine support

Recently I had some problems with a Sphinx search server which was upgraded through

apt-get update && apt-get upgrade

This caused the Sphinx Storage engine inside my MySQL server fail to work.

When doing a search inside Mysql, eg by:

select * from dbname.content_index;

I got the follwoing error:

Error Code: 1286. Unknown table engine 'SPHINX'

Also, when i was looking inside MySQL, to show the Engines, did not see any Sphinx engine active:

root@mysql:~#  mysql -uroot -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

mysql>

After searching my google friend, I never really found a straightforward solution to this problem. That’s why I bundled all steps I had to take to fix this problem.

I know my Shpinx was installed by enabling the ha_sphinx.so from within MySQL, but then i got this error:

mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';
ERROR 1126 (HY000): Can't open shared library '/var/lib/mysql/plugins//ha_sphinx.so' (errno: 2 undefined symbol: _ZN7handler5cloneEP11st_mem_root)
mysql>

Time to redo Sphinx – MySQL integration it seems…. This is because your sphinx plugin is build on the installed release version of MySQL :'(

These are the steps you need to perform to do this:

1. Look up the current installed version of MySQL

# dpkg --list | grep mysql-server
ii  mysql-server-5.1                        5.1.63-0+squeeze1            MySQL database server binaries and system database setup
ii  mysql-server-core-5.1                   5.1.63-0+squeeze1            MySQL database server binaries

It seems that i have MySQL 5.1.63 installed on this system.

2. Download the MySQL source for THIS version and also the latest Sphinx version from the site

I got mine as follows:

wget http://sphinxsearch.com/files/sphinx-2.0.6-release.tar.gz
wget http://downloads.skysql.com/archives/mysql-5.1/mysql-5.1.63.tar.gz

Extract the archives

tar -xvf mysql-5.1.63.tar.gz
tar -xvf sphinx-2.0.6-release.tar.gz

Copy the mysqlse directory from sphinx to mysql engine directory from the sources:

cp -R sphinx-2.0.6/mysqlse/ mysql-5.1.63/storage/sphinx

Build your sphinx files by:

cd mysql-5.1.63 
sh BUILD/autorun.sh
./configure; make

Your newly created sphinx.so files can now be copied to the mysql live instyalltion directory:

* You night need some pre-requisits to perform this:

apt-get install build-essential
apt-get install cmake
apt-get instal libncurses5-dev

 

But 1st check your plugin dir details in

/etc/sphinx/default

or /etc/mysql/my.cnf

cp storage/sphinx/.libs/ha_sphinx.* /usr/lib64/mysql/plugin

Time to load your sphinx plugin :

mysql -u root -p

mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

 

root@mysql:~/#  mysql -uroot -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| SPHINX     | YES     | Sphinx storage engine 2.0.6-release                            | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>