Crash when starting searchd

[Waldemar Hamm]

Hello Janos,

for some reason, when I visited one of my piler instances I saw

Error: SQLSTATE[HY000] [2002] Connection refused on database: sphinx

I tried running searchd manually and saw this:

Any idea what I could try? Do you need more infos?

[Janos SUTO]

Hello Waldemar, yes, I need more info: what has changed on the host? It looks like you have a sphinx 2.2.x compatible sphinx.conf file, but you are running sphinx-3.2.1.

[Waldemar Hamm]

Hello Janos,

thank you very much for replying.

I can’t remember why I did it then but I upgraded from Sphinx 2 to Sphinx 3.1.1 at some point. It worked flawlessly for at least a few days after that tho.

This happened in June 2019 from what I can gather on the file date.

I don’t think I did anything else than that.

EDIT: Other than trying Sphinx 3.2.1 the other day which didn’t help.

[Janos SUTO]

Odd. Try reverting the sphinx version to 2.2.x, and let’s see how it works.

[Waldemar Hamm]

We’re getting closer.

‌

I think that sph stuff is from Sphinx 3.X Can I revert those indices somehow? Maybe with reindex -a?

[Waldemar Hamm]

Running reindex and indexer.delta.sh at least the dailydelta and delta indices work again.

Sadly indexer.main.sh doesn’t work since it fails to merge dailydelta1 due to mismatching sph version. (All the main indices are still on a sph version from a newer sphinx binary…)

Is there some way to completely recreate the index?

[Janos SUTO]

Well, I suggest to stop search, remove all index files under /var/piler/sphinx, then install sphinx-3.2.1, and make sure, sphinx.conf has the proper SPHINX_VERSION settings.

Then run indexer --all --config /usr/local/etc/piler/sphinx.conf to initialize the index data. And run reindex -a. Then the cron jobs take care of the rest.

[Waldemar Hamm]

Where exactly do I find the SPHINX_VERSION setting? I don’t seem to have it in my sphinx.conf

[Waldemar Hamm]

Is it really as easy as adding this line to the sphinx.conf ?

http://www.mailpiler.org/wiki/current:sphinx3

I don’t have any other lines like this in there; syntax doesn’t look familiar at all.

[Waldemar Hamm]

My sphinx.conf looks like this: https://bitbucket.org/jsuto/piler/src/1a0acb0185de0e0e6711a97bac4aec8aa2a81e52/etc/sphinx.conf.in?at=master

I don’t have this change in there: https://bitbucket.org/jsuto/piler/src/afcda4939eaf960cad1994d14b90c27d2cfd4cae/etc/sphinx.conf.in?at=master

[Waldemar Hamm]

I followed these instructions to upgrade to latest piler 1.3.8 https://bitbucket.org/jsuto/piler/issues/890/how-to-update#comment-44192049

Sadly the sphinx.conf stayed the same. Is there some other step I have to follow to change the sphinx.conf to the current version?

[Waldemar Hamm]

I’ll follow these steps from the postinstall and see if it helps now:

# cp etc/sphinx.conf.in /usr/local/etc/piler/sphinx.conf
# chmod +x /usr/local/etc/piler/sphinx.conf
# su - piler
$ indexer --all --config /usr/local/etc/piler/sphinx.conf

‌

[Janos SUTO]

Check out sphinx.conf.in in the piler source tree etc directory.

‌

[Waldemar Hamm]

Yeah, I copied the sphinx.conf.in from the etc folder in the files used for the upgrade.

I changed the SPHINX_VERSION, localstatedir and mysql credentials.

indexer --all --config

and

reindex -a

is running now.

Will also run indexer.delta.sh and indexer.main.sh afterwards.

Will tell you how it went. Thanks a lot!

[Waldemar Hamm]

After doing the above everything seems to have went through.

I got a HTTP 500 at first but it seemed that the config-site.php was overwritten. I replicated it and now it seems that everything’s running again.

I hope this issue is a help to others that may face the same problem.

Thanks for accompanying me on my way to success Janos.

‌

[Waldemar Hamm]

Hey Janos,

one thing makes me wonder now, though.

I only see mails from since I “repaired” sphinx now in the piler web GUI.

Any idea what might be the issue?

[Janos SUTO]

Connect to searchd with

mysql -h 127.0.0.1 -P9306

then run

select * from main1,dailydelta1,delta1;

What IDs do you get?

[Waldemar Hamm]

Hello Janos,

here is the output:

‌

[Janos SUTO]

Indeed you miss anything before 159661. Do the following. Run

cd /tmp
reindex -f 1 -t 100
su -c /usr/local/libexec/piler/indexer.delta.sh piler

Then check the previous query again. It should display from 1 to 20.

[Waldemar Hamm]

Yes I can see IDs 1 to 20 now.

If reindex did the trick, I don’t understand why, though. I did a reindex -a before doing indexer.delta.sh and indexer.main.sh

What should I do now to get all the mails again?

[Janos SUTO]

Not sure why. Anyway, repeat this process from 101 to 159660. Perhaps in 10k batches, eg. 101-10000, then 10001-20000, etc.

[Waldemar Hamm]

I don’t know why but reindex -a and indexer.delta.sh did the trick this time.

Was it wrong to run indexer.main.sh afterwards when I did this before?

[Janos SUTO]

No. The main indexer also runs every day.