Forum

I am getting a SQL error when using the search module.

Looks like they changed the default SQL mode to include ONLY_FULL_GROUP_BY in v5.7.5 of MySQL.

This means that a query with fields in the ORDER BY clause also need to have those fields in the SELECT statement when they are grouped by DISTINCT.

From what I can see, there are a couple of options:

1) Change my.cnf and remove ONLY_FULL_GROUP_BY from "sql-mode" variable. Probably not an option for people on shared hosting.

2) Fix queries.

SQL query: select distinct concat('/forum/topic/', t.id, '/#', m.id) as url, concat(f.title, ' :: ', t.subject) as text, m.content from forums f, forum_topics t, forum_messages m where f.id=t.forum_id and t.id=m.topic_id and ((t.subject like '%test%') or (m.content like '%test%') or (m.username like '%test%')) order by m.timestamp desc
Error message: Expression #1 of ORDER BY clause is not in SELECT list, references column 'banshee61.m.timestamp' which is not in SELECT list; this is incompatible with DISTINCT
SQL query: select distinct concat('/poll/', p.id) as url, p.question as text from polls p, poll_answers a where p.id=a.poll_id and now()>=end and ((p.question like '%test%') or (a.answer like '%test%')) order by begin desc
Error message: Expression #1 of ORDER BY clause is not in SELECT list, references column 'banshee61.p.begin' which is not in SELECT list; this is incompatible with DISTINCT
SQL query: select distinct concat('/weblog/', w.id) as url, w.title as text, w.content from weblogs w left join weblog_comments c on w.id=c.weblog_id where ((w.title like '%test%') or (w.content like '%test%') or (c.content like '%test%') or (c.author like '%test%')) order by w.timestamp desc
Error message: Expression #1 of ORDER BY clause is not in SELECT list, references column 'banshee61.w.timestamp' which is not in SELECT list; this is incompatible with DISTINCT

Queries are in models/search.php...

Line 56: search_forum()

Line 152: search_polls()

Line 196: search_weblog()

Please, try this new search model and let me know if it works.

It works and is exactly the same as the code I changed to fix the problem.

Thanks!

A couple more places in models/weblog.php:

114c114
< 			         "where visible=%d order by year desc";
---
> 			         "where visible=%d order by timestamp desc";
121c121
< 			         "where visible=%d order by year, month desc";
---
> 			         "where visible=%d order by timestamp desc";