0

I've been trying to get the Sphinx Search plugin for WordPress working but have run into a head scratcher. I have Sphinx on the server and running fine with the test database install that comes with the source. However, when I try to index WP using the sphinx.conf provided by Sphinx Search plugin (copy here) it refuses to process. When I run it by command line I get the following output:

# /usr/local/bin/indexer --rotate --config /home/example.com/public_html/wp-content/uploads/sphinx/sphinx.conf dk_delta dk_main dk_stats
Sphinx 2.2.9-id64-release (rel22-r5006)
Copyright (c) 2001-2015, Andrew Aksyonoff
Copyright (c) 2008-2015, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/home/example.com/public_html/wp-content/uploads/sphinx/sphinx.conf'...
indexing index 'dk_delta'...
ERROR: index 'dk_delta': sql_range_query: 'example.com.p.post_title' isn't in GROUP BY (DSN=mysql://dekanta_flyus3r:***@localhost:3306/example.com).
total 0 docs, 0 bytes
total 0.003 sec, 0 bytes/sec, 0.00 docs/sec
indexing index 'dk_main'...
ERROR: index 'dk_main': sql_range_query: 'example.com.p.post_title' isn't in GROUP BY (DSN=mysql://dekanta_flyus3r:***@localhost:3306/example.com).
total 0 docs, 0 bytes
total 0.003 sec, 0 bytes/sec, 0.00 docs/sec
indexing index 'dk_stats'...
collected 0 docs, 0.0 MB
total 0 docs, 0 bytes
total 0.003 sec, 0 bytes/sec, 0.00 docs/sec
total 1 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 7 writes, 0.000 sec, 0.4 kb/call avg, 0.0 msec/call avg
rotating indices: successfully sent SIGHUP to searchd (pid=20768).

The problem is surely that there is no dk_delta, dk_main or dk_stats tables in the wordpress database (I'm using dk_ prefix) which is what has confused me as this plugin is compatible up to and including latest wordpress. This leads me to believe that I must be missing an important step in the process e.g do I have to index all the database first with sphinx, install extra sql tables etc.

I've posted on wordpress support forum for this plugin but no-one has replied and its a month since there was any activity there so not getting my hopes up. Any advice is appreciated, thanks.

d1ch0t0my
  • 443
  • 7
  • 22

1 Answers1

0

I think this is because you are using a recent version of MySQL, which has more strict semantics on GROUP BY queries. The ONLY_FULL_GROUP_BY option has been turned on.

Those errors are produced by the MySQL server, indexer is just 'reporting' it was unable to run the MySQL queries provided in the config file.

http://www.tocker.ca/2014/01/24/proposal-to-enable-sql-mode-only-full-group-by-by-default.html

MySQL : isn't in GROUP BY

https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

https://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_only_full_group_by

http://mechanics.flite.com/blog/2013/02/12/why-i-use-only-full-group-by/

The queries in that config file are for a older version of MySQL, historically MySQL is very lenient how you write GROUP BY queries.

Either

  1. the MySQL queries need updating to work with ONLY_FULL_GROUP_BY enabled or
  2. can change MySQL Server settings to allow these types of queries.

for 1. make liberal use of the ANY_VALUE() function

http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

(in fact the documentation for that function, is perhaps the most concise explanation of the problem!)

Community
  • 1
  • 1
barryhunter
  • 20,886
  • 3
  • 30
  • 43
  • That makes sense, I am using mysql Ver 15.1 Distrib 5.5.41-MariaDB on this box. I'll read through the docs you posted to see if I can get further with this, appreciate your help. – d1ch0t0my Jul 31 '15 at 14:41
  • OK, you nailed it, for speed purposes so I can get this up and running I removed ONLY_FULL_GROUP_BY from sql_mode in the my.cnf, restarted and presto indexer works its magic as it should. Thanks again. – d1ch0t0my Jul 31 '15 at 15:08