topic Re: Could not connect to source endpoint running MySQL 5.7 in Qlik Replicate

Could not connect to source endpoint running MySQL 8

desmondchew — Fri, 28 Apr 2023 09:49:31 GMT

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

Re: Could not connect to source endpoint running MySQL 5.7

Dana_Baldwin — Thu, 27 Apr 2023 16:04:39 GMT

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-specified-module-could-n

Thanks,

Dana

Re: Could not connect to source endpoint running MySQL 5.7

desmondchew — Fri, 28 Apr 2023 02:22:07 GMT

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

Re: Could not connect to source endpoint running MySQL 5.7

Steve_Nguyen — Fri, 28 Apr 2023 11:51:05 GMT

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

Re: Could not connect to source endpoint running MySQL 5.7

SwathiPulagam — Fri, 28 Apr 2023 16:26:22 GMT

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

Re: Could not connect to source endpoint running MySQL 5.7

desmondchew — Sat, 29 Apr 2023 12:22:25 GMT

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

Re: Could not connect to source endpoint running MySQL 8

desmondchew — Mon, 01 May 2023 05:14:22 GMT

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

Re: Could not connect to source endpoint running MySQL 8

Steve_Nguyen — Mon, 01 May 2023 16:15:35 GMT

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

Re: Could not connect to source endpoint running MySQL 5.7

desmondchew — Thu, 04 May 2023 02:45:23 GMT

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

Re: Could not connect to source endpoint running MySQL 5.7

desmondchew — Wed, 30 Aug 2023 03:28:11 GMT

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

Re: Could not connect to source endpoint running MySQL 8

john_wang — Thu, 31 Aug 2023 04:44:25 GMT

Hello @desmondchew ,

Thanks for sharing the information. The mysql_native_password authentication plugin works fine in the existing MySQL major versions still (8.x, 8.1). At least in our labs test, ODBC 8.0.32 works fine with MySQL 8.1.0 Server in our smoking tests. Qlik R&D will support the newer authentication plugins in the coming Replicate patch/versions as well.

Hope this helps.

Regards,

John.

Re: Could not connect to source endpoint running MySQL 8

john_wang — Tue, 30 Jul 2024 07:33:52 GMT

Hello @desmondchew ,

Update about this issue:

Support for caching SHA-2 pluggable authentication with MySQL endpoints

Replicate May 2024 introduces support for caching SHA-2 pluggable authentication (caching_sha2_password) when working with MySQL sources or targets, either on-premises or in the cloud. In previous versions, only Native Pluggable Authentication (mysql_native_password) was supported.

Hope this helps.

John.