Piler reindexing not working

[Damon McManus]

Hi Janos,

Love your application but having some issues with reindexing mail. We had an issue with the indexes when we had around 12615212 messages stored in the database. I turned off searchd and reindex all the email. It took a few months but I’ve turned it back on and our mail count is at 13410544 but indexes are only showing for the few days that I’ve had it back on.

So:

• When emails arrive via SMTP they are indexed correctly and can be searched; but
• When I run the reindex command the indexes appear in the sph_index table, are processed by the indexer every 15 mins but they never become searchable in the piler application

So I thought that perhaps when manually reindexing the application is using the wrong sphinx database. So I tried specifying my configuration file when I run reindex but I get a mysql error:

/usr/local/bin/reindex -f 1 -t 1000 -c /usr/local/etc/piler/sphinx.conf

cant connect to mysql server
error: cannot connect to mysql server

But given that reindexing just seems to write to the metadata to the database table, could it even be that? If it isn’t that, then why is piler able to correctly put the data in the sph_index table and have it appear but reindex cannot?

Any help would be appreciated.

Cheers,

Damon

[Damon McManus]

Hi Janos,

Also it looks like the pilerexport command is working. So that might be another clue.

Cheers,

Damon

[Janos SUTO]

Hello Damon, the reindex utility actually does not work with sphinx. It only reads the archived message, and writes its contents to sph_index table. -c is for the piler config, so it’s no wonder it can’t connect to mysql when you give it a sphinx config file.

Then do the following:

cd /tmp

reindex -f 1 -t 10

su - piler

/usr/local/libexec/piler/indexer.delta.sh

‌

Then mysql -h 127.0.0.1 -P9306

mysql> select * from dailydelta1 where id < 10;

[Damon McManus]

Hi Janos,

Here are my results:

[root@xxxxx tmp]# /usr/local/bin/reindex -f 1 -t 10
put 10 messages to sph_index table for reindexing
[root@xxxxx tmp]# su - piler
Last login: Tue Feb 25 05:25:53 UTC 2020 on pts/3
-sh-4.2$ /usr/local/libexec/piler/indexer.delta.sh
WARNING: failed to open pid_file '/var/run/piler/searchd.pid'.
WARNING: indices NOT rotated.
WARNING: failed to open pid_file '/var/run/piler/searchd.pid'.
WARNING: indices NOT rotated.
-sh-4.2$ mysql -h 127.0.0.1 -P9306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2.2.11-id64-release (95ae9a6)

Copyright (c) 2000, 2017, 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> select * from dailydelta1 where id < 10;
Empty set (0.00 sec)

‌

Which looks like it’s still not seeing those new indexes. It that because of the warnings on indexer.delta.sh?

Cheers,

Damon

‌

[Janos SUTO]

My best bet is searchd is not started from /etc/init.d/rc.searchd, so it’s running with some other config, not piler’s.

[Damon McManus]

Ok! I got it! Thanks for the help. Here were my steps in case anyone else has similar problems.

Searchd was running from crontab job that is setup for the root user:

[root@xxxxx init.d]# ps aux | grep searchd
root 18590 0.0 0.0 110456 2196 pts/0 S+ 23:41 0:00 grep --color=auto searchd
sphinx 21391 0.0 0.0 93180 3772 ? S Feb25 0:00 /usr/bin/searchd --config /usr/local/etc/piler/sphinx.conf
sphinx 21392 0.1 4.4 403224 180828 ? Sl Feb25 2:34 /usr/bin/searchd --config /usr/local/etc/piler/sphinx.conf
[root@xxxxxx init.d]# crontab -l

* * * * * if [ `ps -U sphinx | grep "searchd" | wc -l` -eq 0 ] ; then searchd --config /usr/local/etc/piler/sphinx.conf --logdebug ; fi

When I do restart it through I use “service searchd restart”:

[root@xxxxxx init.d]# service searchd restart
Stopping searchd: [ OK ]
Starting searchd: Sphinx 2.2.11-id64-release (95ae9a6)
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/usr/local/etc/piler/sphinx.conf'...
listening on 127.0.0.1:9312
listening on 127.0.0.1:9306
precaching index 'main1'
rotating index 'main1': success
precaching index 'main2'
precaching index 'main3'
precaching index 'main4'
precaching index 'dailydelta1'
rotating index 'dailydelta1': success
precaching index 'delta1'
rotating index 'delta1': success
precaching index 'tag1'
rotating index 'tag1': success
precaching index 'note1'
rotating index 'note1': success
precaching index 'att1'
rotating index 'att1': success
precached 9 indexes in 5.027 sec
[ OK ]

I stopped the service, started it with “/etc/init.d/searchd start” and got the same results as the previous post.

I only have one copy of searchd in /usr:
[root@xxxxxx tmp]# find /usr -name "searchd"
/usr/bin/searchd

So then I took a look at files in /var/piler/sphinx. Half the files were owned by piler and half by sphinx. I believe the ones that were generated by the indexer.delta.sh were owned by piler and the ones that were generated by searchd were owned by sphinx:

[root@xxxxx sphinx]# cd /var/piler/sphinx
[root@xxxxx sphinx]# ls -alh
total 972M
drwxrwxr-x 2 piler sphinx 14K Feb 27 00:15 .
drwxr-xr-x 13 root root 6.0K Dec 16 06:17 ..
-rw-r--r-- 1 piler sphinx 72K Feb 24 06:09 13391804.eml
-rw-r--r-- 1 piler piler 139M Feb 27 00:10 att1.new.spa
-rw-r--r-- 1 piler piler 308M Feb 27 00:10 att1.new.spd
-rw-r--r-- 1 piler piler 2.4M Feb 27 00:10 att1.new.spe
-rw-r--r-- 1 piler piler 395 Feb 27 00:10 att1.new.sph
-rw-r--r-- 1 piler piler 19M Feb 27 00:10 att1.new.spi
-rw-r--r-- 1 piler piler 0 Feb 27 00:10 att1.new.spk
-rw-r--r-- 1 piler piler 0 Feb 27 00:10 att1.new.spm
-rw-r--r-- 1 piler piler 9.9M Feb 27 00:10 att1.new.spp
-rw-r--r-- 1 piler piler 1 Feb 27 00:10 att1.new.sps
-rw-r--r-- 1 piler piler 139M Feb 26 23:41 att1.spa
-rw-r--r-- 1 piler piler 308M Feb 26 23:41 att1.spd
-rw-r--r-- 1 piler piler 2.4M Feb 26 23:41 att1.spe
-rw-r--r-- 1 piler piler 395 Feb 26 23:41 att1.sph
-rw-r--r-- 1 piler piler 19M Feb 26 23:41 att1.spi
-rw-r--r-- 1 piler piler 0 Feb 26 23:41 att1.spk
-rw------- 1 sphinx sphinx 0 Feb 26 23:49 att1.spl
-rw-r--r-- 1 piler piler 0 Feb 26 23:41 att1.spm
-rw-r--r-- 1 piler piler 9.8M Feb 26 23:41 att1.spp
-rw-r--r-- 1 piler piler 1 Feb 26 23:41 att1.sps
-rw-r--r-- 1 piler piler 0 Feb 27 00:15 att1.tmp.spl
-rw-r--r-- 1 piler piler 0 Feb 27 00:15 att1.tmp.sps
-rw-r--r-- 1 piler piler 0 Feb 27 00:15 att1.tmp.tmp0
-rw-r--r-- 1 piler piler 0 Feb 27 00:15 att1.tmp.tmp1
-rw-r--r-- 1 piler piler 0 Feb 27 00:15 att1.tmp.tmp2
-rw-r--r-- 1 piler piler 0 Feb 27 00:15 att1.tmp.tmp7
-rw-r--r-- 1 piler piler 0 Feb 27 00:15 att1.tmp.tmps
-rw-r--r-- 1 piler piler 23K Feb 27 00:05 dailydelta1.new.spa
-rw-r--r-- 1 piler piler 1.3M Feb 27 00:05 dailydelta1.new.spd
-rw-r--r-- 1 piler piler 5.6K Feb 27 00:05 dailydelta1.new.spe
-rw-r--r-- 1 piler piler 622 Feb 27 00:05 dailydelta1.new.sph
-rw-r--r-- 1 piler piler 293K Feb 27 00:05 dailydelta1.new.spi
-rw-r--r-- 1 piler piler 0 Feb 27 00:05 dailydelta1.new.spk
-rw-r--r-- 1 piler piler 0 Feb 27 00:05 dailydelta1.new.spm
-rw-r--r-- 1 piler piler 2.3M Feb 27 00:05 dailydelta1.new.spp
-rw-r--r-- 1 piler piler 1 Feb 27 00:05 dailydelta1.new.sps
-rw-r--r-- 1 piler piler 20K Feb 26 23:45 dailydelta1.spa
-rw-r--r-- 1 piler piler 1.1M Feb 26 23:45 dailydelta1.spd
-rw-r--r-- 1 piler piler 4.0K Feb 26 23:45 dailydelta1.spe
-rw-r--r-- 1 piler piler 622 Feb 26 23:45 dailydelta1.sph
-rw-r--r-- 1 piler piler 266K Feb 26 23:45 dailydelta1.spi
-rw-r--r-- 1 piler piler 0 Feb 26 23:45 dailydelta1.spk
-rw------- 1 sphinx sphinx 0 Feb 26 23:49 dailydelta1.spl

Lets look at the groups access for those users:

[root@xxxxx sphinx]# groups sphinx
sphinx : sphinx piler
[root@xxxxx sphinx]# groups piler
piler : piler

So any new files made in those directories that was owned by piler could not be accessed by sphinx as they were in the sphinx group. So the new indexes could not be read by searchd. This happened because before I regenerated by entire sphinx indexes I copied the old directory aside and lost the permissions. I have added a sticky bit to the group of files in that directory and changed the group and then your commands worked.

chmod g+s .
chgrp sphinx *

mysql> select * from dailydelta1 where id < 10;
+------+------------+------+-------------+
| id | sent | size | attachments |
+------+------------+------+-------------+
| 1 | 1503442680 | 0 | 0 |
| 2 | 1503443147 | 0 | 3 |
| 3 | 1503443151 | 0 | 1 |
| 4 | 1503443151 | 0 | 1 |
| 5 | 1503442574 | 0 | 1 |
| 6 | 1503443182 | 0 | 0 |
| 7 | 1503443197 | 0 | 1 |
| 8 | 1503442464 | 0 | 1 |
| 9 | 1503442715 | 0 | 1 |
+------+------------+------+-------------+
9 rows in set (0.00 sec)

Not sure if that was how its meant to be. Let me know if there is a better way.

Cheers,

Damon

[Janos SUTO]

I haven’t seen such a deployment. You basically set up for certain failure. The better way is to follow the official documentation. And if you get any trouble, then ask. The remedy for this terrible (sorry to say it) hack is the following:

‌

disable the crontab hack for root

stop all searchd process

chown -R piler:piler /var/piler/sphinx

/etc/init.d/rc.searchd start

reindex whatever you miss

‌

When done correctly then piler used should run searchd, and no other user should ever touch the sphinx database files.

[Damon McManus]

Hi Janos,

That did it. Now that searchd is running as the piler user the indexer.delta.sh script no longer gives warnings when it runs and the indexes now appear both the database and the web interface! No need for the chmod g+s anymore.

mysql> select count(*) from dailydelta1;
+----------+
| count() |
+----------+
| 20485 |
+----------+
1 row in set (0.00 sec)

Thanks very much for your help! Really appreciated.

Cheers,

Damon

[Janos SUTO]

You are welcome.