How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?

Question

My laravel eloquent is like this :

$products = Product::where('status', 1)
            ->where('stock', '>', 0)
            ->where('category_id', '=', $category_id)
            ->groupBy('store_id')
            ->orderBy('updated_at', 'desc')
            ->take(4)
            ->get();

When executed, there exist error like this :

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myshop.products.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from products where status = 1 and stock > 0 and category_id = 5 group by store_id order by updated_at desc limit 4)

How can I solve it?

score 67 · Answer 1 · answered Jul 18 '17 at 13:45

67

I had a similar Problem and solved it by disabling mysql strict mode in the database connection setting.

'connections' => [
    'mysql' => [
        // Behave like MySQL 5.6
        'strict' => false,

        // Behave like MySQL 5.7
        'strict' => true,
    ]
]

You can find even more configuration settings in this blog post by Matt Stauffer

answered Jul 18 '17 at 13:45

naabster

1,366
10
14

4

if we make 'strict' => false then it may give security bug – DPS Aug 28 '17 at 04:39
after a day wasted come here and this solved my problem – arun Dec 21 '17 at 11:17
@naabster thank you, you probably saved me a day trying to figure this out. Question: is there is any downside to not using strict here? Another comment mentioned the security bug. In my case raw SQL was working but when I went through Laravel (Query builder, 5.6) I got the "only full group" error. – PeterG Apr 05 '18 at 18:01
Took a while 'till I find this answer. Best solution. Thank you so much! Please, OP. Mark this as right answer. – PlayHardGoPro Oct 25 '19 at 12:35

score 19 · Answer 2 · answered Jul 01 '18 at 23:12

In folder config => database.php make sure mysql strict is false, like this

'mysql' => [
    'driver' => 'mysql',
    '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' => 'utf8',
    'collation' => 'utf8_general_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

if strict is true, make it false then clear config cash by run this command in cmd

php artisan config:clear

Thankyou, your `'strict' => false` suggestion works fine for me — Abid Ali, Jan 18 '21 at 06:01

score 17 · Answer 3 · answered Feb 10 '19 at 15:41

I solved this problem by adding the "modes" option and setting only the modes I want to be enabled in config => database.php

'mysql' => [
    ...
    'modes' => [
        'STRICT_ALL_TABLES',
        'ERROR_FOR_DIVISION_BY_ZERO',
        'NO_ZERO_DATE',
        'NO_ZERO_IN_DATE',
        'NO_AUTO_CREATE_USER',
    ],
],

See more details in this tutorial

score 14 · Answer 4 · answered May 04 '17 at 07:47

14

That's because latest versions of MySQL behave like most dbms already do regarding group by clauses; the general rule is

if you're using group by, all columns in your select must be either present in the group by or aggregated by an aggregation function (sum, count, avg and so on)

Your current query is grouping by store_id, but since you're selecting everything the rule above is not respected.

answered May 04 '17 at 07:47

Stefano Zanini

5,738
2
11
32

If I run this query : `select * from products where status = 1 and stock > 0 and category_id = 5 group by store_id order by updated_at desc limit 4` in mysql, it works. It seems that the error is therefore via eloquent laravel – samuel toh May 04 '17 at 08:55
1

I like this answer (which explains WHY this is happening) far more than simply 'turn off strict mode'! Changing the query seems more appropriate & 'local' than turning off a much more global setting. Thanks – Ben A. Hilleli May 01 '20 at 03:38

score 10 · Answer 5 · answered Apr 09 '19 at 11:42

10

set

'strict' => false

in your config/database.php file. In array connections => mysql =>

in my case I'm using mysql 5.7 Laravel 5.7

answered Apr 09 '19 at 11:42

Akbar Mirsiddikov

996
9
19

If it doesn't work, do a `php artisan config:clear` – Arne Oct 27 '20 at 14:22

score 8 · Answer 6 · answered Feb 24 '21 at 18:06

You should NOT disable strict or remove ONLY_FULL_GROUP_BY. The problem is that your query is ambiguous. That may not make a difference to your output, or it could cause huge problems. It's better for you to be sure.

A great explanation can be read on Percona (summed up below).

Problem

Consider the following situation:

+----+--------------------+---------+---------------------+
| id | page_url           | user_id | ts                  |
+----+--------------------+---------+---------------------+
|  1 | /index.html        |       1 | 2019-04-17 12:21:32 |
|  2 | /index.html        |       2 | 2019-04-17 12:21:35 |
|  3 | /news.php          |       1 | 2019-04-17 12:22:11 |
|  4 | /store_offers.php  |       3 | 2019-04-17 12:22:41 |
|  5 | /store_offers.html |       2 | 2019-04-17 12:23:04 |
|  6 | /faq.html          |       1 | 2019-04-17 12:23:22 |
|  7 | /index.html        |       3 | 2019-04-17 12:32:25 |
|  8 | /news.php          |       2 | 2019-04-17 12:32:38 |
+----+--------------------+---------+---------------------+

Now we want to issue a query to calculate the most visited pages. This is probably what you're used to writing:

SELECT page_url, user_id, COUNT(*) AS visits 
FROM web_log 
GROUP BY page_url 
ORDER BY COUNT(*) DESC;

But look at the results:

+-------------------+---------+--------+
| page_url          | user_id | visits |
+-------------------+---------+--------+
| /index.html       |       1 |      3 |
| /news.php         |       1 |      2 |
| /store_offers.php |       3 |      2 |
| /faq.html         |       1 |      1 |
+-------------------+---------+--------+

The query works, but it’s not really correct. It is easily understandable that page_url is the column of the grouping function, the value we are most interested in and we want to be unique for counting. Also, the visits column is good, as it’s the counter. But what about user_id? What does this column represent?

We grouped on the page_url so the value returned for user_id is just one of the values in the group. In fact, it was not only user to visit the index.html, as users 2 and 3 visited the page. So what should we make of that value? Is it the first visitor? Is it the last one?

We don’t know the right answer! The user_id column’s value is a random item of the group!

Solution

You need to consider if you need the values not used in the groupBy(). If not, then just use a select() to explicitly name the column you need.

If you do need that column not used in the groupBy(), use an aggregate function (like ANY_VALUE() or GROUP_CONCAT() or MAX()) as part of a Laravel selectRaw query. Then you can be sure that your query is giving you what you expect.

So in the above example, you could do:

SELECT page_url, ANY_VALUE(user_id), COUNT(*) AS visits 
FROM web_log 
GROUP BY page_url 
ORDER BY COUNT(*) DESC;

Or in Laravel:

WebLog::selectRaw('page_url', 'ANY_VALUE(user_id)', 'COUNT(*) AS visits')
->groupBy('page_url')
->orderBy('visits')
->get();

Shashi Gharti · Answer 7 · 2019-08-21T19:50:37.907

I solved it by setting modes in config/database.php file.

Set modes as follows:

'modes'  => [
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_ENGINE_SUBSTITUTION',
            ]

for mysql driver

'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
        'modes'  => [
            'ONLY_FULL_GROUP_BY',
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_ENGINE_SUBSTITUTION',
        ]
    ],

It worked by removing `'ONLY_FULL_GROUP_BY'` from the list. – Ibrahim Mohamed Aug 19 '19 at 11:24 — Ibrahim Mohamed, Aug 19 '19 at 11:24

score 4 · Answer 8 · answered Jul 30 '18 at 16:17

4

In the .env file ADD variable: DB_STRICT=false.

And REPLACE in file from the location: config/database.php, next codes 'strict' => true ON 'strict' => (env('DB_STRICT', 'true') === 'true' ? true : false).

good luck.

answered Jul 30 '18 at 16:17

amiron

693
9
10

I have done it in Lumen 8 but still not working, any other suggestion. Thanks. – Kamlesh Jun 10 '21 at 19:07

Dach0 · Answer 9 · 2020-05-03T11:37:46.380

What I did as a workaround and to prevent further security issues I make it happen like this:

 public function getLatestModels (){
        \DB::statement("SET SQL_MODE=''");
        $latestInserted = Glasses::with('store.deliveryType','glassesHasTags','glassesHasColors','glassesHasSizes','glassesHasImages','glassesBrand','glassesMaterial')->whereRaw("store_id in (select distinct store_id from glasses)")->groupBy('store_id')->orderBy('created_at')->take(8)->get();
        \DB::statement("SET SQL_MODE=only_full_group_by");

        return $latestInserted;
    }

this is a kind of combination of other answers. Also if you are using "use Illuminate\Support\Facades\DB;" you don't need backslashes in those DB statements above.

The only disadvantage here is that we are making three calls to db :(

p.s. As I see @Felipe Pena answer I guess the second statement is unnecessary

Mayank Pandeyz · Answer 10 · 2021-06-11T08:01:45.023

1

Check the query:

Product::where('status', 1)
            ->where('stock', '>', 0)
            ->where('category_id', '=', $category_id)
            ->groupBy('store_id')
            ->orderBy('updated_at', 'desc')
            ->take(4)
            ->get();

here you are grouping the data by store_id and fetching all columns in the result set which is not allowed. To solve it either select store_id or aggregate function on it or change the system variable sql_mode=only_full_group_by to SET sql_mode = ''.

Reference

To set this in Laravel try this:

'strict' => false

in your config/database.php file. In array connections => mysql =>

edited Jun 11 '21 at 08:01

answered May 04 '17 at 07:46

Mayank Pandeyz

24,624
3
35
55

2

If I run this query : `select * from products where status = 1 and stock > 0 and category_id = 5 group by store_id order by updated_at desc limit 4` in mysql, it works. It seems that the error is therefore via eloquent laravel – samuel toh May 04 '17 at 08:55
1

how to set sql mode in laravel? – Sameera K Feb 21 '19 at 02:07
how to change SET sql_mode = '' on shared server? – Kamlesh Jun 10 '21 at 19:04

score 1 · Answer 11 · answered Apr 04 '19 at 15:57

1

 #Have the following method in your helper file
if (!function_exists('set_sql_mode')) {
/**
 * @param string $mode
 * @return bool
 */
function set_sql_mode($mode = '')
{
    return \DB::statement("SET SQL_MODE=''");
}
}

Then call set_sql_mode(''); just before eloquent/query

answered Apr 04 '19 at 15:57

Godfrey Makori

59
3

1

This nukes the whole setting. You might want to extract only ONLY_FULL_GROUP_BY from it.. – Alex Jun 18 '19 at 17:05

score 1 · Answer 12 · answered Apr 26 '19 at 20:58

As said, set strict mode to false may give security bugs, what i am doing is to set sql_mode to empty before queries that require it. Note that it is a TEMPORARY change, once your connection is close (by laravel request) you will be set to original sql_mode=only_full_group_by (or beyond).

DB::statement("SET sql_mode = '' ");

Cheers, happy coding...

ps.: its not laravel fault, if you try to execute this query directly on your DB you will face same result. This work around works in mysql as well as first statement and again, will be a temporary session change, not permanent.

I am using Lumen 8. I have added this statement "DB::statement("SET sql_mode = '' ");" in constructor of every controller and it worked for me. Thanks dear. — Kamlesh, Jun 11 '21 at 16:58

score 0 · Answer 13 · answered Mar 04 '19 at 12:54

0

To select only aggregated columns, use the one of the raw methods provided by Laravel. For example:

Product::selectRaw('store_id')
        ->where('status', 1)
        ->groupBy('store_id')
        ->get();

answered Mar 04 '19 at 12:54

Yvan

1
1

score 0 · Answer 14 · answered Mar 07 '22 at 07:37

All these answers may solve your problem in short term

set

'strict' => false

in your config/database.php file. In array connections => mysql =>

But you have many problems in the future. for example, we set this config only in one of our microservice last year and last week our support team reported a strange bug.

The main reason for this bug is strict=false that we apply it on our config file.

when we want to insert a record to field this set 0 and didn't return or log any errors or exceptions. we have many customers with 0 data.

The second bad news is we need to test all last year's commits from the beginning of when we set this config.

You should know this config has many side effects. on your test scenarios and also on your architecture.

How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?

14 Answers14

Problem

Solution

Then call set_sql_mode(''); just before eloquent/query

Linked