framework: query that worked in Laravel 5.2 gives me error in Laravel 5.3

Ok, Laravel can also enable this mode when querying, check your config/database.php

And check if inside the mysql settings there is one that is like:

'strict' => false,

If this is set to true then it’ll add the ONLY_FULL_GROUP_BY when querying.

Update Just some trivia, strict enables these modes when querying:

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

Instead of disabling strict what you could do is pass an array to the config, enabling only the modes that you want:

'modes' => [
    'NO_ZERO_DATE',
     'ONLY_FULL_GROUP_BY',
],

The above example enables both the group by and no zero date when querying.

Briefly with strict mode on you will have to: 1 - Add all columns to group by. 2 - Won’t be able to use date’s such as 0000-00-00 00:00:00 3 - Fields like boolean will throw fatal if you pass something that isn’t a boolean value, like 1, before it would convert it to true and save, with strict it fails. 4 - You’ll get an error if you divide a field by 0 (or another field that has 0 as value).

And etc, notice that in previous versions of Laravel when creating timestamps the migration added the default value 0000-00-00 00:00:00 to the fields, so remember to check that if you enable this mode after upgrading from an older version, also some packages may not work with it enabled yet since it’s quite a new thing…

To be completely honest, I don’t remember exactly what the details were, but it was some sort of data-loss problem. With strict mode disabled it was possible for operations to fail silently and end up with incorrect data stored in the database with no error or warning to the calling code.

It was determined that the default should be set to the safest setting since it is easy for devs to change if necessary. However, I would recommend leaving it on and updating code to work with strict mode enabled rather than just disable it.

This is pretty strange actually, because this is a sql error but both 5.2 and 5.3 are generating the same query, you probably have ONLY_FULL_GROUP_BY inside the SQL_MODE variable in your DB.

And this causes the MariaDB in your case to request that all the columns that are in the select (galeries.*) must also be in your group by.

Of course either adding manually all of these columns to your groupBy(), or removing ONLY_FULL_GROUP_BY from your SQL_MODE should fix your query but it’s strange that 5.2 is behaving differently as you stated.

Also i checked now and on the master branch this is set to true on laravel/laravel. I’ll create a PR there an link with this issue.

go to folder > config/database.php > set ‘strict’ => false,

damn it was so simple 😃 well great it solved now

A lot of thanks!! ❤️

This was very helpfull thread !

Same problem. It was working on my machine with mysql but not on cloud with maria db. Anyways its solved by solution of fernandobandeira. Thank you.

Solved it, thanks fernandobandeira.

@fernandobandeira you saved my life

Yep changing strict to false fixed my error also, thanks @fernandobandeira

I run this SELECT @@sql_mode in my database and got this NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION, as you can see ONLY_FULL_GROUP_BY is not there.

I had to do this but its not eleqant and I dont like it, however it works: groupBy(‘gallery.id’, ‘gallery.title’, ‘gallery.hash’, ‘gallery.user_id’, ‘gallery.published’, ‘gallery.views’ , ‘gallery.created_at’, ‘gallery.updated_at’)

how do I turn of ONLY_FULL_GROUP_BY, if it’s on it’s strange that it’s working for 5.2 since it’s connecting to the same database as 5.3

I updated the query so its now according to documentation and it looks like this:

            $galleries = Gallery::with(array(
                    'images' => function ($query) {
                        $query->orderBy('order', 'asc');
                    }
                ))
                ->with('votes')
                ->leftJoin('votes',function($query){
                             $query->on('votes.votable_id','gallery.id');
                 })->selectRaw(
                    'gallery.*, count(case votes.status when "upvote" then 1 else null end) - count(case votes.status when "downvote" then 1 else null end) as points'
                )
                ->where('votes.votable_type','App\Gallery')
                ->groupBy('gallery.id')
                ->orderBy('points', 'desc')
                ->published()->orderBy('gallery.created_at', 'desc')->paginate(20);

it produces this sql:

“select gallery.*, count(case votes.status when “upvote” then 1 else null end) - count(case votes.status when “downvote” then 1 else null end) as points from ‘gallery’ left join ‘votes’ on ‘votes’.‘votable_id’ = ‘gallery’.‘id’ where ‘votes’.‘votable_type’ = ? and ‘published’ = ? group by ‘gallery’.‘id’ order by ‘points’ desc, ‘gallery’.‘created_at’ desc”

and if I put paginate(20) back into query I get the same error as before: SQLSTATE[42000]: Syntax error or access violation: 1055 ‘images.gallery.title’ isn’t in GROUP BY

Thanks @fernandobandeira & @Adam14Four

I’ve updated my comment to add more info about this mode,

I recommend that if you encounter this issue you should check which mode is causing your issue and then decide if you should leave it on or not,

What is the context for this change ?https://github.com/laravel/laravel/commit/f237656c687bec2c53a09c9eefbe9c897aacdf1c

Thanks @fernandobandeira, solved it quickly thanks to you. =) Cheers!

Could you post the result of the method toSql() of this query, both on 5.2 and 5.3 instead of the paginate(30)?

Seems that the 5.3 isn’t including all the columns on the groupBy, maybe it doesn’t include the ones from the selectRaw only, i wanna see how the 5.2 does.