Note as of 2009-07-21: The patch does not work with the latest version of mysql50-server (mysql-server-5.0.83) due to incompatibilities with the versions of autoconf/automake used with the mysql distfiles and what is available in ports.
Update 2009-07-25: However I do now have a fix for mysql51-server (mysql-server-5.1.36) — the text below has been updated and the new patch uploaded to this site. I am still working on getting mysql50-server patches to work correctly.
I'm the maintainer of the textproc/sphinxsearch port[1] of the Sphinx search engine to FreeBSD. A few weeks ago a user asked me about enabling the Sphinx Search Engine table type in MySQL. This is something I'd muttered about doing when I originally created the sphinxsearch port, and given the recent release of SphinxSearch 0.9.8 it was high time to do something about it.
I've come up with the following two patches: one for the databases/mysql50-server port, and the other for databases/mysql51-server port. I've also now got a test version of a slave port that will provide mysql51-server+sphinxse: more news about that will appear on the freebsd-ports@freebsd.org mailing list as soon as I have something worth testing.
diff -Nur /usr/ports/databases/mysql50-server/Makefile mysql50-server/Makefile --- /usr/ports/databases/mysql50-server/Makefile 2008-07-27 09:56:19.000000000 +0100 +++ mysql50-server/Makefile 2008-08-01 13:22:20.000000000 +0100 @@ -68,6 +68,18 @@ CONFIGURE_ARGS+=--with-collation=${WITH_COLLATION} .endif +# Sphinx Engine +.if defined(WITH_SPHINXSE) +SPHINX_PORT?= textproc/sphinxsearch +SPHINX_WRKSRC= cd ${PORTSDIR}/${SPHINX_PORT} && ${MAKE} -V WRKSRC +SPHINX_PATCH= sphinx.5.0.37.diff + +EXTRACT_DEPENDS+= ${NONEXISTENT}:${PORTSDIR}/${SPHINX_PORT}:extract +RUN_DEPENDS+= searchd:${PORTSDIR}/${SPHINX_PORT} +CONFIGURE_ARGS+=--with-sphinx-storage-engine +USE_AUTOTOOLS+= autoconf:262 automake:19 +.endif + .include <bsd.port.pre.mk> .if ${ARCH} == "i386" @@ -141,8 +153,16 @@ @${ECHO} " WITH_CSV=yes Enable support for CSV Storage Engine." @${ECHO} " WITH_FEDERATED=yes Enable support for Federated Storage Engine." @${ECHO} " WITH_NDB=yes Enable support for NDB Cluster." + @${ECHO} " WITH_SPHINXSE=yes Enable support for Sphinx Storage Engine." @${ECHO} "" +.if defined(WITH_SPHINXSE) +pre-patch: + ${CP} -pr $$( ${SPHINX_WRKSRC} )/mysqlse/ ${WRKSRC}/sql/sphinx + cd ${WRKSRC} && ${PATCH} -p1 < sql/sphinx/${SPHINX_PATCH} + #cd ${WRKSRC} && ${SH} BUILD/autorun.sh +.endif + post-patch: @${REINPLACE_CMD} -e "s|SUBDIRS =|SUBDIRS = include @docs_dirs@ scripts @sql_server_dirs@ @man_dirs@ support-files|g" ${WRKSRC}/Makefile.in @${REINPLACE_CMD} -e "s|all: config.h|all: config.h all-local|g" ${WRKSRC}/Makefile.in
diff -Nur /usr/ports/databases/mysql51-server/Makefile mysql51-server/Makefile --- /usr/ports/databases/mysql51-server/Makefile 2009-07-21 09:27:37.000000000 +0100 +++ mysql51-server/Makefile 2009-07-24 13:36:23.000000000 +0100 @@ -114,6 +114,16 @@ ndb_select_count.1 ndb_show_tables.1 ndb_size.pl.1 ndb_waiter.1 .endif +# Sphinx Engine +.if defined(WITH_SPHINXSE) +SPHINX_PORT?= textproc/sphinxsearch +SPHINX_WRKSRC= cd ${PORTSDIR}/${SPHINX_PORT} && ${MAKE} -V WRKSRC + +EXTRACT_DEPENDS+= ${NONEXISTENT}:${PORTSDIR}/${SPHINX_PORT}:extract +RUN_DEPENDS+= searchd:${PORTSDIR}/${SPHINX_PORT} +USE_AUTOTOOLS+= autoconf:262:env automake:110:env +.endif + INFO= mysql CONFIGURE_ARGS+=--with-embedded-server @@ -137,8 +147,15 @@ @${ECHO} " BUILD_STATIC=yes Build a static version of mysqld." @${ECHO} " (use it if you need even more speed)." @${ECHO} " WITH_NDB=yes Enable support for NDB Cluster." + @${ECHO} " WITH_SPHINXSE=yes Enable support for Sphinx Storage Engine." @${ECHO} "" +.if defined(WITH_SPHINXSE) +pre-patch: + ${CP} -pr $$( ${SPHINX_WRKSRC} )/mysqlse/ ${WRKSRC}/storage/sphinx + cd ${WRKSRC} && ${SH} BUILD/autorun.sh +.endif + post-patch: @${REINPLACE_CMD} -e "s|SUBDIRS =|SUBDIRS = include @docs_dirs@ sql-common @sql_server_dirs@ libmysql storage scripts @sql_server@ @man_dirs@ @libmysqld_dirs@ support-files|g" ${WRKSRC}/Makefile.in @${REINPLACE_CMD} -e "s|bin_SCRIPTS =|bin_SCRIPTS = @server_scripts@|g" ${WRKSRC}/scripts/Makefile.in
I have managed to produce a slave port of mysql51-server which incorporates the SphinxSE engine. This still has a number of rough edges, but it basically works, and I intend to submit this as a new port eventually. I'd be grateful for any feedback from anyone testing this out.
Shar archive of proposed new port: databases/mysql51-server+sphinxse [Download].
These patches pretty much replicate the installation instructions given here but in the usual style of FreeBSD ports.
To install databases/mysql5X-server with the patch applied:
# cd /usr/ports/databases # patch -p0 < mysql5X-server.diff
# echo WITH_SPHINXSE=yes >> /etc/make.conf
# portinstall databases/mysql5X-server
This port should ultimately produce a nearly identical effect to the patches show above, but as it is isolated into its own port, it should be possible to add it to the ports tree without imposing significantly on the maintainer of the mysql51-server port.
It is not necessary to make any modifications to /etc/make.conf when using the port: any WITH_SPHINXSE variable settings won't do anything. The mysql51-server+sphinxse port will conflict with any previous database/mysql51-server installation whether or not it is SphinxSE enabled.
# cd /usr/ports/databases # sh mysql51-server+sphinxse.shar
# portinstall databases/mysql51-server+sphinxse
# portupgrade -o databases/mysql51-server+sphinxse -f mysql-server-5.1.36
Once installed and running, you should see Sphinx listed amongst the available search engines:
mysql> SHOW ENGINES; +------------+---------+----------------------------------------------------------------+--------------+-----+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+----------------------------------------------------------------+--------------+-----+------------+ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | 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 0.9.8 | NO | NO | NO | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +------------+---------+----------------------------------------------------------------+--------------+-----+------------+ 8 rows in set (0.00 sec)
I shall attempt to keep the port/patches updated as new MySQL releases come out, and as support for SphinxSE becomes available for different versions. Please do not hesitate to contact me if you have any problems with FreeBSD ports specific stuff — problems with SphinxSE itself on FreeBSD should be referred to the Sphinx developers in the usual way.