Not visable to old mails in piler webui

[Adiyal Ketan]

Not visable my old email in piler.

piler version :- 1.3.6 build 998

mariadb-server-10.1 1:10.1.47-0ubuntu0.18.04.1

please find the attached snapshot.

[Adiyal Ketan]

email visable only July 2021 month and current month Oct 2021 not visable Aug and Sep month email but i checked mail logs it’s showing email was received ans stored but not visable.

‌

please guide what we can do.

please find mail log snapshot.

also check from mysql command line but process is killed due to lot of email.

‌

‌

[Janos SUTO]

For starters check the mysql piler database, and verify that emails for August and September are present in the metadata table. Look for the id and sent columns. The sent column is a timestamp. Note the id values for some of those emails.

Then open the sphinx database: mysql -h 127.0.0.1 -P9306

And search for those ids, eg. select * from main1,dailydelta1,delta1 where id=1234; -- use the real id values.

‌

[Adiyal Ketan]

Thanks for your update.

I checked as per given command line old month data are getting output 2021-8-13 Aug. but check Sep month data not getting output.

‌

So please help me waht we can do. i can see the old email logs and it’s showing email were received.

so where is stuck the old email month of Sep - Oct.

MySQL [(none)]> SELECT id FROM main1,dailydelta1,delta1 WHERE sent >= 1628735276 AND sent <= 1628821676 AND MATCH('') ORDER BY sent DESC LIMIT 0,20 OPTION max_matches=1000;
+----------+
| id |
+----------+
| 16690831 |
| 16690853 |
| 16723093 |
| 16690867 |
| 16690851 |
| 16690865 |
| 16690844 |
| 16690881 |
| 16690835 |
| 16690825 |
| 16690848 |
| 16723015 |
| 16690830 |
| 16690833 |
| 16690814 |
| 16690838 |
| 16690868 |
| 16690827 |
| 16690832 |
| 16690877 |
+----------+
20 rows in set (0.04 sec)

MySQL [(none)]> SELECT id FROM main1,dailydelta1,delta1 WHERE sent >= 1632450476 AND sent <= 1632536876 AND MATCH('') ORDER BY sent DESC LIMIT 0,20 OPTION max_matches=1000;
Empty set (0.00 sec)

MySQL [(none)]> select * from main1,dailydelta1,delta1 where id=11;
+------+------------+------+-------------+
| id | sent | size | attachments |
+------+------------+------+-------------+
| 11 | 1615897681 | 0 | 0 |
+------+------------+------+-------------+
1 row in set (0.00 sec)

MySQL [(none)]>

[Janos SUTO]

I can’t see the mysql table search query output. Did you check in the mysql database as well or only in the sphinx db? Anyway, I can see status=stored for Sep 26 emails on the screenshot. Try searching in the metadata table for the message-id as well.

[Adiyal Ketan]

please give me mysql database command so i will check and how to search in sphinx db. is there any command ?

as per the logs i can see the status=stored in mail logs.

where i can check ?

could you please guide flow of piler email.

[Janos SUTO]

Oh, Dear! The mysql command is “mysql piler”.

The sql search is usually called a query, not a command. It’s something like a select … from table where <conditions>

[Janos SUTO]

Hopefully you managed to make it. Otherwise re-open this issue.

[Adiyal Ketan]

Hi Janos,

I checked in the mysql tables and data is showing. pls check the snapshot of output.

‌

‌

[Adiyal Ketan]

I have login in to mysql for search query but size is showing zero. so please suggest why old email not reflacting in piler web ui.

‌

[Janos SUTO]

I’m glad that you returned half year later. The size=0 column is indeed odd. Check the /var/piler/sphinx dir, and verify that everything is owned by piler:piler.

Also check the timestamps or last modification dates on dailydelta1.* and delta1.* files. And be sure to run select * from main1,dailydelta1,delta1 order by id desc query as well.

[Adiyal Ketan]

/var/piler/sphinx dir permission is ok. pls check the snapshot. also find the output of snapshot select * from main1,dailydelta1,delta1 order by id desc query as well.

‌

[Janos SUTO]

You didn’t run the sql query I asked. Check again what I asked.

[Adiyal Ketan]

Hi,

Sorry for the delay reply. please check the snapshot i have run the query and following output is showing.

sphix database is removed the data i think so. please suggest why data is not visable.

‌

[Janos SUTO]

I can’t tell. Try reindexing the missing date range.

[Adiyal Ketan]

so please gauide for reindexing.

howe we can do.

[Janos SUTO]

https://www.mailpiler.org/wiki/current:recreate-sphinx-data