How to Fix SQLSTATE[42000]: Syntax error or access violation: 1055 isn’t in GROUP BY Error in Laravel 5.7

In this post i will guide how to fix SQLSTATE[42000]: Syntax error or access violation: 1055 isn’t in GROUP BY Error.

Actually It’s not a Laravel Error. This is MySQL Error and which comes in your MySQL DB SQL_MODE have ONLY_FULL_GROUP_BY Value.

In few Weeks before i search this Error in Internet i got following Solution.

Open config/database.php File. in the connections => mysql turn strict value to false. So do it like as bellow:

'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,
],

Now access your Laravel Application its Working.

I thing this is Not a right solution. If you are change ‘strict’ value to false. It will remove following SQL modes.

ONLY_FULL_GROUP_BY
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION

We got this error is just because of ONLY_FULL_GROUP_BY SQL_MODE.

Laravel offers we can define our SQL modes with every DB connection in our configuration file. So, i turn Mysql strict to true and specify SQL_MODE like below

'mysql' => [
            'driver' => 'mysql',
            'host' => 'HOST_NAME',
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'DB_NAME'),
            'username' => 'USER_NAME',
            'password' => 'PASSWORD',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
            'modes' => [
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_AUTO_CREATE_USER',
                'NO_ENGINE_SUBSTITUTION'
            ],
 
        ]

Now works Fine. Happy Coding…

You May Also Like

Leave a Reply

Your email address will not be published. Required fields are marked *