Issues with Sphinx Search Engine Wordforms

Q: How come sometimes Sphinx search engine's index does not obey the rules I put in wordforms configurations?

To give you more context Sphinx is an open-source SQL full-text search engine. Wordforms is a functionality it provides where you can specify synonyms so that your index covers them. For example suppose your index contains documents that contain the word 't-shirt' and you also want them to be matched when the search keyword is 'tshirt'. Without setting up such synonym mapping in Sphinx, Sphinx wouldn't know 't-shirt' and 'tshirt' mean the same thing. Once you set the configuration accordingly Sphinx will get you the desired search results.

Sometimes I run into the problem where I define the mapping, rebuild Sphinx index, rerun Sphinx search daemon, but still fail to have Sphinx recognize the new mapping I put in. If this happens to you simply insert a new line at the end of the configuration file, or reorder the mappings. For example if your original mapping is the following:

mockneck > mock neck
ae > american eagle
peacoat > pea coat
slitneck > slit neck

Reorder them to the following:

mockneck > mock neck
slitneck > slit neck
ae > american eagle
peacoat > pea coat

Rebuilt Sphinx index, rerun search daemon, and try querying your index again. Hope this helps! By the way this fix applies to other configuration for Sphinx as well such as Stopwords. If you run into similar problems with other kinds of configuration try the same fix! Questions? Let me know!

Install MySQL Include Files On Unix

Q: While installing Sphinx search engine I get an error saying I have to install MySQL include files. How do I do it?

My Unix version is Ubuntu 9.10. Here's what happens. When I run ./configure tool to install Sphinx I got the following error:

configure: error: invalid MySQL root directory '/var/lib/mysql'; neither bin/mysql_config, nor include/ and lib/ were found there
To give you some context into why this is needed, if you install any application that needs to communicate with your MySQL server you'll have to let that application know the MySQL protocol via MySQL libraries in the form of MySQL include files. Note this problem is NOT limited to Sphinx search engine only; whatever Unix application software that needs to talk to MySQL server also applies!

To solve this error I needed to use 'apt-get' to install libmysql++-dev. However when I run 'sudo apt-get install libmysql++-dev' it says it cannot find the library and asks me to run 'apt-get update', but when I do that I get a bunch of 404 errors:

Ign http://security.ubuntu.com intrepid-security Release.gpg
Ign http://security.ubuntu.com intrepid-security/main Translation-en_US
Ign http://security.ubuntu.com intrepid-security/restricted Translation-en_US
Ign http://archive.ubuntu.com intrepid Release.gpg
...
Ign http://archive.ubuntu.com intrepid-updates/restricted Packages
Ign http://archive.ubuntu.com intrepid-updates/universe Packages
Ign http://archive.ubuntu.com intrepid-updates/main Sources
Err http://security.ubuntu.com intrepid-security/main Packages
404 Not Found [IP: 91.189.92.167 80]
Err http://security.ubuntu.com intrepid-security/restricted Packages
404 Not Found [IP: 91.189.92.167 80]
...
W: Failed to fetch http://archive.ubuntu.com/ubuntu/dists/intrepid-updates/restricted/source/Sources.gz 404 Not Found [IP: 91.189.92.169 80]

W: Failed to fetch http://archive.ubuntu.com/ubuntu/dists/intrepid-updates/universe/source/Sources.gz 404 Not Found [IP: 91.189.92.169 80]

E: Some index files failed to download, they have been ignored, or old ones used instead.

==== or the following error ====

E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?
This happens because my repositories are out of date. So I updated my sources.list (Where is sources.list? It is usually located at /etc/apt/sources.list) to (after backing it up of course) to the default of Ubuntu 9.10 which is the following:

# deb cdrom:[Ubuntu-Server 9.10 _Karmic Koala_ - Release i386 (20091027.2)]/ karmic main restricted

#deb cdrom:[Ubuntu-Server 9.10 _Karmic Koala_ - Release i386 (20091027.2)]/ karmic main restricted
# See http://help.ubuntu.com/community/UpgradeNotes for how to upgrade to
# newer versions of the distribution.

deb http://us.archive.ubuntu.com/ubuntu/ karmic main restricted
deb-src http://us.archive.ubuntu.com/ubuntu/ karmic main restricted

## Major bug fix updates produced after the final release of the
## distribution.
deb http://us.archive.ubuntu.com/ubuntu/ karmic-updates main restricted
deb-src http://us.archive.ubuntu.com/ubuntu/ karmic-updates main restricted

## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu
## team. Also, please note that software in universe WILL NOT receive any
## review or updates from the Ubuntu security team.
deb http://us.archive.ubuntu.com/ubuntu/ karmic universe
deb-src http://us.archive.ubuntu.com/ubuntu/ karmic universe
deb http://us.archive.ubuntu.com/ubuntu/ karmic-updates universe
deb-src http://us.archive.ubuntu.com/ubuntu/ karmic-updates universe

## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu
## team, and may not be under a free licence. Please satisfy yourself as to
## your rights to use the software. Also, please note that software in
## multiverse WILL NOT receive any review or updates from the Ubuntu
## security team.
deb http://us.archive.ubuntu.com/ubuntu/ karmic multiverse
deb-src http://us.archive.ubuntu.com/ubuntu/ karmic multiverse
deb http://us.archive.ubuntu.com/ubuntu/ karmic-updates multiverse
deb-src http://us.archive.ubuntu.com/ubuntu/ karmic-updates multiverse

## Uncomment the following two lines to add software from the 'backports'
## repository.
## N.B. software from this repository may not have been tested as
## extensively as that contained in the main release, although it includes
## newer versions of some applications which may provide useful features.
## Also, please note that software in backports WILL NOT receive any review
## or updates from the Ubuntu security team.
# deb http://us.archive.ubuntu.com/ubuntu/ karmic-backports main restricted universe multiverse
# deb-src http://us.archive.ubuntu.com/ubuntu/ karmic-backports main restricted universe multiverse

## Uncomment the following two lines to add software from Canonical's
## 'partner' repository.
## This software is not part of Ubuntu, but is offered by Canonical and the
## respective vendors as a service to Ubuntu users.
# deb http://archive.canonical.com/ubuntu karmic partner
# deb-src http://archive.canonical.com/ubuntu karmic partner

deb http://security.ubuntu.com/ubuntu karmic-security main restricted
deb-src http://security.ubuntu.com/ubuntu karmic-security main restricted
deb http://security.ubuntu.com/ubuntu karmic-security universe
deb-src http://security.ubuntu.com/ubuntu karmic-security universe
deb http://security.ubuntu.com/ubuntu karmic-security multiverse
deb-src http://security.ubuntu.com/ubuntu karmic-security multiverse
Now I run 'apt-get update' and 'apt-get install libmysql++-dev' successfully! This time when I run configure for Sphinx it successfully recognizes the MySQL includes and is installed correctly!

How Do You Escape Special Characters In Sphinx Configuration?

Q: My database password contains bang character (!) and when I specify the password in sphinx config I keep getting errors. Why?

SOLUTION
It goes without saying that it must have something to do with escaping special characters! How nasty is that? And the Sphinx user manual does not really mention how to deal with special characters. Anyway the solution is that you simply use backslash (\) to escape special characters. Suppose my host is localhost and user name is root and password is crack!t, then here's a typical 'source' block in sphinx configuration:

source someSource
{
type = mysql
sql_host = localhost
sql_user = root
sql_pass = crack\!t
sql_db = mffl
sql_port = 3306

sql_query = \
select store_id+1000,store_id as id,'store' as type,title,title as data,body from store union \
select brand_id+2000,brand_id as id,'brand' as type,title,title as data,body from brand union \
select style_id+3000,style_id as id,'keyword' as type,title,title as data,body from keyword

sql_attr_uint = id
sql_attr_uint = sort
sql_attr_string = type
sql_field_string = title
sql_field_string = data
}

Note how I escape the exclamation mark in sql_pass with a backslash (\) haha!