mysql5.6日期字段无法同步
版本：otter-4.2.3 os：CentOS release 6.3 mysql：5.6.10-log MySQL Community Server
字段类型： create_time | datetime | YES | | NULL | | update_time | datetime | YES | | NULL | | mysql配置：开启binlog，其他的采用默认配置
配置了一个单向同步channel，源表里有create_time、update_time字段，启动channel同步就会出现挂起，查看日志发现报错，提示时间格式不对，相应的错误日志如下：
pid:3 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 [update tb set create_time = ? , update_time = ? where ]; Data truncation: Incorrect datetime value: '1383277380000' for column 'create_time' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1383277380000' for column 'create_time' 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.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:607) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:599) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:527) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:449) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:274) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:160) 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:198) at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:189) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) at java.util.concurrent.FutureTask.run(FutureTask.java:166) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) at java.util.concurrent.FutureTask.run(FutureTask.java:166) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:724) Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1383277380000' for column 'create_time' at row 1 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3560) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2398) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2316) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2301) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
... 23 more PairId: 5 , TableId: 1 , EventType : U , Time : 1383277861000 - Consistency : M , Mode : ---Pks EventColumn[index=0,columnType=12,columnName=sub_serial,columnValue=426931043,isNull=false,isKey=true,isUpdate=true] ---oldPks
---Columns EventColumn[index=7,columnType=93,columnName=create_time,columnValue=1383277380000,isNull=false,isKey=false,isUpdate=true] 中间省略 EventColumn[index=8,columnType=93,columnName=update_time,columnValue=1383277864000,isNull=false,isKey=false,isUpdate=true] EventColumn[index=9,columnType=4,columnName=status,columnValue=1,isNull=false,isKey=false,isUpdate=true]
我把同步表中的时间相关的字段去掉再开启同步，该问题不会出现。
原提问者GitHub用户 scriptguy
写回答
-
根据错误日志中的提示，问题出在时间格式不正确。你可以检查一下源表中的日期字段是否是以毫秒为单位的时间戳格式，比如像
1383277380000这样的值。如果是的话，需要对这些日期字段进行转换，将它们转换成标准的日期格式，比如yyyy-MM-dd HH:mm:ss。在otter中，可以使用
otter.script.sql.DatetimeSqlLoadAction来进行日期字段的转换。具体的操作步骤如下：- 在同步任务的json配置文件中，将
loadDataSql字段的值设置为otter.script.sql.DatetimeSqlLoadAction。例如：
{ "id": 1, "name": "sync_task", "source": { ... }, "target": { ... }, "transformer": { ... }, "loader": { "type": "sql", "loadDataSql": "otter.script.sql.DatetimeSqlLoadAction" } }- 在
otter.script.sql目录下创建一个DatetimeSqlLoadAction.groovy文件，文件内容如下：
import com.alibaba.otter.node.etl.load.exception.LoadException import com.alibaba.otter.node.etl.load.loader.AbstractLoadAction import com.alibaba.otter.shared.common.model.config.data.db.DbMediaSource import com.alibaba.otter.shared.common.utils.date.DateUtils import com.alibaba.otter.shared.common.utils.reflect.ClassUtils import org.slf4j.Logger import org.slf4j.LoggerFactory import java.sql.PreparedStatement import java.sql.SQLException import java.util.* import java.util.regex.Pattern class DatetimeSqlLoadAction extends AbstractLoadAction { private static final Logger logger = LoggerFactory.getLogger(DatetimeSqlLoadAction.class) private static final String DATE_REGEX = "^\\d{13}$" private static final Pattern DATE_PATTERN = Pattern.compile(DATE_REGEX) private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss" public DatetimeSqlLoadAction(DbMediaSource dbMediaSource) { super(dbMediaSource) } @Override protected void doLoad(PreparedStatement ps, List<List<Object>> rows) throws SQLException { for (List<Object> row : rows) { for (int i = 0; i < row.size(); i++) { Object value = row.get(i); if (value != null && DATE_PATTERN.matcher(value.toString()).matches()) { row.set(i, DateUtils.format(new Date(Long.valueOf(value.toString())), DATE_FORMAT)); } } ps.clearParameters(); int index = 1; for (Object value : row) { if (value == null) { ps.setNull(index, java.sql.Types.NULL); } else { ps.setObject(index, value); } index++; } ps.addBatch(); } ps.executeBatch(); } }这个脚本会将时间戳转换成标准的日期格式，并将转换后的日期写入到数据库中。
- 重启同步任务，然后再次进行同步，看看是否可以正常同步了。
2023-06-23 14:31:57赞同 展开评论 打赏 - 在同步任务的json配置文件中，将
-
你这数据应该是开启了反查数据库模式，反查出来的结果使用了Long的时间戳，在目标端没有转化为对应的时间类型，已修复
原回答者GitHub用户agapple
2023-06-21 18:28:05赞同 展开评论 打赏 -
北京阿里云ACE会长Otter在同步日期字段时，报错提示时间格式不对，导致同步挂起。这可能是由于MySQL 5.6在处理日期时间类型时，存在一些兼容性问题，导致Otter无法正确解析日期时间类型。
为了解决这个问题，您可以尝试以下解决方案：
尝试在Otter的配置文件中添加timezone参数，以指定时区。例如，您可以将timezone参数设置为“Asia/Shanghai”，以指定中国标准时间。
尝试在MySQL 5.6中设置时间格式。例如，您可以将时间格式设置为“YYYY-MM-DD HH:MI:SS”，以确保Otter能够正确解析日期时间类型。
尝试升级Otter版本。Otter在最新版本中可能已经修复了这个问题，因此升级到最新版本可能会解决这个问题。
尝试使用其他同步工具。如果以上解决方案都无效，您可以尝试使用其他同步工具，例如DataX、Canal等工具，以确保数据能够正确同步。
需要注意的是，这个问题可能不仅限于日期时间类型，其他类型字段也可能会出现这个问题。因此，建议您在同步数据时使用以上解决方案之一，以确保同步数据的准确性。
2023-06-21 17:52:36赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
