表字段位置改变，数据同步错乱

[alynnchen]

environment

• canal version v1.1.3
• mysql version v5.7.20
• kafka version v1.0

Issue Description

在同步mysql数据时，如果移动表字段位置，canal只捕获alter操作但没有处理，导致数据同步错乱。在canal --> kafka、adapter的hbase 均重现该问题

Steps to reproduce

比如重现步骤：

1. 创建了一个hello库quniya4表
2. 表里有三个字段（name,value,id）
3. 我先建的name，value，最后建的id主键自增。现在我把id的位置移动到name、value的上面，
   也就是说id的字段位置变成了 frist，现在的表字段位置为（id,name,value）
4. 我插入数据（1,a,aaa）=> (aaa,1,a) ; name变成了1，value变成了a，
   而id还是没有移动位置前的第三位，所以kafka消费这条数据时id=aaa，hbase亦是如此

Expected behaviour

kafka预期显示：

{"data":[{"id":"1","name":"a","value":"aaa"}]      ...}

Actual behaviour

kafka结果显示：

{"data":[{"name":"1","value":"a","id":"aaa"}],"database":"hello","es":1553330817000,"id":20,"isDdl":false,"mysqlType":{"name":"varchar(255)","value":"varchar(255)","id":"int"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"name":4,"value":12,"id":12},"table":"quniya4","ts":1553330817903,"type":"INSERT"}

hbase结果显示：

hbase(main):009:0> scan 'hello.quniya4'
ROW                 COLUMN+CELL                                          
 ccc|3              column=cf:id, timestamp=1553331439400, value=ccc     
 ccc|3              column=cf:name, timestamp=1553331439400, value=3     
 ccc|3              column=cf:value, timestamp=1553331439400, value=c

[agapple]

canal是否有开启tablemeta tsdb ? 可以提供一下对应的DDL语句

[alynnchen]

{"data":null,"database":"hello","es":1553330627000,"id":17,"isDdl":true,"mysqlType":null,"old":null,"pkNames":null,"sql":"/* ApplicationName=DataGrip 2018.3.1 */ create table quniya4\n(\n\tname varchar(255) null,\n\tvalue varchar(255) null,\n\tid int not null,\n\tconstraint quniya4_pk\n\t\tprimary key (id)\n)","sqlType":null,"table":"quniya4","ts":1553330627130,"type":"CREATE"}

{"data":null,"database":"hello","es":1553330711000,"id":18,"isDdl":true,"mysqlType":null,"old":null,"pkNames":null,"sql":"/* ApplicationName=DataGrip 2018.3.1 */ alter table quniya4 modify id int not null first","sqlType":null,"table":"quniya4","ts":1553330711411,"type":"ALTER"}

{"data":null,"database":"hello","es":1553330789000,"id":19,"isDdl":true,"mysqlType":null,"old":null,"pkNames":null,"sql":"/* ApplicationName=DataGrip 2018.3.1 */ alter table quniya4 modify id int auto_increment","sqlType":null,"table":"quniya4","ts":1553330789377,"type":"ALTER"}

配置为true了啊

# table meta tsdb info
canal.instance.tsdb.enable = true
canal.instance.tsdb.dir = ${canal.file.data.dir:../conf}/${canal.instance.destination:}
canal.instance.tsdb.url = jdbc:h2:${canal.instance.tsdb.dir}/h2;CACHE_SIZE=1000;MODE=MYSQL;
canal.instance.tsdb.dbUsername = canal
canal.instance.tsdb.dbPassword = canal
# dump snapshot interval, default 24 hour
canal.instance.tsdb.snapshot.interval = 24
# purge snapshot expire , default 360 hour(15 days)
canal.instance.tsdb.snapshot.expire = 360

[rewerma]

adapter 的es、hbase和rdb(mirrorDB配置除外)，不会对ddl语句做处理，需要手动修改配置映射

[alynnchen]

@rewerma 请问 在哪里配置，在canal server的canal.properties or instance.properties 还是adapter的application.yml 又怎么写呢 ?

hbase是用的adapter，kafka没有用adapter

[rewerma]

确认tsdb是否开启

[agapple]

确认是tablemeta tsdb针对DDL支持问题，针对alter table quniya4 modify id int not null first

[alynnchen]

@agapple 正式1.1.3会更新这个问题吗？

[agapple]

1.1.3最新版会安排修复

[hqch0708]

你好，我目前使用的是canal v1.1.3。但是这个问题还是没有修复。我这边不是主键顺序，是其他的同步字段顺序有变动，值是错乱的。开启了tablemeta tsdb，是直接通过deployer发送kafka

[alynnchen]

建议v1.14，已验证无该问题