Nextcloud asks to convert the database encoding, but I cannot run the MySQL commands

[csolisr]

My YunoHost server

Hardware: Rented VPS (OVH)
YunoHost version: 3.6.4.6
I have access to my server : Through SSH and webadmin
Are you in a special context or did you perform some particular tweaking on your YunoHost instance ? : yes, I added a local shared folder to Nextcloud in order to share music to an Airsonic instance

Description of my issue

When I try to upload certain files to my Nextcloud instance (for example, ones with emoji), the upload fails due to lack of support for the encoding. Browsing around I found out that the server must be manually configured to support UTF8mb4 specifically, and Nextcloud’s website has a list of steps to follow in order to configure MySQL to support 4-byte UTF8. However, since Yunohost is the one managing the databases, I cannot actually log into MySQL. Trying to run the application as root or admin outputs an error message stating that the user doesn’t have permissions:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

The next best thing I found was this post on the forum which recommended adding a few parameters to the my.cnf configuration file, which didn’t work either. When running mysql the error message was now as follows:

mysql: unknown variable 'innodb_large_prefix=ON'

And when attempting to run the Nextcloud occ script as recommended on the link above, the error message is:

$ sudo -u nextcloud php occ db:convert-mysql-charset
The database is not properly setup to use the charset utf8mb4.
For more information please read the documentation at https://docs.nextcloud.com/server/15/go.php?to=admin-mysql-utf8mb4

So then, what would be the proper way to enable UTF8mb4 on my server? Does it require manually logging in as whatever user was assigned as the MySQL administrator and if so, how do I obtain the auto-generated password?

[anonyme18]

Bonjour,
Je me connecte directement sur mon serveur, donc pas de SSH. En revanche, en root, je lance mysql dans un terminal et je suis directement connecté !
Puis, je crois que c’est ‘use nextcloud’ …
Une fois connecté, cet article me paraît bien.
Mais je ne suis pas sûr, qu’ensuite, Nextcloud supporte ?
Hello,
I connect directly to my server, so no SSH. On the other hand, as root, I run mysql in a terminal and I am directly connected!
Then, I think it’s ‘use nextcloud’…
Once connected, this article looks good to me.
But I’m not sure, that Nextcloud will support it afterwards?

[csolisr]

There’s the problem, I’m unable to use MySQL on my own server as either admin or root - is there a way to find which of my users has access to MySQL, obtain that user’s password and then reconfigure MySQL from there?

[csolisr]

For the record I found the solution:

sudo systemctl stop mysqld mariadb
sudo mysqld --user=mysql --pid-file=/var/lib/mysql/mysqld.pid --socket=/var/lib/mysql/mysql.sock  --datadir=/var/lib/mysql --skip-grant-tables --skip-networking &
sudo mysql --database mysql --socket=/var/lib/mysql/mysql.sock

And that gives me temporary access to MySQL, after that it’s a matter of setting a password for root:

UPDATE user SET Password=PASSWORD('my_password') where USER='root';
quit

Finally you just reboot the machine and continue with the guides posted above.