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

[ClaudineiODT]

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

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.

[ClaudineiODT]

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

[aarun_arasu]

Hello @ClaudineiODT ,

Glad to hear that !!

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

Regards

Arun

[ClaudineiODT]

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.

[ClaudineiODT]

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.

[SushilKumar]

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

Regards,

Sushil Kumar

[john_wang]

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.

[ClaudineiODT]

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:

1. I double-checked the installation process of the MySQL ODBC driver.
2. 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.
3. 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.

[john_wang · Accepted solution]

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.

[ClaudineiODT]

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