SQL ERROR... Is this a bug ?

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Anti-Spam Guide
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Incoming
Registered User
Posts: 97
Joined: Fri Mar 13, 2015 7:35 am
Location: Bordeaux, France

Re: SQL ERROR... Is this a bug ?

Post by Incoming » Thu May 12, 2016 10:15 pm

After some googling, it seems that it could be a MySQL 5.7 "ONLY_FULL_GROUP_BY" default problem... So phpBB isn't MySQL 5.7 compatible? Do you have a patch for this?

https://dev.mysql.com/doc/refman/5.5/en ... l_group_by

User avatar
david63
Jr. Extension Validator
Posts: 14754
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: SQL ERROR... Is this a bug ?

Post by david63 » Thu May 12, 2016 10:22 pm

Incoming wrote:After some googling, it seems that it could be a MySQL 5.7 "ONLY_FULL_GROUP_BY" default problem... So phpBB isn't MySQL 5.7 compatible? Do you have a patch for this?

https://dev.mysql.com/doc/refman/5.5/en ... l_group_by
If that is the case then you should create a bug report
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

Incoming
Registered User
Posts: 97
Joined: Fri Mar 13, 2015 7:35 am
Location: Bordeaux, France

Re: SQL ERROR... Is this a bug ?

Post by Incoming » Thu May 12, 2016 11:04 pm

OK I've created this, I hope it could help.

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

User avatar
cprgolds
Registered User
Posts: 446
Joined: Thu Oct 11, 2007 5:49 am
Location: Portland, Oregon

Re: SQL ERROR... Is this a bug ?

Post by cprgolds » Fri May 13, 2016 1:22 am

You could either revert to an earlier version of mySQL or edit your configuration file (probably /etc/my.cnf) to disable the FULL_GROUP option.

I would choose reverting the the earlier version and wait for phpBB to catch up.

Incoming
Registered User
Posts: 97
Joined: Fri Mar 13, 2015 7:35 am
Location: Bordeaux, France

Re: SQL ERROR... Is this a bug ?

Post by Incoming » Fri May 13, 2016 4:51 am

Is it a big work fix for the phpbb team to fix this? I migrated on 05/05 and will loose a huge amount of data. But if it's the only one solution...

I will not disable the sql mode as it appears to be worst than everything (it's like disable the SElinux policy...).

Waiting for an "official" guy from the team before move.

lrgrthnlf
Registered User
Posts: 57
Joined: Fri Jun 14, 2002 7:29 am
Location: Melbourne, Australia

Re: SQL ERROR... Is this a bug ?

Post by lrgrthnlf » Fri Dec 16, 2016 7:42 am

I've noticed this recently on my board (phpBB 3.1.10, MySQL(i) 5.7.16-1, php 5.6.26-1) when calling search.php..

Code: Select all

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

SQL

SELECT DISTINCT SQL_CALC_FOUND_ROWS t.topic_id FROM phpbb3_topics t, phpbb3_posts p WHERE MATCH (p.post_subject) AGAINST ('+SEARCHSTRING ' IN BOOLEAN MODE) AND t.topic_id = p.topic_id AND p.forum_id NOT IN (2, 4, 5, 6, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 45, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 64, 65, 66, 67, 68, 69) AND ((p.forum_id NOT IN (30, 9, 34, 33, 35, 36, 50, 25, 45, 2, 24, 8, 11, 15, 53, 54, 12, 16, 27, 13, 17, 23, 38, 39, 40, 41, 47, 48, 49, 52, 51, 26, 21, 22, 28, 5, 29, 4, 55, 56, 18, 57, 61, 59, 58, 60, 62, 64, 65, 66, 67, 68, 69, 31, 6, 37, 42, 10, 19, 43, 32) AND p.post_visibility = 1) OR p.forum_id = 14) AND p.post_id = t.topic_first_post_id ORDER BY t.topic_title DESC LIMIT 250

BACKTRACE

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

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

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

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

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 261
CALL: phpbb\db\driver\mysql_base->_sql_query_limit()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 321
CALL: phpbb\db\driver\driver->sql_query_limit()

FILE: [ROOT]/phpbb/search/fulltext_mysql.php
LINE: 573
CALL: phpbb\db\driver\factory->sql_query_limit()

FILE: [ROOT]/search.php
LINE: 590
CALL: phpbb\search\fulltext_mysql->keyword_search()
Anyone fix this when it's happened?

User avatar
david63
Jr. Extension Validator
Posts: 14754
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: SQL ERROR... Is this a bug ?

Post by david63 » Fri Dec 16, 2016 8:15 am

lrgrthnlf wrote:I've noticed this recently on my board (phpBB 3.1.10, MySQL(i) 5.7.16-1, php 5.6.26-1) when calling search.php..

Code: Select all

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

SQL

SELECT DISTINCT SQL_CALC_FOUND_ROWS t.topic_id FROM phpbb3_topics t, phpbb3_posts p WHERE MATCH (p.post_subject) AGAINST ('+SEARCHSTRING ' IN BOOLEAN MODE) AND t.topic_id = p.topic_id AND p.forum_id NOT IN (2, 4, 5, 6, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 45, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 64, 65, 66, 67, 68, 69) AND ((p.forum_id NOT IN (30, 9, 34, 33, 35, 36, 50, 25, 45, 2, 24, 8, 11, 15, 53, 54, 12, 16, 27, 13, 17, 23, 38, 39, 40, 41, 47, 48, 49, 52, 51, 26, 21, 22, 28, 5, 29, 4, 55, 56, 18, 57, 61, 59, 58, 60, 62, 64, 65, 66, 67, 68, 69, 31, 6, 37, 42, 10, 19, 43, 32) AND p.post_visibility = 1) OR p.forum_id = 14) AND p.post_id = t.topic_first_post_id ORDER BY t.topic_title DESC LIMIT 250

BACKTRACE

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

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

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

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

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 261
CALL: phpbb\db\driver\mysql_base->_sql_query_limit()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 321
CALL: phpbb\db\driver\driver->sql_query_limit()

FILE: [ROOT]/phpbb/search/fulltext_mysql.php
LINE: 573
CALL: phpbb\db\driver\factory->sql_query_limit()

FILE: [ROOT]/search.php
LINE: 590
CALL: phpbb\search\fulltext_mysql->keyword_search()
Anyone fix this when it's happened?
That is well documented issue, both on here and in a Google search, with MySql 5.7
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

User avatar
WelshPaul
Registered User
Posts: 247
Joined: Tue Aug 19, 2014 2:09 pm

Re: SQL ERROR... Is this a bug ?

Post by WelshPaul » Fri Dec 16, 2016 3:40 pm

Never mind, just realized this was an old post bumped. :roll:

lrgrthnlf
Registered User
Posts: 57
Joined: Fri Jun 14, 2002 7:29 am
Location: Melbourne, Australia

Re: SQL ERROR... Is this a bug ?

Post by lrgrthnlf » Thu Dec 22, 2016 12:04 am

david63 wrote:That is well documented issue, both on here and in a Google search, with MySql 5.7
Google is what brought me here to this thread, as I was looking for SQL statements similar to the one that was erroring on my board. So for the benefit of others who land here from Google, perhaps, maybe, linking to the answer might help?

Using: site:phpbb.com mysql 5.7 I find: So which is the proper fix?
... adding "p.post_time" after "DISTINCT SQL_CALC_FOUND_ROWS p.post_id"?
... or removing DISTINCT?
... or changing the MySQL server configuration?

db/driver/mysqli.php seems to fiddle with session sql_mode, so I've patched it to find the evil, so to speak.

Code: Select all

                if ($this->db_connect_id && $this->dbname != '')
                {
                        @mysqli_query($this->db_connect_id, "SET NAMES 'utf8'");

                        // enforce strict mode on databases that support it
                        if (version_compare($this->sql_server_info(true), '5.0.2', '>='))
                        {
                                $result = @mysqli_query($this->db_connect_id, 'SELECT @@session.sql_mode AS sql_mode');
                                if ($result !== null)
                                {
                                        $row = @mysqli_fetch_assoc($result);

                                        $modes = array_map('trim', explode(',', $row['sql_mode']));
                                }
                                else
                                {
                                        $modes = array();
                                }
                                @mysqli_free_result($result);

                                // TRADITIONAL includes STRICT_ALL_TABLES and STRICT_TRANS_TABLES
                                if (!in_array('TRADITIONAL', $modes))
                                {
                                        if (!in_array('STRICT_ALL_TABLES', $modes))
                                        {
                                                $modes[] = 'STRICT_ALL_TABLES';
                                        }

                                        if (!in_array('STRICT_TRANS_TABLES', $modes))
                                        {
                                                $modes[] = 'STRICT_TRANS_TABLES';
                                        }
                                }


                                $find_the_evil = array_search('ONLY_FULL_GROUP_BY',$modes);
                                if ($find_the_evil !== false)
                                {
                                        unset($modes[$find_the_evil]);
                                        $modes = array_values($modes);
                                }

                                $mode = implode(',', $modes);

                                @mysqli_query($this->db_connect_id, "SET SESSION sql_mode='{$mode}'");
                        }
                        return $this->db_connect_id;
                }

lrgrthnlf
Registered User
Posts: 57
Joined: Fri Jun 14, 2002 7:29 am
Location: Melbourne, Australia

Re: SQL ERROR... Is this a bug ?

Post by lrgrthnlf » Mon Jan 01, 2018 5:00 am

Noticed this issue on my board again, and had to re-apply the mysqli driver patch to "find the evil" :)

Given I'd think MySQL 5.7 would be fairly common now, is there a better solution now?

User avatar
Mick
Support Team Member
Support Team Member
Posts: 20134
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: SQL ERROR... Is this a bug ?

Post by Mick » Mon Jan 01, 2018 9:41 am

I don’t think you’ll get very far replying to topics from over a year ago. Please start a new topic by filling out the Support Request Template and posting it back in the new topic to enable us to assist you better.
"The more connected we get the more alone we become" - Kyle Broflovski

There are no ‘threads’ in phpBB, they are topics.

User avatar
WelshPaul
Registered User
Posts: 247
Joined: Tue Aug 19, 2014 2:09 pm

Re: SQL ERROR... Is this a bug ?

Post by WelshPaul » Mon Jan 01, 2018 4:05 pm

Is support running on for phpBB 3.1.x? I thought it was EOL come December 2017 and that support would come to an end?

User avatar
Mick
Support Team Member
Support Team Member
Posts: 20134
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: SQL ERROR... Is this a bug ?

Post by Mick » Tue Jan 02, 2018 10:10 am

https://www.phpbb.com/about/release/
“Release & Update Policy” wrote:End of Life EOL occurs to a branch 24 months after its first stable (3.x.0) release. When this occurs we:

Strongly recommend you upgrade your version of phpBB to the latest stable release.
No longer patch security issues.
No longer make any releases of this branch.
No longer accept new or updated contributions (styles/extensions/etc) for this branch in the customisation database.
Begin actively purging documentation for this branch of phpBB.
Continue to provide support past EOL, with at least six months notice prior to dropping support.
"The more connected we get the more alone we become" - Kyle Broflovski

There are no ‘threads’ in phpBB, they are topics.

Locked

Return to “[3.1.x] Support Forum”

Who is online

Users browsing this forum: No registered users and 25 guests