为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】: 8.0
【操作系统】:
【CPU】:
【问题描述】*:使用DTS工具,从MYSQL迁移至DM报错。表结构迁移成功,数据迁移失败。以下是日志:
[INFO] 2022-12-14 14:44:29.928> [SOURCE] jdbc:mysql://10.10.20.44:3306/auctionent?tinyInt1isBit=false&transformedBit1sBoolean=false([JDBC]:8.0 [DB]:5.7)
[INFO] 2022-12-14 14:44:29.928> [DEST] jdbc:dm://10.10.20.13:5236([JDBC]:8.1 [DB]:8.1)
[INFO] 2022-12-14 14:44:29.928> [START]{13}start transform…
[INFO] 2022-12-14 14:44:29.933> [TASK]@ANALYZE_TABLE:start
[INFO] 2022-12-14 14:44:30.364> [TASK]@ANALYZE_TABLE:end
[INFO] 2022-12-14 14:44:30.378> [TASK]{table-“d_ac_auction”:“AUCTIONENT.D_AC_AUCTION”}@COPY_DATA:start:copied=0
[ERROR] 2022-12-14 14:44:30.684> [TASK]{table-“d_ac_auction”:“AUCTIONENT.D_AC_AUCTION”}@COPY_DATA:fail:copied=0
com.dameng.dts.plugin.support.task.TransformDataException: dm.jdbc.driver.DMException: 不支持该数据类型
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.doInsert(BaseCopyDataTask.java:1593)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.insert(BaseCopyDataTask.java:1461)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.insert(BaseCopyDataTask.java:1497)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.doImport(BaseCopyDataTask.java:1404)
at com.dameng.dts.plugin.support.task.BaseDm7CopyDataTask.doImport(BaseDm7CopyDataTask.java:220)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask$DoImportTask.run(BaseCopyDataTask.java:2810)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.doParallelRun(BaseCopyDataTask.java:1101)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.parallelImport(BaseCopyDataTask.java:1306)
at com.dameng.dts.plugin.support.task.BaseDm7CopyDataTask.parallelImport(BaseDm7CopyDataTask.java:374)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.internalImp(BaseCopyDataTask.java:1230)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.imp(BaseCopyDataTask.java:319)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.doRun(BaseCopyDataTask.java:296)
at com.dameng.common.task.Task.run(SourceFile:127)
at com.dameng.common.task.TaskRunner$TaskAdapter.call(SourceFile:251)
at com.dameng.common.task.TaskRunner$TaskAdapter.call(SourceFile:1)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
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:750)
Caused by: dm.jdbc.driver.DMException: 不支持该数据类型
at dm.jdbc.driver.DBError.throwz(DBError.java:751)
at dm.jdbc.driver.DmdbPreparedStatement.do_setObject(DmdbPreparedStatement.java:951)
at dm.jdbc.driver.DmdbPreparedStatement.do_setObject(DmdbPreparedStatement.java:860)
at dm.jdbc.driver.DmdbPreparedStatement.do_setObject(DmdbPreparedStatement.java:856)
at dm.jdbc.driver.DmdbPreparedStatement.setObject(DmdbPreparedStatement.java:1313)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.setPsParamValue(BaseCopyDataTask.java:2191)
at com.dameng.dts.plugin.support.task.BaseDm7CopyDataTask.setPsParamValue(BaseDm7CopyDataTask.java:501)
at com.dameng.dts.plugin.mysql.dm7.task.CopyDataTask.setPsParamValue(CopyDataTask.java:145)
at com.dameng.dts.plugin.support.task.BaseCopyDataTask.doInsert(BaseCopyDataTask.java:1587)
… 18 more
[INFO] 2022-12-14 14:44:30.686> [END]执行完成. 任务总数:2, 完成:1, 出错:1, 取消:0, 耗时:760毫秒
迁移报错数据是:
“1768191128949157888”,“JJD202207290003”,“”,“0”,“”,“”,“”,“”,“”,“purgroup”,“purgroup”,“”,“”,“”,“”,“”,“auctionType_001”,“quotationType_001”,“”,“”,“rankMethod_001”,“”,“”,“”,“”,“admin”,“13555555556”,“”,“”,“800”,“”,“1747853328286613504”,“庄培锋”,“2022-07-29T10:38:25”,“1747853328286613504”,“庄培锋”,“2022-07-29T10:38:25”,“auctionDirection_001”,“quotePublicMethod_001”,“”,“”,“”,“”,“admin”,“1”
表结构SQL:
CREATE TABLE “AUCTIONENT”.“D_AC_AUCTION”
(
“AUCTIONID” BIGINT NOT NULL,
“AUCTIONNO” VARCHAR(50),
“SCALINGID” BIGINT,
“STATUS” TINYINT,
“AUCTIONTITLE” VARCHAR(200),
“STARTTIME” TIMESTAMP(0),
“STOPTIME” TIMESTAMP(0),
“PURCHASINGORGCODE” VARCHAR(50),
“PURCHASINGORGNAME” VARCHAR(100),
“PURCHASINGGROUPCODE” VARCHAR(50),
“PURCHASINGGROUPNAME” VARCHAR(100),
“MATERIALGROUPCODE” VARCHAR(50),
“MATERIALGROUPNAME” VARCHAR(100),
“CURRENCYCODE” VARCHAR(50),
“CURRENCYNAME” VARCHAR(100),
“CURRENCYSYMBOL” VARCHAR(10),
“AUCTIONTYPE” VARCHAR(20),
“QUOTATIONTYPE” VARCHAR(20),
“PRICEREDUCTION” VARCHAR(20),
“QUOTATIONMETHOD” VARCHAR(20),
“RANKMETHOD” VARCHAR(20),
“ENDTIME” TIMESTAMP(0),
“OVERTIMES” INT,
“EFFECTIVEDATE” TIMESTAMP(0),
“EXPIRATIONDATE” TIMESTAMP(0),
“LINKMAN” VARCHAR(50),
“LINKMANTEL” VARCHAR(50),
“REMARK” VARCHAR(2000),
“ISQUOTE” TINYINT,
“CLIENTCODE” VARCHAR(50),
“FILEGROUPID” BIGINT,
“CREATEUSERID” BIGINT,
“CREATEUSERNAME” VARCHAR(100),
“CREATETIME” TIMESTAMP(0),
“MODIFYUSERID” BIGINT,
“MODIFYUSERNAME” VARCHAR(100),
“MODIFYTIME” TIMESTAMP(0),
“AUCTIONDIRECTION” VARCHAR(30),
“QUOTATIONPUBLICTYPE” VARCHAR(30),
“MINBEFOREEND” TINYINT,
“MINDELAY” TINYINT,
“DELAYTIMES” TINYINT,
“SALESMANCODE” VARCHAR(50),
“SALESMANNAME” VARCHAR(50),
“SALESMANUSERID” BIGINT,
NOT CLUSTER PRIMARY KEY(“AUCTIONID”)) STORAGE(ON “AUCTIONENT”, CLUSTERBTR) ;
COMMENT ON TABLE “AUCTIONENT”.“D_AC_AUCTION” IS ‘竞价主单’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“AUCTIONDIRECTION” IS ‘竞价方向’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“AUCTIONID” IS ‘竞价主单ID’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“AUCTIONNO” IS ‘竞价单号’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“AUCTIONTITLE” IS ‘竞价单标题’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“AUCTIONTYPE” IS ‘竞价类别’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“CLIENTCODE” IS ‘客户端编码’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“CREATETIME” IS ‘创建时间’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“CREATEUSERID” IS ‘创建者ID’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“CREATEUSERNAME” IS ‘创建者名称’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“CURRENCYCODE” IS ‘报价货币编码’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“CURRENCYNAME” IS ‘报价货币名称’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“CURRENCYSYMBOL” IS ‘货币符号’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“DELAYTIMES” IS ‘延迟次数’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“EFFECTIVEDATE” IS ‘生效日期’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“ENDTIME” IS ‘实际结束时间’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“EXPIRATIONDATE” IS ‘失效日期’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“FILEGROUPID” IS ‘附件组ID’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“ISQUOTE” IS ‘是否报价(1:否/2:是 默认1)’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“LINKMAN” IS ‘联系人’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“LINKMANTEL” IS ‘联系方法’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“MATERIALGROUPCODE” IS ‘物料组编码’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“MATERIALGROUPNAME” IS ‘物料组名称’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“MINBEFOREEND” IS ‘结束前几分钟’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“MINDELAY” IS ‘延长分钟数’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“MODIFYTIME” IS ‘修改时间’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“MODIFYUSERID” IS ‘修改者ID’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“MODIFYUSERNAME” IS ‘修改者名称’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“OVERTIMES” IS ‘超时次数’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“PRICEREDUCTION” IS ‘降价方式’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“PURCHASINGGROUPCODE” IS ‘采购组编码’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“PURCHASINGGROUPNAME” IS ‘采购组名称’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“PURCHASINGORGCODE” IS ‘采购组织编码’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“PURCHASINGORGNAME” IS ‘采购组织名称’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“QUOTATIONMETHOD” IS ‘报价方式’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“QUOTATIONPUBLICTYPE” IS ‘报价公开方式’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“QUOTATIONTYPE” IS ‘报价类型’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“RANKMETHOD” IS ‘排名方式’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“REMARK” IS ‘备注’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“SALESMANCODE” IS ‘业务员编码’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“SALESMANNAME” IS ‘业务员名称’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“SALESMANUSERID” IS ‘业务员ID’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“SCALINGID” IS ‘定标主单ID’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“STARTTIME” IS ‘开始时间’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“STATUS” IS ‘单据状态’;
COMMENT ON COLUMN “AUCTIONENT”.“D_AC_AUCTION”.“STOPTIME” IS ‘截止时间’;
CREATE INDEX “IDX_AUCTION_PCGROUPCODE” ON “AUCTIONENT”.“D_AC_AUCTION”(“PURCHASINGGROUPCODE” ASC) STORAGE(ON “AUCTIONENT”, CLUSTERBTR) ;
CREATE INDEX “IDX_AUCTION_STATUS” ON “AUCTIONENT”.“D_AC_AUCTION”(“STATUS” ASC) STORAGE(ON “AUCTIONENT”, CLUSTERBTR) ;
CREATE INDEX “IDX_PK_AUCTIONID” ON “AUCTIONENT”.“D_AC_AUCTION”(“AUCTIONID” ASC) STORAGE(ON “AUCTIONENT”, CLUSTERBTR) ;
CREATE INDEX “IDX_AUCTIONNO” ON “AUCTIONENT”.“D_AC_AUCTION”(“AUCTIONNO” ASC) STORAGE(ON “AUCTIONENT”, CLUSTERBTR) ;
CREATE INDEX “IDX_AUCTION_PCORGCODE” ON “AUCTIONENT”.“D_AC_AUCTION”(“PURCHASINGORGCODE” ASC) STORAGE(ON “AUCTIONENT”, CLUSTERBTR) ;
CREATE INDEX “IDX_AUCTION_MGROUPCODE” ON “AUCTIONENT”.“D_AC_AUCTION”(“MATERIALGROUPCODE” ASC) STORAGE(ON “AUCTIONENT”, CLUSTERBTR) ;
还有一种可能是由于驱动包导致,在连接mysql数据库时指定驱动进行数据迁移
1)可以尝试换一个mysql驱动包,最好与mysql版本一致;2)试下换一个新点的达梦DTS工具,可以在官网下载今年的版本https://eco.dameng.com/download/
应该是驱动包的问题,你的时间数据中间加了个T,换个驱动mysql-connector-java-8.0.16.jar
1768191128949157888”,“JJD202207290003”,“”,“0”,“”,“”,“”,“”,“”,“purgroup”,“purgroup”,“”,“”,“”,“”,“”,“auctionType_001”,“quotationType_001”,“”,“”,“rankMethod_001”,“”,“”,“”,“”,“admin”,“13555555556”,“”,“”,“800”,“”,“1747853328286613504”,“庄培锋”,“2022-07-29T10:38:25”,“1747853328286613504”,“庄培锋”,“2022-07-29T10:38:25”,“auctionDirection_001”,“quotePublicMethod_001”,“”,“”,“”,“”,“admin”,“1”
MySQL 中时间类型 TIMESTAMP 默认 default 设置为 0000-00-00 00:00:00。
DM 中 TIMESTAMP 类型数据不能为 0000-00-0000:00:00,在 DM 中是不合法的,必须在 0001-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999 之间,所以可通过直接修改 MySQL 中的业务表数据后进行数据迁移。