[debian-mysql] Bug#1104533: debian-start: ERROR 1267 (HY000): Illegal mix of collations

[Samuli Suonpää]

> 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