E-Mail not found, but file exists.

[Former user]

Hello,

I have an E-Mail with Date 28.01

‌

physically the file is also on disk

‌

but as auditor I can’t find it:

‌

Any ideas, why?

[Janos SUTO]

How does the sphinx query look like?

[Former user]

date2=2020-01-29&match=&sort=date

[Janos SUTO]

I meant the syslogged sphinx query in the mail logs.

[Former user]

sphinx query: 'SELECT id FROM main1,dailydelta1,delta1 WHERE sent <= 1580338799 AND MATCH('') ORDER BY sent DESC LIMIT 0,20 OPTION max_matches=1000'

So E-Mails from yesterday:

'SELECT id FROM main1,dailydelta1,delta1 WHERE sent >= 1580770800 AND sent <= 1580857199 AND MATCH('') ORDER BY sent DESC LIMIT 0,20 OPTION max_matches=1000' in 0.00 s, 20 hits, 289 total found

EMails from 28.January:

'SELECT id FROM main1,dailydelta1,delta1 WHERE sent >= 1580166000 AND sent <= 1580338799 AND MATCH('') ORDER BY sent DESC LIMIT 0,20 OPTION max_matches=1000' in 0.00 s, 0 hits, 0 total found

‌

[Janos SUTO]

OK, then we need a little digging. Run the following sql query:

select id, deleted from metadata where piler_id='400000…';

You get an id and the deleted flag. Then connect to sphinx: mysql -h 127.0.0.1 -P9306, and run

select * from main1 where id=123;

Be sure to use the proper 'piler_id' and the ‘id’ values.

[Former user]

Where can be ‘piler_id’ found?

Di You think, the message(s) are deleted? It was 6 or 7 messages (28.Jan) and only one, yesterday “deleted”. But I am still able to find the message and read it. In “WebGui”-> audit → column ‘action’ = remove message → klick on reference number.

[Janos SUTO]

See the filename on the image you attached. It’s a long hexa string 40000… ending with ‘.m’ That’s the piler_id without the ‘.m’ part.

[Former user]

OK. First query result:

+----+---------+
| id | deleted |
+----+---------+
| 1 | 0 |
+----+---------+

Second query: no results (“Empty set”).

[Janos SUTO]

OK, try the following. Assuming that pilerget is able to retrieve this message, try pilerget -i 400000….

then as user piler run:

cd /var/piler/imap; reindex -f 1 -t 1; /usr/local/libexec/piler/indexer.delta.sh

Then check again on sphinx:

select * from delta1 where id=1;

You should get a row.

[Former user]

Well…

invalid piler-id: -i

then without ‘-i’ E-Mail was displayed.

reindex:
put 1 messages to sph_index table for reindexing

select:
+------+------------+------+-------------+
| id | sent | size | attachments |
+------+------------+------+-------------+
| 1 | 1580223003 | 0 | 0 |
+------+------------+------+-------------+

Yes. E-Mail is now on the audit-list. Do you know what happened?

I still miss 6 test-E-Mails. What should I do? First looking with “find . +mtime” and then “pilerget”, “reindex”?

[Janos SUTO]

If you don’t have several thousands of emails, then you may reindex everything with reindex -a. If you do have tons of messages, then you may reindex a specific range only.

Also check if the crontab entries for piler are installed. Look inside util/postinstall.sh or etc/cron* for details what should be run from crontab.

[Former user]

After ‘reindex -a’, all E-Mails back. Thank you.

Crontab seems to be OK.

[Janos SUTO]

Then case is closed.