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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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'; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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> |