Using a different instance of MySQL (in the same place, with the same data files) seems to stop the Mattermost server container from starting.
Steps to reproduce
I’m using Mattermost 9.1.2 installed on Kubernetes using the mattermost-operator, with (formerly) a MySQL instance installed via a preexisting mysql-operator as its database back-end. So far, so good.
Unfortunately, we’ve had a lot of trouble with the MySQL operator both in general and with regard to some other applications, and so decided to eliminate it, since it wasn’t adding a lot of value to our small cluster anyway. We believed that we could handle Mattermost’s database the same way we had successfully handled others, namely:
Scale down the Mattermost deployment on the cluster to zero pods.
Eliminate the current database instance and the mysql-operator installation, but leave the private volume with all the MySQL data files behind.
Manually create a MySQL deployment and service to run MySQL over that same private volume, so it would pick up the previous databases just as they were before, and would be accessible at the same location (mattermostdb.mattermost.svc.cluster.local).that they were before.
Make sure that that database is accessible (it is) and isn’t configured read-only/super-read-only (it isn’t).
Start Mattermost back up, at which point everything should work just like it did before.
Expected behavior
Mattermost starts up and runs normally.
Observed behavior
The Mattermost container won’t stay started. It exits shortly after starting up with exit code 2. No logs are displayed for the pod.
I’ve been giving this a try (following the procedure given here to migrate the existing data - Migration guidelines from MySQL to PostgreSQL — Mattermost documentation), and while I have the new db up and running, unfortunately pgloader doesn’t work for me as the old database was running on MySQL 8.2. Is there a migration procedure that uses some other tool?
Have you set default-authentication-plugin=mysql_native_password in your mysql.cnf?
If you have any existing user, then it might be worth doing a ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'mostest'; and then retry.
Following those steps and compiling pgloader from source did the trick and we use the exact method in our CI pipelines.
Yes, both - and confirmed that was working by forcing a test login using mysql_native_password. Unfortunately, even compiled from source, pgloader still wouldn’t connect to the db.
At this point, I’ve had to give up on the database migration (we can’t afford the downtime), and am hoping now to be able to extract the old data from a MySQL dump in such a way that I can get it back into Mattermost using the bulk loading tools.
@cerebrate Sorry to hear the struggle you had. MySQL 8 and pgLoader are not working great to say the least. As Agniva mentioned, we had to do some adjustments to our CI pipelines. There are two main issues we identified:
1- The authentication problem (aka UNSUPPORTED-AUTHENTICATION), to overcome this issue I’ll echo what Agniva said with an additional step; we recommend setting the default authentication to mysql_native_password in the mysql configuration file. Also altering user auth type and then restarting the mysql server and/or flushing the privileges.
2- The ECASE exception problem. This is a problem would occur after a successful authentication. Yet this is another issue with the mysql-8 and pgLoader with it’s pre-compiled versions. I don’t know why exactly it happens but compiling the tool from source fixes this problem.
If you get some time to do another attempt, please update us with your findings.
