SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,COERCIBLE) and (utf8_unicode_ci,COERCIBLE) for operation '=' (SQL: select * from tags where json_unquote(json_extract(name, '$."en"')) = test and type is null limit 1)

[ahamed-kashif]

Hi,
I'm using laravel-tags 4.3 in laravel 9. when I try to attach or sync tags from a form data it gives this error

[fri3ndly]

I'm seeing this too. I've ensured all tables are using utf8_unicode_ci but still seeing it

EDIT: It's a MariaDB issue:
#239

EDIT 2:
I have resolved this by updating my database tables charsets. Here's the migration if it helps 👍

    public function up()
    {
        // Set for DB
        \Illuminate\Support\Facades\DB::statement('ALTER DATABASE ' . config('database.mysql.database') . ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');

        $tables = array_map('reset', \Illuminate\Support\Facades\DB::select('SHOW TABLES'));

        // Set for each table
        foreach ($tables as $table) {
            echo "Fixing collation for table `$table` \n";
            \Illuminate\Support\Facades\DB::statement('ALTER TABLE ' . $table . ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
        }
    }

[surya9teja]

I have a big DB with most of the tables are in collation utf8_unicode_ci except tags related tables. Changing every tables into utf8mb4_unicode_ci might broke the existing code. Do you suggest any other way?

[surya9teja]

I am facing the same issue, So I changed my tables tags, taggables and tag_types with collationutf8_unicode_ci still didn't resolve my issue. But If I change my mysql connection config with charset utf8mb4 and collation utf8mb4_unicode_ci. It works with out any error of mismatch collations. Any idea? Why is it happens like this? Even though the changed the related tables into same collation as query, it is still throwing an error.