Laravel : Syntax error or access violation: 1055 Error

Question

I want to use WhereIn and Groupby in the same query to fetch Result.

I've tried this:

$loadids=explode("#@*",$reciptdet->loading_id);
$loadingdatas=DB::table('loading')->groupBy('vehicle_no')->whereIn('id',$loadids)->get();

But I got this error message:

SQLSTATE[42000]: Syntax error or access violation: 1055 'sbrtpt.loading.id' isn't in GROUP BY (SQL: select * from loading where id in (14, 15, 16) group by vehicle_no)

SQLSTATE[42000]: Syntax error or access violation: 1055 'sbrtpt.loading.id' isn't in GROUP BY (SQL: select * from `loading` where `id` in (14, 15, 16) group by `vehicle_no`) @aynber — Karthikvijayaveni, Dec 01 '16 at 18:18
https://laracasts.com/discuss/channels/eloquent/sqlstate42000-syntax-error-or-access-violation-isnt-in-group-by — aynber, Dec 01 '16 at 18:36

score 359 · Accepted Answer · answered Jul 08 '17 at 10:03

359

Short answer

In config\database.php --> "mysql" array

Set 'strict' => false to disable all.

.... or

You can leave 'strict' => true and add modes to "mysql" option in

'mysql' => [
       ...
       ....
       'strict' => true,
       'modes' => [
            //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION'
        ],
 ]

Detailed answer

You may not need to disable all strict options ... Kindly have a look on this answer about this issue.

answered Jul 08 '17 at 10:03

Husam

8,149
3
38
45

How can we set this strict mode to false on oracle connection in laravel? Please help me – Vikas Chauhan May 13 '19 at 08:36
@VikasChauhan, Sorry I didn't use Oracle before – Husam May 13 '19 at 15:23
6

This worked!!! For anybody who is still getting the same error after changing that setting, try clearing the config cache by running `php artisan config:cache` – Altin Oct 27 '19 at 16:43
4

My concern about this kind of hard-coded workaround is that I feel some kind of technical debt that will not make me sleep well at night, because the kind of questions that will be floating in my head will be like: what happens when Laravel and/or MySQL spec changes wrt the `modes` (e.g. addition/removal of some of the modes specified in that array, or worse still, the name of one of the modes specified in that array changes). So, I just leave `strict=true` and won't touch any `mode` thing. I'll rather update my code to work with such strict setting. `ThisPractice === SleepWellEveryNight` :) – Damilola Olowookere Feb 09 '20 at 15:40
1

@Husam may I know what is the pros and cons disabling strict? Thanks for the answer – Mathew Magante Feb 26 '20 at 15:39
2

A much better solution is write better SQL – Chuck Le Butt Jul 14 '20 at 21:06
1

I wasted hours messing with different things, attempting to set the permission globally and had no idea that config option was available! – Adam Patterson May 11 '22 at 20:05

score 123 · Answer 2 · answered Dec 01 '16 at 19:15

123

This is probably a SQL_MODE problem. In your config/database.php, in the connection, change

strict => false

As in

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

answered Dec 01 '16 at 19:15

Antonio Carlos Ribeiro

86,191
22
213
204

1

In this Method can make any security issue @Antonio Carlos Ribeiro – Karthikvijayaveni Dec 02 '16 at 02:02
I believe it's safe or you would not have an option on Laravel to disable it. – Antonio Carlos Ribeiro Dec 02 '16 at 11:16
Don't forget to execute this command after applying the change: php artisan config:cache – Bu Saeed Mar 29 '21 at 06:44

score 43 · Answer 3 · answered Feb 16 '21 at 12:30

43

No need to change any where in your System jut use code like in laravel

\DB::statement("SET SQL_MODE=''");//this is the trick use it just before your query

$data=Task::where('user_id', Auth::user()->id)->where('status', 0)->groupBy('task_code')->get(['id','task_code', 'title']);

answered Feb 16 '21 at 12:30

Sk Bindas

699
7
4

Thanks this works like charm \DB::statement("SET SQL_MODE=''"); – Vinit Kadkol Apr 08 '21 at 17:29
Thank you, been searching and trying all other solution and not work on me. Your solution is simple and works. Thank you so much – Kyusuf Muhammad Aug 10 '21 at 11:40
It's My pleasure Mr. @KyusufMuhammad and Mr. Vinit Kadkol . – Sk Bindas Aug 11 '21 at 12:19
Thanks, worked like a charm – 4 R4C81D Nov 30 '21 at 18:18
1

Thank you @SkBindas, it worked for me too. Also I think this is the best solution since it does not change global settings. – Andrei Dec 21 '21 at 20:35
Thank you @SkBindas this seems to be a much safer way if you are running only one query other than setting it for the whole application – Zack May 05 '23 at 08:47

cespon · Answer 4 · 2021-01-14T08:14:06.330

16

Without modifiying `config\database.php` file

Set 'strict' => false in the config\database.php could be a security issue. So, a simple Laravel solution could be first call get() and then groupBy('vehicle_no):

$loadids = explode("#@*", $reciptdet->loading_id);
$loadingdatas = DB::table('loading')->whereIn('id', $loadids)->get();
$grouped = $loadingdatas->groupBy('vehicle_no');

edited Jan 14 '21 at 08:14

answered Feb 18 '19 at 01:12

cespon

5,630
7
33
47

I couldn't get proper result when 'strict' => false. then this solution works well if you try group data when query. Thank you for the answer. – ireshan pathirana Mar 28 '20 at 20:44
1

You do not want to group by on the web server, you want that group by to be done in SQL. Try your group by approach with a table of 147mil records... – John C Mar 10 '22 at 02:14
1

Don't do that if you have a big data. Groupping the data outside SQL will kill the performance. – Bulent Jun 20 '22 at 07:56

score 12 · Answer 5 · edited Jun 25 '22 at 11:51

12

update config/database.php

set:

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

instead of:

'mysql' => [
     'strict' => true,
],

and don't forget to clear cache:

php artisan config:cache

edited Jun 25 '22 at 11:51

Osanda Gamage

446
2
6
16

answered Mar 16 '20 at 10:46

Gouda Elalfy

6,888
1
26
38

beside being replied in 2016 if i can vote you a thousand time i will . banging with it about 24 hours without any clue its being cached. Love you man <3 – Faisal Mehmood Awan Apr 06 '20 at 17:55

score 12 · Answer 6 · edited Jun 12 '21 at 19:15

12

You can leave 'strict' => true and add modes to "mysql" option in.

'mysql' => [
   ...
   ....
   'strict' => true,
   'modes' => [
        //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
        'STRICT_TRANS_TABLES',
        'NO_ZERO_IN_DATE',
        'NO_ZERO_DATE',
        'ERROR_FOR_DIVISION_BY_ZERO',
        // 'NO_AUTO_CREATE_USER', // This has been deprecated and will throw an error in mysql v8

        'NO_ENGINE_SUBSTITUTION'
    ],
 ]

edited Jun 12 '21 at 19:15

Sven Eberth

3,057
12
24
29

answered Jun 12 '21 at 01:26

Christopher C Okonkwo

131
2
7

I think this is much better than setting to false – Sodmond Nov 09 '22 at 15:02
What does "strict" even do? Is there any documentation for that configuration option? – ChrisDeDavidMindflowAU Jun 29 '23 at 21:39

score 8 · Answer 7 · edited Apr 25 '19 at 16:18

8

I was having this problem also but after changing 'strict' => true, to 'strict' => false, the error disappeared.

You can find this setting in:

config\database.php

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

edited Apr 25 '19 at 16:18

Chuck Le Butt

47,570
62
203
289

answered Jun 06 '18 at 12:55

Zakhele

97
1
2

How can we do that for oracle connectiion in laravel – Vikas Chauhan May 13 '19 at 10:29

score 8 · Answer 8 · answered Jun 21 '18 at 08:23

Whenever using groupBy in eloquent, always include the column name used in the groupBy function in the select() function.

$loadids=explode("#@*",$reciptdet->loading_id);
$loadingdatas=DB::table('loading')->select('vehicle_no')->groupBy('vehicle_no')->whereIn('id',$loadids)->get();//add select('vehicle_no')

Also it is a bad practice to disable strict mode in the config file. Doing so may cause corrupt data to enter the database such as invalid dates without any warnings.Don't do that unless absolutely necessary.

Addition of ->select('column') worked for me. Thanks buddy :) — Shaan, Jun 09 '20 at 10:35

score 5 · Answer 9 · edited Aug 08 '21 at 17:23

SQLSTATE[42000]: Syntax error or access violation: 1055  in GROUP BY

If you get the above error, add the following into your database.php file in the config folder:

'mysql' => [
    'strict' => true,
    'modes' => [
        'STRICT_TRANS_TABLES',
        'NO_ZERO_IN_DATE',
        'NO_ZERO_DATE',
        'ERROR_FOR_DIVISION_BY_ZERO',
        'NO_AUTO_CREATE_USER',
        'NO_ENGINE_SUBSTITUTION'
    ],
]

score 5 · Answer 10 · edited Dec 03 '22 at 08:49

5

Later, after googling for a while, I learnt that the problem was caused by the sql_mode setting, and modified the configuration of config/database.php as strict => false.

Refer to: https://stdworkflow.com/147/laravel-syntax-error-or-access-violation-1055-error

edited Dec 03 '22 at 08:49

SEYED BABAK ASHRAFI

4,093
4
22
32

answered Apr 01 '22 at 14:46

drodriguez

150
2
5

Indeed, setting like this solved my problem: \DB::statement("SET SQL_MODE=''"); – Omar Tarek Apr 27 '22 at 08:55

Debiprasad · Answer 11 · 2020-01-10T11:16:40.097

3

This restriction makes sense as when you use GROUP BY in MySQL, it returns one row for each value in the columns used in GROUP BY. So, the values of other columns in the selected rows do not make sense to use anywhere. So, it's always recommended to use the best practice and I would recommend not to disable MySQL Strict Mode.

Often developers may need rows of a query grouped by the value of a column. Here they don't need only one row per the unique values of the columns. But they need multiple rows grouped by the unique values of a particular column. For some reason, they use groupBy Query Builder method of Laravel which generates a MySQL GROUP BY query and the developers encounter the above error.

The solution to their problem is to use groupBy Collection method instead. For example,

$loadingData = DB::table('loading')
    ->whereIn('id', $loadIds)
    ->get()
    ->groupBy('vehicle_no');

This will give them the desired result.

edited Jan 10 '20 at 11:16

answered Jan 10 '20 at 08:06

Debiprasad

5,895
16
67
95

1

You do not want to group by on the web server, you want that group by to be done in SQL. Try your group by approach with a table of 147mil records... – John C Mar 10 '22 at 02:12
@JohnC In that case, they need to write the queries differently. – Debiprasad Mar 10 '22 at 10:00
the only thing you are missing from it being a valid SQL group by, is a valid select. That's all the issue is in all of these... – John C Mar 10 '22 at 19:24
@JohnC Yes, of course. You can do that. My answer only addresses the error in the question. – Debiprasad Apr 13 '22 at 12:17
Actually it doesn't, the answer is to simply add the correct select statement, and not group by after you made the query. – John C Apr 13 '22 at 14:02
@JohnC What if you need to select more columns than the group by columns? – Debiprasad Nov 12 '22 at 06:44
That would break the query, as the SQL-92 standard does not allow selecting anything but grouped and aggregated columns in the select clause when using group by. If you are using MySQL, you can use GROUP_CONCAT or ANY_VALUE to work around that limitation. – John C Dec 02 '22 at 20:13

score 3 · Answer 12 · answered Jul 01 '20 at 11:54

3

You might as well use:

distinct('vehicle_no')

instead of groupBy('vehicle_no') each case scenario is different, but looking at your query, distinct might be the way to go since you're not aggregating data.

answered Jul 01 '20 at 11:54

ben.c

31
1

score 2 · Answer 13 · answered May 14 '21 at 15:27

2

This is on laravel doc 8.x , it works well

 $users = DB::table('users')
             ->select(DB::raw('count(*) as user_count, status'))
             ->where('status', '<>', 1)
             ->groupBy('status')
             ->get();

answered May 14 '21 at 15:27

Rachid Loukili

623
6
15

score -1 · Answer 14 · answered Mar 16 '20 at 14:40

add \Schema::defaultStringLength(191); to boot method

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }

    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        //
         \Schema::defaultStringLength(191);
    }
}

score -1 · Answer 15 · answered Feb 23 '22 at 19:33

-1

I solve a similar issue with using group by by first getting the columns i needed to sort then use the group by.

$SampleData= DB::table('tableExampleName')->get(['col1','col2'])->groupBy('col3');

then you can dd() the values and check if they are the right grouping. You can all assign it and pass it into your view for further testing.

Note: This is using Laravel 8.

answered Feb 23 '22 at 19:33

ZackAttack

126
12

You do not want to group by on the web server, you want that group by to be done in SQL. Try your group by approach with a table of 147mil records... – John C Mar 10 '22 at 02:13

Laravel : Syntax error or access violation: 1055 Error

15 Answers15

Short answer

.... or

Detailed answer

Without modifiying `config\database.php` file

Linked

Related

Laravel : Syntax error or access violation: 1055 Error

15 Answers15

Short answer

.... or

Detailed answer

Without modifiying config\database.php file

Linked

Related

Without modifiying `config\database.php` file