topic Re: Getting error for a new tasks using MySQL as both source and target endpoints. in Qlik Replicate

Getting error for a new tasks using MySQL as both source and target endpoints.

desmondchew — Tue, 07 Feb 2023 11:53:57 GMT

I have setup a new task using MySQL as source and target end points. We do have license using MySQL as source.

We are unable to load not a single table. Both MySQL are running 8.0.31. Qlik replicate server is Nov 2021 version odbc driver 8.0.23 connector.

Stream component 'st_0_OnPrem_MySQLDev' terminated
Cannot initialize subtask
Failed while preparing stream component 'st_0_OnPrem_MySQLDev'.
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 'hostname A'

Please help.

Regards

Thanks
Desmond

Re: Getting error for a new tasks using MySQL as both source and target endpoints.

Steve_Nguyen — Tue, 07 Feb 2023 12:49:12 GMT

Qlik Replicate doesn't support caching_sha2_password.

Please, have a look at the following Community post where my colleague provides some additional explanation on that: https://community.qlik.com/t5/Qlik-Replicate/Does-Qlik-Replicate-support-quot-Authentication-plugin-caching/td-p/1906331

Re: Getting error for a new tasks using MySQL as both source and target endpoints.

john_wang — Tue, 07 Feb 2023 13:16:10 GMT

Hello @desmondchew ,

This is because in MySQL 8.0.4 the default authentication plugin is caching_sha2_password (in previous versions eg v5.7 the default plugin is mysql_native_password) , Replicate does not support caching_sha2_password at the moment (will support it in next versions). there are different ways to solve the problem eg providing the relevant DLLs (this is not recommended), or change the user authentication plugin (this is the best solution) so Steve & Lyka's link solve the problem perfectly.

Regards,

John.

Re: Getting error for a new tasks using MySQL as both source and target endpoints.

desmondchew — Wed, 08 Feb 2023 07:39:04 GMT

So I have applied the link that Steve's shared as a workaround. The task was able to run. Then I have another tasks that uses MySQL as source and Snowflake as target endpoint, seems to give a similar error. I am attaching the logs in verbose mode, masking some details.

I am checking with another team that owns the MySQL if it's running MySQL 8. I am guess is it's on MySQL 5.7 which should not give us the same error.

00013820: 2023-02-08T01:20:11:373808 [SOURCE_CAPTURE ]E: 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 'hostname04' [1020414] (mysql_endpoint_capture.c:313)
00013820: 2023-02-08T01:20:11:373808 [SOURCE_CAPTURE ]E: Errors in MySQL server validation. [1020414] (mysql_endpoint_imp.c:670)
00013820: 2023-02-08T01:20:11:373808 [TASK_MANAGER ]E: Failed while preparing stream component 'st_0_MeriApps_DEV'. [1020414] (subtask.c:853)

Re: Getting error for a new tasks using MySQL as both source and target endpoints.

john_wang — Wed, 08 Feb 2023 07:50:39 GMT

Hello @desmondchew ,

You may run query to understand further (by a privileged account):

SELECT USER,HOST,PLUGIN FROM MYSQL.USER WHERE USER='xxxx';

where 'xxxx' is the user name in MySQL.

Hope this helps.

Regards,

John.

Re: Getting error for a new tasks using MySQL as both source and target endpoints.

desmondchew — Fri, 10 Feb 2023 03:04:52 GMT

It turns out the MySQL is also running 8.0.29. That explains the error. Thank you.

Can Replicate use binary logging=mixed? We hit into an error and it asked to revert to ROW. The prerequisites manual says need ROW but the application team folks is sceptical.

Desmond

Re: Getting error for a new tasks using MySQL as both source and target endpoints.

john_wang — Fri, 10 Feb 2023 03:27:12 GMT

Hello @desmondchew ,

Thanks for your following up.

The binlog format must be row (see Replicate user guide binlog_format), otherwise the binlog information is insufficient to meet Replicate capture changes prerequisites.

Regards,

John.

Re: Getting error for a new tasks using MySQL as both source and target endpoints.

desmondchew — Fri, 10 Feb 2023 06:34:24 GMT

Hi John,

I have reverted to use native password and the error has no longer there. This is for the task replicating both MySQL as source and target endpoints.

However, I am hitting the error

Creating Metadata Manager's utility components failed
Cannot create the source utility component
Failed while preparing stream component 'OnPrem_MySQLDev'.
Cannot connect to ODBC provider
RetCode: SQL_ERROR SqlState: HY000 NativeError: 1045 Message: [MySQL][ODBC 8.0(w) Driver]Access denied for user 'username'@'hostname.com' (using password: YES)

We are using MySQL odbc connector 8.0.23

Re: Getting error for a new tasks using MySQL as both source and target endpoints.

john_wang — Fri, 10 Feb 2023 08:14:52 GMT

Hello @desmondchew ,

In general this is a pure MySQL account issue, include the account is not allowed from this IP, or the password is incorrect etc, many reasons may lead the exact same error. You may try to use the provided credential to try to connect to MySQL Server manually, detailed information can be found at MySQL website https://dev.mysql.com/doc/refman/8.0/en/problems-connecting.html .

Let me know if it helps.

Regards,

John.

Re: Getting error for a new tasks using MySQL as both source and target endpoints.

desmondchew — Fri, 10 Feb 2023 08:17:05 GMT

Gotcha. It's resolved now. It's an account issue.

Thanks
Desmond

Re: Getting error for a new tasks using MySQL as both source and target endpoints.

john_wang — Fri, 10 Feb 2023 08:19:15 GMT

Hello @desmondchew ,

Glad to hear that.

if the provided solution helps please "Accept as Solution". Thank you,

John.