SOLVED: Set Global sql_mode does not persist even when entered into my.ini
I need to remove the "only_full_group_by " I have read the various entries on the forum, and set the my.ini accroding as below.
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION'
However, when I check the variables in PhpMyAdmin, it does not persist AND my queries are still showing the same classic errror "this is incompatible with sql_mode=only_full_group_by" as before. Same result if I check throught the CLI.
How do I remove "only_group_by"
Many thanks !
@vincej Ok, I found the problem and solution. I use a php framework, called Laravel. Within Laravel, there is a database.config file. Within the file there is a "strict" setting within the mysql array. The default is "true". Set this to "false" and Laravel will obey what is in my.ini.
'mysql' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'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' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : ,
Quite simple really... When in your phpMyAdmin go to variables and search for sql mode. As soon as you do that you will get this code
all you need to do then is click on edit sql mode on the LHS and then delete ONLY_FULL_GROUP_BY, include comma and then click on save.
That's it! it should end up like this every time .....
I'm using mysql-5.7.33-winx64. I hope that helps you folks. I found this out after inserting a new script that used an older version of MSQL and got this error message...
Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'newsletter.t1.date_sent' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by