Advanced search fails

[Eduardo]

Hi I am searching using a new user called [e-mail]

The search works ok, but i can’n find any related to from:ediaz@mydomain or using [e-mail] the search allways return 0 and the interface show working….

root@mailarchive:/var/log# piler -V
piler 1.3.12, build 1001, Janos SUTO [e-mail]

Build Date: Sat May 28 11:05:45 UTC 2022
ldd version: ldd (Ubuntu GLIBC 2.31-0ubuntu9.7) 2.31
gcc version: gcc version 9.4.0 (Ubuntu 9.4.0-1ubuntu1~20.04.1)
OS: Linux 4b75018c4ab3 5.4.0-113-generic #127-Ubuntu SMP Wed May 18 14:30:56 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux
Configure command: ./configure --prefix=/usr --sysconfdir=/etc --localstatedir=/var --with-database=mariadb
MySQL client library version: 3.3.3
Extractors: /usr/bin/pdftotext /usr/bin/catdoc /usr/bin/catppt /usr/bin/xls2csv /usr/bin/unrtf /usr/bin/tnef libzip

Some examples of the mail.log file

a normal search ediaz

Dec 30 14:50:49 mailarchive piler-webui[39322]: sphinx query: 'SELECT id FROM main1,dailydelta1,delta1 WHERE        MATCH(' ediaz') ORDER BY `sent` DESC LIMIT 0,50 OPTION max_matches=1000' in 0.65 s, 50 hits, 5282 total found

A search [e-mail]

don show any info on the logs only see the logo Working....

Any guide to try to debug this because I don’t understand what is failling..

Regards!

[Janos SUTO]

I’d like to see the sphinx query for the not working search term.

[Eduardo]

if I don’t see any log win the piler-webui must be a sphinx query anyways?

I am not a expert I will search how to make a query on sphinx in order to try it.

Regards!

[Janos SUTO]

No matter if simple or advanced search you use there will be a sphinx query logged in the mail log.

[Eduardo]

This are form the /var/log/mail.log file grep by

root@mailarchive:/var/log# tail -f mail.log| grep piler-web

‌

This search ediaz
Jan  5 10:23:08 mailarchive piler-webui[297083]: sphinx query: 'SELECT id FROM main1,dailydelta1,delta1 WHERE        MATCH(' ediaz') ORDER BY `sent` DESC LIMIT 0,50 OPTION max_matches=1000' in 0.01 s, 50 hits, 5309 total found

This search ediaz@

I don't see any info on the piler-webui

On the apache log file I see this..

[Thu Jan 05 10:24:48.461486 2023] [php:error] [pid 296737] [client 88.1.139.12:59964] PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 index dailydelta1,delta1,main1: query error: no field 'sender' found in schema in /datos/piler/www/system/database/sphinx.php:42\nStack trace:\n#0 /datos/piler/www/system/database/sphinx.php(42): PDOStatement->execute()\n#1 /datos/piler/www/system/db.php(23): Sphinx->query()\n#2 /datos/piler/www/model/search/search.php(230): DB->query()\n#3 /datos/piler/www/model/search/search.php(44): ModelSearchSearch->get_results()\n#4 /datos/piler/www/controller/search/helper.php(62): ModelSearchSearch->search_messages()\n#5 /datos/piler/www/system/front.php(36): ControllerSearchHelper->index()\n#6 /datos/piler/www/system/front.php(14): Front->execute()\n#7 /datos/piler/www/index.php(114): Front->dispatch()\n#8 {main}\n  thrown in /datos/piler/www/system/database/sphinx.php on line 42, referer: https://mailarchive.adycoaduanas.com/search.php

As far i see there is any issue related to the schema I will search on the doc and post here the reseults

‌

[Eduardo]

Hi, after of some debug i find that the schema is broken..

now the search webui don’t get any result but show at least my search.

Jan 5 13:13:57 mailarchive piler-webui[1520]: sphinx query: 'SELECT id FROM main1,dailydelta1,delta1 WHERE MATCH('@sender ediazrodXgmailXcom ') ORDER BY sent DESC LIMIT 0,50 OPTION max_matches=1000' in 0.00 s, 0 hits, 0 total found

I recreate the sphinx.conf file on /etc/piler/sphinx.conf from the original with my mysql user and password.

I have the impresion that I need to reindex all the mails in order to get all the form head again..

At the end I was thinking about create a new install from 0 and export and import the mails maybe is better

‌

[Eduardo]

After of make some testing with reindex -f 1 -t 1000 and see that the search results is working I will reindex all the mails again.

with user piler

i will disable crontab indexer.delta.sh and indexer.main.sh

enable a session with screen

reindex -a

Enable the crontab again.

wise me luck :-P

I will mark as solve if works with no problems.!

[Janos SUTO]

OK, I wish you luck.

[Eduardo]

Thanks!

I have 2M mails, on 24h hours, I search 600K in 24h. it is working… many errors related to:

Syntax Warning: Invalid least number of objects reading page offset hints table
Syntax Warning: Invalid least number of objects reading page offset hints table
Syntax Error (56): Dictionary key must be a name object
Syntax Error (86): Dictionary key must be a name object
Syntax Error (96): Dictionary key must be a name object
Syntax Error (149): Dictionary key must be a name object
Syntax Error (168): Dictionary key must be a name object
Syntax Error: Couldn't find trailer dictionary
Syntax Error: Couldn't find trailer dictionary
Syntax Error: Couldn't read xref table
Syntax Error (56): Dictionary key must be a name object
Syntax Error (86): Dictionary key must be a name object
Syntax Error (96): Dictionary key must be a name object
Syntax Error (149): Dictionary key must be a name object
Syntax Error (168): Dictionary key must be a name object
Syntax Error: Couldn't find trailer dictionary
Syntax Error: Couldn't find trailer dictionary
Syntax Error: Couldn't read xref table
Syntax Warning: Invalid least number of objects reading page off

But must of the e-mail don’t get a error.

‌

There is any procedure to export a migrate all the mails to a new system?

Regards!

‌

[Janos SUTO]

The mentioned errors come from pdf2text utility, you may ignore them. For the migration, check out the FAQ.