12

I am trying to migrate my rails application from mysql to postgres. Since we have already running application so I am moving mysql data to postgres database using pgloader. But when I do 

pgloader mysql://root:root_password@127.0.0.1/mysql_database postgresql://postgres_user:postgres_pass@127.0.0.1/postgres_database

I get error - Failed to connect to mysql at "127.0.0.1" (port 3306) as user "root": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled. I can easily log in to mysql from terminal though. Thanks in advance.

Harshwardhan Rathore
  • 249
  • 2
  • 12

1 Answers1

16

The problem is that currently pgloader doesn't support caching_sha2_password authentication plugin, which is default for MySQL 8, whereas older MySQL versions use mysql_native_password plugin. The corresponding issue is opened on Github.

Based on this comment, the workaround here is to edit my.cnf (if you don't know where it is, look here) and in [mysqld] section add

default-authentication-plugin=mysql_native_password

Then restart your MySQL server and execute:

ALTER USER 'youruser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

After that the error must be gone.

Michael Berdyshev
  • 1,286
  • 1
  • 12
  • 18
  • I tried this but the error still remains with MySQL 8.0.32 Homebrew. The `ALTER USER` command returned `Query OK, 0 rows affected`, is that to be expected? – Richard Kranendonk Jan 21 '23 at 12:59
  • 1
    @RichardKranendonk The command output is totally fine. Are you sure that you are using the same right credentials? I mean username, password, server, port, etc. And you can check the current authentication plugin by executing `SELECT user, host, plugin FROM mysql.user;` – Michael Berdyshev Jan 22 '23 at 16:19
  • plugin for root is mysql_native_password; mysql.infoschema, mysql.session and mysql.sys use caching_sha2_password – Richard Kranendonk Jan 26 '23 at 09:32
  • @RichardKranendonk Could you check the current server plugin then: `sudo mysqladmin variables | grep default_authentication_plugin`? – Michael Berdyshev Jan 27 '23 at 11:06
  • Returns: `default_authentication_plugin | caching_sha2_password` – Richard Kranendonk Jan 31 '23 at 12:22
  • 1
    @RichardKranendonk That's the point. It must be `mysql_native_password` after the changes. Either you haven't edited `my.cnf` file or you haven't restarted the MySQL server. Please follow my instructions exactly. – Michael Berdyshev Feb 01 '23 at 09:58
  • This solution just doesn't work for me. I'm running mysql in a docker-compose situation and the docker-compose.yml file specifies `command: --default-authentication-plugin=mysql_native_password`. I took the suggested steps nevertheless, and it didn't work. – tadasajon Feb 22 '23 at 22:05
  • @tadasajon It looks like the command is not complete. I haven't tested my solution with _docker-compose_, but could you check [this GitHub comment](https://github.com/dimitri/pgloader/issues/782#issuecomment-1000153539)? – Michael Berdyshev Feb 24 '23 at 00:59