QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION attempting to load data from mysql 8.0.4-rc.

[nlevitt]

QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION attempting to load data from mysql 8.0.4-rc.

I found that mysql's default_authentication_plugin changed in this version: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin That looked promising, but I got the same error after setting it to the old default value.

• [ ] pgloader --version

  pgloader version "3.4.01f877bad7"
  compiled with SBCL 1.3.1.debian
  

• [ ] did you test a fresh compile from the source tree?

yes

• [ ] did you search for other similar issues?

yes

• [ ] how can I reproduce the bug?

To install this version of mysql on ubuntu:

curl -sLSvO https://dev.mysql.com/get/mysql-apt-config_0.8.9-1_all.deb
sudo dpkg -i /tmp/mysql-apt-config_0.8.9-1_all.deb # choose 8.0
sudo apt-get update
sudo apt-get install mysql-server

$ lsb_release -d
Description:    Ubuntu 16.04.4 LTS
$ mysql --version
mysql  Ver 8.0.4-rc for Linux on x86_64 (MySQL Community Server (GPL))

Then run

sudo MYSQL_PWD=***** -u archiveit ./build/bin/pgloader --verbose mysql://archiveit@wbgrp-svc413/archiveit3 postgresql:///archiveit3

• [ ] pgloader output you obtain

2018-04-17T23:56:47.055000Z NOTICE Starting pgloader, log system is ready.
2018-04-17T23:56:47.146000Z LOG Migrating from #<MYSQL-CONNECTION mysql://archiveit@wbgrp-svc413:3306/archiveit3 {10097067C3}>
2018-04-17T23:56:47.154000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://archiveit@UNIX:5432/archiveit3 {1009941FF3}>
KABOOM! 
FATAL error: Failed to connect to mysql at "wbgrp-svc413" (port 3306) as user "archiveit": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
An unhandled error condition has been signalled:
   Failed to connect to mysql at "wbgrp-svc413" (port 3306) as user "archiveit": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.




What I am doing here?

Failed to connect to mysql at "wbgrp-svc413" (port 3306) as user "archiveit": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.

• [ ] data that is being loaded, if relevant

• [ ] How the data is different from what you expected, if relevant

[dimitri]

Given https://github.com/qitab/qmynd/blob/master/src/mysql-protocol/authentication.lisp ; can you try the mysql_native_password on the server side? I guess we should now add the new default too, if that's possible. Do you want to look into doing that?

[Deph0]

What's the status on this?

I'm trying to migrate a mysql database to psql and getting this error

[dimitri]

See https://github.com/qitab/qmynd/blob/master/src/mysql-protocol/authentication.lisp#L125 for the code handling the MySQL password challenge. The current status is that mysql_native_password authentication plugin is supported just fine, but caching_sha2_password is not there yet.

Also, a quick reading through https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html shows that to enable that default authentication scheme we also need to implement the --get-server-public-key somehow.

How easy/complex is it to just setup mysql_native_password on the server for pgloader use?

[Deph0]

From what I've attempted, it was with no success to with pgloader- I may also be miss-informed but this is what I think is what you have to do (however, again- with little difference): ALTER USER 'youruser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

[19h]

Using MySQL 8.0.11. Created a new user with mysql_native_password and empty password; also one with a password. Both connection attempts failed with QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION.

[nmcalabroso]

Dealing with this similar issue as well. Having a user identified by mysql_native_password doesn't work as well.

mysql --version
mysql  Ver 8.0.11 for osx10.13 on x86_64 (Homebrew)

KABOOM!
FATAL error: Failed to connect to mysql at "localhost" (port 3306) as user "ncalabroso": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
An unhandled error condition has been signalled:
   Failed to connect to mysql at "localhost" (port 3306) as user "ncalabroso": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.




What I am doing here?

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

[BrandonZacharie]

Can anyone describe the conditions in which pgloader works at all? Is there some magic combination of MySQL version, MySQL authentication method, OpenSSL version, and/or PostgreSQL version that works? I have had no success with any of the combinations I have tried so far. I always get either a The alien function "CRYPTO_num_locks" is undefined. or Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled. error.

Update: I got everything to work with MySQL (Amazon RDS) and PostgreSQL (localhost)

[dimitri]

When using debian, the SSL problems should be fixed in 3.5.2 packaging. Otherwise I'm not sure how to help you, SSL make things more complex than they seem. Then for MySQL, it's simple, the new default authentication scheme isn't yet supported in pgloader / qmynd, and I am currently lacking time to work on that. Contributions welcome!

[ghost]

Same problem, mysql 8.0.12, turned mysql_native_password on, pgloader v3.5.2. Ubuntu 18.04 if it matters

[jsangilve]

Same problem with mysql 8.0.15. It only worked after switching to mysql 5.7

[rolurq]

Any updates on this? I'm having the same issue.

[caiocutrim]

I have the same problem with mysql 8.0.16 installed in MacOs :-( (Mysql community server -gpl)

[anagram3k]

Same problem here. Any workarounds?

[zoechi]

I had to go back to MySql 5.6. 5.7 and 8 did not work.

[rolurq]

@zoechi are you sure about 5.7? because I did the same and I got back to MySql 5.7 and it worked

[lukaskuzmiak]

In order to avoid the QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION error you have to switch your mysqld to use mysql_native_password by default.

Edit your my.cnf and in [mysqld] section add:

default-authentication-plugin=mysql_native_password

Then you need to update your user's password to mysql_native_password type like this:

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

Voila.

Edit: I just want to make sure everyone understands, this means you are downgrading your authentication scheme, which is always a bad idea. This is a work-around, definitely not meant to be a permanent modification. But it gets the job done for a one-time migration. If you need to use pgloader periodically consider working on this issue or sponsoring it 🙂

[paveldvorak5]

is it possible to change only the user, not whole server?

[helsont]

I can confirm that @lukaskuzmiak's answer does work with MySQL 8.0.17. It's a pain but it gets the job done. Thanks!

[denischiciudean]

Any status on this one? I'm using a digital ocean cluster for both and I'm trying to migrate from a MySql one to a postgres one, and can't change any mysql config, and also all the users authe method is native

[fredow68]

The lukaskuzmiak's workaround works for me too (mysql 8.0.18 on centos 7 - pgloader 3.6.1). Thanks to him !

[eugeneYWang]

Any progress on supporting MySQL 8.0's default authentication?? changing my.cnf file on the server is basically not an option.....

[eugeneYWang]

As @paveldvorak5 stated, is it possible to make user level setting to work around this issue?

PS: tried ALTER USER command in MySQL 8, but then noticed that most users created before MySQL 8 already belong to mysql_native_password...., this means MySQL 8 is ignoring user-level setting. Otherwise, my user-level setting originally is already mysql_native_password..

This is the query I used to check:

select user,plugin,host from mysql.user where plugin = 'mysql_native_password';

However...still how that there is a workaround not changing my.cnf on the mysql server ....

[dimitri]

Yeah I still did not have time to work on the new default authentication scheme for MySQL 8.0.1 and later. If someone would like to help, that might be the most needed contribution to pgloader at the moment. Reminder: you can contribute your own time and skills or your company's money. I will then redirect the money to a Common Lisp hacker with free time in their hands, and yeah I have a list of people ready to do just that.

[manoa2617]

Any luck with a fix? I'm on MySQL Azure PaaS v8, altered the user but like others said, it makes not difference. Changing my.cnf is not possible.

[blazespinnaker]

@dimitri , can you give us a very brief rundown on where to change the code? I am familar with crypto/sec, but not pgloader or how it's organized. I'd be happy to help get the ball rolling tho

[lukaskuzmiak]

@blazespinnaker I think he has already mentioned that earlier in this thread, it is something that needs to appear in QMyND itself (IMHO), that is where the QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION is coming from.

[blazespinnaker]

Yes, perhaps not quite that brief. Anyways, no sweat. I am able to get this to work by change mysql.cnf

[aym0406]

is there anyway to avoid the QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION error without modifying the my.cnf file ? because default-authentication-plugin would be considered a vulnerability issue

[aym0406]

Any progress on supporting MySQL 8.0's default authentication?? changing my.cnf file on the server is basically not an option.....

@dimitri

[eugeneYWang]

@aym0406 I gave up pgloader. Currently it is not an option for cases like yours and mine.