Simple Machines Community Forum

SMF Support => SMF 2.0.x Support => Topic started by: Hurga on June 09, 2017, 05:40:21 AM

Title: Database error on 2.0.14: ORDER BY clause is not in SELECT list
Post by: Hurga on June 09, 2017, 05:40:21 AM
When clicking "Show new replies to your posts" I get:

Database Error
Expression #1 of ORDER BY clause is not in SELECT list, references column 'ef_forum.t.id_last_msg' which is not in SELECT list; this is incompatible with DISTINCT
File: /home/forum/smf/Sources/Recent.php
Line: 1103

SMF 2.0.14, Ubuntu 16.04.2 LTS, MySQL 5.7.18-0ubuntu0.16.04.1, PHP 7.0.18-0ubuntu0.16.04.1

Rest of the forum seems to be fine. Didn't happen before upgrade to SMF 2.0.14

- Hurga
Title: Re: Database error on 2.0.14: ORDER BY clause is not in SELECT list
Post by: SpacePhoenix on June 09, 2017, 02:49:53 PM
It's probably fallen foul of MySQL's strict mode
Title: Re: Database error on 2.0.14: ORDER BY clause is not in SELECT list
Post by: vbgamer45 on June 09, 2017, 03:57:52 PM
To disable  then restart mysql
Add this:

[mysqld]
sql-mode=""

To:
/etc/mysql/my.cnf
Title: Re: Database error on 2.0.14: ORDER BY clause is not in SELECT list
Post by: Hurga on June 09, 2017, 08:15:44 PM
Didn't know this was necessary. It's been only necessary to remove the ONLY_FULL_GROUP_BY part from sql_mode - works fine now. Thanks a lot :)

- Hurga
Title: Re: Database error on 2.0.14: ORDER BY clause is not in SELECT list
Post by: piotao on April 18, 2019, 12:46:16 PM
Quote from: vbgamer45 on June 09, 2017, 03:57:52 PM
[mysqld]
sql-mode=""
To:
/etc/mysql/my.cnf

HOLY MOLY, this error I had also on 2.0.15 forum under MariaDB on both php 7.0 and 7.2. It gave me quite a lot sleepless nights; now I disabled this and ... everything seems to be working! Thank you!
Title: Re: Database error on 2.0.14: ORDER BY clause is not in SELECT list
Post by: Wittsend on November 16, 2020, 10:52:50 AM
Quote from: vbgamer45 on June 09, 2017, 03:57:52 PM
To disable  then restart mysql
Add this:

[mysqld]
sql-mode=""

To:
/etc/mysql/my.cnf


Many thanks, this patch worked for me with my 2.0.15 forum running with php 5.6.40