MySQL timeout with azure data factory copy

Question

I am using azure data factory to copy data from MySQL server as source. The data is big in size. When I setup the pipeline and execute it:

MySQL: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I think this can be solved with this answer. How can I add this configuration to my data factory pipeline using MySQL as source?

Update: I am using a normal script to copy data from on-premise MySQL to SQL data warehouse. The MySQL query is simple select: select * from mytable; Complete Error:

Copy activity encountered a user error at Source side: GatewayNodeName=MYGATEWAY,ErrorCode=UserErrorFailedMashupOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message='Type=Microsoft.Data.Mashup.MashupValueException,Message=MySQL: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.,Source=Microsoft.MashupEngine,',Source=,'.

Can you confirm that it's about default time out configuration? The answer you post is just using MySQL Connector, it's different from data factory on Azure. — Wayne Yang
The issue I am facing is that the query to fetch data takes too much time. Might even take more than hour. I am getting above error in the pipeline around 10 minutes into execution. — Ruchit Rami
It might be MYSQL timeout issue. Post SHOW GLOBAL VARIABLES LIKE "%timeout%"; output — ad4s

Wayne Yang Wayne Yang · Accepted Answer · 2017-09-20T05:43:24

Well, if this issue is about default time out configuration ,you can add these scripts in "activities" in your pipeline settings to set timeout to 1 hour:

"Policy": {
       "concurrency": 1,
       "timeout": "01:00:00"
 }

----------Update----------

the whole JSON of pipeline configuration like this:

{
     "name": "ADFTutorialPipelineOnPrem",
     "properties": {
     "description": "This pipeline has one Copy activity that copies data from an on-prem SQL to Azure blob",
     "activities": [
       {
         "name": "CopyFromSQLtoBlob",
         "description": "Copy data from on-prem SQL server to blob",
         "type": "Copy",
         "inputs": [
           {
             "name": "EmpOnPremSQLTable"
           }
         ],
         "outputs": [
           {
             "name": "OutputBlobTable"
           }
         ],
         "typeProperties": {
           "source": {
             "type": "SqlSource",
             "sqlReaderQuery": "select * from emp"
           },
           "sink": {
             "type": "BlobSink"
           }
         },
         "Policy": {
           "concurrency": 1,
           "executionPriorityOrder": "NewestFirst",
           "style": "StartOfInterval",
           "retry": 0,
           "timeout": "01:00:00"
         }
       }
     ],
     "start": "2016-07-05T00:00:00Z",
     "end": "2016-07-06T00:00:00Z",
     "isPaused": false
   }
 }

This following sample assumes you have created a table “MyTable” in MySQL and it contains a column called “timestampcolumn” for time series data.Setting “external”: ”true” informs the Data Factory service that the table is external to the data factory and is not produced by an activity in the data factory.:

{
        "name": "MySqlDataSet",
        "properties": {
            "published": false,
            "type": "RelationalTable",
            "linkedServiceName": "OnPremMySqlLinkedService",
            "typeProperties": {},
            "availability": {
                "frequency": "Hour",
                "interval": 1
            },
            "external": true,
            "policy": {
                "externalData": {
                    "retryInterval": "00:01:00",
                    "retryTimeout": "01:00:00",
                    "maximumRetry": 3
                }
            }
        }
    }

More details about how to create pipeline for Azure data factory, refer to this document

More about the whole tutorial to move data from on-premise MySQL to Azure Data Factory,refer to this link.

MySQL timeout with azure data factory copy

1 Answers