Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.11.11, 11.4.5
-
None
-
Debian 12
Description
Hi,
We are upgrading our MariaDB 10.11 LTS installations to 11.4 and encountering quite some issues we don't know how to resolve.
Our issues seem similar to MDEV-36037 - but we haven't been able to fully resolve it.
This is our (minimal) MariaDB 10.11 configuration that has always worked for us:
[client]
|
default-character-set = utf8mb4
|
port = 3306
|
socket = /run/mysqld/mysqld.sock
|
 |
[mysqld]
|
basedir = /usr
|
character-set-server = utf8mb4
|
collation-server = utf8mb4_unicode_520_ci
|
datadir = /var/lib/mysql
|
init-connect = "SET NAMES utf8mb4"
|
pid-file = /run/mysqld/mysqld.pid
|
port = 3306
|
socket = /run/mysqld/mysqld.sock
|
user = mysql
|
When upgrading MariaDB 10.11 -> 11.4 using this configuration, everything keeps working as intended, no errors shown!
But when we install a new MariaDB 11.4 installation using this configuration, things break. This is shown in the logs:
Apr 16 12:35:03 debian12 systemd[1]: Started mariadb.service - MariaDB 11.4.5 database server.
|
Apr 16 12:35:03 debian12 /etc/mysql/debian-start[334960]: Upgrading MariaDB tables if necessary.
|
Apr 16 12:35:03 debian12 /etc/mysql/debian-start[334974]: Checking for insecure root accounts.
|
Apr 16 12:35:03 debian12 debian-start[334977]: --------------
|
Apr 16 12:35:03 debian12 debian-start[334977]: SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('', 'mysql_native_password', 'mysql_old_password')
|
Apr 16 12:35:03 debian12 debian-start[334977]: --------------
|
Apr 16 12:35:03 debian12 debian-start[334977]: ERROR 1267 (HY000) at line 1: Illegal mix of collations (utf8mb4_uca1400_ai_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='
|
We were able to resolve this issue by changing the collation-server directive to `utf8mb4_uca1400_ai_ci` and adding a new directive:
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
|
This way, we could get new MariaDB 11.4 installations working without any errors.
However, when upgrading MariaDB 10.11 -> 11.4, and then applying this new configuration, it breaks again, with this error:
Apr 16 12:43:34 debian12 systemd[1]: Started mariadb.service - MariaDB 11.4.5 database server.
|
Apr 16 12:43:34 debian12 /etc/mysql/debian-start[343328]: Upgrading MariaDB tables if necessary.
|
Apr 16 12:43:35 debian12 /etc/mysql/debian-start[343339]: Checking for insecure root accounts.
|
Apr 16 12:43:35 debian12 debian-start[343342]: --------------
|
Apr 16 12:43:35 debian12 debian-start[343342]: SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('', 'mysql_native_password', 'mysql_old_password')
|
Apr 16 12:43:35 debian12 debian-start[343342]: --------------
|
Apr 16 12:43:35 debian12 debian-start[343342]: ERROR 1267 (HY000) at line 1: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation '='
|
We are able to resolve this error thanks to the comments in MDEV-36037 - which mentions running a `drop view mysql.user` and then running a `mariadb-upgrade --force`.
And this does seem to fix this specific error, but there are more errors not shown in the mariadb log, but still thrown by the mariadb-upgrade --force command:
Phase 2/8: Installing used storage engines... Skipped
|
Phase 3/8: Running 'mysql_fix_privilege_tables'
|
ERROR 1267 (HY000) at line 3191: Illegal mix of collations (utf8mb3_uca1400_ai_ci,IMPLICIT) and (utf8mb3_general_ci,IMPLICIT) for operation '='
|
ERROR 1267 (HY000) at line 3240: Illegal mix of collations (utf8mb3_uca1400_ai_ci,IMPLICIT) and (utf8mb3_general_ci,IMPLICIT) for operation '='
|
FATAL ERROR: Upgrade failed
|
In order not to get errors, this now leaves us requiring 2 different my.cnf configurations on 11.4: one for new installations, and another for upgraded installations.
This is not ideal, and we'd like to only have one my.cnf per MariaDB version.
In order to reproduce the issue, these are the steps:
On Debian 12:
1. Install fresh MariaDB 10.11
2. Add the following my.cnf configuration:
[client]
|
default-character-set = utf8mb4
|
port = 3306
|
socket = /run/mysqld/mysqld.sock
|
 |
[mysqld]
|
basedir = /usr
|
character-set-server = utf8mb4
|
collation-server = utf8mb4_unicode_520_ci
|
datadir = /var/lib/mysql
|
init-connect = "SET NAMES utf8mb4"
|
pid-file = /run/mysqld/mysqld.pid
|
port = 3306
|
socket = /run/mysqld/mysqld.sock
|
user = mysql
|
3. Restart MariaDB. Check logs, observe no errors
4. Upgrade MariaDB to 11.4
5. Check logs, observe no errors
6. Add new my.cnf configuration:
[client]
|
default-character-set = utf8mb4
|
port = 3306
|
socket = /run/mysqld/mysqld.sock
|
 |
[mysqld]
|
basedir = /usr
|
character-set-server = utf8mb4
|
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-server = utf8mb4_uca1400_ai_ci
|
datadir = /var/lib/mysql
|
init-connect = "SET NAMES utf8mb4"
|
pid-file = /run/mysqld/mysqld.pid
|
port = 3306
|
socket = /run/mysqld/mysqld.sock
|
user = mysql
|
|
7. Restart MariaDB
8. In the logs, observe an error, thrown by `/etc/mysql/debian-start`. Running `/etc/mysql/debian-start` also reproduces the error
9. On MariaDB CLI, run: `drop view mysql.user`
10. Run mariadb-upgrade --force
11. Observe 2 errors in mariadb upgrade command
12. Re-run `/etc/mysql/debian-start` (or restart mariadb and check logs). Observe the errors regarding the mysql.user table are gone.
In this scenario, the errors in the mariadb-upgrade command remain present, and we do not know how to fix it.
Could anyone suggest the correct steps for upgrading MariaDB with our config? Or if there is another configuration that we need in 11.4 that does better align with 10.11 and 11.4?
Thanks!
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-