Illegal mix of collations MySQL Error [MySQL 8]

[mrlinnth]

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' (SQL: select `value` from `preferences` where `key` = appshell.quicklinks and `user_id` = 1 limit 1) (View: /...../vendor/konekt/appshell/src/resources/views/layouts/default/_header.blade.php)

I got this error when I logged in, on my fresh setup of Vanilo 2.0-dev.
I solved it by changing the charset of preferences.key column.
I think I will also need to change settings.id column charset since I found it in the migration code.

Is there any specific reason that you are using latin1 charset for those columns?

[fulopattila122]

None of the migrations define to use (latin1) charset.

Were there any tables manually created or manipulated?

https://laracasts.com/discuss/channels/laravel/sqlstatehy000-general-error-1267-illegal-mix-of-collations-latin1-swedish-ciimplicit-and-utf8mb4-unicode-cicoercible

[mrlinnth]

gears/src/resources/database/2018_02_26_213828_create_gears_tables.php file has columns with latin1 charset defined.

[fulopattila122]

Can you please provide with more details about your db setup?
Engine: MySQL or MariaDB? Which version?
Any particular DB settings in .env or in config/database.php?
Also which OS; docker/vagrant/native?

Thx!

[mrlinnth]

Sure I'll share my local server info later in like next 8~10 hrs. But my mysql is using default configuration if I'm not mistaken. I also think it's a bit weird since the migration file is created long time ago and there is no one who faced the issue. Let's see.

…

On Thu, Oct 29, 2020, 4:49 AM Attila Fulop ***@***.***> wrote: Can you please provide with more details about your db setup? Engine: MySQL/MariaDB/Postgres, version? Any particular D settings in .env or in config/database.php? Thx! — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#4 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABFZEFN22KWVYHV24TGE7C3SNCKF3ANCNFSM4TC3GRUQ> .

[fulopattila122]

Thx, looking forward to getting the details so that I can reproduce the bug.
Then, I expect to find the root cause and elaborate on a fix

[mrlinnth]

My PC's info is attached in the screenshot.

I used Valet Linux Plus for local development.

MYSQL Version

> mysql --version
mysql  Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

database.php

        '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' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=dbname
DB_USERNAME=dbuser
DB_PASSWORD=dbpassword

[fulopattila122]

Thanks for the info, it helped me to figure out that it is a MySQL 8 specific problem https://bugs.mysql.com/bug.php?id=92741

I'm going to find a way to mitigate this issue. Will give an update

[mrlinnth]

Thank for your hard work. I found out about Vanilo only last week so I am still at testing and learning phase.
Hope I can contribute better in future to this project.

Again appreciate what you are doing with Vanilo :)

[fulopattila122]

Thx! This detailed bug report is already a great contribution 👍
I've been avoiding MySQL 8 for various reasons and just realized that none of our CI's are configured to run them again MySQL 8. We only test MySQL 5.x, MariaDB 10.x, Postgres and SQLite.

This will change soon ;)