I upgraded my system from MySQL 5.4 to 5.7.17 with PHP for a web application I’m working on. I have a query that is dynamically created, and when run in older versions of MySql it works fine. Since upgrading to 5.7.17 I get this error:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xxx.tablenamehere’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This is my query:

$sql = <<<SQL
                    SELECT o.appraisal_order_id, o.requestor_id, c.org_name
                        from $dbom.appraisal_orders o 
                            inner join $dbom.requestor c on o.requestor_id = c.requestor_id
                                where o.provider_id =%1% group by c.requestor_id
SQL;
            
            $rtn = $db->GetRows($sql, $UID);
            return $rtn;

So I tried to alter the query a few times, run it again but there was no luck. Then I found a solution and decide to give it a try. Logged in to MySQL server and I ran these 2 statements that I found on stackoverflow forums. And it works.

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

or in Mysql/my.ini, you can add this sql_mode line in mysqld section, then restart MySQL server.

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION