config - How to swith off Laravel11 + Mysql ONLY_FULL_GROUP_BY - Stack Overflow

admin管理员组

文章数量:1122846

As I found out there is strict = true in config.database.php which causes the problem with ONLY_FULL_GROUP_BY mode for Mysql. This mode does not allow to make simple group by and add some weird restrictions on sql queries. I dont want to switch strict mode in database off I only need to remove ONLY_FULL_GROUP_BY option. How can I do it in Laravel 11. I suppose it is possible in config/database.php. Thanks for help.

EDIT: This is what ChatGPT gave me as answer for config/database.php

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DB_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'laravel'),
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
    'sslmode' => env('DB_SSLMODE', 'required'),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => env('DB_CHARSET', 'utf8mb4'),
    'collation' => env('DB_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'),
         PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''))",
    ]) : [],
],

As I found out there is strict = true in config.database.php which causes the problem with ONLY_FULL_GROUP_BY mode for Mysql. This mode does not allow to make simple group by and add some weird restrictions on sql queries. I dont want to switch strict mode in database off I only need to remove ONLY_FULL_GROUP_BY option. How can I do it in Laravel 11. I suppose it is possible in config/database.php. Thanks for help.

EDIT: This is what ChatGPT gave me as answer for config/database.php

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DB_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'laravel'),
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
    'sslmode' => env('DB_SSLMODE', 'required'),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => env('DB_CHARSET', 'utf8mb4'),
    'collation' => env('DB_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'),
         PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''))",
    ]) : [],
],

• mysql
• config
• laravel-11

Share Improve this question Follow edited Nov 21, 2024 at 10:35 Čamo asked Nov 21, 2024 at 10:18 ČamoČamo 4,1531818 gold badges7777 silver badges138138 bronze badges 11

• 1 This mode does not allow to make simple group by and add some weird restrictions on sql queries, the optimal way is to fix the queries. I do not recommend disabling sql_mode ONLY_FULL_GROUP_BY . – Ergest Basha Commented Nov 21, 2024 at 10:23
• No way, the query is so complicated and I really dont understand this mode. Many years everythig worked without it and everybody was ok with it. Imagin you have some old project with huge amouth sql queries which stop working bacause ot this. This is my case. Its nonsense. – Čamo Commented Nov 21, 2024 at 10:27
• 'EDIT: This is what ChatGPT gave me as answer for config/database.php' - this question can be closed then? – P.Salmon Commented Nov 21, 2024 at 10:38
• @Čamo because on the old MySQL versions it was disabled by default, in newer versions it is enabled by default. With ONLY_FULL_GROUP_BY you would have arbitrary results for columns not part of the group by clause or of an aggregate function. Imagin you have some old project with huge amouth sql , yes, it is a pain I work with ViciDial which is one of those old projects. Why don't you disable from the mysql configuration ? – Ergest Basha Commented Nov 21, 2024 at 10:44
• 3 Best practice would be to rewrite code to comply with SQL(not just mysql) not fiddle with set up and risk non determinate outcomes.If you do then don't be surprised to see different results in comparison tests – P.Salmon Commented Nov 21, 2024 at 16:48

 |  Show 6 more comments

2 Answers 2

Sorted by: Reset to default 1

You can set the sql_mode option in your MySQL Server's my.cnf file.

Then you can skip the PDO::MYSQL_ATTR_INIT_COMMAND in your connection profile.

But the setting in the my.cnf file will affect all clients who use this MySQL Server, not just your Laravel application. And you may not have permission to change the options at the server level, depending on your environment.

There's no option to customize sql_mode for a certain client or a certain database or table. It's either global for all clients, or per session, using the PDO::MYSQL_ATTR_INIT_COMMAND as you show.

I agree with the comments that it's a bad practice to use queries in your application that only work if ONLY_FULL_GROUP_BY is disabled. Other brands of SQL database don't permit such queries at all (except for SQLite). See my answer to Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause for an explanation.

Finaly I found a solution which works better.

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'laravel'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'sslmode' => env('DB_SSLMODE', 'required'),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'modes' => [  // This rewrites above strict config strict in MySqlConnector.php
                //'ONLY_FULL_GROUP_BY',
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_ENGINE_SUBSTITUTION',
            ],
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

Then in MySqlConnector.php script will join this options together....

本文标签： configHow to swith off Laravel11Mysql ONLYFULLGROUPBYStack Overflow