SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated

Question

when i got upgraded my ubuntu from 15.10 to 16.04 i have this erro in my yii2 project

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3
of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'iicityYii.opportunity_conditions.money' which is not functionally dependent 
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The SQL being executed was:

SELECT SUM(oc.money),op.id,oc.money,
            op.mantaghe,
            op.`time`, op.`id`, `op`.`logo`,
           `pd`.`user_id`, `op`.`name`, 
           `pd`.`co_name`, `op`.`address`, 
           `op`.`project_type_id`, `op`.`state_id`
FROM `opportunity` op 
INNER JOIN `profile_details` pd  ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON   op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

how to solve my problem ?

Answer 1

Run the:

sudo mysql -u root -p

And change the SQL Mode for your MySQL Server Instance:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

---

Another way would be using the MySQL configs. Go to /etc/mysql/my.cnf:

• add a section for [mysqld] and right below it add the statement sql_mode = ""

• restart the mysql service:

  sudo systemctl restart mysql