mysql的datetime类型设置'0000-00-00' 导致的otter同步失败

Posted on Edited on In

背景

在线下环境给业务方同步全量同步数据中，写目标库的时候发生了写入失败，报错信息如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
pid:1 nid:1 exception:setl:com.alibaba.otter.node.etl.load.exception.LoadException: java.util.concurrent.ExecutionException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into `drugs`.`user_pro_device_bind_log`(`userProDeviceId` , `userProDeviceBindLogType` , `processDate` , `uuid` , `appDeviceType` , `createDate` , `modifyDate` , `id`) values (? , ? , ? , ? , ? , ? , ? , ?) on duplicate key update `userProDeviceId`=values(`userProDeviceId`) , `userProDeviceBindLogType`=values(`userProDeviceBindLogType`) , `processDate`=values(`processDate`) , `uuid`=values(`uuid`) , `appDeviceType`=values(`appDeviceType`) , `createDate`=values(`createDate`) , `modifyDate`=values(`modifyDate`) , `id`=values(`id`)]; Data truncation: Incorrect datetime value: '0000-00-00 00:00:00' for column 'createDate' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '0000-00-00 00:00:00' for column 'createDate' at row 1
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2.doInTransaction(DbLoadAction.java:655)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:647)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:574)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:485)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:279)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:165)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$FastClassByCGLIB$$d932a4cb.invoke()
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$EnhancerByCGLIB$$80fd23c2.load()
at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:192)
at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:183)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

从业务方了解到，源库是mysql5.6版本，字段类型默认DEFAULT ‘0000-00-00 00:00:00’ 处理，目标库是mysql5.7，表结构如下：

1
2
3
4
5
6
7
8
CREATE TABLE `tanlb...` (
  // .......
  `fromDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '起始的过期时间',
  `toDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '在经过相关业务以后过期时间，业务类型取决于@userProStatLogType',
  `createDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '激活时间',
  `modifyDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '过期时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=153233 DEFAULT CHARSET=utf8mb4;

解决方案

连接数据库转化为对象出错的解决办法为在数据库连接后面加上参数zeroDateTimeBehavior=convertToNull 这样如果碰到 ‘0000-00-00：00:00:00’的日期类型时，将会转化为null值

1
jdbcurl=jdbc:mysql://192.168.1.52:3306/db?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull

针对数据插入数据‘0000-00-00：00:00:00’ 数据本身不接受的解决办法为，用root用户登录，重新设置数据库的模式（尽量使用root用户 要不然 GLOBAL设置不成功，但是可以设置SESSION的）

  1. 查询数据库现有的模式

    1
    select @@sql_mode;

  2. 把NO_ZERO_IN_DATE,NO_ZERO_DATE去掉，然后重新设置

    1
    SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

    这样关闭数据库客户端的连接，重新登录，然后再执行那种比较操蛋的插入语句即可正确的插入。