Mysql Question posted in
The official MySQL documentation can be found here.

How to swith off Laravel11 + Mysql 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', ''))",
    ]) : [],
],
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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....


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search