SQL error only in production

I have the following query which works perfectly in my local environment. However when I deploy to production (Heroku) I get the error detailed below. Would love some help :-(

$tracks = DB::table('tracks as t')
                ->select(DB::raw('AVG(tt.time_listened) as avg, tt.track_id, t.id, t.track, t.count, t.id'))
                ->where([
                    ['t.user_id', $user_id],
                    ['tt.user_id', $user_id],
                ])
                ->Join('track_times as tt', 'tt.track_id', '=', 't.id')
                ->groupBy('tt.track_id')
                ->paginate(15);

Syntax error or access violation: 1055 'pfoe42ui3fzinf1p.t.id' isn't in GROUP BY (SQL: select AVG(tt.time_listened) as avg, tt.track_id, t.id, t.track, t.count, t.id from `tracks` as `t` inner join `track_times` as `tt` on `tt`.`track_id` = `t`.`id` where (`t`.`user_id` = 75 and `tt`.`user_id` = 75) group by `tt`.`track_id` limit 15 offset 0)

realrandyallen

7 years ago

Level 44

My guess is your local environment has a different version of MySQL than your production environment...those types of errors tend to pop up when you upgrade to MySQL 5.7+

If you wanted to fix it immediately you could try turning strict to false in config/database.php

'connections' => [
    'mysql' => [
        'strict' => false,
    ]
]

Although, I think it'd be better to leave strict on and change your query.

https://stackoverflow.com/questions/36228836/syntax-error-or-access-violation-1055-expression-8-of-select-list-is-not-in-gr

Tylk

7 years ago

Level 11

@mick79, check if it has to do how your DBMS is configured in production to handle the group by clause.

In SQL ANSI (the most portable one), you should include all selected fields in the group by clause. I know MySQL simplifies this sintax, but only in certain scenarios where it's very clear that the field in the group by clause is identifying the row as unique. I don't know your database structure, but I'd point in this direction.

Take a look at docs:

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

"MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.) "

I'd recommend you to store complex SQL as a VIEW. That way you would be encapsulating any view's complexity in the database (you can create a migration file for views too), so in your PHP code you just select from "vwVeryDescriptiveName". In my opinion, it's much cleanear and easy to understant what you want to retrieve.

Another good choice would be to create different models for each table you are joining, so you can chan these models in an eloquent way as any other relation.

Hope it helps

1 like

Mick79

7 years ago

Best Answer

Level 5

I had to add every column in my "select" to my "group by". This solved it.

1 like

Please or to participate in this conversation.