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

[desmondchew]

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

[Steve_Nguyen · Accepted solution]

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

[john_wang]

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.

[desmondchew]

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)

[john_wang]

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.

[desmondchew]

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

Desmond

[john_wang]

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.

[desmondchew]

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

[john_wang]

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.

[desmondchew]

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

Thanks
Desmond