Could not connect to source endpoint running MySQL 8

[desmondchew]

Hi,

We have a source MySQL database running on version 5.7. We attempted to connect with a new task created. But hit into the following error. Please advise how to workaround?

Cannot initialize subtask
Failed while preparing stream component 'st_0_Meridian_Prod'.
Errors in MySQL server validation.
Error 1045 (Plugin caching_sha2_password could not be loaded: The specified module could not be found. Library path is 'C:/Program Files (x86)/mariadb-connector-c/lib/mariadb/plugin/caching_sha2_password.dll') connecting to MySQL server

[Dana_Baldwin · Accepted solution]

Hi @desmondchew 

Please modify the my.ini in a [mysqld] section like this:

#default_authentication_plugin=caching_sha2_password (comment line!)
default_authentication_plugin=mysql_native_password (new line)

Connect to your MySQL under root in a command line client and execute:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxx';

Where xxx is your current password.

re: https://stackoverflow.com/questions/50169576/mysql-8-0-11-error-connect-to-caching-sha2-password-the...

Thanks,

Dana

[desmondchew]

Hi Dana,

Our source MySQL is running 8.22 why is Qlik requires and older MySQL authentication method which is less secure? Our security team has concerned and refuse to allow us to workaround.

Is there any fix to this?

Thank you.
Desmond

[Steve_Nguyen]

@desmondchew 

1. is the error on the test connection or running the task?

2. on the Replicate server, what is the MySQL ODBC version?

3. have you test using Windows ODBC 64 bit and try to connect the MySQL server, does it have the issue error or success ?

[SwathiPulagam]

Hi @desmondchew ,

As Dana mentioned you have to use the mysql_native_password as sha2 is not supported.

If you want to work with sha2 then please do create a feature request in ideation:

 To submit Feature Requests going forward please use our Qlik Community "Product Insight and Ideas" forum as these requests will no longer be handled as technical support cases. As a Qlik Community member, you can actively engage with our Product Management team, vote on a product idea coming for other Qlik customers, submit your own ideas and get feedback from other members.

To get started please see our article: "Getting Started with Ideas": https://community.qlik.com/t5/Qlik-Product-Insight-Blog/Getting-Started-with-Ideas/ba-p/1684011

Thanks,

Swathi

[desmondchew]

Hi,

Please see reply in questions.

1. is the error on the test connection or running the task?

The error happens when I click on start full reload. Test connection runs fine.

Stream component 'st_0_xxxx_Prod' terminated
Cannot initialize subtask
Failed while preparing stream component 'st_0_xxxx_Prod'.
Errors in MySQL server validation.
Error 1045 (Plugin caching_sha2_password could not be loaded: The specified module could not be found. Library path is 'C:/Program Files (x86)/mariadb-connector-c/lib/mariadb/plugin/caching_sha2_password.dll') connecting to MySQL server 'xxxx'

2. on the Replicate server, what is the MySQL ODBC version?

8.0.23

3. have you test using Windows ODBC 64 bit and try to connect the MySQL server, does it have the issue error or success ?

No issue using MySQL to connect. I was able to launch MySQL client and connect to the DB. It's likely the way Qlik Replicate connects via ODBC and I don't understand why it uses Mariadb connector.

Desmond

[desmondchew]

We are still facing the same issue. Have opened a ticket however the support doesn't have a solution yet. This is very strange as we are connecting to a MySQL running on 8.0.29. The MySQL is an upgraded version from 5.7 previously. Could it be the reason that it might not be using the latest password algorithm?

Desmond

[Steve_Nguyen]

@desmondchew  if you open a case, please work with support, to isolate issue. 

[desmondchew]

Hi Swathi,

I appreciate your guide to submit a feature request. However, I would reckon my request was not for a special needs but basic one. MySQL 8 has been out for several years. Qlik supports many newer endpoints. Most shops setup are using the newer MySQL 8 and all AWS MySQL RDS are all on MySQL 8. Today security is a top priority in most setup. We can't fall back to a lesser security one without a good justification.

I believe we should make this as a priority.

Thank you.
Desmond

[desmondchew]

Hi,

I would like to share that the latest MySQL 8.0.31 version is depreciating the "mysql_native_password". 

• The mysql_native_password authentication plugin now is deprecated and subject to removal in a future version of MySQL. CREATE USER, ALTER USER, and SET PASSWORD operations now insert a deprecation warning into the server error log if an account attempts to authenticate using mysql_native_password as an authentication method. (Bug #35336317)

https://dev.mysql.com/doc/relnotes/mysql/8.1/en/news-8-1-0.html#mysqld-8-1-0-binlog

Does this mean we can't use 8.0.31 and above as we need to use "mysql_native_password"?

Thank you.
Desmond