sqlserver到阿里云的离线全量同步报错

最新推荐文章于 2023-12-06 16:03:25 发布

CJun_Frank

最新推荐文章于 2023-12-06 16:03:25 发布

阅读量786

点赞数

文章标签： sqlserver 阿里云 microsoft

本文链接：https://blog.csdn.net/chenjundjz/article/details/122369970

版权

线下IDC机房到阿里云的离线全量抽数报以下错误:

com.alibaba.datax.common.exception.DataXException: Code:[postHandlerErrorCode-17], Description:[run time errors]. - Connection reset - com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset

但是我们线下的IDC数据库没有设置超时时间

解决方案:

1.降低并发和连接数的设置,发现这样可以同步一部分数据,但是如果数据量太大还是无法同步

2.自适应切分

添加如下:

"enableSelfAdaptionSplit": "true",
    "selfAdaptionSplitErrorMessage": [
        "Connection reset"
    ],
    "selfAdaptionSplitWriterPlugin": [
        "mysqlwriter"
    ],

"writeMode":"replace"

原来:
  "type": "job",
  "version": "2.0",
  "steps": [
    {
      "stepType": "sqlserver",
      "parameter": {
        "envType": 1,
        "datasource": "CRM_RETAIL_DW_REAL_TIME",
        "column": [
          "ID",
          "CUSTOMER_ID",
          "MERCHANT_ID",
          "ORDER_NO",
          "OUT_TRADE_NO",
          "REQ",
          "RES",
          "CREATED"
        ],
        "where": "",
        "splitPk": "ID",
        "table": "dbo.HXD_MYBANK_LOG"
      },
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "analyticdb_for_mysql",
      "copies": 1,
      "parameter": {
        "postSql": [],
        "envType": 1,
        "datasource": "flink_db",
        "column": [
          "ID",
          "CUSTOMER_ID",
          "MERCHANT_ID",
          "ORDER_NO",
          "OUT_TRADE_NO",
          "REQ",
          "RES",
          "CREATED"
        ],
        "writeMode": "insert",
        "batchSize": 2048,
        "table": "HXD_MYBANK_LOG",
        "preSql": []
      },
      "name": "Writer",
      "category": "writer"
    }
  ],
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  },
  "setting": {
    "executeMode": "distribute",
    "errorLimit": {
      "record": ""
    },
    "locale": "zh_CN",
    "speed": {
      "throttle": false,
      "concurrent": 10
    }
  }
}

现在:
{
    "type": "job",
    "version": "2.0",
    "steps": [
        {
            "stepType": "sqlserver",
            "parameter": {
                "datasource": "CRM_RETAIL_DW_REAL_TIME",
                "envType": 1,
                "enableSelfAdaptionSplit": "true",
                "selfAdaptionSplitErrorMessage": [
                    "Connection reset"
                ],
                "selfAdaptionSplitWriterPlugin": [
                    "mysqlwriter"
                ],
                "column": [
                    "ID",
                    "CUSTOMER_ID",
                    "MERCHANT_ID",
                    "ORDER_NO",
                    "OUT_TRADE_NO",
                    "REQ",
                    "RES",
                    "CREATED"
                ],
                "where": "",
                "splitPk": "ID",
                "table": "dbo.HXD_MYBANK_LOG"
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "analyticdb_for_mysql",
            "parameter": {
                "postSql": [],
                "datasource": "flink_db",
                "envType": 1,
                "column": [
                    "ID",
                    "CUSTOMER_ID",
                    "MERCHANT_ID",
                    "ORDER_NO",
                    "OUT_TRADE_NO",
                    "REQ",
                    "RES",
                    "CREATED"
                ],
                "writeMode": "replace",
                "batchSize": 2048,
                "encoding": "UTF-8",
                "table": "HXD_MYBANK_LOG",
                "preSql": []
            },
            "name": "Writer",
            "category": "writer"
        }
    ],
    "setting": {
        "errorLimit": {
            "record": ""
        },
        "locale": "zh_CN",
        "speed": {
            "throttle": false,
            "concurrent": 10
        }
    },
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    }
}