Audit stopped working after migration to new node. Sphinx: ERROR 1064 (42000): sort-by attribute 'sent' not found

[Sebastian Händel]

After migrating our mailpiler to a new host, exactly as described in the docs, the audit doesn't work anymore.

Syslog reports:

sphinx query: 'SELECT id FROM main1,dailydelta1,delta1 WHERE MATCH('') ORDER BY sent DESC LIMIT 0,20 OPTION max_matches=1000' in 0.02 s, 0 hits, 199370 total found

When I try the select by my self:

mysql -h127.0.0.1 -P9306

use piler;

SELECT id FROM main1,dailydelta1,delta1 WHERE MATCH('') ORDER BY sent DESC LIMIT 0,20 OPTION max_matches=1000;

It shows the error:

ERROR 1064 (42000): sort-by attribute 'sent' not found

[Janos SUTO]

There must be some configuration drift between the 2 nodes. What are the sphinx versions on the nodes?

[Sebastian Händel]

Old node:

searchd -v
Sphinx 3.1.1 (commit 612d99f)
Copyright (c) 2001-2018, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com))

Built on: Linux ubuntu16 4.4.0-116-generic #140-Ubuntu SMP Mon Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
Built with: gcc 5.4.0
Build date: Oct 17 2018
Build type: release
Configure flags: '--enable-dl' '--with-mysql' '--with-pgsql' '--with-unixodbc' 'CXXFLAGS=-DSPHINX_TAG= -DNDEBUG -O3 -g1' 'LDFLAGS=-static-libstdc++'
Compiled DB drivers: mysql-dynamic pgsql-dynamic odbc-dynamic

---

New Node:

searchd -v
Sphinx 3.4.1 (commit efbcc65)
Copyright (c) 2001-2021, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com))

Built on: Linux ubuntu16 4.4.0-116-generic #140-Ubuntu SMP Mon Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
Built with: gcc 5.4.0
Build date: Jul 9 2021
Build type: release
Configure flags: '--enable-dl' '--with-mysql' '--with-pgsql' '--with-unixodbc' 'CXXFLAGS=-DSPHINX_TAG= -DNDEBUG -O3 -g1' 'LDFLAGS=-static-libstdc++'
Compiled DB drivers: mysql-dynamic pgsql-dynamic odbc-dynamic
Versions: binlog_format v.8, index_format v.55, udf_api v.17

---

I just noticed that the new node has the entry

define('SPHINX_VERSION', 331); // If you have sphinx-3.3.1, then set SPHINX_VERSION to 331

in the sphinx.conf, the old node has not.

Should I change the define('SPHINX_VERSION', 331); to define('SPHINX_VERSION', 341); ? And any additional Tasks?

‌

[Janos SUTO]

I think it might be the difference in sphinx version. Try using the same version on new server, and let’s see if it improves the situation. Btw. I’m still confused how this issue causes stopping the “audit from working”. Probably you meant that the search for an auditor user doesn’t work anymore.

[Sebastian Händel]

Sorry if I may have put myself wrong. But the Audit doesn’t show any Mails.

At the moment i temporary fixed it like the user in this issue: https://bitbucket.org/jsuto/piler/issues/463/no-email-can-found-in-piler

and added the “sent” column to the sphinx-select statements in the search.php.

But i will try to downgrade the sphinx version and give it a try. I will report the result.

[Sebastian Händel]

Now it works. I did the following steps:

1.) Downgrade sphinx from 3.4.1 to 3.3.1 and checked the variables:

/var/piler/www/config.php:

\$config['SPHINX_STRICT_SCHEMA'] = 1;

/usr/local/etc/piler/sphinx.conf:

define('SPHINX_VERSION', 331);

define('SPHINX_STRICT_SCHEMA', 1);

‌

2.) Deleted everthing in /var/piler/sphinx

3.) indexer --all --config /usr/local/etc/piler/sphinx.conf

4.) reindex all mails. I did the reindex with a shell script to reindex only 1000 mails after another to reduce the space consumption. After every 1000 mails i did a /usr/local/libexec/piler/indexer.delta.sh and removed the temporary files.

Now the original “search.php” works as designed…

[Janos SUTO]

Great.