canal-1.6.0 mysql关键字报错问题 #4241
Description
各位大佬好：
我根据以往的issue，修改了
dbMapping:
targetKeywordsIdentifier:
prefix: '`'
suffix: '`'
也尝试了：
mapAll: false
targetColumns:
"\`from\`": from
这种写法
我这张表里面有1关键字from，一直报from没转义成功
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from,insurance,third_order_sn
建表sql：
CREATE TABLE `jdz_order` (
`order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_sn` varchar(64) NOT NULL DEFAULT '',
`pay_type` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '',
`admin_id` int(10) unsigned NOT NULL DEFAULT '0',
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
`battery_id` int(10) unsigned NOT NULL DEFAULT '0',
`battery_sn` varchar(64) NOT NULL DEFAULT '',
`hire_price` int(11) NOT NULL DEFAULT '0',
`hire_duration` int(11) NOT NULL DEFAULT '0',
`hire_duration_unit` tinyint(4) NOT NULL DEFAULT '1',
`hire_money` int(11) NOT NULL DEFAULT '0' COMMENT '租金',
`pledge_money` int(11) NOT NULL DEFAULT '0' COMMENT '押金',
`insurance_money` int(11) NOT NULL DEFAULT '0' COMMENT '',
`insurance_limit` int(11) NOT NULL DEFAULT '12' COMMENT '',
`pay_with_pledge` tinyint(4) NOT NULL DEFAULT '1' COMMENT '',
`money` int(10) unsigned NOT NULL DEFAULT '0',
`status` tinyint(4) NOT NULL DEFAULT '0',
`ctime` int(10) unsigned NOT NULL DEFAULT '0',
`pay_time` int(10) unsigned NOT NULL DEFAULT '0',
`contract_id` int(10) unsigned NOT NULL DEFAULT '0',
`update_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '操作更新时间',
`release_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '设备解绑时间',
`order_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '订单类型 0:租电 1:租车',
`hire_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '租赁类型 0:新租 1:续租',
`car_images` varchar(1024) NOT NULL DEFAULT '' COMMENT '车辆照片',
`car_images2` varchar(1024) NOT NULL DEFAULT '' COMMENT '归还时车辆照片',
`car_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '车状态: 1:租赁中 2:申请还车 3:还车成功 4: 还车失败',
`return_reason` varchar(256) NOT NULL DEFAULT '' COMMENT '还车失败原因',
`return_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '申请归还时间',
`op_return_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '管理员操作归还时间',
`from` varchar(32) NOT NULL DEFAULT 'wx' COMMENT '下单终端: wx/ali/...',
`insurance` text COMMENT '',
`third_order_sn` varchar(64) NOT NULL DEFAULT '' COMMENT '',
`profitsharing_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '分账/分佣状态 0: 1:需要分账 2:分账成功 3:分账失败',
`profitsharing_fail_reason` varchar(256) NOT NULL DEFAULT '' COMMENT '分账失败原因,格式:{err_code:xx, err_code_des:xxx}',
`refund_hire_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT ' 0:未退款 1:申请退款 2:审核失败 3:退款失败 3:退款成功',
`refund_insurance_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0:未退款 1:申请退款 2:审核失败 3:退款失败 4:退款成功',
`old_battery_sn` varchar(64) NOT NULL DEFAULT '' COMMENT '',
`exchange_battery_sn` varchar(64) NOT NULL DEFAULT '' COMMENT '',
`hire_origin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
`dev_id` varchar(64) NOT NULL DEFAULT '' COMMENT '',
`box_sn` varchar(16) NOT NULL DEFAULT '' COMMENT '',
`cab_hire_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
`cab_hire_fail_reason` varchar(256) NOT NULL DEFAULT '' COMMENT '',
`cab_hire_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '',
`cab_rt_dev_id` varchar(64) NOT NULL DEFAULT '' COMMENT '',
`cab_rt_box_sn` varchar(16) NOT NULL DEFAULT '' COMMENT '',
`cab_rt_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
`cab_rt_fail_reason` varchar(256) NOT NULL DEFAULT '' COMMENT '',
`cab_rt_ctime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '',
`cab_rt_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '',
`fee` int(11) NOT NULL DEFAULT '0' COMMENT '',
`memo` varchar(300) NOT NULL DEFAULT '' COMMENT '',
`refund_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '',
`shop_name` varchar(256) NOT NULL DEFAULT '',
`shop_phone` varchar(256) NOT NULL DEFAULT '' COMMENT '门店手机号',
`biz_user_id` varchar(32) NOT NULL DEFAULT '' COMMENT '支付渠道用户id',
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=357830 DEFAULT CHARSET=utf8;
请问各位大佬 还有别的解决办法吗？
源数据库版本：5.5.68-MariaDB
目标数据库版本：5.7.37-log
jdz_order rdb配置文件(屏蔽from字段能全量同步)：
dataSourceKey: defaultDS
destination: example
groupId: g1
outerAdapterKey: mysql1
concurrent: true
dbMapping:
#mirrorDb: true
database: new_energy
table: jdz_order
targetTable: jdz_order
targetPk:
order_id: order_id
#targetKeywordsIdentifier:
#prefix: '`'
#suffix: '`'
mapAll: false
targetColumns:
order_id:
order_sn:
pay_type:
admin_id:
user_id:
battery_id:
battery_sn:
hire_price:
hire_duration:
hire_duration_unit:
hire_money:
pledge_money:
insurance_money:
insurance_limit:
pay_with_pledge:
money:
status:
ctime:
pay_time:
contract_id:
update_time:
release_time:
order_type:
hire_type:
car_images:
car_images2:
car_status:
return_reason:
return_time:
op_return_time:
# "`from`": from
insurance:
third_order_sn:
profitsharing_status:
profitsharing_fail_reason:
refund_hire_status:
refund_insurance_status:
old_battery_sn:
exchange_battery_sn:
hire_origin:
dev_id:
box_sn:
cab_hire_status:
cab_hire_fail_reason:
cab_hire_at:
cab_rt_dev_id:
cab_rt_box_sn:
cab_rt_status:
cab_rt_fail_reason:
cab_rt_ctime:
cab_rt_at:
fee:
memo:
refund_status:
shop_name:
shop_phone:
biz_user_id:
etlCondition: "where c_time>={}"
commitBatch: 3000 # 批量提交的大小
## Mirror schema synchronize config
#dataSourceKey: defaultDS
#destination: example
#groupId: g1
#outerAdapterKey: mysql1
#concurrent: true
#dbMapping:
# mirrorDb: true
# database: mytest