Canal1.1.4同步数据至Es时间格式问题

环境描述:

　　centos7、mysql5.6、canal1.1.4、es6.6

参考博客:https://blog.csdn.net/weixin_41546244/article/details/108381219

1、下载canal源码

2、打开idea修改源码（注意修改：ESSyncUtil.class 中的带有Util.timeZone的时间格式），如下图，共有8处，都需修改。

3、找到编译后的包，将其传入/data/soft/canal1.4/canal/adapter/plugin下面

client-adapter.elasticsearch-1.1.4-jar-with-dependencies.jar

4、重启adapter,就可以了....

-------------------心累，整这个整了好久--------------

FAQ:

错误一：

2020-11-07 22:57:18.662 [pool-2-thread-1] ERROR c.a.otter.canal.client.adapter.es.service.ESSyncService - sync error, es index: t_ad_account_rebate, DML : Dml{destination='instance_t_ad_account_rebate', database='skynetods', table='t_ad_account_rebate', type='INSERT', es=1604761038000, ts=1604761038584, sql='', data=[{id=409, exten_interface=微信朋友圈2, exten_account="", third_company=null, open_account_company=null, rebate=1.13, is_delete=0, create_time=2020-01-01 00:00:00.0, update_time=null, device=2}], old=null}
2020-11-07 22:57:18.664 [pool-2-thread-1] ERROR c.a.o.canal.adapter.launcher.loader.CanalAdapterWorker - java.lang.NullPointerException
java.lang.RuntimeException: java.lang.NullPointerException
        at com.alibaba.otter.canal.client.adapter.es.service.ESSyncService.sync(ESSyncService.java:110)
        at com.alibaba.otter.canal.client.adapter.es.service.ESSyncService.sync(ESSyncService.java:58)
        at com.alibaba.otter.canal.client.adapter.es.ESAdapter.sync(ESAdapter.java:169)
        at com.alibaba.otter.canal.client.adapter.es.ESAdapter.sync(ESAdapter.java:148)
        at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.batchSync(AbstractCanalAdapterWorker.java:201)
        at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.lambda$null$1(AbstractCanalAdapterWorker.java:62)
        at java.util.ArrayList.forEach(ArrayList.java:1259)
        at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.lambda$null$2(AbstractCanalAdapterWorker.java:58)
        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)
Caused by: java.lang.NullPointerException: null
        at com.alibaba.otter.canal.client.adapter.es.support.ESTemplate.insert(ESTemplate.java:74)
        at com.alibaba.otter.canal.client.adapter.es.service.ESSyncService.singleTableSimpleFiledInsert(ESSyncService.java:442)
        at com.alibaba.otter.canal.client.adapter.es.service.ESSyncService.insert(ESSyncService.java:133)
        at com.alibaba.otter.canal.client.adapter.es.service.ESSyncService.sync(ESSyncService.java:93)
        ... 11 common frames omitted
2020-11-07 22:57:18.665 [Thread-4] ERROR c.a.o.canal.adapter.launcher.loader.CanalAdapterWorker - Outer adapter sync failed!  Error sync but ACK!

解决方案：这种就是你对应的canal目录下es的配置文件有问题。我监察很久才发现

错误二：

java.lang.RuntimeException: java.lang.RuntimeException: java.lang.RuntimeException: ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse [cardtime_date]]]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=Invalid format: "2019-10-21T08:44:31-05:00" is malformed at "T08:44:31-05:00"]];
        at com.alibaba.otter.canal.client.adapter.support.Util.sqlRS(Util.java:65)
        at com.alibaba.otter.canal.client.adapter.es.service.ESEtlService.executeSqlImport(ESEtlService.java:80)
        at com.alibaba.otter.canal.client.adapter.support.AbstractEtlService.lambda$importData$1(AbstractEtlService.java:91)
        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)

解决方案：这个就是时间格式问题了。。。可以按照上面的方法修改

错误三："data":null

{"data":null,"database":"","es":1606317332000,"id":1,"isDdl":false,"mysqlType":null,"old":null,"pkNames":null,"sql":"update XXXXXXX","sqlType":null,"table":"TABLE","ts":1606318913663,"type":"QUERY"}

解决方案: binlog_format 没有指定为ROW

错误四：

错误描述：mysql到es的时间格式多14小时

解决方案：设置mysql的时区为东八区　　

set global time_zone = '+8:00' ;
set time_zone = '+8:00' ;
flush privileges ;

错误五：

错误描述: 使用canal同步mysql数据至es，left join的sql语句不能包含子查询语句，否则启动adapter失败....

错误六：

错误描述：
    canal同步mysql数据至es规则：
    1、单表同步可以
    2、同库/异库两张表可以
    3、同库三张表可以(但是不能包含子查询)