mysql同步tidb显示 字段重复/字符集不一致 的问题
-
版本:社区版 v2.2.0.8
数据库源端:mysql 5.6
目标端类型: 5.7.25-TiDB-v5.2.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
任务在什么阶段异常:增量
提供异常信息:报错显示有字段重复,源端和目标端字符集排序规则不一致。检查后的实际情况为部分字段存在字符集排序规则不一致。
说明如何复现问题:源端表结构:
CREATE TABLE `d_inspect` ( `inspect_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `company_id` int(11) DEFAULT NULL COMMENT '企业ID', `department_id` int(11) DEFAULT NULL COMMENT '部门ID', `project_id` int(11) DEFAULT NULL COMMENT '项目ID', `type` tinyint(4) DEFAULT NULL COMMENT '检查类别', `items` int(11) DEFAULT NULL COMMENT '分部分项', `position` varchar(128) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '位置', `member_id` char(32) DEFAULT NULL COMMENT '整改人', `day` int(11) DEFAULT NULL COMMENT '整改期限', `is_read` tinyint(4) DEFAULT NULL COMMENT '是否已读', `read_time` datetime DEFAULT NULL COMMENT '阅读时间', `status` tinyint(4) DEFAULT NULL COMMENT '状态', `is_visible` tinyint(4) DEFAULT NULL COMMENT '是否晒一晒', `comment` varchar(1024) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '问题描述', `modify_id` char(32) DEFAULT NULL COMMENT '修改人', `create_id` char(32) DEFAULT NULL COMMENT '创建人', `gmt_create` datetime DEFAULT NULL COMMENT '创建时间', `gmt_modify` datetime DEFAULT NULL COMMENT '修改时间', `second_items` tinyint(4) DEFAULT NULL COMMENT '二级分项', `finish_time` datetime DEFAULT NULL COMMENT '完成时间', `source_type` tinyint(4) DEFAULT '1' COMMENT '来源类型', `inspect_type` tinyint(4) DEFAULT '1' COMMENT '检查类型 ', `danger_classes` tinyint(4) DEFAULT NULL COMMENT '危险等级', `can_see` tinyint(4) DEFAULT NULL COMMENT '可见范围 ', `item_id` int(11) DEFAULT NULL COMMENT '项id', `first_item_code` tinyint(4) DEFAULT NULL COMMENT 'ss', `unit_source` tinyint(4) DEFAULT '0' COMMENT '所属单位来源', `unit_id` varchar(64) DEFAULT NULL COMMENT '所属单位ID', `task_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '任务', `area_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '测量区', `rectification_requirement` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '整改要求', `emergency_degree` tinyint(4) DEFAULT NULL COMMENT '紧急程度', `sub_status` tinyint(4) DEFAULT NULL COMMENT '子状态', `plan_finish_time` datetime DEFAULT NULL COMMENT '计划完成时间', `item_type` tinyint(4) DEFAULT '1' COMMENT '分项类型', `rectified_time` datetime DEFAULT NULL COMMENT '整改时间', `lead_checker_id` char(32) DEFAULT NULL COMMENT '带班检查人', `check_unit_id` int(11) DEFAULT NULL COMMENT '检查单位', `patrol_record_id` bigint(20) DEFAULT NULL COMMENT '关联Id', PRIMARY KEY (`inspect_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='检查表';
目标端结构
CREATE TABLE `d_inspect` ( `inspect_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `company_id` int(11) DEFAULT NULL COMMENT '企业ID', `department_id` int(11) DEFAULT NULL COMMENT '部门ID', `project_id` int(11) DEFAULT NULL COMMENT '项目ID', `type` tinyint(4) DEFAULT NULL COMMENT '检查类别', `items` int(11) DEFAULT NULL COMMENT '分部分项', `position` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '位置', `member_id` char(32) DEFAULT NULL COMMENT '整改人', `day` int(11) DEFAULT NULL COMMENT '整改期限', `is_read` tinyint(4) DEFAULT NULL COMMENT '是读', `read_time` datetime DEFAULT NULL COMMENT '阅间', `status` tinyint(4) DEFAULT NULL COMMENT '状态', `is_visible` tinyint(4) DEFAULT NULL COMMENT '是否晒一晒', `comment` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '问题描述', `modify_id` char(32) DEFAULT NULL COMMENT '修改人', `create_id` char(32) DEFAULT NULL COMMENT '创建人', `gmt_create` datetime DEFAULT NULL COMMENT '时间', `gmt_modify` datetime DEFAULT NULL COMMENT '修改时间', `second_items` tinyint(4) DEFAULT NULL COMMENT '分项', `finish_time` datetime DEFAULT NULL COMMENT '完成时间', `source_type` tinyint(4) DEFAULT '1' COMMENT '来型', `inspect_type` tinyint(4) DEFAULT '1' COMMENT '检型', `danger_classes` tinyint(4) DEFAULT NULL COMMENT '危险等级', `can_see` tinyint(4) DEFAULT NULL COMMENT '可见范围', `item_id` int(11) DEFAULT NULL COMMENT '(新)分部分项id', `first_item_code` tinyint(4) DEFAULT NULL COMMENT '所属', `unit_source` tinyint(4) DEFAULT '0' COMMENT '所属单', `unit_id` varchar(64) DEFAULT NULL COMMENT '所属单位', `task_id` varchar(64) DEFAULT NULL COMMENT '实务id', `area_id` varchar(64) DEFAULT NULL COMMENT '区id', `rectification_requirement` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '整求', `emergency_degree` tinyint(4) DEFAULT NULL COMMENT '紧急程', `sub_status` tinyint(4) DEFAULT NULL COMMENT '子状态', `plan_finish_time` datetime DEFAULT NULL COMMENT '计划完成时间', `item_type` tinyint(4) DEFAULT '1' COMMENT '分项类型', `rectified_time` datetime DEFAULT NULL COMMENT '整改时间', `lead_checker_id` char(32) DEFAULT NULL COMMENT '带', `check_unit_id` int(11) DEFAULT NULL COMMENT '检查单位', `patrol_record_id` bigint(20) DEFAULT NULL COMMENT '关联', PRIMARY KEY (`inspect_id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=431944 COMMENT='检查表'
报错信息
2022-08-22 10:00:23.314 [incre-apply-disruptor-4-thd-11] ERROR c.c.c.mysql.worker.writer.incre.MySqlIncreDdlExecutor - DDL error.sql:/*ccw*/ALTER TABLE `dpkb`.`yw_xj_d_inspect` ADD COLUMN `unit_source` TINYINT DEFAULT 0 COMMENT '所属单位来源:0.自定义单位 1.劳务参建单位' AFTER `first_item_code`,msg:SQLSyntaxErrorException: Duplicate column name 'unit_source' java.sql.SQLSyntaxErrorException: Duplicate column name 'unit_source' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:762) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:646) at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:632) at com.clougence.cloudcanal.mysql.worker.writer.incre.MySqlIncreDdlExecutor.executeSqls(MySqlIncreDdlExecutor.java:40) at com.clougence.cloudcanal.base.service.task.applier.AbstractRdbIncreDDLExecutor.doDDL(AbstractRdbIncreDDLExecutor.java:63) at com.clougence.cloudcanal.mysql.worker.writer.incre.AbstractMySQLIncreApplyHandler.processDdl(AbstractMySQLIncreApplyHandler.java:107) at com.clougence.cloudcanal.mysql.worker.writer.incre.MyIncreBatchApplyNoTxHandler.handleWithDdl(MyIncreBatchApplyNoTxHandler.java:172) at com.clougence.cloudcanal.mysql.worker.writer.incre.MyIncreBatchApplyNoTxHandler.handle(MyIncreBatchApplyNoTxHandler.java:124) at com.clougence.cloudcanal.mysql.worker.writer.incre.MyIncreBatchApplyNoTxHandler.handle(MyIncreBatchApplyNoTxHandler.java:39) at com.clougence.cloudcanal.task.applier.incre.IncrementDisruptorApplyHandler.onEvent(IncrementDisruptorApplyHandler.java:25) at com.clougence.cloudcanal.task.applier.incre.IncrementDisruptorApplyHandler.onEvent(IncrementDisruptorApplyHandler.java:12) at com.lmax.disruptor.BatchEventProcessor.processEvents(BatchEventProcessor.java:168) at com.lmax.disruptor.BatchEventProcessor.run(BatchEventProcessor.java:125) at java.lang.Thread.run(Thread.java:748) 2022-08-22 10:00:23.315 [incre-apply-disruptor-4-thd-11] ERROR c.c.c.b.service.task.applier.AbstractRdbIncreDDLExecutor - DDL error.context:{"db":"dpkb","table":"yw_xj_d_inspect","sql":"[ALTER TABLE `dpkb`.`yw_xj_d_inspect`\n\tADD COLUMN `unit_source` TINYINT DEFAULT 0 COMMENT '所属单位来源:0.自定义单位 1.劳务参建单位' AFTER `first_item_code`]","isDdl":true,"pks":[]},msg:SQLSyntaxErrorException: Duplicate column name 'unit_source' java.sql.SQLSyntaxErrorException: Duplicate column name 'unit_source' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:762) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:646) at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:632) at com.clougence.cloudcanal.mysql.worker.writer.incre.MySqlIncreDdlExecutor.executeSqls(MySqlIncreDdlExecutor.java:40) at com.clougence.cloudcanal.base.service.task.applier.AbstractRdbIncreDDLExecutor.doDDL(AbstractRdbIncreDDLExecutor.java:63) at com.clougence.cloudcanal.mysql.worker.writer.incre.AbstractMySQLIncreApplyHandler.processDdl(AbstractMySQLIncreApplyHandler.java:107) at com.clougence.cloudcanal.mysql.worker.writer.incre.MyIncreBatchApplyNoTxHandler.handleWithDdl(MyIncreBatchApplyNoTxHandler.java:172) at com.clougence.cloudcanal.mysql.worker.writer.incre.MyIncreBatchApplyNoTxHandler.handle(MyIncreBatchApplyNoTxHandler.java:124) at com.clougence.cloudcanal.mysql.worker.writer.incre.MyIncreBatchApplyNoTxHandler.handle(MyIncreBatchApplyNoTxHandler.java:39) at com.clougence.cloudcanal.task.applier.incre.IncrementDisruptorApplyHandler.onEvent(IncrementDisruptorApplyHandler.java:25) at com.clougence.cloudcanal.task.applier.incre.IncrementDisruptorApplyHandler.onEvent(IncrementDisruptorApplyHandler.java:12) at com.lmax.disruptor.BatchEventProcessor.processEvents(BatchEventProcessor.java:168) at com.lmax.disruptor.BatchEventProcessor.run(BatchEventProcessor.java:125) at java.lang.Thread.run(Thread.java:748) 2022-08-22 10:00:23.320 [incre-apply-disruptor-4-thd-11] ERROR c.c.c.mysql.worker.writer.AbstractMySQLApplyHandler - write to target error. Msg:SQLSyntaxErrorException: Duplicate column name 'unit_source' com.clougence.cloudcanal.base.service.task.exception.runtime.ApplierHandlerException: DDL error.context:{"db":"dpkb","table":"yw_xj_d_inspect","sql":"[ALTER TABLE `dpkb`.`yw_xj_d_inspect`\n\tADD COLUMN `unit_source` TINYINT DEFAULT 0 COMMENT '所属单位来源:0.自定义单位 1.劳务参建单位' AFTER `first_item_code`]","isDdl":true,"pks":[]},msg:SQLSyntaxErrorException: Duplicate column name 'unit_source' at com.clougence.cloudcanal.base.service.task.applier.AbstractRdbIncreDDLExecutor.doDDL(AbstractRdbIncreDDLExecutor.java:69) at com.clougence.cloudcanal.mysql.worker.writer.incre.AbstractMySQLIncreApplyHandler.processDdl(AbstractMySQLIncreApplyHandler.java:107) at com.clougence.cloudcanal.mysql.worker.writer.incre.MyIncreBatchApplyNoTxHandler.handleWithDdl(MyIncreBatchApplyNoTxHandler.java:172) at com.clougence.cloudcanal.mysql.worker.writer.incre.MyIncreBatchApplyNoTxHandler.handle(MyIncreBatchApplyNoTxHandler.java:124) at com.clougence.cloudcanal.mysql.worker.writer.incre.MyIncreBatchApplyNoTxHandler.handle(MyIncreBatchApplyNoTxHandler.java:39) at com.clougence.cloudcanal.task.applier.incre.IncrementDisruptorApplyHandler.onEvent(IncrementDisruptorApplyHandler.java:25) at com.clougence.cloudcanal.task.applier.incre.IncrementDisruptorApplyHandler.onEvent(IncrementDisruptorApplyHandler.java:12) at com.lmax.disruptor.BatchEventProcessor.processEvents(BatchEventProcessor.java:168) at com.lmax.disruptor.BatchEventProcessor.run(BatchEventProcessor.java:125) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLSyntaxErrorException: Duplicate column name 'unit_source' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:762) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:646) at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:632) at com.clougence.cloudcanal.mysql.worker.writer.incre.MySqlIncreDdlExecutor.executeSqls(MySqlIncreDdlExecutor.java:40) at com.clougence.cloudcanal.base.service.task.applier.AbstractRdbIncreDDLExecutor.doDDL(AbstractRdbIncreDDLExecutor.java:63) ... 9 common frames omitted
Copyright © 2020 ClouGence, Inc.备案号:浙ICP备20007605号-2