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?

What's the status on this?

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

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?

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';

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.

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.

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)

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!

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

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

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

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

Same problem here. Any workarounds?

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

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

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.

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

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!

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

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

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

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 ....

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.

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.

@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

@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.

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

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

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

@dimitri

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

@eugeneYWang so what did you use instead ?
Could you give me futher information please

@aym0406 not a smart approach, but since I just need to move specific tables in a weekly basis, I just used mysql_fdw to move the whole table to Postgres.

I think @lukaskuzmiak answer doesn't work with mysql 8.0.19 :(.

Here my log:

➜  Downloads pgloader mysql://yusuf:1@localhost/kelime postgresql:///kelime 
2020-10-23T22:52:03.023000+01:00 LOG pgloader version "3.6.2"
2020-10-23T22:52:03.026000+01:00 LOG Data errors in '/private/tmp/pgloader/'
KABOOM!
FATAL error: Failed to connect to mysql at "localhost" (port 3306) as user "yusuf": 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 "yusuf": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.



2020-10-23T22:52:03.169000+01:00 LOG Migrating from #<MYSQL-CONNECTION mysql://yusuf@localhost:3306/kelime {10051E1193}>
2020-10-23T22:52:03.169000+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://yusufusta@UNIX:5432/kelime {1005326FF3}>

What I am doing here?

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