Sphinx: deleted main1, confused about "reindex"

[Axel]

Hi.
I’m running piler on Ubuntu 18. The piler-folder is mapped to a NAS - not only pilers “store”-folder, but the whole piler-folder.

Everything was running fine, until 4 4 days ago. The NAS was suddenly rebooted while the piler-Ubuntu-server was still running.
The result was, that the Web-GUI then shows an error, so I thought I just have to reindex the Sphinx-Index. The I made a stupid misstake usinf the command “indexer --config…[path to sphinx.conf] main1”. As I know by now, this commands resets the main1-index!
So I googled and also read the piler-documentation, but I am a bit confused about how to reindex the Mailarchive.

I did the following so far:

- stop searchd
- rm -f /var/piler/sphinx/*
- su -c "indexer --all --config /usr/local/etc/piler/sphinx.conf" piler
- startd searchd
- reindex all emails, using su - piler; cd /tmp; reindex -a;

After the reindex-command I get

I’ve started searchd and the reindex-command as user “piler”. The user “piler” has write-access to /tmp.

The reindex is still running (showing above “failed to add”-messages) because there are about 900.000 Files to reindex.

Are these steps okay, and I only have to wait until the reindex finishes? Do I need any further step after the reindex finishes? Or am I wrong at all to rebuild the Index?

Regards

Axel

Ubuntu 18
Piler: Latest
Sphinx: 2.2.11

[Janos SUTO]

The steps seem to be fine. The failed to add issue usually indicates that the given email is already in the sph_index mysql table.

Run su -c /usr/local/libexec/piler/indexer.delta.sh piler to process this table.

[Axel]

Thanks a lot for your fast reply. The command finished without errors.
But still something seems to be wrong, because no Mails are archived since yesterday. The Frontend shows: SMTP Status Piler:ERROR, Smarthost: OK.

The mail.log shows:

and tons of those entrys:

reindex[4339]: ERROR: 400000005d99982e0160592c00741e6b22a6 failed to store index data for id=620211, sql_errno=1366
Mar 4 05:24:05 mrubuntu3 reindex[4339]: ERROR: 400000005d99a48e0469fb640006e600e2db: mysql_stmt_execute() 'Incorrect string value: '\xF0\x9F\x93\xB7 A...' for column 'subject' at row 1' (errno: 1366)

The entry PILER_HOST in the config-file is already set, and also “ALTER TABLE sph_index CHANGE COLUMN body body MEDIUMBLOB default NULL;” (I’ve found these hints in former Posts).

Any idea what can I do next to solve it?

Regards
Axel

[Janos SUTO]

Check if piler-smtp is running. Also try telnet <archivehost> 25, and see if the mail server can access piler’s smtp daemon.

The incorrect string value issue should be fine if you fixed the subject column as well, eg.

alter table sph_index change column subject subject` blob(512) default null;

After that run mv /var/piler/error/* /var/piler/tmp/0 to reprocess the error emails.

[Axel]

That’s voodo! Now the Piler:Error in Frontend is gone. Great. And the error-Mails seem to be processed now. Later I will check the mail.log again.

Thanks a lot and take care!

[Janos SUTO]

I will, case is closed.