1. Janos SUTO Avatar Janos SUTO
  2. Piler
  3. piler
  4. Issues

Search UI blank : "select * from main1;" always empty...

Issue #1173 resolved
Nicolas Cazanave created an issue

Hello

I don’t understand why i always have this query"select * from main1;" returning an empty set.

I’ve read many post on the issue section, but I am lost, hence my question.

In particular, the post Issue #945 describe kind of the same problem, but no solution provided in the comments section.

I have a fresh install of piler 1.3.11

piler 1.3.11, build 1001, Janos SUTO <sj@acts.hu>

Build Date: Sun May 9 13:31:16 UTC 2021
ldd version: ldd (Ubuntu GLIBC 2.27-3ubuntu1.4) 2.27
gcc version: gcc version 7.5.0 (Ubuntu 7.5.0-3ubuntu1~18.04)
OS: Linux toto.xxxxxxx.net 4.15.0-140-generic #144-Ubuntu SMP Fri Mar 19 14:12:35 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
Configure command: ./configure --localstatedir=/var --with-database=mysql --enable-tcpwrappers
MySQL client library version: 5.7.34
Extractors: /usr/bin/pdftotext /usr/bin/catdoc /usr/bin/catppt /usr/bin/xls2csv /usr/local/bin/ppthtml /usr/bin/unrtf /usr/bin/tnef libzip

To be sure to start on something brand new, I’ve reseted the archive following the steps described in the FAQ.

/etc/init.d/rc.searchd stop
/etc/init.d/rc.piler stop
rm -rf /var/piler/store/00/*
mysql> drop database piler;
mysql> create database piler character set utf8;
mysql -u piler -p piler < /path/to/piler-source-directory/util/db-mysql.sql
rm -rf /var/piler/sphinx/*
su - piler

The command indexer --all --config /usr/local/etc/piler/sphinx.conf returns

Sphinx 3.3.1 (commit b72d67b)
Copyright (c) 2001-2020, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/usr/local/etc/piler/sphinx.conf'...
indexing index 'main1'...
collected 0 docs, 0.0 MB
total 0 docs, 0.0 Kb
total 0.0 sec, 0.0 Kb/sec, 0 docs/sec
indexing index 'main2'...
collected 0 docs, 0.0 MB
total 0 docs, 0.0 Kb
total 0.0 sec, 0.0 Kb/sec, 0 docs/sec
indexing index 'main3'...
collected 0 docs, 0.0 MB
total 0 docs, 0.0 Kb
total 0.0 sec, 0.0 Kb/sec, 0 docs/sec
indexing index 'main4'...
collected 0 docs, 0.0 MB
total 0 docs, 0.0 Kb
total 0.0 sec, 0.0 Kb/sec, 0 docs/sec
indexing index 'dailydelta1'...
collected 0 docs, 0.0 MB
total 0 docs, 0.0 Kb
total 0.0 sec, 0.0 Kb/sec, 0 docs/sec
indexing index 'delta1'...
collected 0 docs, 0.0 MB
total 0 docs, 0.0 Kb
total 0.1 sec, 0.0 Kb/sec, 0 docs/sec
indexing index 'tag1'...
collected 0 docs, 0.0 MB
total 0 docs, 0.0 Kb
total 0.0 sec, 0.0 Kb/sec, 0 docs/sec
indexing index 'note1'...
collected 0 docs, 0.0 MB
total 0 docs, 0.0 Kb
total 0.0 sec, 0.0 Kb/sec, 0 docs/sec

and then

/etc/init.d/rc.searchd start
/etc/init.d/rc.piler start

Then i did an import of emails from an imap account (working on the same computer)

cd /var/piler/tmp/

pilerimport -i localhost -u myuser@maydomain.fr -p mypassword which returns

List of IMAP folders:
=> '"Sent Messages" [\HasNoChildren \Sent]'
=> '"Deleted Messages" [\HasNoChildren]'
=> 'Drafts [\HasNoChildren \Drafts]'
=> 'Notes [\HasNoChildren]'
=> 'INBOX [\HasNoChildren]'
processing folder: Drafts... found 0 messages
processing folder: "Sent Messages"... found 1 messages
processed:       1 [100%]
processing folder: "Deleted Messages"... found 1 messages
processed:       2 [100%]
processing folder: INBOX... found 17 messages
processed:      19 [100%]
processing folder: Notes... found 0 messages

In order to “force indexing”, i’ve then executed manualy the scripts listed in piler’s crontab (as piler user), i.e.

piler@toto:~/tmp$ /usr/local/libexec/piler/indexer.delta.sh
piler@toto:~/tmp$ /usr/local/libexec/piler/indexer.main.sh
piler@toto:~/tmp$ /usr/local/libexec/piler/purge.sh
piler@toto:~/tmp$ /usr/local/bin/indexer --quiet tag1 --rotate --config /usr/local/etc/piler/sphinx.conf
piler@toto:~/tmp$ /usr/local/bin/indexer --quiet note1 --rotate --config /usr/local/etc/piler/sphinx.conf
piler@toto:~/tmp$ /usr/bin/find /var/piler/error -type f|wc -l > /var/piler/stat/error
piler@toto:~/tmp$ /usr/bin/find /var/piler/www/tmp -type f -name i.* -exec rm -f {} ;

However when checking sphinx database :

mysql -h 127.0.0.1 -P 9306

mysql> select * from main1;
Empty set (0.00 sec)

Hence when logging as auditor@local on the webui, search page remains blanks which makes sense as the mail.log shows that

May 18 08:15:42 toto piler-webui[31585]: sphinx query: 'SELECT id FROM main1,dailydelta1,delta1 WHERE MATCH('') ORDER BY sent DESC LIMIT 0,20 OPTION max_matches=1000' in 0.00 s, 0 hits, 0 total found

But what i am missing??? Why can’t I see the messages just imported in auditor search page ?

As asked by Janos in the comments section of Issue #945

ls -la /var/piler/sphinxreturns

‌

total 204
drwx------  2 piler piler  4096 May 18 09:30 .
drwxr-xr-x 12 piler piler 12288 May 18 09:10 ..
-rw-r--r--  1 piler piler     0 May 18 08:13 dailydelta1.spa
-rw-r--r--  1 piler piler     1 May 18 08:13 dailydelta1.spd
-rw-r--r--  1 piler piler     1 May 18 08:13 dailydelta1.spe
-rw-r--r--  1 piler piler  1247 May 18 08:13 dailydelta1.sph
-rw-r--r--  1 piler piler     1 May 18 08:13 dailydelta1.spi
-rw-r--r--  1 piler piler     8 May 18 08:13 dailydelta1.spj
-rw-r--r--  1 piler piler     0 May 18 08:13 dailydelta1.spk
-rw-r--r--  1 piler piler     0 May 18 08:13 dailydelta1.spl
-rw-r--r--  1 piler piler     1 May 18 08:13 dailydelta1.spp
-rw-r--r--  1 piler piler     0 May 18 08:10 delta1.spa
-rw-r--r--  1 piler piler     1 May 18 08:10 delta1.spd
-rw-r--r--  1 piler piler     1 May 18 08:10 delta1.spe
-rw-r--r--  1 piler piler  1247 May 18 08:10 delta1.sph
-rw-r--r--  1 piler piler     1 May 18 08:10 delta1.spi
-rw-r--r--  1 piler piler    47 May 18 08:10 delta1.spj
-rw-r--r--  1 piler piler     0 May 18 08:10 delta1.spk
-rw-r--r--  1 piler piler     0 May 18 08:10 delta1.spl
-rw-r--r--  1 piler piler     1 May 18 08:10 delta1.spp
-rw-r--r--  1 piler piler     0 May 18 08:13 main1.spa
-rw-r--r--  1 piler piler     1 May 18 08:13 main1.spd
-rw-r--r--  1 piler piler     1 May 18 08:13 main1.spe
-rw-r--r--  1 piler piler  1247 May 18 08:13 main1.sph
-rw-r--r--  1 piler piler     1 May 18 08:13 main1.spi
-rw-r--r--  1 piler piler     0 May 18 08:13 main1.spk
-rw-r--r--  1 piler piler     0 May 18 08:13 main1.spl
-rw-r--r--  1 piler piler     1 May 18 08:13 main1.spp
-rw-r--r--  1 piler piler     0 May 18 08:10 main2.spa
-rw-r--r--  1 piler piler     1 May 18 08:10 main2.spd
-rw-r--r--  1 piler piler     1 May 18 08:10 main2.spe
-rw-r--r--  1 piler piler  1247 May 18 08:10 main2.sph
-rw-r--r--  1 piler piler     1 May 18 08:10 main2.spi
-rw-r--r--  1 piler piler     8 May 18 08:10 main2.spj
-rw-r--r--  1 piler piler     0 May 18 08:10 main2.spk
-rw-r--r--  1 piler piler     0 May 18 08:10 main2.spl
-rw-r--r--  1 piler piler     1 May 18 08:10 main2.spp
-rw-r--r--  1 piler piler     0 May 18 08:10 main3.spa
-rw-r--r--  1 piler piler     1 May 18 08:10 main3.spd
-rw-r--r--  1 piler piler     1 May 18 08:10 main3.spe
-rw-r--r--  1 piler piler  1247 May 18 08:10 main3.sph
-rw-r--r--  1 piler piler     1 May 18 08:10 main3.spi
-rw-r--r--  1 piler piler     8 May 18 08:10 main3.spj
-rw-r--r--  1 piler piler     0 May 18 08:10 main3.spk
-rw-r--r--  1 piler piler     0 May 18 08:10 main3.spl
-rw-r--r--  1 piler piler     1 May 18 08:10 main3.spp
-rw-r--r--  1 piler piler     0 May 18 08:10 main4.spa
-rw-r--r--  1 piler piler     1 May 18 08:10 main4.spd
-rw-r--r--  1 piler piler     1 May 18 08:10 main4.spe
-rw-r--r--  1 piler piler  1247 May 18 08:10 main4.sph
-rw-r--r--  1 piler piler     1 May 18 08:10 main4.spi
-rw-r--r--  1 piler piler     8 May 18 08:10 main4.spj
-rw-r--r--  1 piler piler     0 May 18 08:10 main4.spk
-rw-r--r--  1 piler piler     0 May 18 08:10 main4.spl
-rw-r--r--  1 piler piler     1 May 18 08:10 main4.spp
-rw-r--r--  1 piler piler     0 May 18 09:30 note1.spa
-rw-r--r--  1 piler piler     1 May 18 09:30 note1.spd
-rw-r--r--  1 piler piler     1 May 18 09:30 note1.spe
-rw-r--r--  1 piler piler   911 May 18 09:30 note1.sph
-rw-r--r--  1 piler piler     1 May 18 09:30 note1.spi
-rw-r--r--  1 piler piler     8 May 18 09:30 note1.spj
-rw-r--r--  1 piler piler     0 May 18 09:30 note1.spk
-rw-r--r--  1 piler piler     0 May 18 09:30 note1.spl
-rw-r--r--  1 piler piler     1 May 18 09:30 note1.spp
-rw-r--r--  1 piler piler     0 May 18 09:30 tag1.spa
-rw-r--r--  1 piler piler     1 May 18 09:30 tag1.spd
-rw-r--r--  1 piler piler     1 May 18 09:30 tag1.spe
-rw-r--r--  1 piler piler   910 May 18 09:30 tag1.sph
-rw-r--r--  1 piler piler     1 May 18 09:30 tag1.spi
-rw-r--r--  1 piler piler     8 May 18 09:30 tag1.spj
-rw-r--r--  1 piler piler     0 May 18 09:30 tag1.spk
-rw-r--r--  1 piler piler     0 May 18 09:30 tag1.spl
-rw-r--r--  1 piler piler     1 May 18 09:30 tag1.spp

‌

Please help….

Comments (5)

  3. Janos SUTO repo owner

    Thank you for the detailed description. The problem is that the sphinx database is empty. Try the following.

    Import again those 17 emails. Then check if the sph_index mysql table in the piler database has 17 rows. Then as user run indexer.delta.sh script. Verify that the sph_index table becomes empty, and the dailydelta* files are updated.

    Then run select * from dailydelta1; in the sphinx database.

  4. Nicolas Cazanave reporter

    Thanks for your quick reply.

    So as you asked, importing again leads to duplicate massages :

    cd /var/piler/tmp/

    pilerimport -i localhost -u myuser@maydomain.fr -p mypassword which returns

    List of IMAP folders:
=> '"Sent Messages" [\HasNoChildren \Sent]'
=> '"Deleted Messages" [\HasNoChildren]'
=> 'Drafts [\HasNoChildren \Drafts]'
=> 'Notes [\HasNoChildren]'
=> 'INBOX [\HasNoChildren]'
processing folder: Drafts... found 0 messages
processing folder: "Sent Messages"... found 1 messages
duplicate: 7078-imap-1.txt (duplicate id: 1)

processing folder: "Deleted Messages"... found 1 messages
duplicate: 7078-imap-2.txt (duplicate id: 2)

processing folder: INBOX... found 18 messages
duplicate: 7078-imap-3.txt (duplicate id: 3)
duplicate: 7078-imap-4.txt (duplicate id: 4)
duplicate: 7078-imap-5.txt (duplicate id: 5)
duplicate: 7078-imap-6.txt (duplicate id: 6)
duplicate: 7078-imap-7.txt (duplicate id: 7)
duplicate: 7078-imap-8.txt (duplicate id: 8)
duplicate: 7078-imap-9.txt (duplicate id: 9)
duplicate: 7078-imap-10.txt (duplicate id: 10)
duplicate: 7078-imap-11.txt (duplicate id: 11)
duplicate: 7078-imap-12.txt (duplicate id: 12)
duplicate: 7078-imap-13.txt (duplicate id: 13)
duplicate: 7078-imap-14.txt (duplicate id: 14)
duplicate: 7078-imap-15.txt (duplicate id: 15)
duplicate: 7078-imap-16.txt (duplicate id: 16)
duplicate: 7078-imap-17.txt (duplicate id: 17)
duplicate: 7078-imap-18.txt (duplicate id: 18)
duplicate: 7078-imap-19.txt (duplicate id: 19)
processed:      20 [100%]
processing folder: Notes... found 0 messages

    then

    mysql> select id from sph_index; returns 

    +----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
+----+
20 rows in set (0.00 sec)

    Should we expect 19 rows, as 19 messages were previously imported (17 +1 +1)?

    Then piler@toto:~/tmp$ /usr/local/libexec/piler/indexer.delta.sh + select id from sph_index; but still 20 rows in set and dailydelta* files aren’t updated and

    mysql> select * from main1;
    Empty set (0.00 sec)

    I’ve missed this line in mail.log

    May 18 13:50:15 toto piler: INDEXER ERROR: delta indexing and merging is already running. It started at Mon May 17 12:04:53 UTC 2021

    due to a remainng file delta.indexer.tmp in /var/piler/run/

    ‌

  6. Log in to comment
Assignee
–
Type
bug
Priority
major
Status
resolved
Votes
0
Watchers
1
Jira: the preferred issue tracker for Bitbucket. Join the team!