Admin container fails to connect to external MariaDB database

[kswtch]

Environment & Version

• Docker Compose version v2.12.0
• Version: 2024.06

Description

After upgrading to the latest 2024.06 version the admin container is unable to connect to an external MariaDB database due to incompatible collation.

Replication Steps

• Use MariaDB as an external database backend. In my case version 11.5.2.
• Update mailu to the latest version.

Observed behaviour

The admin container failes with

sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Expected behaviour

Admin container should be able to connect to either mysql or mariadb backends.

Cause

MariaDB has no support for utf8mb4_0900_ai_ci which is the new default since MySQL version 8.0. In the current sqlalchemy version shipped with mailu, the mysqlconnector sets utf8mb4_0900_ai_ci as the collation to use when connecting. This causes all MariaDB connections to fail.

Workaround

Overwrite the default collation for mysqlconnector in the connection string in mailu.env:

SQLALCHEMY_DATABASE_URI=mysql+mysqlconnector://<user>:<passwd>@<host>/<database>?collation=utf8mb4_unicode_ci

Possible Solution

Include the "mariadbconnector" module for sqlalchemy and use it in the sqlalchemy connection string. Currently it is not included with the version shipped by mailu.

[nextgens]

Well that's exactly the type of details you would expect sqlalchemy to abstract us from...

Does mysql support utf8mb4_unicode_ci? If so do we care about the differences?
https://stackoverflow.com/a/51289054

Adding a module is trivial, I'm not sure that changing the default in a way that doesn't break other's people setup is.

[kswtch]

Adding a module is trivial, I'm not sure that changing the default in a way that doesn't break other's people setup is.

Leave the current module (mysql+mysqlconnector) the default and just include mariadbconnector. In the documentation you can then mention to use a different connection string if you are using MariaDB. Or just leave it as it is and add the workaround above to the documentation. I just wanted to mention the workaround somewhere so people can find a solution if they run into the same problem as I did.

[StopDenBus]

I'm using the helm chart version of mailu (https://github.com/Mailu/helm-charts), how can I use the workaround in this helm chart ?

[jamesarbrown]

@StopDenBus Same problem. Tried adusting the mail-envvars, but then nginx/postfix started coughing about the same, so I think its multiple places. Rolled back to chart 1.5.0 in the end.

[jamesarbrown]

@nextgens Thanks. Once this hits the Helm chart will try. Does an issue need raising there as this is a new chart value?