[RESOLVED] Sphinx indexer issue with MySQL 5.7.*

[qpo]

When you've decided move your forum search to ultimate fast Sphinx engine, you can got an indexer error:

$ indexer --config /home/eham/sphinx.conf --all
Sphinx 2.2.11-id64-release (95ae9a6)
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/home/vbulletin/sphinx.conf'...
indexing index 'vbulletin_disk'...
ERROR: index 'vbulletin_disk': sql_query_pre[3]: Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'vbulletin.n.nodeid' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by (DSN=mysql://vbulletin:***@localhost:3306/vbulletin).

This happends due permament changes in MySQL 5.7 database server - since version 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.)

You can implement quick&dirty fix for this issue - just add one SQL query

sql_query_pre = SET sql_mode = ''

before first sql_query_pre line in your vbulletin-sphinx.php configuration file.
This SQL query makes your MySQL server compatible with old SQL syntax

Also it would be good if the vbulletin development team will rewrite this SQL query by aligning it with new MySQL 5.7 syntax requirements

[Trevor Hannant]

There are a number of Sphinx bug fixes coming in 5.3.3. If this issue still occurs after this is released, please raise a Bug in our Tracker so our Developers can look into this and resolve.