> What are the steps to reproduce this?
>
> Both before and after the upgrade when I run these SQL commands I get
> the exact same results. I am not able to reproduce the illegal mix of
> collations you have.
I did some testing with kvm virtual machines and I believe I now have a way to
reproduce this.
1. I set up a clean Debian bookworm (12.10.0) on a vm.
2. apt-get install mariadb-server mariadb
3. apt-get full-upgrade (to trixie)
This triggers the problem. However, If I first upgrade the clean bookworm to
trixie and only then install mariadb, no problems whatsoever.
With bookworm:
MariaDB [mysql]> SELECT count(*) FROM mysql.user WHERE user='root' and
password='' and password_expired='N' and plugin in ('',
'mysql_native_password', 'mysql_old_password');
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.002 sec)
MariaDB [mysql]> SHOW VARIABLES LIKE '%collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb3_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.002 sec)
MariaDB [mysql]>
After upgrading to trixie:
MariaDB [mysql]> SELECT count(*) FROM mysql.user WHERE user='root' and
password='' and password_expired='N' and plugin in ('',
'mysql_native_password', 'mysql_old_password');
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,COERCIBLE)
and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '='
MariaDB [mysql]> SHOW VARIABLES LIKE '%collation%';
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value
|
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| character_set_collations |
utf8mb3=utf8mb3_uca1400_ai_ci,ucs2=ucs2_uca1400_ai_ci,utf8mb4=utf8mb4_uca1400_ai_ci,utf16=utf16_uca1400_ai_ci,utf32=utf32_uca1400_ai_ci
|
| collation_connection | utf8mb4_uca1400_ai_ci
|
| collation_database | utf8mb4_general_ci
|
| collation_server | utf8mb4_uca1400_ai_ci
|
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.003 sec)
MariaDB [mysql]>
Trixie, with mariadb first installed only after upgrading to trixie:
MariaDB [mysql]> SELECT count(*) FROM mysql.user WHERE user='root' and
password='' and password_expired='N' and plugin in ('',
'mysql_native_password', 'mysql_old_password');
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.002 sec)
MariaDB [mysql]> SHOW VARIABLES LIKE '%collation%';
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value
|
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| character_set_collations |
utf8mb3=utf8mb3_uca1400_ai_ci,ucs2=ucs2_uca1400_ai_ci,utf8mb4=utf8mb4_uca1400_ai_ci,utf16=utf16_uca1400_ai_ci,utf32=utf32_uca1400_ai_ci
|
| collation_connection | utf8mb4_uca1400_ai_ci
|
| collation_database | utf8mb4_uca1400_ai_ci
|
| collation_server | utf8mb4_uca1400_ai_ci
|
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.002 sec)
MariaDB [mysql]>
I believe collation should be “utf8mb4_uca1400_ai_ci” in trixie (MariaDB
11.8.1), when in bookworm (MariaDB 10.11.11) it it “utf8mb4_general_ci”.
Here we can see that if MariaDB 10.11.1 (bookworm) is upgraded to MariaDB
11.8.1 (trixie), collation_connection and collation_server will be
“utf8mb4_uca1400_ai_ci”, but collation_database remain utf8mb4_general_ci.
Is this where the problem might be?
Unfortunately I really do not understand how this collation thing works.
Anyway, please advice if I should provide more information.
Regards,
Samuli
