About laravel solving mysql only_full_group_by problem

                                                                                                                                                     

The following column Laravel Tutorial will introduce to you how laravel solves the mysql only_full_group_by problem. I hope it will be helpful to friends in need!

After MySQL 5.7, only_full_group_by is enabled by default, which leads to more stringent SQL detection, which will lead to the following error being reported

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

There is no way to solve this problem Avoid detours and try one by one according to the methods found online

Solution ideas

View sql_mode

select @@GLOBAL.sql_mode;SELECT @@SESSION.sql_mode

First check and modify the mysql configuration file. My.cnf is very embarrassing. There is no ONLY_FULL_GROUP_BY in my. According to the client configuration, add [client] configuration

[mysqld]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Restart mysql, check sql_mode in the MySQL tool, and the displayed result is indeed the same as the configuration, but the program still reports the same error

After another search, I suddenly realized the problem of global session, which is the two statements querying sql_mode at the top of the article. I asked what the difference is.

mysql variable settings There are two ways, One is global configuration, which is global, and acts on the whole world; One is session configuration session, which only acts on the current connection

Will it happen? It is laravel that has set sql_mode in the current connection.

When the laravel program prints sql_mode

[client]sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

, it turns out that it is the program that has set it.

The first thing that comes to mind is mysql There is a strict mode in the configuration. I have always turned it on and set it to false. The problem was solved smoothly

$result = \DB::select('SELECT @@GLOBAL.sql_mode');
print_r($result);exit;
结果：
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

$result = \DB::select('SELECT @@SESSION.sql_mode');
print_r($result);exit;
结果：
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

This can solve the problem, but based on technology, I just want to figure out how it is written, and I don’t want to Set it to false directly,

Search for strict in the vendor/laravel/framework/src/ILLuminate/Database folder, and directly find the core code
• File: vendor/laravel/ framework/src/ILLuminate/Database/Connectors/MySqlConnector.php

'strict' => false,

The first judgment directly determines whether there is a modes configuration. If so, just use this

to do it

protected function setModes(PDO $connection, array $config){
    if (isset($config['modes'])) {
        $this->setCustomModes($connection, $config);
    } elseif (isset($config['strict'])) {
        if ($config['strict']) {
            $connection->prepare($this->strictMode($connection))->execute();
        } else {
            $connection->prepare("set session sql_mode='NO_ENGINE_SUBSTITUTION'")->execute();
        }
    }}

Test, solve the problem directly

In the spirit of searching for the root cause, then read on

If strict = true

will be set directly in the program The hard-coded sql_mode, laravel distinguishes mysql 8.0.11 from other versions

'modes' => ['STRICT_TRANS_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'ERROR_FOR_pISION_BY_ZERO', 'NO_AUTO_CREATE_USER', 'NO_ENGINE_SUBSTITUTION'],

Then if strict = false, directly set sql_mode to NO_ENGINE_SUBSTITUTION

protected function strictMode(PDO $connection)
{
    if (version_compare($connection->getAttribute(PDO::ATTR_SERVER_VERSION), '8.0.11') >= 0) {
        return "set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'";
    }

    return "set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'";
}

The problem is completely solved here

Final solution

$connection->prepare("set session sql_mode='NO_ENGINE_SUBSTITUTION'")->execute();

Retain strict = true, add the modes option, the parameters inside are the underlying configuration of laravel, but only remove ONLY_FULL_GROUP_BY

Summary: After taking a lot of detours and spending a lot of time, the problem was finally solved, and there was no need to modify any configuration of mysql

The above is the detailed content of About laravel solving mysql only_full_group_by problem. For more information, please follow other related articles on the PHP Chinese website!