/admin/changes: ErrorException: SQLSTATE[42000]: Syntax error or access violation: 1055

[eminos]

Page /admin/changes gives this error:

Method Bkwld\Decoy\Fields\Listing::__toString() must not throw an exception, caught ErrorException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'opio.changes.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select changes.id, CONCAT(first_name, " ", last_name) name from changes inner join admins on admins.id = admin_id group by admin_id) (View: C:\Users\emin\Dev\opio\vendor\bkwld\decoy\views\shared\list_full_header.haml)

Laravel 5.4

[weotch]

What database and version are you using?

[eminos]

mysql 5.7

[weotch]

From https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5.

We're running 5.6 here, which is why we haven't encountered this.

---

I added a commit to master, can you see if dev-master fixes it for you?

[eminos]

Yea, that solved it. Thanks :)

[noxify]

The same problem occurs with postgres (9.6.10) :)

[weotch]

@noxify Did the master branch fix? We're gonna tag a new release soon

[noxify]

@weotch i used the "composer require ..." from the docs - I will change it and test it again.

Thanks for the hint :)

[noxify]

@weotch - tested it with postgres and mysql5.6, but with both databases I got the following error:

Method Bkwld\Decoy\Fields\Listing::__toString() must not throw an exception, caught ErrorException: SQLSTATE[42000]: Syntax error or access violation: 1055 'decoy.admins.id' isn't in GROUP BY (SQL: select admins.id, CONCAT(first_name, " ", last_name) name from `changes` inner join `admins` on `admins`.`id` = `admin_id` group by `admin_id`) (View: /Applications/MAMP/htdocs/decoy/vendor/bkwld/decoy/views/shared/list/_full_header.haml)

• OSX
• MAMP with PHP7.1
• mysql 5.6
• postgres 9.6.10

composer.json:

"require": {
        "php": ">=5.6.4",
        "bkwld/decoy": "dev-master",
        "laravel/framework": "5.4.*", //5.4.19
        "laravel/tinker": "~1.0"
    },

[noxify]

I changed the getAdmins() method to the following:

    public static function getAdmins()
    {
        return Admin::all(['id', 'email'])->pluck('email', 'id');
    }

and everything works fine :)

If i'm not wrong, the getAdmins() will be used to show the values for the search dropdown.
Does it not make sense to show all admins without the limitation?

[weotch]

@noxify Yeah, works for me, thanks for looking into it.

The issue is that only postgres doesn't use CONCAT