Mysql 5.7.9 issues

[DoYouSpeakWak??]

Support Request Template
What version of phpBB are you using? phpBB 3.1.6 and 3.2.0-dev.
What is your board's URL? http://demo.at.home
Who do you host your board with? me
How did you install your board? I used the download package from phpBB.com
What is the most recent action performed on your board? Fresh Install
Is registration required to reproduce this issue? No
Do you have any MODs installed? No
Do you have any extensions installed? No
What styles do you currently have installed? prosilver
What language(s) is your board currently using? english
Which database type/version are you using? MySQL 5.7.9
What is your level of experience? Experienced with PHP and phpBB
What actions did you take (updating your board; installing a MOD, style or extension; etc.) prior to this problem becoming noticeable? Fresh install.
Please describe your problem.

When the post number is pressed.

Example.
http://domain.com/forum/search.php?author_id=2&sr=posts

This error is shown.

Code: Select all

General Error
SQL ERROR [ mysqli ]

Expression #1 of ORDER BY clause is not in SELECT list, references column 'psy.p.post_time' which is not in SELECT list; this is incompatible with DISTINCT [3065]

SQL

SELECT DISTINCT SQL_CALC_FOUND_ROWS p.post_id FROM phpbb_posts p WHERE p.poster_id = 2 AND (p.post_visibility = 1 OR p.forum_id IN (1, 2)) ORDER BY p.post_time DESC

BACKTRACE

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

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 855
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php
LINE: 193
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 329
CALL: phpbb\db\driver\mysqli->sql_query()

FILE: [ROOT]/phpbb/search/fulltext_native.php
LINE: 1197
CALL: phpbb\db\driver\factory->sql_query()

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

I tried narrowing down the problem. But its clearly related to mysql 5.7.x series, which is the new stable from myql. When replaced with the 5.6.X series the problem goes away.

Im hoping this will be fixed in the next release of 3.1.x and fixed before 3.2.x is released. Any code fix before then would be greatly appriciated.

[david63]

Would this not be better in the bug tracker?

[DoYouSpeakWak??]

Good idea.

https://tracker.phpbb.com/browse/PHPBB3-14300

[ocs_foundation]

This fix worked for me.

Code: Select all

IN ./includes/search/fulltext_native.php

FIND
    DISTINCT SQL_CALC_FOUND_ROWS p.post_id
ADD AFTER
    , p.post_time

IN ./phpbb/search/fulltext_native.php

FIND
    DISTINCT SQL_CALC_FOUND_ROWS p.post_id
ADD AFTER
    , p.post_time