Search for tag or note shows false Emails

[Peter Bernsmann]

My piler is version 1.1.1, build 904 fresh installed on Ubuntu 14.04. Sphinxsearch is "sphinxsearch_2.2.9-release-0ubuntu12~trusty_amd64.deb" downloaded from http://sphinxsearch.com/downloads/release/.

I can add tags and notes to emails. After updating the view displays the respective fields correctly.

But if i open the advanced search dialog and search with filling note oder tag field, false hits are shown, partly completely different emails from other users.

How can i fix it?

[Janos SUTO]

Try upgrading to the master branch (see the download section), and be sure to run the sql upgrade script, util/db-upgrade-1.1.0-vs-1.2.0.sql. Then let's try it again.

[Peter Bernsmann]

Thank you for your very fast response. But: same problem after upgrade.. :(

[Janos SUTO]

Can we do a teamviewer troubleshooting? If so, then find me on skype (janos.suto).

[Peter Bernsmann]

I'm sorry, but according to our company´s policies it is disallowed to use products like teamviewer. Can you help me 'old-school-style' ?

[Peter Bernsmann]

Found this in the logfile:

Jul 10 09:30:07 ema1 piler-webui[19810]: sphinx query: SELECT id FROM note1 WHERE uid=3 AND MATCH('@note Test') , hits: 1 Jul 10 09:30:07 ema1 piler-webui[19810]: sphinx query: 'SELECT id FROM main1,dailydelta1,delta1 WHERE id IN (8) ORDER BY sent DESC LIMIT 0,1000 OPTION max_matches=1000' in 0.00 s, 1 hits, total found

So, result of 'SELECT id FROM note1 WHERE uid=3 AND MATCH('@note Test') ' is '8'. In the mysql-table 'note' the value '8' is the value of column named '_id', not 'id'. Sphinx query reads the wrong column..

[Peter Bernsmann]

sphinx.conf looks like this:

[...]

source note : base
{
   sql_query_pre = SET NAMES utf8
   sql_query  = SELECT `_id`, `id` AS iid, `uid`, `note` FROM `note`

   sql_attr_uint = iid
   sql_attr_uint = uid

}

[...]

I think, sphinx query should start like this: 'SELECT iid FROM note1 WHERE ...' .

[Janos SUTO]

Actually it has iid in the query. Check model/search/search.php, and locate the get_sphinx_id_list() function, and verify that it has the following:

$q = $this->sphx->query("SELECT iid FROM $sphx_table WHERE uid=" . $session->get("uid") . " AND MATCH('@$field $s') LIMIT 0," . MAX_SEARCH_HITS . " OPTION max_matches=" . MAX_SEARCH_HITS);
if(ENABLE_SYSLOG == 1) { syslog(LOG_INFO, "sphinx query: " . $q->query . ", hits: " . $q->total_found); }

[Peter Bernsmann]

found search.php, changed 'id' to 'iid' in two lines ('SELECT iid FROM', '$a['iid']'):

   private function get_sphinx_id_list($s = '', $sphx_table = '', $field = '') {
      $id_list = '';

      $session = Registry::get('session');

      $s = $this->fixup_sphinx_operators($s);

      $q = $this->sphx->query("SELECT iid FROM $sphx_table WHERE uid=" . $session->get("uid") . " AND MATCH('@$field $s') ");
      if(ENABLE_SYSLOG == 1) { syslog(LOG_INFO, "sphinx query: " . $q->query . ", hits: " . $q->total_found); }

      foreach($q->rows as $a) {
         $id_list .= "," . $a['iid'];
      }

      if($id_list) { $id_list = substr($id_list, 1, strlen($id_list)); }

      if($id_list == '') { $id_list = "-1"; }
      return $id_list;
   }

Is this the correct fix?

[Janos SUTO]

I think so. Or upgrade the gui as I suggested.

[Peter Bernsmann]

ok, found my mistake. I only updated the root of the gui tree, missed '-r'.

Now, it works with gui version 1.2.0. :)

Thank you!

[Peter Bernsmann]

Fixed by updating to 1.2.0