How To Solve Problems Illegal mix of collations (latin1_swedish

Are you having database error problems “SQLSTATE [HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci, IMPLICIT) and (utf8_unicode_ci, COERCIBLE) for operation ‘='” on laravel? If yes, solve the problem with the following solution.

“General error: 1267 Illegal mix of collations” I experienced when trying to change the backend side of my application using the Laravel framework. This happens because the default charset and collation format on laravel are different from the charset and collation format in your database.

At lumen 5.7 (in my case), use the standard as follows

'mysql' => [
    'read' => [
        'host' => ['192.168.1.1'],
    ],
    'write' => [
        'host' => ['196.168.1.2'],
    ],
    'sticky'    => true,
    'driver'    => 'mysql',
    'database'  => 'database',
    'username'  => 'root',
    'password'  => '',
    'charset'   => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix'    => '',
],

'mysql' => [

'read' => [

'host' => ['192.168.1.1'],

'write' => [

'host' => ['196.168.1.2'],

'sticky' => true,

'driver' => 'mysql',

'database' => 'database',

'username' => 'root',

'password' => '',

'charset' => 'utf8mb4',

'collation' => 'utf8mb4_unicode_ci',

'prefix' => '',

i.e. charset utf8mb4 and collation utf8mb4_unicode_ci.

You can read more at https://laravel.com/docs/5.7/database

While the charset and collation on my database use latin1 and latin1_swedish_ci. To solve the above problem, please add DB_CHARSET and DB_COLLATION in the .env configuration

as an example

APP_ENV=local
APP_DEBUG=true
APP_KEY=
APP_TIMEZONE=UTC

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=db_worker
DB_USERNAME=root
DB_PASSWORD=123456
DB_CHARSET=latin1
DB_COLLATION=latin1_swedish_ci

CACHE_DRIVER=file
QUEUE_DRIVER=sync