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:

sudo mysql -u root -p mysql> SELECT @@global.sql_mode; 

(Then optionally copy the output to your notes somewhere in case you want to revert to those original settings later.)

And change the SQL Mode for your MySQL Server Instance:

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

(If you ever want to roll back, you could run something like mysql> SET GLOBAL sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; using the value you saved.)

---

A more permanent way (which will survive restarts of MySQL) would be using the MySQL configs. Go to /etc/mysql/my.cnf (or you may need to run sudo vim /etc/mysql/mysql.conf.d/mysql.cnf):

• Add a section for [mysqld] and right below it add the statement sql_mode = "" or something like sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION".

• Restart the MySQL service:

   sudo systemctl restart mysql 

(or sudo service mysql restart)

See also https://dba.stackexchange.com/a/113153/18098

Answer 2

In laravel with MySql go to file config/database.php and it change in array MySql mode strict to false.

'connections' => [     'mysql' => [         'driver' => 'mysql',         'host' => env('DB_HOST', '127.0.0.1'),         'port' => env('DB_PORT', '3306'),         'database' => env('DB_DATABASE', 'forge'),         'username' => env('DB_USERNAME', 'forge'),         'password' => env('DB_PASSWORD', ''),         'unix_socket' => env('DB_SOCKET', ''),         'charset' => 'utf8mb4',         'collation' => 'utf8mb4_unicode_ci',         'prefix' => '',         'strict' => false, //from true         'engine' => null,     ], ],