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

[Samuli Suonpää]

Package: mariadb-server
Version: 1:11.8.1-4
Severity: normal

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.

Samuli

-- System Information:
Debian Release: trixie/sid
  APT prefers testing
  APT policy: (900, 'testing'), (700, 'unstable'), (500, 'experimental')
Architecture: amd64 (x86_64)

Kernel: Linux 6.12.22-cloud-amd64 (SMP w/2 CPU threads; PREEMPT)
Locale: LANG=en_IE.UTF-8, LC_CTYPE=en_IE.UTF-8 (charmap=UTF-8), LANGUAGE=en_IE:en
Shell: /bin/sh linked to /usr/bin/dash
Init: systemd (via /run/systemd/system)
LSM: AppArmor: enabled

Versions of packages mariadb-server depends on:
ii  adduser                3.150
ii  debconf [debconf-2.0]  1.5.91
ii  galera-4               26.4.21-1
ii  gawk                   1:5.2.1-2+b1
ii  init-system-helpers    1.68
ii  iproute2               6.14.0-3
ii  libc6                  2.41-7
ii  libdbi-perl            1.647-1
ii  libgcc-s1              14.2.0-19
ii  libpam0g               1.7.0-3
ii  libssl3t64             3.5.0-1
ii  libstdc++6             14.2.0-19
ii  lsof                   4.99.4+dfsg-2
ii  mariadb-client         1:11.8.1-4
ii  mariadb-common         1:11.8.1-4
ii  mariadb-server-core    1:11.8.1-4
ii  passwd                 1:4.17.4-2
ii  perl                   5.40.1-3
ii  procps                 2:4.0.4-8
ii  psmisc                 23.7-2
ii  rsync                  3.4.1+ds1-3
ii  socat                  1.8.0.3-1
ii  zlib1g                 1:1.3.dfsg+really1.3.1-1+b1

Versions of packages mariadb-server recommends:
ii  libhtml-template-perl           2.97-2
ii  mariadb-plugin-provider-bzip2   1:11.8.1-4
ii  mariadb-plugin-provider-lz4     1:11.8.1-4
ii  mariadb-plugin-provider-lzma    1:11.8.1-4
ii  mariadb-plugin-provider-lzo     1:11.8.1-4
ii  mariadb-plugin-provider-snappy  1:11.8.1-4
ii  pv                              1.9.31-1

Versions of packages mariadb-server suggests:
ii  bsd-mailx [mailx]  8.1.2-0.20220412cvs-1
pn  mariadb-test       <none>
ii  netcat-openbsd     1.228-1

-- Configuration Files:
/etc/logcheck/ignore.d.paranoid/mariadb-server [Errno 13] Permission denied: '/etc/logcheck/ignore.d.paranoid/mariadb-server'
/etc/logcheck/ignore.d.server/mariadb-server [Errno 13] Permission denied: '/etc/logcheck/ignore.d.server/mariadb-server'
/etc/logcheck/ignore.d.workstation/mariadb-server [Errno 13] Permission denied: '/etc/logcheck/ignore.d.workstation/mariadb-server'

-- debconf information:
  mariadb-server/old_data_directory_saved:
  mariadb-server/nis_warning:
  mariadb-server/postrm_remove_databases: false