database error in "?action=unreadreplies"

[CyanGaming]

database error:

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


this only appears in the "new replies to your posts" area, however the "unread posts since last visit" works fine.
anyone have any ideas how to fix this?

[SychO]

You have to say what changes you have made (if any) and mods you recently installed, additionally attaching Recent.php source file

[CyanGaming]

This is the section from the Recent.php file that isn't working

Code Select Expand

$request = $smcFunc['db_query']('unread_replies', '
SELECT DISTINCT t.id_topic
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}messages AS m ON (m.id_topic = t.id_topic AND m.id_member = {int:current_member})' . (strpos($_REQUEST['sort'], 'ms.') === false ? '' : '
INNER JOIN {db_prefix}messages AS ms ON (ms.id_msg = t.id_first_msg)') . (strpos($_REQUEST['sort'], 'mems.') === false ? '' : '
LEFT JOIN {db_prefix}members AS mems ON (mems.id_member = ms.id_member)') . '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:current_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = t.id_board AND lmr.id_member = {int:current_member})
WHERE t.' . $query_this_board . '
AND t.id_last_msg >= {int:min_message}
AND (IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0))) < t.id_last_msg
AND t.approved = {int:is_approved}
ORDER BY {raw:order}
LIMIT {int:offset}, {int:limit}',
array_merge($query_parameters, array(
'current_member' => $user_info['id'],
'min_message' => (int) $min_message,
'is_approved' => 1,
'order' => $_REQUEST['sort'] . ($ascending ? '' : ' DESC'),
'offset' => $_REQUEST['start'],
'limit' => $context['topics_per_page'],
'sort' => $_REQUEST['sort'],                                                      //---Line 1103---
))
);


[SychO]

again, have you made any changes or installed any mods prior to the error

[CyanGaming]

I don't know when the error occurred since I never actually used this feature before, it was only when pretty much every user of the forums started complaining.
I do have a ~recent.php file though

[d3vcho]

You still haven't make clear whether you have mods installed or not. We need more details before being able to help you...

[CyanGaming]

You still haven't make clear whether you have mods installed or not. We need more details before being able to help you...

I have mods installed

[d3vcho]

Which ones? Can you post a list of them? Can you leave an URL to your forum?

[CyanGaming]

http://sploder.tk/index.php?action=forum

- Quick Spoiler
- Say Thanks
- Global Headers Footers
- SimplePortal
- SMF Awards Master

[shawnb61]

What versions of php, SMF & mysql are you using?

Similar error here:
https://www.simplemachines.org/community/index.php?topic=558157.msg3956058#msg3956058

[CyanGaming]

What versions of php, SMF & mysql are you using?

Similar error here:
https://www.simplemachines.org/community/index.php?topic=558157.msg3956058#msg3956058

I'm running the newest version, I also don't have phpmyadmin

[Arantor]

Please be more specific than 'the latest version'. If you really are running 'the latest' version of things (like PHP 7.3 betas), it's hardly surprising things don't work perfectly...

[CyanGaming]

Please be more specific than 'the latest version'. If you really are running 'the latest' version of things (like PHP 7.3 betas), it's hardly surprising things don't work perfectly...

sorry, I meant of smf, I'm running 7.0 php

[shawnb61]

Mysql version?    (I guess we must do this one question at a time...)

Note that this sounds a lot like a mysql strict mode issue.  Another example here:
https://www.simplemachines.org/community/index.php?topic=554500.0

[CyanGaming]

I don't know the mysql version

I tried the fixes in that thread, didn't seem to work for me

[shawnb61]

You can find a pretty good picture of all the important system versions on your forum, under Admin | Support and Credits.  It will look something like this:
    Support Information
    Version Information:
    Forum version: SMF 2.0.15
    Current SMF version: SMF 2.0.15
    GD version: bundled (2.1.0 compatible)
    Database Server: Percona
    MySQL version: 5.6.39-83.1
    PHP: 5.6.30
    Server version: Apache

You can also find all relevant info using phpinfo.  More here:
   https://wiki.simplemachines.org/smf/What_is_a_phpinfo()_file

[shawnb61]

Can you ask your host to disable mysql strict mode?   

[CyanGaming]

Version Information:
Forum version: SMF 2.0.15 (more detailed)
Current SMF version: SMF 2.0.15
GD version: 2.2.5
Database Server: MySQL
MySQL version: 5.7.23-0ubuntu0.18.04.1
PHP: 7.0.31-1+ubuntu18.04.1+deb.sury.org+1
Server version: Apache/2.4.29 (Ubuntu)

[CyanGaming]

Can you ask your host to disable mysql strict mode?

idek

[CyanGaming]

is that the only way to fix this?

[shawnb61]

Contacting your host would be the preferred way. 

If you had access to mysql either thru phpmyadmin or a prompt we could try something.  Do you have access to a mysql prompt?

[shawnb61]

The 2.1 code fix may be found here:
https://github.com/SimpleMachines/SMF2.1/pull/3262/files

It may work here.

I believe that root cause is that 5.7 has a stricter strict mode...

This may help with this query, but you may find others.  Which is why having your host disable it would be preferred.

[CyanGaming]

The 2.1 code fix may be found here:
https://github.com/SimpleMachines/SMF2.1/pull/3262/files

It may work here.

I believe that root cause is that 5.7 has a stricter strict mode...

This may help with this query, but you may find others.  Which is why having your host disable it would be preferred.

That fixed the issue, thank you!