-
Notifications
You must be signed in to change notification settings - Fork 10.7k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
query that worked in Laravel 5.2 gives me error in Laravel 5.3 #14997
Comments
I'm having the same issue after upgrading from 5.2 to 5.3. Everything works fine on 5.2. |
Codetimeup post your query here so Talyor can look at it |
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. |
running toSql on 5.2 I get this: and exactly the same sql query on 5.3 they are identical: |
I posted about this on stackoverflow and someone suggested that I run the join according to new 5.3 documentation. Here is new query which also does not work.
this new query fails and gives me
if I run toSql on it output is this: |
I updated the query so its now according to documentation and it looks like this:
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: |
All queries that have selectRaw or DB::Raw is having this issue for me. toSql method gave the same output in both versions of Laravel(5.2 and 5.3), I'll post an example of my code when i get home. |
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 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 |
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 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. |
Ok, Laravel can also enable this mode when querying, check your And check if inside the 'strict' => false, If this is set to true then it'll add the Update 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: 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... |
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. |
damn it was so simple :) well great it solved now |
Solved it, thanks fernandobandeira. |
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. |
This was very helpfull thread ! |
A lot of thanks!! <3 |
Thanks @fernandobandeira, solved it quickly thanks to you. =) Cheers! |
Thanks @fernandobandeira |
What is the context for this change ?laravel/laravel@f237656 |
@chadlinden ask @Adam14Four. |
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. |
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, |
Thanks @fernandobandeira & @Adam14Four |
@fernandobandeira thanks for the tip! Helped me as well ;) |
I'm having a similar issue, but in a different project than Laravel (it uses PDO). And it seems that it only raises the error when there are no matched records found. I'm still looking into this, but thought that it might help to solve the main problem, rather than disabling the strict mode. |
Same, I'm having a similar issue too changing config/database.php strict to false did the job, |
Yep changing strict to false fixed my error also, thanks @fernandobandeira |
@fernandobandeira you saved my life |
go to folder > config/database.php > set 'strict' => false, |
Just caught me out! ended up just adding to GROUP BY like @nikocraft on #14997 (comment) |
This actually worked for me in L5.5 go to folder > config/database.php > set 'strict' => false, |
I changed config/database.php 'strict' to false, but now, no records are returned |
If you want to leave Full instructions:
and register it in |
@fernandobandeira |
Hi, initially i had done strict => false, and it worked. When i changed i tried it in a different file, the same code does not work. What could be the problem? |
@JYvman I do not know, please try to improve your question giving more context, versions used, repo for testing, etc. Also try to keep your versions up-to-date as much as possible. |
This query works in 5.2:
my gallery table looks like this
id, title, hash, user_id, published, views, created_at, updated_at
I am trying to select all galleries that have votes, when I run this in 5.3 I get this
The text was updated successfully, but these errors were encountered: