Mails imported but not showing in web-ui

[Kilian Legters]

Hi Jsuto! I have just now installed your software but I am experiencing some issue getting the mails to show up in the UI.

When logging in on the default auditor account and searching for mail I get the message: “Empty search result. Try adding the wildcard character(*) after a word snippet (min. 5 characters), eg. duplic* to find "duplicate", "duplicated", etc.“. I have tried putting in several searches of which also “*” but to no avail…

I am importing the mails via the “pilerimport” command, which works. I see all expected 18 mails being imported and no errors. If I run the same command I am told the mails are, as to be expected, duplicates… The metadata table show all 18 mails with the “deleted” collumn containing “0” across the board. I have turned of selinux, “sudo getenforce” returns “permissive”. I have also tried to reindex, though I am not entirely sure this went smoothly, sometimes I had to use “sudo” or “--rotate”. I could not find any obvious places mailpiler logs, only stats, does piler not use any logging?

‌

Thanks in advance!

‌

My setup is a fresh install on AlmaLinux 4.18.0-348.2.1.el8_5.x86_64 with mariadb as database.

Output of “searchd -v”:

$ searchd -v
Sphinx 3.4.1 (commit efbcc65)
Copyright (c) 2001-2021, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

Built on: Linux ubuntu16 4.4.0-116-generic #140-Ubuntu SMP Mon Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
Built with: gcc 5.4.0
Build date: Jul  9 2021
Build type: release
Configure flags:  '--enable-dl' '--with-mysql' '--with-pgsql' '--with-unixodbc' 'CXXFLAGS=-DSPHINX_TAG= -DNDEBUG -O3 -g1' 'LDFLAGS=-static-libstdc++'
Compiled DB drivers: mysql-dynamic pgsql-dynamic odbc-dynamic
Versions: binlog_format v.8, index_format v.55, udf_api v.17

Output of “piler -V”:

$ piler -V
piler 1.3.11, build 1001, Janos SUTO <[e-mail]>

Build Date: Wed Dec 8 12:31:15 UTC 2021
ldd version: ldd (GNU libc) 2.28
gcc version: gcc version 8.5.0 20210514 (Red Hat 8.5.0-4) (GCC)
OS: Linux <REDACTED> 4.18.0-305.10.2.el8_4.x86_64 #1 SMP Thu Jul 22 07:08:07 EDT 2021 x86_64 x86_64 x86_64 GNU/Linux
Configure command: ./configure --localstatedir=/var --with-database=mysql --enable-memcached
MySQL client library version: 10.5.5
Extractors: /bin/pdftotext /bin/catdoc /bin/catppt /bin/xls2csv /bin/ppthtml /bin/unrtf /bin/tnef libzip

‌

[Janos SUTO]

Check if user piler has installed its cron entries, particularly indexer.delta.sh and indexer.main.sh. Also verify that the sphinx index files (dailydelta1.* and delta1.*) have been updated recently (in the last 30 mins)

[Kilian Legters]

Yes those are installed and active.

‌

[<REDACTED> piler]$ sudo ls -la /var/piler/sphinx
total 280
drwx------. 2 piler piler  4096 Dec 13 19:30 .
drwxr-xr-x. 9 piler piler  4096 Dec 13 17:59 ..
-rw-r--r--. 1 piler piler   440 Dec 13 19:05 dailydelta1.spa
-rw-r--r--. 1 piler piler 23864 Dec 13 19:05 dailydelta1.spd
-rw-r--r--. 1 piler piler     1 Dec 13 19:05 dailydelta1.spe
-rw-r--r--. 1 piler piler  1257 Dec 13 19:05 dailydelta1.sph
-rw-r--r--. 1 piler piler 21859 Dec 13 19:05 dailydelta1.spi
-rw-r--r--. 1 piler piler     4 Dec 13 19:05 dailydelta1.spk
-rw-r--r--. 1 piler piler     0 Dec 13 19:05 dailydelta1.spl
-rw-r--r--. 1 piler piler 40857 Dec 13 19:05 dailydelta1.spp
-rw-r--r--. 1 piler piler     0 Dec 13 19:05 delta1.spa
-rw-r--r--. 1 piler piler     1 Dec 13 19:05 delta1.spd
...
-rw-r--r--. 1 piler piler     0 Dec 13 19:30 tag1.spl
-rw-r--r--. 1 piler piler     1 Dec 13 19:30 tag1.spp
[<REDACTED> piler]$ date
Mon Dec 13 19:36:09 UTC 2021
[<REDACTED> piler]$ sudo ls -la /var/piler/sphinx
total 280
drwx------. 2 piler piler  4096 Dec 13 19:35 .
drwxr-xr-x. 9 piler piler  4096 Dec 13 17:59 ..
-rw-r--r--. 1 piler piler   440 Dec 13 19:35 dailydelta1.spa
-rw-r--r--. 1 piler piler 23864 Dec 13 19:35 dailydelta1.spd
-rw-r--r--. 1 piler piler     1 Dec 13 19:35 dailydelta1.spe
-rw-r--r--. 1 piler piler  1257 Dec 13 19:35 dailydelta1.sph
-rw-r--r--. 1 piler piler 21859 Dec 13 19:35 dailydelta1.spi
-rw-r--r--. 1 piler piler     4 Dec 13 19:35 dailydelta1.spk
-rw-r--r--. 1 piler piler     0 Dec 13 19:35 dailydelta1.spl
-rw-r--r--. 1 piler piler 40857 Dec 13 19:35 dailydelta1.spp
-rw-r--r--. 1 piler piler     0 Dec 13 19:35 delta1.spa
-rw-r--r--. 1 piler piler     1 Dec 13 19:35 delta1.spd
...
-rw-r--r--. 1 piler piler     0 Dec 13 19:30 tag1.spl
-rw-r--r--. 1 piler piler     1 Dec 13 19:30 tag1.spp

‌

[Kilian Legters]

By the way, I have not configured any retention rules and/or archiving rules. Also the auditor and admin users are just the default ones, should I have edited anything in those to see the mails?

[Janos SUTO]

No need to configure any rules to make the archive work. Try the following:

$ mysql -h 127.0.0.1 -P9306
mysql> select * from main1,dailydelta1,delta1;

Then you should see some hits. If that’s the case then edit /var/piler/www/model/search/search.php, and locate the sphinx queries (lines with “SELECT id FROM ” . SPHINX_MAIN_INDEX )

and fix it to “SELECT * FROM “ …..

Finally go the web gui, login as an auditor and hit the red search button. No search criteria, just click on it.

[Kilian Legters]

You’re a wizard, it works now. What was the problem in this case, and can I be of any use resolving it?

[Janos SUTO]

Well, it seems that sphinx 3.4.1 has changed since 3.3.1, and the apparent fix is replacing “id” with “*”.

[Kilian Legters]

Hi @Janos SUTO,

Hate to bother you again but the mails seem to have dissapeared again. I logged in today to add users but noticed no mail being present.

I have gone back and looked at everything in this ticket and other obvious things. Every single thing remains the same with exception of the SQL statement you had me query on sphinx. Now, no lines are returned. I have not changed the "www/models/search/search.php" back to its original state.

Only error in Nginx is as follows: 2021/12/27 17:31:02 [error] 2001#0: *1 FastCGI sent in stderr: "PHP message: PHP Warning: opendir(/var/piler/sphinx/): failed to open dir: Permission denied in /var/piler/www/model/health/health.php on line 326" while reading response header from upstream, client: <REDACTED>, server: <REDACTED>, request: "GET /index.php?route=health/worker&_=1640626262254 HTTP/1.1", upstream: "fastcgi://unix:/var/run/php-fpm/www.sock:", host: "<REDACTED>", referrer: "https://<REDACTED>/index.php?route=health/health"

Any clue what is going on?

[Kilian Legters]

It seems a reindex has resolved my issue now. Not sure why manually running the “delta” and “main” script from the cronjobs didn’t do the trick…