<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body style="overflow-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;"><div style="caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); overflow-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;"><div dir="auto" style="overflow-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;"><div><blockquote type="cite">Interestingly, I get slightly different results:<br></blockquote><span style="caret-color: rgb(255, 255, 255); color: rgb(255, 255, 255);">...</span><br style="caret-color: rgb(255, 255, 255); color: rgb(255, 255, 255);"><blockquote type="cite"># mariadb -e "SHOW VARIABLES LIKE '%collation%';"<br>+----------------------+--------------------+<br>| Variable_name | Value |<br>+----------------------+--------------------+<br>| collation_connection | latin1_swedish_ci |<br>| collation_database | utf8mb4_general_ci |<br>| collation_server | utf8mb4_general_ci |<br>+----------------------+--------------------+<br></blockquote><div style="caret-color: rgb(255, 255, 255); color: rgb(255, 255, 255);"><br></div></div><div>I am now fairly sure this is a locale issue. I believe we get different results because we have different locale configurations. Mine is .UTF-8. Yours probably latin1?</div><div><br></div><div>Two test runs.</div><div><br></div><div>1. Clean bookworm, default locale C.UTF-8.</div><div># locale</div><div><div>LANG=C.UTF-8</div><div>LANGUAGE=</div><div>LC_CTYPE="C.UTF-8"</div><div>LC_NUMERIC="C.UTF-8"</div><div>LC_TIME="C.UTF-8"</div><div>LC_COLLATE="C.UTF-8"</div><div>LC_MONETARY="C.UTF-8"</div><div>LC_MESSAGES="C.UTF-8"</div><div>LC_PAPER="C.UTF-8"</div><div>LC_NAME="C.UTF-8"</div><div>LC_ADDRESS="C.UTF-8"</div><div>LC_TELEPHONE="C.UTF-8"</div><div>LC_MEASUREMENT="C.UTF-8"</div><div>LC_IDENTIFICATION="C.UTF-8"</div><div>LC_ALL=</div></div><div># apt install mariadb-server</div><div>…</div><div><div># mariadb -e "SHOW VARIABLES LIKE '%collation%';"</div><div>+----------------------+--------------------+</div><div>| Variable_name | Value |</div><div>+----------------------+--------------------+</div><div>| collation_connection | utf8mb3_general_ci |</div><div>| collation_database | utf8mb4_general_ci |</div><div>| collation_server | utf8mb4_general_ci |</div><div>+----------------------+——————————+</div></div><div><div># mariadb -e "SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('','mysql_native_password', 'mysql_old_password');"</div><div>+----------+</div><div>| count(*) |</div><div>+----------+</div><div>| 0 |</div><div>+----------+</div></div><div># echo deb http://deb.debian.org/debian/ trixie main contrib non-free non-free-firmware > /etc/apt/sources.list</div><div># apt update</div><div>…</div><div># apt full-upgrade</div><div>…</div><div><div># mariadb -e "SHOW VARIABLES LIKE '%collation%';"</div><div>+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+</div></div><div><div>| Variable_name | Value |</div><div>+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+</div><div>| 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 |</div><div>| collation_connection | utf8mb4_uca1400_ai_ci |</div><div>| collation_database | utf8mb4_uca1400_ai_ci |</div><div>| collation_server | utf8mb4_uca1400_ai_ci |</div><div>+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+</div></div><div><div># mariadb -e "SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('','mysql_native_password', 'mysql_old_password’);”</div><div><div>--------------</div><div>SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('','mysql_native_password', 'mysql_old_password')</div><div>--------------</div><div><br></div><div>ERROR 1267 (HY000) at line 1: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '='</div></div><div></div></div><div><br></div><div>2. Clean bookworm, default locale NONE</div><div># locale</div><div><div>LANG=</div><div>LANGUAGE=en_US:en</div><div>LC_CTYPE="POSIX"</div><div>LC_NUMERIC="POSIX"</div><div>LC_TIME="POSIX"</div><div>LC_COLLATE="POSIX"</div><div>LC_MONETARY="POSIX"</div><div>LC_MESSAGES="POSIX"</div><div>LC_PAPER="POSIX"</div><div>LC_NAME="POSIX"</div><div>LC_ADDRESS="POSIX"</div><div>LC_TELEPHONE="POSIX"</div><div>LC_MEASUREMENT="POSIX"</div><div>LC_IDENTIFICATION="POSIX"</div><div>LC_ALL=</div></div><div># apt install mariadb-server</div><div>…</div><div><div># mariadb -e "SHOW VARIABLES LIKE '%collation%';"</div><div>+----------------------+--------------------+</div><div>| Variable_name | Value |</div><div>+----------------------+--------------------+</div><div>| collation_connection | latin1_swedish_ci |</div><div>| collation_database | utf8mb4_general_ci |</div><div>| collation_server | utf8mb4_general_ci |</div><div>+----------------------+——————————+</div></div><div><div># mariadb -e "SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('','mysql_native_password', 'mysql_old_password');"</div><div>+----------+</div><div>| count(*) |</div><div>+----------+</div><div>| 0 |</div><div>+----------+</div></div><div># echo deb http://deb.debian.org/debian/ trixie main contrib non-free non-free-firmware > /etc/apt/sources.list</div><div># apt update</div><div>…</div><div># apt full-upgrade</div><div>…</div><div># mariadb -e "SHOW VARIABLES LIKE '%collation%’;"</div><div><div># mariadb -e "SHOW VARIABLES LIKE '%collation%';"</div><div>+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+</div><div>| Variable_name | Value |</div><div>+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+</div><div>| 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 |</div><div>| collation_connection | latin1_swedish_ci |</div><div>| collation_database | utf8mb4_uca1400_ai_ci |</div><div>| collation_server | utf8mb4_uca1400_ai_ci |</div><div>+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+</div></div><div># mariadb -e "SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('','mysql_native_password', 'mysql_old_password');”</div><div><div># mariadb -e "SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('','mysql_native_password', 'mysql_old_password');"</div><div>+----------+</div><div>| count(*) |</div><div>+----------+</div><div>| 0 |</div><div>+----------+</div></div><div><br></div><div>My guess is, this problem has something to do with the difference between utf8mb3 and utf8mb4 (i.e. 3-byte UTF8 and 4-byte UTF8). I guess utf8mb3 collation is incompatible with utf8mb4.</div><div><br></div><div>Until MariaDB 11.5, the default character set was latin1, except in Debian, where it was utf8mb3. Thus, in bookworm, MariaDB defaults to utf8mb3.</div><div><br></div><div>From 11.6 on, the default character set has been utf8mb4, both in vanilla MariaDB and Debian MariaDB. So trixie defaults to utf8mb4.</div><div><br></div><div>Also, please note this (using the vm in example 1, Clean bookworm, default locale C.UTF-8):</div><div><br></div><div><div># LC_CTYPE=C mariadb -e "SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('','mysql_native_password', 'mysql_old_password');"</div><div>+----------+</div><div>| count(*) |</div><div>+----------+</div><div>| 0 |</div><div>+----------+</div><div>root@playground:~# LC_CTYPE=C.UTF-8 mariadb -e "SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('','mysql_native_password', 'mysql_old_password');"</div><div>--------------</div><div>SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('','mysql_native_password', 'mysql_old_password')</div><div>--------------</div><div><br></div><div>ERROR 1267 (HY000) at line 1: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '='</div></div><div><br></div></div></div><br class="Apple-interchange-newline" style="caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0);"></body></html>