SQLSTATE[42000]: Syntax error or access violation: 1055

Question

I am using Laravel 5.5 i added this dependency for making simple search engine

https://github.com/nicolaslopezj/searchable

but getting problem when i try to search anything

SQLSTATE[42000]: Syntax error or access violation: 1055 'myreview.movies.name' isn't in GROUP BY (SQL: select count() as aggregate from (select movies., max((case when LOWER(movies.name) LIKE car then 150 else 0 end) + (case when LOWER(movies.name) LIKE car% then 50 else 0 end) + (case when LOWER(movies.name) LIKE %car% then 10 else 0 end) + (case when LOWER(movies.description) LIKE car then 150 else 0 end) + (case when LOWER(movies.description) LIKE car% then 50 else 0 end)

i have already tried with change mysql database i am using mariadb 10.2 after change to mysql 5.7 its working fine

but i have mariadb on my cpanel server

help me regard this thank you

You don't give the actual SQL statement but usually you are trying to use a reserved word as a fieldname. https://stackoverflow.com/questions/4544051/sqlstate42000-syntax-error-or-access-violation-1064-you-have-an-error-in-you — , Dec 18 '17 at 10:17
Like this? SQLSTATE[42000]: Syntax error or access violation: 1055 'myreview.movies.name' isn't in GROUP BY (SQL: select count(*) as aggregate from (select `movies`.*, max((case when LOWER(`movies`.`name`) LIKE car then 150 else 0 end) + (case when LOWER(`movies`.`name`) LIKE car% then 50 else 0 end) + (case when LOWER(`movies`.`name`) LIKE %car% then 10 else 0 end) + (case when LOWER(`movies`.`description`) LIKE car then 150 else 0 end) + (case when LOWER(`movies`.`description`) LIKE car% then 50 else 0 end) — Sid Heart, Dec 18 '17 at 10:22
Have a look at [`12.19.3 MySQL Handling of GROUP BY`](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) — M Khalid Junaid, Dec 18 '17 at 10:25
"'myreview.movies.name' isn't in GROUP BY " that tells you everything that you need to know how to fix the error i.e. should there be a dot between movies and name? — , Dec 18 '17 at 10:27
You indeed have a syntax error in your query: `[...]aggregate from (select movies., max([...]`. After the dot behind `movies` there is a column name missing. — Tobias F., Dec 18 '17 at 10:29

score 10 · Accepted Answer · answered May 26 '18 at 17:11

Open config/database.php

Change value strict from true to false

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

Save

You can read full in my post => How to Fix SQLSTATE[42000]: Syntax error or access violation: 1055

You saved me the night: this error was driving me crazy and I couldn't understand why my query didn't work! — Brigo, Sep 09 '18 at 19:51

score 0 · Answer 2 · answered Apr 12 '19 at 05:44

0

The SQL reason why the query fails is that only columns which appeared in the GROUP BY clause can be used in the SELECT clause of an SQL statement that uses an aggregate function. The accepted answer in this SO post explained this adequately.

answered Apr 12 '19 at 05:44

ultrajohn

2,527
4
31
56

SQLSTATE[42000]: Syntax error or access violation: 1055

2 Answers2