ynh_mysql_setup_db sets up database with latin1 collation

[cdauth]

I'm installing an app whose installation script sets up a MySQL database using ynh_mysql_setup_db --db_user=$db_user --db_name=$db_name. The problem is that the database is created with a latin1_swedish_ci collation, which causes problems in the app when users try to save characters that are not contained in the Latin-1 charset.

It seems to me that it would be better if databases were initialized with the utf8mb4_general_ci collation, which allows to store all unicode characters. Potentially, a custom collation could be specified in an optional parameter.

As far as I know, there are two ways to specify the collation:

• Either the default collation of the mysql server is set by setting collation-server and character_set_server
• Or the collation of the database is set on creation in the form CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci.

From the source code it seems that the SQL query does not specify any collation.

What's really odd is that /etc/mysql/mariadb.conf.d/50-server.cnf already specifies the default collation as utf8mb4_general_ci (as seems to be standard on Debian), but it seems that this setting is not getting applied. What I suspect is that Yunohost specifies a custom /etc/mysql/mariadb.cnf file that does not include the files from /etc/mysql/mariadb.conf.d, but I don't know enough about Yunohost to tell if that's really the case.

Workaround

I had success by running the following after creating the database:

echo "ALTER DATABASE $db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" | ynh_mysql_connect_as --user=$db_user --password=$db_pwd --database=$db_name

[zamentur]

Feel free to suggest a PR on this files : https://github.com/YunoHost/yunohost/blob/dev/data/helpers.d/mysql#L92 And create a migration: https://github.com/YunoHost/yunohost/tree/dev/src/yunohost/data_migrations

I think difficulties are to manage migration of old database and restore operation from database version in latin1...

[kay0u]

I've done something like that here: https://github.com/YunoHost-Apps/nextcloud_ynh/pull/384/files