MySQL日期转换问题 [英] MySQL Date Conversion Issue

问题描述

这似乎是集成运行时的一个问题，所以如果这不是放置它的正确位置然后让我知道。

背景：
>
我有一个MySQL数据库，我将数据从中提取并复制到Azure SQL数据库。

MySQL数据库具有日期（或时间戳）的数据类型，并且禁用NO_ZERO_DATE模式，因此某些日期是0000-00-00。 （我无法控制MySQL数据源结构。）

问题：

从数据类型为日期的表中复制时出现以下错误

{" errorCode"：" ; 2200"，" message"："故障发生在'来源'一侧。 'Type = Microsoft.DataTransfer.Common.Shared.HybridDeliveryException，Message = Odbc
Operation Failed。，Source = Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector，''Type = System.Data.Odbc.OdbcException，Message =错误[22018] [Microsoft] [支持]（40550）转换规范的字符值无效。，Source = MySQLODBC_sb64.dll，''Type = Microsoft.DataTransfer.Common.Shared.HybridDeliveryException，Message = Odbc
操作失败。，Source = Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector，''Type = System.Data.Odbc.OdbcException，Message = ERROR [22018] [Microsoft] [Support]（40550）强制转换规范的字符值无效。，来源= MySQLODBC_sb64.dll，'"，" failureType"：
" UserError"，" target"：" Copy_ogj" }

我在测试时发现的事情：

如果前几条记录有毛派，那么0000-00-00值将被读入为NULL（其中我很喜欢）并且数据加载得很好。

在有（例如）第8000行开头的记录的表上，会抛出上面的ODBC错误。

如果我创建源ADF数据集数据类型的字符串，它看起来像ODBC仍然将数据读取为datetime并抛出相同的错误。

I在我的SELECT语句中尝试了ORDER BY，但只适用于只有一个Date列的表。

我当前的解决方案是将我的SELECT语句修改为CAST，将日期类型作为字符串，然后数据读入就好了。

我的问题：

有没有办法让集成运行时强制使这个函数成为NULL或者它在MySQL数据中读取ADF DataSet上定义的数据类型（所以我不必在SELECT语句中转换每个Date列）？

解决方案

您好群组。感谢您的耐心等待，因为这似乎是一个难题。 有很多潜在的解决方案，我希望你帮忙确定什么是适合你的。

首先，作为一个字符串引入是完全正确的事情，但是如果你需要丰富数据，那么在ADF中存在类型转换。

一种可能性是通过for-each活动推送数据，包含一些改变nulls。

或者，您可以将数据拉入目标，然后清理Azure Sql中的数据，并在那里更改数据类型。

如果您现在只需要获取数据，可以在复制活动中设置容错。

This is seems to be an issue with the integration runtime, so if this isn't the right place to put this issue then let me know.

Background:
I have a MySQL database that I'm pulling the data from and copying it over to an Azure SQL database.
The MySQL database has the Data Type of Date (or Timestamp) and the NO_ZERO_DATE mode is disabled, so some of the dates are 0000-00-00. (I have no control over the MySQL datasource structure).

Issue:
When copying from tables with the Data Type of Date I get the following error
{ "errorCode": "2200", "message": "Failure happened on 'Source' side. 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc Operation Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22018] [Microsoft][Support] (40550) Invalid character value for cast specification.,Source=MySQLODBC_sb64.dll,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc Operation Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22018] [Microsoft][Support] (40550) Invalid character value for cast specification.,Source=MySQLODBC_sb64.dll,'", "failureType": "UserError", "target": "Copy_ogj" }

Things I've found when testing:
If the first few records have 0000-00-00 then the 0000-00-00 values gets read in as NULL (Which is prefer by me) and the data loads in fine.
On tables where the records with 0000-00-00 start at (for example) line 8000, the above ODBC error gets thrown.
If I make the source ADF Dataset Data Type of string it looks like ODBC still reads the data as datetime and throws the same error.
I tried an ORDER BY in my SELECT statement but that only worked with tables with just a single Date column.

My current solution is modifying my SELECT statement to CAST the Date types as string and then the data reads in just fine.

My Questions:
Is there a way to make the integration runtime to either force the 0000-00-00 to become NULL or to have it read in the MySQL Data as the Data Types defined on the ADF DataSet (So I don't have to cast every Date column in my SELECT statement)?

解决方案

Hello.  Thank you for your patience, as this does seem like a difficult issue.  There are a number of potential solutions, and I would like your help to determine what is right for you.

First off, bringing in as a String is exactly the right thing to do, but if you need to enrich the data, there is typecasting in ADF.

One possibility is to push the data through a for-each activity, containing something to alter the 0000-00-00 to nulls.

Alternatively, you could pull the data into your destination, then afterwards, clean the data in the Azure Sql, and change the data type there.

If you just need to get data in now, you can set the fault tolerancing in the copy activity.

这篇关于MySQL日期转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！