为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM database server 64 V8
【操作系统】:麒麟v10sp3
【CPU】: x86_64
【问题描述】*:
Unable to write data to dameng due to violation of unique constraint.
- Table name: C_CB_NATIONAL_ISS_INFO
- Target field: ["SHIP_REG_NO","NATION_VERSION"]
- Data to be written(null): null
- Unique constraint: null <-- Error Message -->
Unable to write data to dameng due to violation of unique constraint.
- Table name: C_CB_NATIONAL_ISS_INFO
- Target field: ["SHIP_REG_NO","NATION_VERSION"]
- Data to be written(null): null
- Unique constraint: null
<-- Simple Stack Trace -->
Caused by: java.sql.BatchUpdateException: 违反表[C_CB_NATIONAL_ISS_INFO]唯一性约束条件[CONS134221323]
dm.jdbc.driver.DBError.throwBatchUpdateException(DBError.java:713)
dm.jdbc.b.a.a(DBAccess.java:874)
dm.jdbc.driver.DmdbPreparedStatement.do_executeLargeBatch(DmdbPreparedStatement.java:567)
dm.jdbc.driver.DmdbPreparedStatement.do_executeBatch(DmdbPreparedStatement.java:514)
dm.jdbc.driver.DmdbPreparedStatement.executeBatch(DmdbPreparedStatement.java:1518)
...
<-- Full Stack Trace -->
java.sql.BatchUpdateException: 违反表[C_CB_NATIONAL_ISS_INFO]唯一性约束条件[CONS134221323]
at io.tapdata.connector.dameng.exception.DamengExceptionCollector.collectViolateUnique(DamengExceptionCollector.java:56)
at io.tapdata.common.dml.NormalRecordWriter.writePart(NormalRecordWriter.java:171)
at io.tapdata.common.dml.NormalRecordWriter.write(NormalRecordWriter.java:98)
at io.tapdata.connector.dameng.DamengConnector.writeRecord(DamengConnector.java:230)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkDataNode.lambda$writeRecord$70(HazelcastTargetPdkDataNode.java:1160)
at io.tapdata.flow.engine.V2.policy.PDkNodeInsertRecordPolicyService.writeRecordWithPolicyControl(PDkNodeInsertRecordPolicyService.java:89)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkDataNode.lambda$writeRecord$75(HazelcastTargetPdkDataNode.java:1156)
at io.tapdata.pdk.core.monitor.PDKInvocationMonitor.invokePDKMethodPrivate(PDKInvocationMonitor.java:165)
at io.tapdata.pdk.core.monitor.PDKInvocationMonitor.lambda$invokePDKMethod$12(PDKInvocationMonitor.java:154)
at io.tapdata.pdk.core.api.Node.applyClassLoaderContext(Node.java:27)
at io.tapdata.pdk.core.monitor.PDKInvocationMonitor.invokePDKMethod(PDKInvocationMonitor.java:154)
at io.tapdata.pdk.core.monitor.PDKInvocationMonitor.invoke(PDKInvocationMonitor.java:96)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkDataNode.lambda$writeRecord$76(HazelcastTargetPdkDataNode.java:1200)
at io.tapdata.aspect.utils.AspectUtils.executeDataFuncAspect(AspectUtils.java:67)
at io.tapdata.flow.engine.V2.node.hazelcast.HazelcastBaseNode.executeDataFuncAspect(HazelcastBaseNode.java:188)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkDataNode.writeRecord(HazelcastTargetPdkDataNode.java:1088)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkDataNode.lambda$processEvents$37(HazelcastTargetPdkDataNode.java:782)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.dispatchTapRecordEvents(HazelcastTargetPdkBaseNode.java:2310)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkDataNode.processEvents(HazelcastTargetPdkDataNode.java:771)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkDataNode.processEvents(HazelcastTargetPdkDataNode.java:749)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.processEventsWithExactlyOnceCheck(HazelcastTargetPdkBaseNode.java:1302)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.processTapEvents(HazelcastTargetPdkBaseNode.java:1282)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.handleTapdataEvents(HazelcastTargetPdkBaseNode.java:1170)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.splitDDL2NewBatch(HazelcastTargetPdkBaseNode.java:1149)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.cdcProcessEvents(HazelcastTargetPdkBaseNode.java:1122)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.lambda$processQueueConsume$33(HazelcastTargetPdkBaseNode.java:1041)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.dispatchTapdataEvents(HazelcastTargetPdkBaseNode.java:932)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.lambda$processQueueConsume$34(HazelcastTargetPdkBaseNode.java:1010)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.drainAndRun(HazelcastTargetPdkBaseNode.java:1086)
at io.tapdata.flow.engine.V2.node.hazelcast.data.pdk.HazelcastTargetPdkBaseNode.processQueueConsume(HazelcastTargetPdkBaseNode.java:1010)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: java.sql.BatchUpdateException: 违反表[C_CB_NATIONAL_ISS_INFO]唯一性约束条件[CONS134221323]
at dm.jdbc.driver.DBError.throwBatchUpdateException(DBError.java:713)
at dm.jdbc.b.a.a(DBAccess.java:874)
at dm.jdbc.driver.DmdbPreparedStatement.do_executeLargeBatch(DmdbPreparedStatement.java:567)
at dm.jdbc.driver.DmdbPreparedStatement.do_executeBatch(DmdbPreparedStatement.java:514)
at dm.jdbc.driver.DmdbPreparedStatement.executeBatch(DmdbPreparedStatement.java:1518)
at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
at io.tapdata.common.dml.NormalWriteRecorder.executeBatch(NormalWriteRecorder.java:154)
at io.tapdata.common.dml.NormalRecordWriter.writePart(NormalRecordWriter.java:159)
... 33 more
执行的merge into Sql:
MERGE INTO "MSA_ADMIN"."C_CB_NATIONAL_ISS_INFO" USING dual ON ("SHIP_REG_NO"=? AND "NATION_VERSION"=?) WHEN MATCHED THEN UPDATE SET "TEMP_NATION_FLAG"=?, "LANGUAGE_TYPE"=?, "SHIP_OPERATOR_CN"=?, "SHIP_OPERATOR_ADDR_CN"=?, "OPERATOR_LEGAL_PERSON"=?, "OPERATOR_CONTACT_PHONE"=?, "OPERATOR_POSTALCODE"=?, "SHIP_MANAGER_CN"=?, "SHIP_MANAGER_ADDR_CN"=?, "MANAGER_LEGAL_PERSON"=?, "MANAGER_CONTACT_PHONE"=?, "MANAGER_POSTALCODE"=?, "SHIP_OWNER_CN"=?, "SHIP_OWNER_ADDR_CN"=?, "SHIP_OPERATOR_EN"=?, "SHIP_OPERATOR_ADDR_EN"=?, "SHIP_OWNER_EN"=?, "SHIP_OWNER_ADDR_EN"=?, "EFFECTIVE_DATE"=?, "EXPIRE_DATE"=?, "CERT_OBTAIN_DATE"=?, "STATUS_FLAG"=?, "PRINT_FLAG"=?, "SEAL_FLAG"=?, "CHECK_FLAG"=?, "ISSUE_FLAG"=?, "ORG_CODE"=?, "CERT_PRINT_NO"=?, "REG_DELETION_NO"=?, "REMARK"=?, "CREATOR_CODE"=?, "CREATE_TIME"=?, "OPERATOR_CODE"=?, "OPERATE_TIME"=?, "OPERATE_FLAG"=?, "SOURCE_CODE"=?, "DATA_ORG_CODE"=?, "CREATE_DATE"=?, "CREATOR"=?, "LAST_UPDATE_DATE"=?, "UPDATE_BY"=?, "DELETE_FLAG"=?, "PROCESS_STATUS"=? WHEN NOT MATCHED THEN INSERT("SHIP_REG_NO","NATION_VERSION","TEMP_NATION_FLAG","LANGUAGE_TYPE","SHIP_OPERATOR_CN","SHIP_OPERATOR_ADDR_CN","OPERATOR_LEGAL_PERSON","OPERATOR_CONTACT_PHONE","OPERATOR_POSTALCODE","SHIP_MANAGER_CN","SHIP_MANAGER_ADDR_CN","MANAGER_LEGAL_PERSON","MANAGER_CONTACT_PHONE","MANAGER_POSTALCODE","SHIP_OWNER_CN","SHIP_OWNER_ADDR_CN","SHIP_OPERATOR_EN","SHIP_OPERATOR_ADDR_EN","SHIP_OWNER_EN","SHIP_OWNER_ADDR_EN","EFFECTIVE_DATE","EXPIRE_DATE","CERT_OBTAIN_DATE","STATUS_FLAG","PRINT_FLAG","SEAL_FLAG","CHECK_FLAG","ISSUE_FLAG","ORG_CODE","CERT_PRINT_NO","REG_DELETION_NO","REMARK","CREATOR_CODE","CREATE_TIME","OPERATOR_CODE","OPERATE_TIME","OPERATE_FLAG","SOURCE_CODE","DATA_ORG_CODE","CREATE_DATE","CREATOR","LAST_UPDATE_DATE","UPDATE_BY","DELETE_FLAG","PROCESS_STATUS") VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
表结构中:
并且这些sql前表中已经存在SHIP_REG_NO='110523000042',NATION_VERSION='01'的记录行,理论上执行merge into应该按update语义执行,单实际结果为:唯一索引冲突异常
对SQL日志进行分析:
ON 条件参数和插入列参数存在类型/尾部空格差异
DM 的 CHAR 会自动填充空格,VARCHAR2 保存实际长度;尾部空格比较还受到 BLANK_PAD_MODE、SPACE_COMPARE_MODE 影响
merge的预期语法理论上需要保证ON 条件参数和插入列参数一致,不应受类型影响,因此这里判断为DM的ON未按预期来做尾部空格补充导致merge into语法未按预期执行。理论上merge into是为了兼容存在就更新不存在就插入的问题,在不存在并非写入场景下不应该出现索引唯一性冲突问题
CREATE TABLE T(SHIP_REG_NO VARCHAR2(20), NATION_VERSION CHAR(8), REMARK VARCHAR2(50));
CREATE UNIQUE INDEX IDX_T ON T(SHIP_REG_NO, NATION_VERSION);
INSERT INTO T VALUES('110523000042','01','ORIGINAL_ROW');
COMMIT;
MERGE INTO T USING DUAL ON (SHIP_REG_NO='110523000042' AND NATION_VERSION='01')
WHEN MATCHED THEN UPDATE SET REMARK='UPD'
WHEN NOT MATCHED THEN INSERT VALUES('110523000042','01','INS');
--BLANK_PAD_MODE=1
--DM BLANK_PAD_MODE=1 的表现与 Oracle 完全吻合(字面量按 CHAR 语义补空格→匹配;VARCHAR 绑定值按非填充语义→不匹配)。符合 Oracle 兼容目标,不构成缺陷。
declare
var1 varchar:='110523000042';
var2 varchar:='01';
begin
EXECUTE IMMEDIATE 'MERGE INTO T USING DUAL ON (SHIP_REG_NO=? AND NATION_VERSION=?)
WHEN MATCHED THEN UPDATE SET REMARK=''UPD''
WHEN NOT MATCHED THEN INSERT VALUES(''110523000042'',''01'',''INS'')' USING var1,var2;
end;
--绕过的办法:对传入的变量做类型转换
declare
var1 varchar:='110523000042';
var2 varchar:='01';
begin
EXECUTE IMMEDIATE 'MERGE INTO T USING DUAL ON (SHIP_REG_NO=? AND NATION_VERSION=cast(? as char(8)))
WHEN MATCHED THEN UPDATE SET REMARK=''UPD''
WHEN NOT MATCHED THEN INSERT VALUES(''110523000042'',''01'',''INS'')' USING var1,var2;
end;
看看是不是你查询的表中有唯一的列重复

SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI
WHERE PARA_NAME IN ('BLANK_PAD_MODE','SPACE_COMPARE_MODE');
先确认一下这两个参数的值