Alibaba Canal 使用记录

项目中使用 canal 来同步数据到 Elasticsearch, 遇到很多问题，做一下记录：

最终建议，如果不是两个结构几乎完全相同的库间同步推送的话，不要使用canal_adapter了，很坑，使用Canal Server 和 client jar 自已写一下吧。

版本问题：

1. 解析binlog出错 ，表现为 limit excceed：xx

目前使用 mariadb 10.9.7/10.10.6 + canal 1.1.6 hotfix ，在这个版本组合中只要binlog中出现对表结构操作的SQL语句就会出错并卡住。

解决办法：

（1）每次在对表结构操作后重启数据库服务，这样会新建一个binlog 文件 。

（2）升级到1.1.7， release说明中说支持mariadb 10.10.x的binlog, 但目前1.1.7是alpha版，从isuse上看此版本的问题还是挺多的，我也简单的试了一下1.1.7-alpha-2版本，在配置没有任何更改的情况下无法同步，查看adapter日志能发现DML#4796，但就是不同步到es, 这种情况可能是配置问题，但由于不想使用alpha版本没有更详细的测试，等到正式版出了再说。

2.日志能发现DML，但不能同步到ES。

这种错误出现的最多，引起的原因也五花八门，目前发现以下原因：

（1）filter配置的问题，这种情况从日志能够看出来，首先deployer在启动时会在日志中打印出转意后的正则，可查看和自己设置的是否一致。另外如果filter通过了在DML日志后会根一条带有data 的日志。

（2）filter的正则问题，filter正则过滤真是让我挠头。

a.  canal会自动为正则加"^"和“$",所以配置时不能再加了，多个正则用逗号分隔。

b.  正则写的时候需要按Java字符串转义后再按正则转义，例如想配置"."那么要写成 "\\.",其中"\\"是java字符串转义，转成"\.", "\"是按正则转义，对"."转义

c.  官方说明过滤内容是  "库名.表名" ，但我设置 :

canal.instance.filter.regex=record_.+\\..*

是想仅同步数据库名以"record_"开头的所有表。但是设置后所有的同步都失效了，目前没有解决，使用了默认的 ".*\\..*"。

(3) sql错误

canal 对sql写法是有要求的，并不是能执行的就行，目前已知的要求有：

a.  只支持 left join

b. 第一个表必须是主表

c. 主表的主键字段名必须是_id

d. left join on 中使用的条件字段必须包含在select 选择列中

e. 不支持多级嵌套查询

对SQL的检查最好是先启动canal,然后再把写好yml文件拷到配置目录中，这时查看日志，会看到动态加载的日志，如果成功就说明sql没问题，否则会出现加载出错信息。

最后，官方文档没说的是：如果使用了 left join 那么 select 中的字段名最好都加上别名引用，如 t.create_at , 还有 字段中如果有计算出的字段，那么计算表达式中至少要引用到表中的一个字段，如 'my' as fixcolum 这个是不行的。 这两种都会抛出异常：

Caused by: java.lang.NullPointerException: null
base_canal.0.pwz9ju6gugkn@ubuntu1    |     at com.alibaba.otter.canal.client.adapter.es8x.support.ES8xTemplate.getESDataFromDmlData(ES8xTemplate.java:318)
base_canal.0.pwz9ju6gugkn@ubuntu1    |     at com.alibaba.otter.canal.client.adapter.es.core.service.ESSyncService.singleTableSimpleFiledUpdate(ESSyncService.java:814)
base_canal.0.pwz9ju6gugkn@ubuntu1    |     at com.alibaba.otter.canal.client.adapter.es.core.service.ESSyncService.update(ESSyncService.java:262)
base_canal.0.pwz9ju6gugkn@ubuntu1    |     at com.alibaba.otter.canal.client.adapter.es.core.service.ESSyncService.sync(ESSyncService.java:97)

3.关于时间日期类型：

参见 elasticsearch 时间日期格式

我需要的时间格式是 yyyy-MM-ddTHH:mm:ss.SSSZ 

sql 的写法： 

select DATE_FORMAT(CONVERT_TZ(created_at, @@session.time_zone, 'UTC'), '%Y-%m-%dT%T.000Z') as created_at form table

我遇到了个奇怪的问题，这个本来是好用的，可是后来不知什么原因，突然就不行了，所有的时间都成了空,(为空的原因找到了，数据库要安装时区库，

#安装时区信息库，不然 使用 CONVERT_TZ 返回空
mysql_tzinfo_to_sql /usr/share/zoneinfo/right |mysql -u root mysql -p

），而如果是使用 json_object方式的还是好用的。

left join (select wi.work_id, JSON_ARRAYAGG(JSON_OBJECT('type',type,'emp_id',emp_id,'emp_name',emp_name,'org_id',org_id,'org_name',org_name,'work_time',DATE_FORMAT(CONVERT_TZ(work_time, @@session.time_zone, 'GMT'), '%Y-%m-%dT%T.000Z'))) as items from work_item wi group by wi.work_id order by work_time desc ) it on it.work_id = w.work_id

找了半天也没找到原因，怀疑是引号的问题，看到elasticsearch 还支持 epoch_millis 格式，所以使用以下方式得到 epoch_millis ：

ROUND(unix_timestamp(created_at) * 1000) as created_at

mappings 定义

        "updated_at": {
          "type": "date",
          "format": "yyyy-MM-ddTHH:mm:ss.SSSZ||epoch_millis"
        },

3.关于 left join 多表字查询

由于 left join 使用字查询时不允许多表，所以想到使用视图，但是不可以直接left join XX_VIEW,这样在update 时会不更新， 使用 left join (select ...fields from XX_VIEW) on .. 这样就没问题