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

[Otto Kekäläinen]

Control: tags -1 moreinfo

> After upgrading from bookworm to trixie, starting mariadb produces this
> error message in journal:
>
> May 01 18:17:39 back debian-start[148992]: --------------
> May 01 18:17:39 back debian-start[148992]: SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('', 'mysql_native_password', 'mysql_old_password')
> May 01 18:17:39 back debian-start[148992]: --------------
> May 01 18:17:39 back debian-start[148992]: ERROR 1267 (HY000) at line 1: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '='
>
> mariadb-server dows start and appears to run fine.
>
> I can also reproduce the error manually with the following query:
>
> MariaDB [(none)]> SELECT * FROM mysql.user where password_expired='N';
> ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '='
>
> Please let me know if I can provide any more information.

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.

MariaDB [(none)]> 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.004 sec)

MariaDB [(none)]> SHOW WARNINGS;
Empty set (0.000 sec)

MariaDB [(none)]> SELECT Host,User FROM mysql.user where password_expired='N';
+-----------+-------+
| Host      | User  |
+-----------+-------+
| localhost | root  |
| localhost | mysql |
+-----------+-------+

MariaDB [(none)]> SHOW WARNINGS;
Empty set (0.001 sec)


I followed the exact steps we also have in CI at
https://salsa.debian.org/otto/mariadb-server/-/jobs/7566186 to start a
Bookworm container, install MariaDB, enable Sid and upgrade with
apt-get full-upgrade -qq --yes.

The CI includes running command 'mariadb --table -e "SELECT
host,user,plugin,authentication_string FROM user;" mysql', in addition
to starting with service file, which both should trigger the warning,
but I don't see any warnings in Bookworm CI job, nor in Buster or
Bullseye upgrades either
(https://salsa.debian.org/otto/mariadb-server/-/jobs/7566190,
https://salsa.debian.org/otto/mariadb-server/-/jobs/7566189).