topic Re: Attunity endpoint: Cannot connect MYSQL server in Qlik Replicate

Attunity endpoint: Cannot connect MYSQL server

sat29 — Wed, 07 Dec 2022 04:31:44 GMT

Setup an endpoint of MYSQL server in Attunity, test connection fail with the following message:

Cannot connect to ODBC provider ODBC general error.RetCode: SQL_ERROR SqlState: HY000 NativeError: 2026 Message: [unixODBC][MySQL][ODBC 5.3(w) Driver]SSL connection error: unknown error number ODBC general error.

Internal Parameters has set:
provider : MySQL ODBC 5.3.8 Unicode Driver

(Try set as either source or target type got the same error)

The Attunity Server and the MYSQL stand in the same segment

But also there has another existing MySQL server which could connected success

Re: Attunity endpoint: Cannot connect MYSQL server

john_wang — Wed, 07 Dec 2022 05:02:09 GMT

Hello @sat29 ,

Thanks for you opening the article.

MYSQL Driver depends on the Replicate version. Below is the Client prerequisites from the 2022.5 user guide:

Install MySQL ODBC Unicode Driver 64-bit on the Replicate Server machine.

Only these driver versions are supported:

5.3.11 to 5.3.13
8.0.23

you may find the correct client versions for other Replicate version in the above link.

If you want to point out the specific driver version then please follow the steps provided in the below community article specify a driver version .

BTW, let us know your MySQL Server version. Install the support version and let us know if the issue solved (remember to restart Replicate Services after MySQL ODBC Driver upgrading).

Hope this helps.

Regards,

John.

Re: Attunity endpoint: Cannot connect MYSQL server

sat29 — Wed, 07 Dec 2022 10:14:09 GMT

After downloaded (https://downloads.mysql.com/archives/c-odbc/) and upgraded mysql-connector-odbc-8.0.23-1.el7.x86_64.rpm, test connection fail with the following message:
=============================================================================================
Cannot connect to ODBC provider ODBC general error.RetCode: SQL_ERROR SqlState: HY000 NativeError: 2026 Message: [unixODBC][MySQL][ODBC 8.0(w) Driver]SSL connection error: Didn't find key on read or update ODBC general error.
=============================================================================================

#rpm --checksig mysql-connector-odbc-8.0.23-1.el7.x86_64.rpm
mysql-connector-odbc-8.0.23-1.el7.x86_64.rpm: (sha1) dsa sha1 md5 gpg OK

Re: Attunity endpoint: Cannot connect MYSQL server

Steve_Nguyen — Wed, 07 Dec 2022 13:19:24 GMT

1. what version of MySQL DB are you working with?

2. are you trying to connect with SSL , have you try to set SSL to none and test ?

3. from the Replicate Server , outside of Replicate, using Windows ODBC 64 bit, are you able to setup the ODBC connect to your MySQL server , does it works?

Re: Attunity endpoint: Cannot connect MYSQL server

sat29 — Thu, 08 Dec 2022 02:24:29 GMT

1. The MySQL version is 8.0.29

2. how to set SSL to none?

3. no sql client in the Replicate Server, but could connect to the MySQL server via another PC. Also the Replicate server could connect to another existing MySQL after upgrade to 8.0.23 odbc driver.

Re: Attunity endpoint: Cannot connect MYSQL server

sureshkumar — Thu, 08 Dec 2022 02:39:37 GMT

Hi Team,

how to set SSL to none?

--> MySQL Source Endpoint => Security => SSL Mode Preferred change this to None.

Regards,

Suresh

Re: Attunity endpoint: Cannot connect MYSQL server

sat29 — Thu, 08 Dec 2022 03:34:30 GMT

I could not found the option from UI, could you have screen dump please? (The Attunity version is 5.5.0.446)

Re: Attunity endpoint: Cannot connect MYSQL server

john_wang — Thu, 08 Dec 2022 03:42:37 GMT

Hello @sat29 ,

Replicate 5.5 reached its end of support date 2 years ago, see Replicate lifecycle .

Please upgrade to supported versions. A sample of the setting in current major versions GUI:

Hope this helps.

Regards,

John.

Re: Attunity endpoint: Cannot connect MYSQL server

Sweetie_A — Fri, 21 Jul 2023 09:18:11 GMT

Hello All

My source MySQL version is mysql Ver 8.0.25 for Win64 on x86_64, I want to use SSLS certificate but getting below error when I mention CA certificate path.

Error :

SYS-E-HTTPFAIL, Cannot connect to ODBC provider.
SYS,GENERAL_EXCEPTION,Cannot connect to ODBC provider,RetCode: SQL_ERROR SqlState: HY000 NativeError: 2026 Message: [MySQL][ODBC 5.3(w) Driver]SSL connection error: error:00000001:lib(0):func(0):reason(1)

Re: Attunity endpoint: Cannot connect MYSQL server

Arun_Arasu — Fri, 21 Jul 2023 09:41:50 GMT

Hello @Sweetie_A

Thanks for reaching out to Qlik community support.

The error message indicates that there is a problem establishing an SSL-encrypted connection to the MySQL database using the ODBC 5.3(w) driver.
The error code 2026 points to a specific SSL connection issue.

The error "error:00000001:lib(0):func(0):reason(1)" suggests that the SSL connection encountered an internal error due to an unspecified reason.

Please verify on the following points

Ensure that the SSL certificates are valid, properly configured, and accessible by the ODBC driver.

Check if the MySQL server is correctly configured to accept SSL connections. Verify the 'ssl-cert', 'ssl-key', and 'ssl-ca' parameters in the MySQL server configuration file.

Ensure that you are using the latest version of the ODBC driver. Sometimes, updating the driver can resolve compatibility issues.

Regards

Arun

Re: Attunity endpoint: Cannot connect MYSQL server

john_wang — Sat, 22 Jul 2023 06:33:06 GMT

Hello @Sweetie_A ,

Not sure what's the Replicate version and the ODBC client version you are running (we can see it's 5.3 however please let us know the exact build name), this information is helpful to understand the issue further.

Anyway, regarding the specific error below, it looks like certificate problem, the server/client certificate will need to be regenerated using a different common name than what was used for the CA certificate.

SYS,GENERAL_EXCEPTION,Cannot connect to ODBC provider,RetCode: SQL_ERROR SqlState: HY000 NativeError: 2026 Message: [MySQL][ODBC 5.3(w) Driver]SSL connection error: error:00000001:lib(0):func(0):reason(1)

A good sample is ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1).

BTW, please take note all the paths in MySQL source endpoint should be a specific file rather than a path only, a sample in my labs:

If the problem persists, the better option is configurating an ODBC DSN and using the DSN access to debug the error. if all works in DSN then it should be fine in Replicate.

Hope this helps.

Regards,

John.