Enable MySQL 4-byte support

[JimboJoe]

Hi,

I don't know if you could do something about it but with NC 12 some apps (like news) are starting to throw errors about non-UTF8 in DB tables.

The problem is known from NC and can ne fixed by following this :

https://docs.nextcloud.com/server/12/admin_manual/configuration_database/mysql_4byte_support.html

Originally posted here by @Bridouz

[Bridouz]

Quick update :

I updated NC to v12 today I re-enabled news app and it was throwing errors about non-UTF8 characters in DB.

I searched a bit on the web and found this. news/issues/164

In order to fix the issue with News you have to.

• Disable and remove news app
• Remove news folder in /data
• Drop the news tables in your DB
• Add 'mysql.utf8mb4' => true, to your nextcloud config.php
• Install News app

And the error is gone and your feeds will be updated like before.

[eauchat]

As of nextcloud 15, this issue it still present. In the same issue @Bridouz mentioned, another post proposes another way to solve the problem without even removing the app, for yunohost it'd be like this:

• instal PHPmyAdmin yunhost app
• in PHPmyAdmin, choose news app tables one by one (news_feeds, news_folders, news_items) and click on "Operations"
• switch collation from utf8_bin to utf8mb4_bin and tick "Change all column collations" -> Go
• edit the NextCloud config (/var/www/nextcloud/config/config.php) and add the line 'mysql.utf8mb4' => true,

[eauchat]

With nextcloud 15.04, adding 'mysql.utf8mb4' => true, to /var/www/nextcloud/config/config.php caused many issues, like not finding users when you share files, and failing to list users in admin panel. There are some discussions about this issue here.

I removed 'mysql.utf8mb4' => true, line from config and everything works fine again.

I still wonder if there would be another way to remove this warning, it's annoying that it shows to everyone, even non admin users.

[JocelynDelalande]

I switched to 4bytes manually on a 15.0.7 (following official doc).

So far so good (but stay tunned…)

There is a warning in the docs that the backup command (mysqldump) should be modified to include a new parameter --default-character-set=utf8mb4.

Choice to be made for the app ?

So the nextcloud_ynh app has to choose a way :

• or ignoring that warning in admin page (apparently prevents proper storage of emojis)
• or switch to mb4 to handle emojis and provide it properly through a migration provided by nextcloud_ynh upgrade script.

If you choose to switch to mb4 on your own :warning:

Meanwhile, individually, BE CAREFUL : if you switch your nextcloud DB to mb4 you have to patch by hand the app scripts :

scripts/backup :

# MYSQL MB4 PATCH START
# PATCH : comment this:
#ynh_mysql_dump_db --database="$db_name" > db.sql

# PATCH : add this:
MYSQL_ROOT_PWD_FILE=/etc/yunohost/mysql
mysqldump -u "root" -p"$(sudo cat $MYSQL_ROOT_PWD_FILE)" --single-transaction --skip-dump-date "$db_name" --default-character-set=utf8mb4 > db.sql
# MYSQL MB4 PATCH END

scripts/restore :

# MYSQL MB4 PATCH START
# PATCH : comment this:
#ynh_mysql_connect_as --user=$db_name --password=$db_pwd --database=$db_name < ./db.sql
MYSQL_ROOT_PWD_FILE=/etc/yunohost/mysql
# PATCH : add this :
mysql -u root -p"$(sudo cat $MYSQL_ROOT_PWD_FILE)" -B "$db_name" --default-character-set=utf8mb4 < ./db.sql
# MYSQL MB4 PATCH END

[JocelynDelalande]

Actually… anyone knows what is the actual use of this 4-bytes stuff ? Emojis seems to work well in nextcloud without it.

[JOduMonT]

If I understood well this article mb4 is more than just Emojis btw; wordpress also suggest to pass to utf8mb4 since few version too.

[JOduMonT]

Error from news apps

Non UTF-8 charset for MySQL/MariaDB database detected! Learn how to convert your database to utf8mb4 (make a backup beforehand)

[JOduMonT]

thank @JocelynDelalande

1. backup

db_name=nextcloud
MYSQL_ROOT_PWD_FILE=/etc/yunohost/mysql
mysqldump -u "root" -p"$(sudo cat $MYSQL_ROOT_PWD_FILE)" --single-transaction --skip-dump-date "$db_name" --default-character-set=utf8mb4 > db.sql

2. adjust nextcloud config

than edit /var/www/nextcloud/config/config.php to add 'mysql.utf8mb4' => true,

3. restore

mysql -u root -p"$(sudo cat $MYSQL_ROOT_PWD_FILE)" -B "$db_name" --default-character-set=utf8mb4 < ./db.sql

and everything run smoothly now, until yunohost decide to update their app . . . .

[Bugsbane]

Thank you everyone who posted solutions for modifying databases with this problem. I'll likely be using your solutions myself. Ultimately though, the point of this issue is to get MB4 to be used by default on new installs.

I just did a fresh install of Yunohost and then Nextcloud recently, and even there, by default, I got the error about the database not being MB4. It's understandable on older installs, but for fresh ones it would be great if the nextcloud_ynh app was updated to use MB4 by default.

[klorydryk]

After update to v19, I get a warning about this issue in the admin page. Do I need to update the database by myself? What is the best way to do it?

[Thatoo]

If we have a look at the forum, we can find several issue linked to that : https://forum.yunohost.org/search?q=UTF8mb4 It seems that it also creates problems with the nextcloud's mail app. We can found a lot of issue talking about Unending loading of mails on the github repo of nextcloud, such as https://github.com/nextcloud/mail/issues/3815 So I guess it would be nice if Yunohost's nextcloud app would switch to UTF8mb4 on new install and could also handle the migration in a future update.

[Thatoo]

If it was an "enhancement" label in 2017 at the time of nextcloud 12, I guess it is a need in 2020 for nextcloud 20 if this issue create problem with news app, mail app and add a disturbing warning message in the admin panel that non adminsys would not know how to handle.

[Bugsbane]

Yeah. When not implementing something causes some of the most popular apps to break and potentially lose data, then it's time to label this as bug rather than an enhancement.

I'd be happy to test with my experimental Nextcloud install if it helps.

Until then, it would be nice to post a manual fix here and/ or in the forums for users hitting this issue already. I know I've spent quite a bit of time searching for one suited to a Yunohost nextcloud install.

[kay0u]

Should be fixed by https://github.com/YunoHost-Apps/nextcloud_ynh/pull/384

[gnouts]

Awesome ! @kay0u Can you tell what will happen if I already applied the changed manually (like commented there) ? Also, your changes do not seem to modify backup/restore scripts :thinking: ?

[kay0u]

@kay0u Can you tell what will happen if I already applied the changed manually (like commented there) ?

Nothing

Also, your changes do not seem to modify backup/restore scripts thinking ?

That was a mistake, it should be ok now (but I'll trigger a test on the CI to be sure)

Thank you for the feedback :+1:

Edit: it should be good now

[zyphlar]

Is there a reason why this isn't included in the upgrade process (DB migrations, app/YNH updates, etc?) I just updated Yunohost and all apps and NextCloud Mail is stuck in an infinite loop. I feel like I really need the work done here, but I can't tell if applying it would work, and if it might create a conflict when/if it's finally included in YNH: https://github.com/YunoHost-Apps/nextcloud_ynh/pull/384

[Thatoo]

Where are we now on this subject?

[ericgaspar]

MySQL 4-byte support is now in master branch

[Thatoo]

So from nextcloud 27 we are sure to have mysql.utf8mb4 enabled right? Is there a way to check that from within nextcloud before attempting to install apps that require mysql.utf8mb4 such as social? Can we then close this issue then?

[Thatoo]

Can this issue be closed?