Syntax error or access violation 1055

Short answer

In configdatabase.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 8, 2017 at 10:03

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 1, 2016 at 19:15

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, 2021 at 12:30

Without modifiying configdatabase.php file

Set 'strict' => false in the configdatabase.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');

answered Feb 18, 2019 at 1: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'
    ],
 ]

update config/database.php

set:

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

instead of:

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

and don’t forget to clear cache:

php artisan config:cache

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

You can find this setting in:

configdatabase.php

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

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.

answered Jun 21, 2018 at 8:23

answered Aug 6, 2021 at 9:18

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.

answered Jan 10, 2020 at 8:06

ben.cben.c

311 bronze badge

hamed hossanihamed hossani

9761 gold badge14 silver badges32 bronze badges

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, 2022 at 19:33