[Solved] Can't migrate 10.0.0 from MySQL 8.4 to Postgres due to MySQL auth changes

Troubleshooting
1

Summary
Mattermost PostgreSQL migration doesn’t work when migrating from MySQL 8.4 due to authentication changes in that version

Steps to reproduce
Attempt to migrate Mattermost 10.0.0 from MySQL 8.4 to Postgresql using pgloader

Expected behavior
Migration proceeds as described in the documentation here: Automated PostgreSQL migration - Mattermost documentation

Observed behavior
I am able to complete steps 1-3, I have created a pgloader configuration. When I try to complete step 4 ‘run pgloader’, it fails with the following error:

ERROR mysql: Failed to connect to mysql at "1.2.3.4" (port 3306) as user "mmmigration": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.

The workaround listed in the pgloader issue here is no longer applicable due to changes in MySQL 8.4: QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION attempting to load data from mysql 8.0.4-rc. · Issue #782 · dimitri/pgloader · GitHub

Based on my research, pgloader cannot connect to MySQL 8.4 at all, due to authentication changes introduced in MySQL 8.4. I am looking for a fix or workaround for this problem

I created my MySQL user like so:

CREATE USER 'mmmigration'@'%' IDENTIFIED WITH mysql_native_password BY 'foo';

in mysql.conf I have this:

mysql-native-password=ON

MySQL 8.4 removes the configuration option default_authentication_plugin so this is as much as I can do to get MySQL to authenticate with native password.

2

Hi @tpdmoore, thanks for reaching out! It looks like the MySQL 8.4 authentication changes are causing issues with pgloader. You may want to check our PostgreSQL Migration documentation for any updates, and consider trying a manual export/import process as a potential workaround. Let us know if you need further assistance!

3

Manual process doesn’t fix the problem because it still uses pgloader

I managed to work around this problem by using proxysql to proxy pgloader’s connection to the Mattermost MySQL database. The migration completed and I’m up and running on postgres

4

Yay! Thanks so much for following up to let us know, David!