ADF SHIR; MySQL; and connectivity issues

Question

tl;dr;

The self-hosted integration runtime, SHIR, reports connection timeout errors to MySQL instances whereas a query from HeidiSQL/MySQL Workbench to the same instance works fine.

2021-10-18 update

It appears to be a timeout issue. The SHIR drops the connection attempt after 15 seconds. My connection takes 30 seconds to resolve. Is there a SHIR setting I can override to allow for my slow connection?

Setup.

192.168.1.180 is a windows 2019 box, running the SHIR for a client. It is patched up to date. The firewall is off. There is no antivirus running on it. SHIR can communicate to azure ADF just fine. SHIR cannot connect to the assorted MySQL instances that are running. I have tested using both the latest SHIR IntegrationRuntime_5.10.7918.2 and the IntegrationRuntime_5.8.7875.2 both yield the same results.

192.168.1.178 is an ubuntu image running an older version of MySQL 5.6.40. I have created a user etl_reader@% and granted it select permissions. I have confirmed I can connect to this MySQL instance from 192.168.1.180 (and a variety of other boxes) using HeidiSQL as well as command line mysql on the linux boxen.

Symptoms.

ADF - add linked server, specify IP, user and password directly. SSL mode of preferred but I've tried with the other options and they all result in "The connection has timed out while connecting to server: 192.168.1.178 at port: 3306."

Remote desktop into .180 and try the diagnostics option from the SHIR. Under Test Connection - Type of MySql, ip of 192.168.1.178, dbname XXX, user etl_reader, password XXX. I have also tried etl_reader@%, etl_reader@192.168.1.178, and etl_reader@192.168.1.180. These all result in "[Microsoft][MySQL] (1004) The connection has timed out while connecting to server: 192.168.1.178 at port: 3306.
ERROR [08001] [Microsoft][MySQL] (1004) The connection has timed out while connecting to server: 192.168.1.178 at port: 3306."

Still in the same remote desktop connection, I use HeidiSQL and connect to the .178 just fine.

So maybe a driver issue? HeidiSQL specifies it's using the libmariadb.dll that it ships with. Documents indicate the SHIR uses built in drivers. I found a reference to Microsoft MySQL ODBC Driver in the Connectors - Integration Runtime event log. So, I installed the 64bit MySQL/Connector 8 and then attempted to use a DNS-less connection string as described in the documents

DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=192.168.1.178;DATABASE=XXX;USER=etl_reader;PASSWORD=XXX;OPTION=3;

That results in a different errors "ERROR [08S01] [MySQL][ODBC 8.0(w) Driver]Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060"

Going down the something screwy with network path, I installed wireshark and captured traffic on 180 to 178. First with HeidiSQL and then with the native MySQL option (No 4023->4446). I think that looks right in both of them: SYN->ECR->CWR; SYN->ACK; ACK. That SSDP seems weird but I am punching well above my weight.

What else.

FIPSAlgorithmPolicy is not enabled on the server.

It's English Language and regional settings.

MySQL ODBC without DSN
https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-without-dsn.html

MySQL Linked Server Setup

{
"name": "MySql1",
"type": "Microsoft.DataFactory/factories/linkedservices",
"properties": {
"annotations": [],
"type": "MySql",
"typeProperties": {
"connectionString": "server=192.168.1.178;port=3306;database=XXX;user=etl_reader;sslmode=1;usesystemtruststore=0",
"encryptedCredential": "XXX"
},
"connectVia": {
"referenceName": "shir-one-eighty",
"type": "IntegrationRuntimeReference"
}
}
}

The MySQL Linked Service is using the shir-one-eighty as noted above instead of the default AutoResolveIntegrationRuntime

Accepted Answer

Once I identified that the issue appears to be the laggardly opening of the connection to MySQL, the resolution was simple.

On the Linked Service, under the Parameters section, I added a "Connect Timeout" parameter, type of Int, value of 60

How did I identify the slow open?

I had a bit python working that used the mysql connector and wrapped the open call in a simple timer and observed it was approximately 30 seconds each time.
I ported same code to PowerShell and observed the ODBC open call kept failing but it also stopped at 15 seconds. I added an explicit ConnectionTimeout of 45 seconds and that code began working.

I used the SHIR Test Connection feature on the Diagnostics tab and a stopwatch and yes, it too times out at 15 seconds so likely a default .NET connection property. But, there does not appear to be an option for specifying the timeout in the SHIR. But, providing the timeout to the Linked Service resolved the issue.

Answer

Hello @,
Thanks for the ask and using Microsoft Q&A platform , I loved the detailed ask . Can you please check what the Integration runtime are you using the linked service ? Ideally it should point to the SHIR .

Please do let me know how it goes .
Thanks
Himanshu

Share via