how to fix connect timeout while copiying from mysql to azure sql using AZURE DATA FACTORY

Arturo Beltran 0

I have this error while deploying the task of copiying 5 tables from mysql to azure sql on datafactory

Operation on target ForEach_qx9 failed: Activity failed because an inner activity failed; Inner activity name: Copy_qx9, Error: ErrorCode=MySqlConnectionError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Unable to connect to the mysql server, errorMsg: Connect Timeout expired.,Source=Microsoft.DataTransfer.Connectors.MySqlCore,''Type=MySqlConnector.MySqlException,Message=Connect Timeout expired.,Source=MySqlConnector,''Type=MySqlConnector.MySqlException,Message=Connect Timeout expired.,Source=MySqlConnector,'

The test connection is fine, i can see all my tables and its data on the preview. I did also checked if theres any ban to adf ip but i set mysql so i can connect from any ip as shown:

mysql> SHOW VARIABLES LIKE 'bind_address';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| bind_address | * |

+---------------+-------+

What should be the problem?

Vinodh247 22,696 Reputation points

2024-08-22T00:01:14.4033333+00:00
Ensure that the outbound IP addresses used by Azure Data Factory are whitelisted in your MySQL server's security settings. Sometimes, even if the connection test is successful, the actual data transfer can fail due to IP restrictions. You can find the outbound IP addresses for your ADF instance in the Azure portal and add them to your MySQL server's allowed IP list

Adjust the connection timeout settings in your ADF linked service configuration. You may need to increase the timeout duration to allow more time for the connection to establish, especially if the MySQL server is under heavy load or if network latency is an issue

Implement Retry Logic to your pipeline

Review the logs and metrics provided by ADF to identify any patterns or specific errors that occur during the copy operation
Arturo Beltran 0 Reputation points

2024-08-22T07:51:46.3933333+00:00
As said first message, I see no limited ip address connection from mysql server mysql> SHOW VARIABLES LIKE 'bind_address'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | bind_address | * | +---------------+-------+

this is the configuration of the mysql linked service connection.I cant see the option to change that but I set the timeout conf of the pipeline to 12hours..

I did also try changing the retry logic but no resuslts..
{ "name": "MySql1", "type": "Microsoft.DataFactory/factories/linkedservices", "properties": { "annotations": [], "type": "MySql", "typeProperties": { "server": "xxxxxxxxxxxx", "port": "3306", "database": "xxxxxxxxx", "username": "xxxxxx", "sslMode": 1, "useSystemTrustStore": 0, "driverVersion": "v2", "encryptedCredential": "xxxxxx" } } }
NIKHILA NETHIKUNTA 2,950 Reputation points Microsoft Vendor

2024-08-26T04:59:28.3633333+00:00

Hi @Arturo Beltran
Thank you for the question and using Microsoft Q&A platform.
Based on the error message you provided, it seems that the copy activity in your data factory is failing because it is unable to connect to the MySQL server. The error message specifically mentions that the connection timed out.

Since you mentioned that you were able to successfully test the connection and preview the tables and data, it is possible that the issue is related to the size of the data being transferred. Copying large amounts of data can sometimes cause timeouts or other connectivity issues.

Increase the timeout value for the MySQL connection in your data factory. You can do this by setting the "Connect Timeout" property in the connection string to a higher value.

You can change the connect timeout by following the below link:
https://stackoverflow.com/questions/14726789/how-can-i-change-the-default-mysql-connection-timeout-when-connecting-through-py

Hope this helps. Please let us know if you have any further questions.
NIKHILA NETHIKUNTA 2,950 Reputation points Microsoft Vendor

2024-08-27T09:26:28.3+00:00

Hi @Arturo Beltran
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
NIKHILA NETHIKUNTA 2,950 Reputation points Microsoft Vendor

2024-08-28T15:29:08.7+00:00

Hi @Arturo Beltran
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

how to fix connect timeout while copiying from mysql to azure sql using AZURE DATA FACTORY

Your answer