> 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 '='
