Not able to view mails on piler webui

[Rajesh]

Hi,

We are not finding some mails on piler webui.

This is one of the mail which is found in smtp log and syslog

Aug 16 21:03:39 piler2 piler-smtp[4644]: received: 4Z6NJIK0NFDQ88EM, from=prvs=2208ecc62=[e-mail], size=15951243, client=172.20.50.21, fd=6, fsync=26040

Aug 17 17:17:39 piler2 piler[23657]: 0/4Z6NJIK0NFDQ88EM: 4000000062fcd563100db7e400d779ebc9b9, size=15951243/12074208, attachments=2, reference=, message-id=[e-mail], retention=2557, folder=0, delay=1.7951, status=stored

This mail is not displaying in piler webui.

/etc/rc,searchd service was also restarted but sill no luck.

Piler version used is 1.3.8 build 998

Kindly suggest anything else to be checked.

Regards,

Rajesh G.L

[Janos SUTO]

I took quite some time from receiving it to actually process it. Anyway, check the serial id in the mysql piler database metadata table, eg.

select id from metadata where piler_id='4000000062fcd563100db7e400d779ebc9b9';

Then check the latest sphinx index ids:

select id from main1,dailydelta1,delta1 order by id desc;

And finally (provided that later messages are indexed) run (Be sure to replace 1234 with the correct metadata id value)

select * from main1,dailydelta1,delta1 where id=1234;

If you have a hit, then you have the message archived and indexed, and probably you need a better search query to find it.

[Rajesh]

Hi,

I ran below commands

=============================================================

mysql -u root -p

use piler;

select id from metadata where piler_id='4000000062fcd563100db7e400d779ebc9b9';

metadata id 62311688

mysql -h 127.0.0.1 -P 9306

select id from main1,dailydelta1,delta1 order by id desc;

id | +----------+ | 62462150 | | 62462149 | | 62462148 | | 62462147 | | 62462146 | | 62462145 | | 62462144 | | 62462143 | | 62462142 | | 62462141 | | 62462140 | | 62462139 | | 62462138 | | 62462137 | | 62462136 | | 62462135 | | 62462134 | | 62462133 | | 62462132 | | 62462131 | +----------+ 20 rows in set (16.41 sec)

================================== select * from main1,dailydelta1,delta1 where id=62311688; Empty set (0.00 sec)

==================================

pilerget 4000000062fcd563100db7e400d779ebc9b9

This is displaying the required mail data

=================================

When i ran find command, i am able to find this file in

/var/piler/store/00/62f/c9/b9 directory

find /var/piler/store/00/62f -iname 4000000062fcd563100db7e400d779ebc9b9.m

==========================

I tried doing reindex all

su piler

reindex -a

This breaks after some time.

How to resolve this issue ?

[Janos SUTO]

I’m not sure if you need to reindex everything. Instead reindex this single email: reindex -f 62311688 -t 62311688

[Rajesh]

Hi Janos,

I did following steps:

cd /var/piler/tmp

su piler

reindex -f 62311688 -t 62311688

It is throwing following error:

failed to add to sph_index table: 62311688.eml
put 0 messages to sph_index table for reindexing

mysql -h 127.0.0.1 -P 9306

select * from main1,dailydelta1,delta1 where id=62311688;
Empty set (0.00 sec)

I find lot of mails not indexed from 11/08/2022 to 17/08/2022

How to reindex for date period 11/08/2022 to 17/08/2022?

‌

‌

‌

[Janos SUTO]

Try again reindexing that single email again. If it says “failed to add…”, then check the sph_index mysql table for this “id”.

To reindex a date range you need to find the value for the -f and the -t options of reindex. Check the metadata table again, the “sent” column holds the Date: header parsed values.

[Rajesh]

Yes , i am able to view this mail on piler webui.

Now i have to explore indexing of Date option

Thanks for the support

[Rajesh]

                                      |                                                                  | 971adb343a532ec7c3833a7ae3db813b1658cad003993a8e1d332073ae07a7c3 | 7c193bc870bd5b98bcac18d98334d8959fd16e7857c22b36efc4db5c73273404 | a05d2ba7e59e983c1a4f9be9e5835da8e6fa893977ed5fb9db8886e32181dcbd |

Hi Janos,

I am not able to find date field in metadata as shown below:

| 62676312 | [e-mail] | abslmf.adityabirlacapital.org | Celebrating 24 years of Wealth Creation ?? | 0 | 1661589096 | 1661578587 | 1882513896 | 0 | 61139 | 16581 | 0 | 0 | 400000006309d67237ff599c006bbe78dae4 | [e-mail] | | a08e3a8698186634a3e843cf822db2b828d6ecc1096d891d2115697ae5533e2f | 8e6ea464aac10d9b47f2f563cf8a92ceab082084eb30c65d65f95a5a9469c20c | d0291a2fabb5e44f38cd1015a0422d035dc8b0266109f5e94abd59fa04b95e3c |
| 62676311 | [e-mail] | sbicapsec.com | 👇 Weekly Highlights You Don’t Wanna Miss! | 0 | 1661589096 | 1661574704 | 1882513896 | 0 | 28384 | 15099 | 0 | 0 | 400000006309d672368680f40039ee2658a6 | [e-mail] | | ===============================================

Is there any other method to reindex only the missing mails ?

I saw in the documentation

the following command will get all messages with metadata table id between 1 and 1000.

reindex -f 1 -t 1000

How to find the missing metadata id from 11/08/2022 to 17/08/2022 , so that i can reindex only those records.

[Janos SUTO]

Check the metadata table again, the “sent” column

‌

[Janos SUTO]

No further input, so case is closed.