Piler Auditor does not list all Messages it finds

[Christian]

Piler Auditor lists as you can see 2 emails, but finds 133745 which it is not listing.
What could be the reason for this, any ideas?
I am trying to migrate from older version to a newer version on a new node.
Thanks for any help you can give.

[Janos SUTO]

I haven’t seen such thing yet. Show me the sphinx query from the mail log, also what’s the value of ENABLE_SAAS in config.php or config-site.php?

[Christian]

from mail.log:
Jan 7 14:30:46 ff7a75d2907c piler-webui[279]: sphinx query: 'SELECT id FROM main1,dailydelta1,delta1 WHERE MATCH(' nachricht*') ORDER BY sent DESC LIMIT 0,50 OPTION max_matches=1000' in 0.09 s, 50 hits, 133745 total found

from config.php:
\$config['ENABLE_SAAS'] = 0;

I noticed that there is a space before the search term (' nachricht*') is that normal? I did not enter a space before the search term in the form field.

[Christian]

could it have something to do with the delta indexer? I can't take Piler offline in the production environment like stated here http://www.mailpiler.org/wiki/current:migration-to-new-host just yet, but reindex runs every 15 minutes… so I think it should not make that big a difference.

I copied all the directories (/var/lib/mysql/*; /var/piler/sphinx/*; /var/piler/store/*)

and other than not listing all the mails it can find, Piler seems to work fine.

[Janos SUTO]

You need to debug a little bit further. For starters check if sphinx actually returns 50 hits for the first page.

To do that edit model/search/search.php. Around line 232 you have the following:

‌

$query = $this->sphx->query("SELECT id FROM " . SPHINX_MAIN_INDEX . " WHERE $a $id $date $attachment $direction $size ....

Add the following line below:

‌

syslog(LOG_INFO, "sphinx results: " . implode(";", $query->rows));

‌

Then on line 261 (or so) you have

‌

$fs_query = $this->db->query("SELECT id FROM " . TABLE_META . " WHERE id IN ($q) ORDER BY `$sort` $order", $ids);

Add the following right below it:

‌

syslog(LOG_INFO, "fs_query results: " . implode(";", $fs_query->rows));

‌

Then perform the search again, and show me the logged lines.

[Christian]

the logged lines are:
Jan 10 09:13:16 ff7a75d2907c piler-webui[280]: sphinx query: 'SELECT id FROM main1,dailydelta1,delta1 WHERE MATCH(' nachricht*') ORDER BY sent DESC LIMIT 0,50 OPTION max_matches=1000' in 0.08 s, 50 hits, 133745 total found
Jan 10 09:13:16 ff7a75d2907c piler-webui[280]: sphinx results: Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array

‌

[Janos SUTO]

How about the 2nd logged line with “fs_query results:“?

[Christian]

Jan 10 11:20:55 6873097f5e1a piler-webui[280]: fs_query results: Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array;Array

[Janos SUTO]

OK, so we have 50 hits at this point. Now edit controller/search/helper.php, and add the following to the 65th line (currently it’s empty):

‌

syslog(LOG_INFO, "all ids: " . $this->data['all_ids']);
syslog(LOG_INFO, "messages: " . count($this->data['messages']));

Also set the following in config-site.php temporarily. It should reveal some sql statements in the mail logs during the execution of a search:

‌

$config['LOG_LEVEL'] = DEBUG;

‌

[Janos SUTO]

OK, we are closer. Now login to mysql piler database, and perform the following queries. The culprit is where you get only 2 rows (give or take) and not 50.

‌

SELECT id, to FROM rcpt WHERE id IN (1041306,913403,1126720,1126718,1126713,1126712,1126711,1126708,1126709,1126710,1126704,1126703,1126698,1126693,1126692,1126690,1126689,1126688,1126686,1126681,1126676,1126675,1126673,1126667,1126668,1126659,1126656,1126650,1126649,1126645,1126642,1126639,1126640,1126635,1126631,1126630,1126627,1126626,1126623,1126618,1126613,1126608,1126606,1126604,1126600,1126598,1126595,1126590,1126586,1126585);
SELECT `id`, `from`, `subject`, `piler_id`, `reference`, `retained`, `size`, `spam`, `sent`, `arrived`, `attachments` FROM `metadata` WHERE `id` IN (1041306,913403,1126720,1126718,1126713,1126712,1126711,1126708,1126709,1126710,1126704,1126703,1126698,1126693,1126692,1126690,1126689,1126688,1126686,1126681,1126676,1126675,1126673,1126667,1126668,1126659,1126656,1126650,1126649,1126645,1126642,1126639,1126640,1126635,1126631,1126630,1126627,1126626,1126623,1126618,1126613,1126608,1126606,1126604,1126600,1126598,1126595,1126590,1126586,1126585);

I suspect that the 1st sql query will be the culprit.

[Christian]

The first sql query returns an error. The second one returns only 2 hits.

[Janos SUTO]

Ehh, my bad. Please re-execute first query but use `to` instead of to.

[Christian]

if I use citation-marks around to (SELECT id, 'to' FROM rcpt WHERE….) it works, but shows only 2 results.

‌

[Janos SUTO]

OK. So the trouble is that you are missing records in the metadata table, that’s why the gui can’t return the rest of the 48 messages.

[Janos SUTO]

No news is good news.