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!

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.