Recently I had some problems with a Sphinx search server which was upgraded through<\/p>\n
apt-get update && apt-get upgrade<\/pre>\nThis caused the Sphinx Storage engine inside my MySQL server fail to work.<\/p>\n
When doing a search inside Mysql, eg by:<\/p>\n
select * from dbname.content_index;<\/pre>\nI got the follwoing error:<\/p>\n
Error Code: 1286. Unknown table engine 'SPHINX'<\/pre>\nAlso, when i was looking inside MySQL, to show the Engines, did not see any Sphinx engine active:<\/p>\n
root@mysql:~# mysql -uroot -p\r\nWelcome to the MySQL monitor. Commands end with ; or \\g.\r\nYour MySQL connection id is 76\r\nServer version: 5.1.63-0+squeeze1 (Debian)\r\n\r\nCopyright (c) 2000, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql> show engines;\r\n+------------+---------+----------------------------------------------------------------+--------------+------+------------+\r\n| Engine | Support | Comment | Transactions | XA | Savepoints |\r\n+------------+---------+----------------------------------------------------------------+--------------+------+------------+\r\n| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |\r\n| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |\r\n| BLACKHOLE | YES | \/dev\/null storage engine (anything you write to it disappears) | NO | NO | NO |\r\n| CSV | YES | CSV storage engine | NO | NO | NO |\r\n| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |\r\n| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |\r\n| ARCHIVE | YES | Archive storage engine | NO | NO | NO |\r\n| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |\r\n+------------+---------+----------------------------------------------------------------+--------------+------+------------+\r\n8 rows in set (0.00 sec)\r\n\r\nmysql><\/pre>\nAfter 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.<\/p>\n
I know my Shpinx was installed by enabling the ha_sphinx.so from within MySQL, but then i got this error:<\/p>\n
mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';\r\nERROR 1126 (HY000): Can't open shared library '\/var\/lib\/mysql\/plugins\/\/ha_sphinx.so' (errno: 2 undefined symbol: _ZN7handler5cloneEP11st_mem_root)\r\nmysql><\/pre>\nTime to redo Sphinx – MySQL integration it seems…. This is because your sphinx plugin is build on the installed release version of MySQL :'(<\/p>\n
These are the steps you need to perform to do this:<\/p>\n
1. Look up the current installed version of MySQL<\/p>\n
# dpkg --list | grep mysql-server\r\nii mysql-server-5.1 5.1.63-0+squeeze1 MySQL database server binaries and system database setup\r\nii mysql-server-core-5.1 5.1.63-0+squeeze1 MySQL database server binaries<\/pre>\nIt seems that i have MySQL 5.1.63 installed on this system.<\/p>\n
2. Download the MySQL source for THIS<\/strong> <\/em>version and also the latest Sphinx version from the site<\/p>\n
I got mine as follows:<\/p>\n
wget http:\/\/sphinxsearch.com\/files\/sphinx-2.0.6-release.tar.gz\r\nwget http:\/\/downloads.skysql.com\/archives\/mysql-5.1\/mysql-5.1.63.tar.gz<\/pre>\nExtract the archives<\/p>\n
tar -xvf mysql-5.1.63.tar.gz\r\ntar -xvf sphinx-2.0.6-release.tar.gz<\/pre>\nCopy the\u00a0mysqlse\u00a0directory from sphinx to mysql engine directory from the sources:<\/p>\n
cp -R sphinx-2.0.6\/mysqlse\/ mysql-5.1.63\/storage\/sphinx<\/pre>\nBuild your sphinx files by:<\/p>\n
cd mysql-5.1.63 \r\nsh BUILD\/autorun.sh\r\n.\/configure; make<\/pre>\nYour newly created sphinx.so files can now be copied to the mysql live instyalltion directory:<\/p>\n
* You night need some pre-requisits to perform this:<\/p>\n
apt-get install build-essential\r\napt-get install cmake\r\napt-get instal libncurses5-dev<\/pre>\n<\/p>\n
But 1st check your plugin dir details in<\/p>\n
\/etc\/sphinx\/default<\/p>\n
or \/etc\/mysql\/my.cnf<\/p>\n
cp storage\/sphinx\/.libs\/ha_sphinx.* \/usr\/lib64\/mysql\/plugin<\/pre>\nTime to load your sphinx plugin :<\/p>\n
mysql -u root -p\r\n\r\nmysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';<\/pre>\n<\/p>\n
root@mysql:~\/# mysql -uroot -p\r\nWelcome to the MySQL monitor. Commands end with ; or \\g.\r\nYour MySQL connection id is 3\r\nServer version: 5.1.63-0+squeeze1 (Debian)\r\n\r\nCopyright (c) 2000, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql> show engines;\r\n+------------+---------+----------------------------------------------------------------+--------------+------+------------+\r\n| Engine | Support | Comment | Transactions | XA | Savepoints |\r\n+------------+---------+----------------------------------------------------------------+--------------+------+------------+\r\n| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |\r\n| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |\r\n| BLACKHOLE | YES | \/dev\/null storage engine (anything you write to it disappears) | NO | NO | NO |\r\n| CSV | YES | CSV storage engine | NO | NO | NO |\r\n| SPHINX | YES | Sphinx storage engine 2.0.6-release | NO | NO | NO |\r\n| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |\r\n| ARCHIVE | YES | Archive storage engine | NO | NO | NO |\r\n| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |\r\n| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |\r\n+------------+---------+----------------------------------------------------------------+--------------+------+------------+\r\n9 rows in set (0.00 sec)\r\n\r\nmysql><\/pre>\n<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[3],"tags":[11,12,13,80],"class_list":["post-42","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-debian-2","tag-mysql-2","tag-sphinx","tag-ubuntu"],"_links":{"self":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts\/42","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=42"}],"version-history":[{"count":14,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts\/42\/revisions"}],"predecessor-version":[{"id":290,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=\/wp\/v2\/posts\/42\/revisions\/290"}],"wp:attachment":[{"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=42"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=42"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nicovs.be\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=42"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}