How to fix Laravel Illegal mix of collations error?

Question

I have a cronjob thats saves a data by calling an API. This was never happened before, I already changed the collations into latin1_swedish_ci,IMPLICIT and utf8mb4_unicode_ci,COERCIBLE and vice versa for testing. But still I get this kind of error. The weird thing is that I can insert half of the data when I checked the database and in the middle of the insertion the error appears.
IlluminateDatabaseQueryException  : SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' (SQL: select count(*) as aggregate from `scraping`.`xp_ecoprop_projectlist` where `projectId` = 9a8d5bceacb54a78a18052f1a8ef2112 and `projectName` = Ki Residences at Brookvale (翠宁苑) and `country` = Singapore and `location` = D21 - Clementi / Upper Bukit Timah and `propertyGroup` = D09, D10, D11, D21 and `latitude` = Singapore and `longitude` = Singapore and `unitsNum` = 660 and `launchDate` = 1605888000000 and `launchDateAltText` = Q4 2020 and `mainImage` = https://img.singmap.com/upload/broke/da3d9f7796fb41409a7febecd684da42/9a8d5bceacb54a78a18052f1a8ef2112/imgs/9674a2c02a08404b9ca533a9d532b64a.jpg and `min_price` = 0 and `max_price` = 0 and `streetAddress` = 2 - 22 Brookvale Dr and `tenure` = 999 Years and `developer` = Hoi Hup Sunway Clementi Pte Ltd)
Here is my config for database
        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            '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' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

Marko Todorovski · Answer

Are you using MySQL 8.0.22? If so, this is a known bug whose fix will be released in 8.0.23:
https://bugs.mysql.com/bug.php?id=101575
You can:

Downgrade to MySQL 8.0.21
Set PDO::ATTR_EMULATE_PREPARES to true in the options when making the database connection

* Use this one with caution because it may break compatibility with your current code. When PDO::ATTR_EMULATE_PREPARES is true, fetched records from the MySQL database are not automatically casted, so all your integers will be fetched as strings for example. This will break compatibility when you do comparisons with the === operator.
To fix this, you may cast attributes manually in all of your models yourself.
** Explanation: PDO::ATTR_EMULATE_PREPARES are true by default when making a PDO connection, Laravel sets that option to false. When PDO::ATTR_EMULATE_PREPARES is false, all SQL statements are prepared on the MySQL server and not on PHP level, hence why the bug occurs on MySQL 8.0.22.

Change the collation of all of your columns to be equivalent to the one set in your settings (utf8mb4_unicode_ci)

How to fix Laravel Illegal mix of collations error?

One Answer

Add your own answers!

Ask a Question