topic ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database in Qlik Replicate

ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

ClaudineiODT — Wed, 06 Mar 2024 16:47:17 GMT

Hello Qlik Support Team,

I am still new in the Qlik Replicate 🙂

I am reaching out to report an issue I encountered while trying to connect to a MySQL source database using Qlik Replicate. Below are the details of the problem:

Error Message:

"ERROR: Cancel async command failed while trying to connect to a MySQL source database."

Context:

I am configuring a source connection to a MySQL database. After entering all the parameters and hitting the 'Test Connection' button, I receive the error mentioned above. Initially, I tried connecting to a MySQL database on a remote server in my private cloud, but without success. Following that, I installed MySQL locally on the same machine running Qlik Replicate, but this also did not succeed.

The only question related to this error that I could find is associated with a Teradata connection.

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-server-crashes-with-quot-Cancel-async-command/ta-p/2145977

Qlik Replicate on linux Red Hat version 8.9.
Version V2023.11.0.282, I upgraded it to this new version. Yesterday version was V2023.11.0.159.

I downloaded this copy of Download area of community site, the license is express.

Thanks in advance.
Claudinei.

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

ClaudineiODT — Thu, 07 Mar 2024 13:27:34 GMT

A brief update:
I have successfully connected to a PostgreSQL database.
Thanks.

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

aarun_arasu — Thu, 07 Mar 2024 13:30:06 GMT

Hello @ClaudineiODT ,

Glad to hear that !!

Would you like to share the troubleshooting step for the benefit of larger audience.

Regards

Arun

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

ClaudineiODT — Thu, 07 Mar 2024 13:51:28 GMT

Hi @aarun_arasu
It is a simple one.

Considering the possibility that the issue might be related to the connection between my local setup and the remote server, I initiated a troubleshooting process by testing with an alternative database system. I utilized a PostgreSQL database hosted on one of my remote servers, which operates on an Ubuntu server within my private cloud environment. To proceed, I configured a Replicate source endpoint for this database and conducted a connectivity test. The outcome was a successful connection.

If you need more in-depth information, please let me know.

Thanks.

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

ClaudineiODT — Tue, 12 Mar 2024 11:37:54 GMT

Hello @aarun_arasu

I want to clarify that the issue with the MySQL database still persists. My goal in using Postgres was specifically to determine whether there were any connection problems with my remote servers.

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

SushilKumar — Tue, 12 Mar 2024 12:28:50 GMT

Hello @ClaudineiODT

Thanks for reaching out to Qlik via Qlik Community Support page. Such issue requires through analysis of Task Settings, hence request you to reach out to technical Support via a case and provide Diagnostics package.

request you to Share the error as well

Please refer to Replicate https://community.qlik.com/t5/Knowledge/How-to-collect-Diagnostics-Package-from-Qlik-Replicate/ta-p/1815056

Regards,

Sushil Kumar

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

john_wang — Wed, 13 Mar 2024 03:56:24 GMT

Hello @ClaudineiODT ,

Looks like the MySQL source endpoint connectivity testing leads the Replicate Server crashing. You may check if the Replicate Server PID changed to see crashing occurred. If it happened, Replicate will try to automatically recover from the error, the program PID (Process ID) must be changed.

However let's try to make sure the MySQL connection works prior to Qlik Replicate:

1. The detailed steps of MySQL ODBC Driver installation on Linux

2. Make sure "/etc/odbcinit.ini" is accessible by Qlik Replicate account (by default it's "attunity", is created during the Replicate installation)

3. I'd like suggest you config a ODBC DSN in "/etc/odbc.ini" file and make sure it works, test the client/server connectivity by using command "isql" on Linux. If it works then Replicate should work as well.

Hope this helps.

John.

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

ClaudineiODT — Wed, 13 Mar 2024 14:42:37 GMT

Hello @john_wang

It seems you are right about the Replicate Server crashing. Let me show you the result of ps -ef | grep attunity before and after I tested the connection.

BEFORE:
-------------

attunity 1485 1 0 10:58 ? 00:00:00 /usr/lib/systemd/systemd --user
attunity 1486 1485 0 10:58 ? 00:00:00 (sd-pam)
attunity 1519 1 0 10:58 ? 00:00:00 /opt/attunity/replicate/bin/repctl -d /opt/attunity/replicate/data service start port=3550 rest_port=3552
attunity 1539 1519 0 11:03 ? 00:00:05 /opt/attunity/replicate/bin/repctl -d /opt/attunity/replicate/data service start port=3550 rest_port=3552
root 1609 319 0 11:25 pts/0 00:00:00 grep --color=auto attunity

AFTER:
----------

attunity 1485 1 0 10:58 ? 00:00:00 /usr/lib/systemd/systemd --user
attunity 1486 1485 0 10:58 ? 00:00:00 (sd-pam)
attunity 1519 1 0 10:58 ? 00:00:00 /opt/attunity/replicate/bin/repctl -d /opt/attunity/replicate/data service start port=3550 rest_port=3552
attunity 1616 1519 5 11:27 ? 00:00:00 /opt/attunity/replicate/bin/repctl -d /opt/attunity/replicate/data service start port=3550 rest_port=3552
root 1625 319 0 11:27 pts/0 00:00:00 grep --color=auto attunity.

The PID of the fourth line has changed, indicating a process update or restart.

Regarding the verification items:

I double-checked the installation process of the MySQL ODBC driver.
For point 2, the file "/etc/odbcinst.ini" did not have the attunity user as its owner. I've corrected this by changing both the owner and group to attunity.
For point 3, I tested the connectivity with the mysql command by accessing my remote database server, and it worked fine.

Despite these adjustments, the problem still persists. I'm continuing to investigate potential causes. In the meantime, if you have any additional insights or suggestions, they would be greatly appreciated.

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

john_wang — Wed, 13 Mar 2024 15:01:18 GMT

Hello @ClaudineiODT ,

Thanks for the update.

You are right, seems there is a serious problem in the client/server connectivity.

3. Please use "isql" as it will access the MySQL via unixODBC, as same as Qlik Replicate.

If the DSN works fine then we may use generic ODBC Target and input the DSN in the Endpoint to compare the regular MySQL endpoint to speed up the troubleshooting process.

If you face error with "isql" command, please share the screen copy and "/etc/odbc.ini" definition.

Thanks,

John.

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

ClaudineiODT — Wed, 13 Mar 2024 17:37:44 GMT

Hi @john_wang

I am encountering issues with the isql command; I am receiving a "Segmentation fault" error. Obviously, this issue is not related to Replicate.

Please see the attached screenshot.

Thanks

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

ClaudineiODT — Wed, 13 Mar 2024 18:14:09 GMT

Hi @john_wang

I finally managed to connect to the MySQL database. I took two actions:

I was encountering an error whenever I tried to install anything on this Linux system. To resolve this, I installed the following packages that were missing: "langpacks-en" and "glibc-all-langpacks". After installing these, the "Segmentation fault" error stopped, and I began receiving a "Could not SQLConnect" error instead.
To further investigate, I enabled error logging in odbcinst.ini with the following instructions:
[ODBC]
trace = Yes
TraceFile = /tmp/sql.log
ForceTrace = YesAfter implementing this, I was able to identify the issue and successfully connect to the MySQL database using isql.

The connection with my remote MySQL database is also working, as you can see in the screenshot provided.

I would like to express my gratitude for the insights and suggestions received here.

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

john_wang — Thu, 14 Mar 2024 02:24:44 GMT

Hello @ClaudineiODT ,

Excellent! isqlis the best tool in Linux platform to troubleshoot the connectivity issues, as it will access the backend database via unixODBC, and it also verifies the "/etc/odbcinit.ini" etc. Thank you so much for your great support!

If you do not mind, please choose a comment and mark it as solution.

Best Regards,

John.

Re: ERROR: Failed to Cancel Async Command While Trying to Connect to a MySQL Source Database

ClaudineiODT — Thu, 14 Mar 2024 16:27:53 GMT

Thaks John