SQL Error Acessing Forum Section "View Your Posts"

Question

I'm having a little problem in my PHPBB Forum. I'm good at HTML, CSS and JS, but I don't know anything about SQL. This error below happens when I hit the "View your posts".

SQL ERROR [ mysqli ]

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'winx_forum.p.post_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [1055]

SQL

SELECT DISTINCT SQL_CALC_FOUND_ROWS p.post_id FROM winx_topics t, winx_posts p WHERE p.poster_id = '2' AND t.topic_id = p.topic_id GROUP BY t.topic_id, t.topic_last_post_time ORDER BY t.topic_last_post_time DESC

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/includes/db/dbal.php
LINE: 757
CALL: trigger_error()

FILE: [ROOT]/includes/db/mysqli.php
LINE: 182
CALL: dbal->sql_error()

FILE: [ROOT]/includes/search/fulltext_native.php
LINE: 1009
CALL: dbal_mysqli->sql_query()

FILE: [ROOT]/search.php
LINE: 540
CALL: fulltext_native->author_search()

The error message says that you can't run the query with your current SQL mode settings. Did you make any changes before the error started to appear? Such as changing the query or your database settings? Or does this happen or a new server (but not on the old one)? — Petr Hejda, Mar 22 '21 at 21:40
It's a whole new one. Never laid a finger on the SQL itself. — Lucas Santos, Mar 22 '21 at 21:43
The programming solution would be to fix the query, which is an on topic question here on SO (although it is a duplicate one - dozens of questions here already deal with this error). However, phpbb is a complete system, so you better ask their support forum how to fix this. If the suggested fix is to change your sql mode setting to allow this sql to pass through, then you better look for another product. — Shadow, Mar 22 '21 at 21:48

score 0 · Answer 1 · answered Mar 22 '21 at 22:26

the error message is clear, every column in the SELECT has to be in the GROUP BY ot have a aggregation function

your SQL_CALC_FOUND_ROWS is deprecated . so try COUNT instead

Further comma separated tables re also a think from the past, today we use Join.

SELECT  
    COUNT(DISTINCT p.post_id)
FROM
    winx_topics t INNER JOIN 
    winx_posts p ON t.topic_id = p.topic_id
WHERE
    p.poster_id = '2'
        
GROUP BY t.topic_id , t.topic_last_post_time
ORDER BY t.topic_last_post_time DESC

SQL Error Acessing Forum Section "View Your Posts"

1 Answers1