Interestingly, I get slightly different results:
...
# mariadb -e "SHOW VARIABLES LIKE '%collation%';" +----------------------+--------------------+
| Variable_name | Value | +----------------------+--------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci | +----------------------+--------------------+
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?
Two test runs.
1. Clean bookworm, default locale C.UTF-8.
# locale
LANG=C.UTF-8
LANGUAGE=
LC_CTYPE="C.UTF-8"
LC_NUMERIC="C.UTF-8"
LC_TIME="C.UTF-8"
LC_COLLATE="C.UTF-8"
LC_MONETARY="C.UTF-8"
LC_MESSAGES="C.UTF-8"
LC_PAPER="C.UTF-8"
LC_NAME="C.UTF-8"
LC_ADDRESS="C.UTF-8"
LC_TELEPHONE="C.UTF-8"
LC_MEASUREMENT="C.UTF-8"
LC_IDENTIFICATION="C.UTF-8"
LC_ALL=
# apt install mariadb-server
…
# mariadb -e "SHOW VARIABLES LIKE '%collation%';" +----------------------+--------------------+
| Variable_name | Value | +----------------------+--------------------+
| collation_connection | utf8mb3_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci | +----------------------+——————————+
# 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');"
+----------+
| count(*) |
+----------+
| 0 |
+----------+
# echo deb
http://deb.debian.org/debian/ trixie main contrib non-free non-free-firmware > /etc/apt/sources.list
# apt update
…
# apt full-upgrade
…
# mariadb -e "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 |
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
# 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’);”
--------------
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) at line 1: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '='
2. Clean bookworm, default locale NONE
# locale
LANG=
LANGUAGE=en_US:en
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=
# apt install mariadb-server
…
# mariadb -e "SHOW VARIABLES LIKE '%collation%';" +----------------------+--------------------+
| Variable_name | Value | +----------------------+--------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci | +----------------------+——————————+
# 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');"
+----------+
| count(*) |
+----------+
| 0 |
+----------+
# echo deb
http://deb.debian.org/debian/ trixie main contrib non-free non-free-firmware > /etc/apt/sources.list
# apt update
…
# apt full-upgrade
…
# mariadb -e "SHOW VARIABLES LIKE '%collation%’;"
# mariadb -e "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 | latin1_swedish_ci |
| collation_database | utf8mb4_uca1400_ai_ci |
| collation_server | utf8mb4_uca1400_ai_ci |
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
# 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');”
# 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');"
+----------+
| count(*) |
+----------+
| 0 |
+----------+
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.
Until MariaDB 11.5, the default character set was latin1, except in Debian, where it was utf8mb3. Thus, in bookworm, MariaDB defaults to utf8mb3.
From 11.6 on, the default character set has been utf8mb4, both in vanilla MariaDB and Debian MariaDB. So trixie defaults to utf8mb4.
Also, please note this (using the vm in example 1, Clean bookworm, default locale C.UTF-8):
# 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');"
+----------+
| count(*) |
+----------+
| 0 |
+----------+
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');"
--------------
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) at line 1: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '='
<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>| 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>| Variable_name |
Value &
nbsp; |</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 &
nbsp; &
nbsp; |</div><div>| collation_database | utf8mb4_uca1400_ai_ci &
nbsp; |</div><
| collation_server | utf8mb4_uca1400_ai_ci &
nbsp; |</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><
<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># 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 &
nbsp; &
nbsp; |</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
&
nbsp; |</div><div>| collation_database | utf8mb4_uca1400_ai_ci &
nbsp; |</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>| 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,&
nbsp;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><br class="Apple-interchange-newline" style="caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0);"></body></html>
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)