SOLVED 从mysql同步数据到clickhouse 表结构 comment 报错

[duzhixin]

Console task execute with error. Context is {“resources”:[],“tryTimes”:null,“paramType”:null,“consoleJobId”:0,“dataJobId”:9}.
Exception stack is “远程执行SQL失败，请联系管理员。错误信息为：invoke rcontroller method failed.Request id:13ed91c0-9ead-11ec-9ac0-8b8099c6ce86msg:Throwable: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 2035 (‘COMMENT’) (line 34, col 121): COMMENT ‘订单票单明细’. Expected one of: AND, OR, NOT, TTL, AS, IN, IS, token, BETWEEN, FORMAT, ILIKE, LIKE, SETTINGS, PARTITION BY, NOT LIKE, NOT ILIKE, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV, Arrow, QuestionMark, SAMPLE BY, INTO OUTFILE, end of query (version 21.3.13.1)
. Target sidecar is 172.31.238.4, route name is ck_rdbApplyScript”
at com.clougence.cloudcanal.console.component.rsocket.service.base.AbstractRdbMetaRService.applyScript(AbstractRdbMetaRService.java:443)
at com.clougence.cloudcanal.console.component.consoletask.handler.createdatajob.AbstractStructMigrationHandlerForNew.applyScript(AbstractStructMigrationHandlerForNew.java:225)
at com.clougence.cloudcanal.console.component.consoletask.handler.createdatajob.AbstractStructMigrationHandlerForNew.tableMigration(AbstractStructMigrationHandlerForNew.java:213)
at com.clougence.cloudcanal.console.component.consoletask.handler.createdatajob.mysql.MySQLToCkStructHandler.schemaMigration(MySQLToCkStructHandler.java:143)
at com.clougence.cloudcanal.console.component.consoletask.handler.createdatajob.mysql.MySQLToCkStructHandler.migration(MySQLToCkStructHandler.java:96)
at com.clougence.cloudcanal.console.component.consoletask.handler.createdatajob.AbstractStructMigrationHandler.execute(AbstractStructMigrationHandler.java:148)
at com.clougence.cloudcanal.console.component.consoletask.handler.createdatajob.AbstractStructMigrationHandler.execute(AbstractStructMigrationHandler.java:61)
at com.clougence.cloudcanal.console.component.consoletask.service.ConsoleJobManagerImpl.doExecuteJob(ConsoleJobManagerImpl.java:220)
at com.clougence.cloudcanal.console.component.consoletask.service.ConsoleJobManagerImpl.lambda$doExecuteJobAsync$1(ConsoleJobManagerImpl.java:174)
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)

[zycgit]

clickhouse 表结构 comment 只有最新版本 clickhouse 才支持，2022年3月时期 CloudCanal 版本并未对 clickhouse 做版本鉴别。

目前最新版 CloudCanal 已解决 该问题，老版的 clickhouse 不会同步 comment。

[junyu-cloudcanal]

@duzhixin clickhouse版本是多少？我们重现下。

[junyu-cloudcanal]

This post is deleted!

[duzhixin]

@junyu-cloudcanal
是不是不能加表注释

[duzhixin]

@junyu-cloudcanal
CREATE TABLE order_ticket_item (
id char(24) COLLATE utf8mb4_bin NOT NULL COMMENT ‘主键’,
order_id char(24) COLLATE utf8mb4_bin NOT NULL COMMENT ‘订单id’,
order_item_id char(24) COLLATE utf8mb4_bin NOT NULL COMMENT ‘子订单id’,
parent_order_item_id char(24) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘父子订单id’,
show_id char(24) COLLATE utf8mb4_bin NOT NULL COMMENT ‘项目id’,
show_name varchar(128) COLLATE utf8mb4_bin NOT NULL COMMENT ‘项目名称’,
show_session_id char(24) COLLATE utf8mb4_bin NOT NULL COMMENT ‘场次id’,
show_session_name varchar(128) COLLATE utf8mb4_bin NOT NULL COMMENT ‘场次名称’,
ticket_id char(24) COLLATE utf8mb4_bin NOT NULL COMMENT ‘票id’,
stock_code_id char(24) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘票码id’,
ticket_no varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘票码’,
original_price decimal(10,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘票价’,
price decimal(10,4) NOT NULL DEFAULT ‘0.0000’ COMMENT ‘售价’,
state varchar(30) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘状态’,
item_type varchar(30) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘单一:SINGLE、套票:COMBO’,
deliver_method varchar(30) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘配送方式’,
seat_plan_id char(24) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘票面id’,
seat_plan_name varchar(128) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘票面名称’,
audience_name varchar(100) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘观演人名称’,
identity_type varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘证件类型’,
identity_number varchar(32) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘证件号码’,
sector_concrete_id char(24) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘扇区id’,
sector_name varchar(128) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘扇区名称’,
zone_id char(24) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘区域id’,
zone_name varchar(128) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘区域名称’,
ticket_seat_id char(24) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘票座位id’,
ticket_seat_code varchar(24) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘库存座位code’,
seat_row varchar(30) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘座位行’,
seat_column varchar(30) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘座位列’,
combo_instance_id char(32) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘套票实例ID’,
is_deleted tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT ‘是否逻辑删除’,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id) USING BTREE,
KEY idx_order_ticket_item_order_id (order_id),
KEY idx_order_ticket_item_order_item_id (order_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=‘订单票单明细’

[duzhixin]

This post is deleted!

[junyu-cloudcanal]

@duzhixin 在 从mysql同步数据到clickhouse 表结构 comment 报错 中说：

AbstractStructMigrationHandlerForNew

发下源端表结构？我们尝试重现并修复下