MySQL error is thrown when laravel pagination is used

Question

I have a database view created and when I use laravel pagination against this view, it throws:

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select count(*) as aggregate from `parameter_log_site_detail` where `site_id` = EPE)

But this error pops up only in live server. Pagination works fine in local server.

DB View: (parameter_log_site_detail)

select `t`.`site_id` AS `site_id`,cast(`t`.`logged_at` as date) AS `logged_on`,`t`.`daily_generation` AS `daily_generation`,`t`.`reading` AS `tot_reading` from `parameter_log_tab` `t` order by cast(`t`.`logged_at` as date) desc

Model:

public function scopeSiteDailyReadings($query)
{
    return $query->from('parameter_log_site_detail');
}

Controller:

$generations = EnergyGeneration::siteDailyReadings()->where('site_id', $site_id)->orderBy('logged_on', 'desc')->paginate(15);

Live server info

MySQL Server version: 5.6.37 - MySQL Community Server (GPL)
PHP version: 5.6.30
Laravel version: 5.4

Local server info (where it runs normally)

MySQL Server version: 5.7.19 - MySQL Community Server (GPL)
PHP version: 7.1.7
Laravel version: 5.4

Could someone explain what can be the reason for this? I read about ONLY_FULL_GROUP_BY. But cannot figure out is it the reason since the server version is 5.6.*.

did you check if `ONLY_FULL_GROUP_BY` is enabled on your server ? — jaysingkar, Aug 26 '17 at 18:22
@jaysingkar for live server `SELECT @@sql_mode` returns `NO_ENGINE_SUBSTITUTION` whereas in local server, it returns `ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_...` — Lakmal Premaratne, Aug 26 '17 at 18:29
from your op, it seems that `ONLY_FULL_GROUP_BY` is disabled in your server whereas it is enabled in your local server. Though it should have been other way around as given MySQL docs. :( .. Could you try enabling the `ONLY_FULL_GROUP_BY` on your server. I know, that I'm not helping you much here, but I'm not able to see any other issue — jaysingkar, Aug 26 '17 at 18:38

Bill Karwin · Accepted Answer · 2017-08-26T18:42:32.040

It's possible to set SQL mode ONLY_FULL_GROUP_BY in MySQL 5.6, but it's not set by default (see https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html).

Aha, I see your comment appeared above, you have confirmed that ONLY_FULL_GROUP_BY is set on your local server (MySQL 5.7).

I think you have misstated something in your problem description. You should get the error on your local server, but not on the live server, if local has ONLY_FULL_GROUP_BY and live does not.

I suggest you make sure to use the same version in development as the version you use in production, and also match the same SQL mode. This will prevent confusion during development.

I make the same suggestion about version of PHP. If you use some new PHP 7 features in development, and then deploy to your PHP 5.6 live server, they won't work.

The SQL you describe should be fine:

select count(*) as aggregate from `parameter_log_site_detail` where `site_id` = EPE

This is actually okay, even if you have ONLY_FULL_GROUP_BY. It's certainly legal to do a select count(*) of all matching rows in the table. You don't need a GROUP BY clause for this query.

But if you mix aggregated columns with non-aggregated columns, you would violate the ONLY_FULL_GROUP_BY requirements, because the non-aggregated columns would be ambiguous.

select id, count(*) as aggregate from ...

I wonder if Laravel is inserting extra column(s) into your select-list before preparing the query. You'd have to enable the MySQL query log to be sure.

I notice that there is some discussion of this error on Laravel issues: https://github.com/laravel/framework/issues/15232

The solution several users in that thread said fixes the problem is to set 'strict'=>false in your Laravel config/database.php.

But I would bet that the root cause is that Laravel is modifying your SQL query.

Thanks for your reply and advises. Solution was to set `'strict' => false` — Lakmal Premaratne, Aug 26 '17 at 18:58
As my local server, I use Laragon and I didn't alter its configuration. I use it as it is shipped. But I do not have much control over the live server. I will change my MySQL and PHP version to sync with live server. Thanks for your advise. — Lakmal Premaratne, Aug 26 '17 at 19:03

MySQL error is thrown when laravel pagination is used

1 Answers1

Linked