欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MYSQLloaddatainfile的故障排除

程序员文章站 2022-04-23 16:21:19
一、案例 今天用户使用informatica从ORACLE抽数到MYSQL,结果报错,信息如下:‘incorrect datetime value ''...

一、案例

今天用户使用informatica从ORACLE抽数到MYSQL,结果报错,信息如下:‘incorrect datetime value '' for column 'CREATED_DATE'’

二、问题

为什么会产生错误‘incorrect datetime value '' for column 'CREATED_DATE'’?

三、数据收集及分析

3.1 收集错误信息的触发条件

经与用户沟通,该错误信息是informatica执行如下语句时产生的。

load data infile '/tmp/T1.out'
into table sdm.t1 character set UTF8
fields terminated by ','
endclosed by '"'
lines terminated by '/n';

分析:分析以上语法正常,打开T1.out文件发现在CREATED_DATE字段没有值,只有",,"这样的表示。初步怀疑是由于这个造成的。

3.2 网上收集案例

通过收集网上的案例发现,这是由于load data infile这个命令对数据库格式的要求问题,如果你的数据文件中的字段时是NULL的话,必须要在数据文件中写入NULL这个字符,不能是",,"这样的表示,具体如下:

错误的例子,不能正常导入:
001,,Mars

正确的例子,能正导入:
001,NULL,Mars

分析:根据正确的例子编辑数据文件,结果能重新导入

3.3 反馈用户

将正确的数据文件格式反馈给用户,但是用户说在informatica里没有参数直接控制转存的文件用NULL代替",," ,但是好幸运,刚好有一个同事处理过类似的问题,给出如下代码:

load data infile '/tmp/T1.out'
into table sdm.t1 character set UTF8
fields terminated by ','
endclosed by '"'
lines terminated by '/n'
(id,@created_date,created_by)
set created_date=if(@created_date='',NULL,@created_date)
;

分析:测试成功

3.4 另一种解决方案

在my.ini里找到 
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” 
把其中的STRICT_TRANS_TABLES,去掉, 
或者把sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
注释
最后,重启mysql即可

分析:去掉STRICT_TRANS_TABLES后,不能严格保证数据健康,对数据有强一致要求的不建议去掉。

四、结论

mysql在执行load data infile时不能处理隐式null即",,"所致,综上所述解决方案:

数据文件使用显式的NULL表达。
load data infile中使用set 语法对",,"进行处理。
修改sql_mode参数,去掉STRICT_TRANS_TABLES

五、题外话

当我将informatica的数据文件上传到mysql服务器上执行load data infile时又出现另一个错误 ”data too long for column.....“,经过收集案例,解决方案如下:

我的数据文件不是UTF8格式,需要转换。
在MYSQL命令行界面使用set names utf8;
同样去掉sql_mode中的strict_trans_tables;