'How to disable only_full_group_by option in Laravel

I am new to laravel and I am having an issue with DB problem.

I have disabled 'only_full_group_by' sql_mode by editing /etc/mysql/my.cnf file. And I checked sql_mode for both global and session using SELECT @@GLOBAL.sql_mode; and SELECT @@SESSION.sql_mode; and confirmed that sql_mode no longer has only_full_group_by.

However, when I make a request through postman, it gives me the error saying this is incompatible with sql_mode=only_full_group_by.

I am so confused. Why do I get this error even after I changed sql_mode? Am I doing something wrong?

Any suggestion or advice would be appreciated.

Thank you.

SQL using toSql()

select A.* 
from `A` 
inner join `B` on `A`.`id` = `B`.`a_id` 
inner join `C` on `C`.`id` = `B`.`c_id` 
group by `A`.`id` having COUNT(A.id) > 0;
phpmysqllaravel-5mysql-5.7


Solution 1:[1]

This is the wrong way of going about this. Rather than turning off only_full_group_by mode, you should be fixing your query so that it doesn't break MySQL:

SELECT a1.*
FROM A a1
INNER JOIN
(
    SELECT A.id
    FROM A
    INNER JOIN B
        ON A.id = B.a_id 
    INNER JOIN C
        ON C.id = B.c_id
    GROUP BY A.id
    HAVING COUNT(A.id) > 0
) a2
    ON a1.id = a2.id;

I don't know why your attempts to turn off strict mode failed, but you should not be relying on it in any case.

Solution 2:[2]

To answer the original question, if you want to disable this in Laravel you have to change the 'strict' value to false in your database configuration in config/database.php.

'connections' => [
...

    'mysql' => [
    ...
        'strict' => false,
        ...

    ],

]

Solution 3:[3]

In Laravel, you could do this on runtime instead of making a global setting

//disable ONLY_FULL_GROUP_BY
DB::statement("SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");

//Your SQL goes here - The one throwing the error (:

//re-enable ONLY_FULL_GROUP_BY
DB::statement("SET sql_mode=(SELECT CONCAT(@@sql_mode, ',ONLY_FULL_GROUP_BY'));");

Solution 4:[4]

Add this modes to config/database.php

'mysql' => [
 ...
        'modes' => [
            'STRICT_ALL_TABLES',
        ],
],

If you set 'strict' => true. Some sql exception will not throw like sql data integrity exception.

Solution 5:[5]

You can disable that on current mysql session

DB::statement("SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))");

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Worthwelle
Solution 3 Paul T.
Solution 4 thebvg1ne-zoren
Solution 5 jaber sabzali

Related Questions