selinux vs searchd

If, like me, you use searchd and mysql then selinux will provoke the following message:

ERROR 1429 (HY000): Unable to connect to foreign data source: failed to connect to searchd (host=127.0.0.1, errno=13, port=931

The solution is simple:

semanage port -a -t mysqld_port_t -p tcp 9312

Sphinx and Mysql 5.7

So I need to move my big application from CentOS 6 to CentOS 8. First problem - systemd. Unfortunately there's no way to remove systemd, and I'm not sure I want to move to Slackware. Second problem - mysql 5.6 won't compile with a recent GNU C++ compiler ! OK, I'll move to 5.7. Wait, SphinxSE won't compile with 5.7. It looks like Sphinx has been abandoned, but it's successor Manticore hasn't moved SphinxSE to 5.7 either. It turns out that 5.7 should have been called 6.0. Maybe this is why the next version is called 8.0?

Fortunately some Googling allowed me to find someone who did the hard work. I cleaned up compiler warnings and updated INSTALL. Source is here.

SELinux vs SphinxSE

It should be noted that SphinxSE wants to talk to searchd on port 9312. SELinux will prevent this. To enable it:

semanage port -a -t mysqld_port_t -p tcp 9312

Still more fun with MySQL

Are you prepared to go mad? If so, compare these to statments and their results:

mysql> SELECT warehouse.NUM,warehouse.date FROM warehouse JOIN sphinx ON sphinx.id = warehouse.DID WHERE sphinx.query = 'filter=tid,288215463; index=Y2015,YD2015; limit=500; maxmatches=2000; mode=all; offset=0; query=dominique; sort=extended:date desc, sNUM desc' ORDER BY warehouse.date DESC,warehouse.NUM DESC LIMIT 50;
Empty set (0.00 sec)

mysql> SELECT warehouse.NUM,warehouse.date FROM warehouse JOIN sphinx ON sphinx.id = warehouse.DID WHERE sphinx.query = 'filter=tid,288215463; index=Y2015,YD2015; limit=500; maxmatches=2000; mode=all; offset=0; query=dominique; sort=extended:date desc, sNUM desc';
+---------+------------+
| NUM     | date       |
+---------+------------+
| AT00105 | 2015-06-17 |
+---------+------------+
1 row in set (0.00 sec)

What's going on is that MySQL is asking searchd (part of Sphinx) to do a full text search on 2 indexes. It then does a join on the results. With the ordering, I get zero results. Without ordering I get the expected results.

This shouldn't be happening. This can't be happening.

But then I found the answer: The first query in the example above was a cut and paste from the query log on my dev VM. This means that MySQL had already run that query and (more importantly) cached the results. The Sphinx indexes had been updated in the meantime. But searchd can't tell MySQL to invalidate the query cache.

mysql> SELECT SQL_NO_CACHE warehouse.NUM,warehouse.date FROM warehouse JOIN sphinx ON sphinx.id = warehouse.DID WHERE sphinx.query = 'filter=tid,288215463; index=Y2015,YD2015; limit=500; maxmatches=2000; mode=all; offset=0; query=dominique; sort=extended:date desc, sNUM desc' ORDER BY warehouse.date DESC,warehouse.NUM DESC LIMIT 50;
+---------+------------+
| NUM     | date       |
+---------+------------+
| AT00105 | 2015-06-17 |
+---------+------------+
1 row in set (0.00 sec)

Sanitiy is restored.

Long and short of this is to ALWAYS use SQL_NO_CACHE when using the Sphinx plugin.

No fun

Trying to get Percona Server 5.6 to work with SphinxSE 2.1.2 was no fun. First off, it used to be that SphinxSE would create a load of .so .la, .a files. Now it just needs ha_sphinx.so. Second off, you have to execute INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so'; This requires grant tables to be on (I generally have them off because it's less trouble and I have networking turned off in MySQL anyway, unless I have replication going). At least this is saved into mysql.plugin so I don't have to do it each time I restart mysqld.

And then, when I get it all set up, I get the following error

ERROR 1429 (HY000): Unable to connect to foreign data source: failed to resolve searchd host (name=localhost)

I'm WTFing just about as hard as I can right now. localhost is in /etc/hosts. It is also in my DNS server. Has MySQL gone insane? Percona? I'll blame Oracle, because they are just mean.

The solution, of course, is ALTER TABLE sphinx CONNECTION='sphinx://127.0.0.1:9312/YCUR';.

Sphinx and daemontools

I'm moving the Big Document Warehouse Soon To Be Known As Quaero to using Sphinx for full-text searching. And of course I want to run searchd from daemontools. But searchd insists on backgrounding itself. Searching around I found people using fghack (which doesn't work) and --console (which isn't what is wanted).

However, peering into the source code to see if I could prevent it from backgrounding, I found just what I wanted: the undocumented --nodetach switch. Well, it has some documentation; there is a changelog entry that calls it a debug switch, which it isn't. Shame on you Sphinx-team!

Anyway, here is my daemontools run script:

#!/bin/bash



config=/opt/sphinx/etc/sphinx.cfg

servicename=sphinx



echo $(date "+%Y/%m/%d %H:%M:%S") $$ Sphinx searchd

exec setuidgid dw \

    /opt/sphinx/bin/searchd --config $config --nodetach 2>&1 \

    | /usr/bin/logger -p info -t $servicename

BTW, I have RPMs of Percona-Server (aka MySQL with a bunch of good patches) with SphinxSE.