【 使用环境 】测试环境
【 OB or 其他组件 】
【 使用版本 】企业版OceanBase 3.2.4.3
【问题描述】使用最新datax开源版从达梦dm数据库迁移数据到oceanbase(oracle模式)，同步表SERVICE_LINK_RUN的数据报错：
com.alibaba.datax.common.exception.DataXException: Code:[DBUtilErrorCode-01], Description:[获取表字段相关信息失败.]. - 获取表:SERVICE_LINK_RUN 的字段的元信息时失败
. 请联系 DBA 核查该库、表信息. - java.sql.SQLSyntaxErrorException: (conn=1078730) ORA-00900: You have an error in your SQL syntax; check the manual that corresponds
to your OceanBase version for the right syntax to use near ‘from SERVICE_LINK_RUN where 1=2’ at line 1
to your OceanBase version for the right syntax to use near ‘from SERVICE_LINK_RUN where 1=2’ at line 1
at com.oceanbase.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:108)
at com.oceanbase.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:200)
at com.oceanbase.jdbc.OceanBaseStatement.executeExceptionEpilogue(OceanBaseStatement.java:310)
at com.oceanbase.jdbc.OceanBaseStatement.executeInternal(OceanBaseStatement.java:421)
at com.oceanbase.jdbc.OceanBaseStatement.executeQuery(OceanBaseStatement.java:691)
at com.alibaba.datax.plugin.rdbms.util.DBUtil.getColumnMetaData(DBUtil.java:566)
at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.dealColumnConf(OriginalConfPretreatmentUtil.java:125)
at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.dealColumnConf(OriginalConfPretreatmentUtil.java:140)
at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.doPretreatment(OriginalConfPretreatmentUtil.java:35)
at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Job.init(CommonRdbmsWriter.java:41)
at com.alibaba.datax.plugin.writer.oceanbasev10writer.OceanBaseV10Writer$Job.init(OceanBaseV10Writer.java:76)
at com.alibaba.datax.core.job.JobContainer.initJobWriter(JobContainer.java:704)
at com.alibaba.datax.core.job.JobContainer.init(JobContainer.java:304)
at com.alibaba.datax.core.job.JobContainer.start(JobContainer.java:113)
at com.alibaba.datax.core.Engine.start(Engine.java:86)
at com.alibaba.datax.core.Engine.entry(Engine.java:168)
at com.alibaba.datax.core.Engine.main(Engine.java:201)
这种问题是需要再job文件中配置什么解决？还是企业版OceanBase 3.2.4.3 需要使用企业版的datax?
完整的任务日志发一下
json配置发一下，才能看到你配置是不是有问题
datax版本也发一下
2023-08-10 10:37:42.419 [main] INFO PerfTrace - PerfTrace traceId=job_-1, isEnable=false
2023-08-10 10:37:42.419 [main] INFO JobContainer - DataX jobContainer starts job.
2023-08-10 10:37:42.420 [main] INFO JobContainer - Set jobId = 0
2023-08-10 10:37:42.923 [job-0] INFO OriginalConfPretreatmentUtil - Available jdbcUrl:jdbc:dm://...:/.
2023-08-10 10:37:42.969 [job-0] INFO OriginalConfPretreatmentUtil - table:[SERVICE_LINK_RUN] has columns:[ID,CONSUMER_ID,CONSUMER_NAME,SERVICE_ID,SERVICE_NAME,SYST
EM_ID,SYSTEM_NAME,TOTAL_NUM,TOTAL_AVG_TIME,SUCCESS_NUM,FAILED_NUM,INSTANCE,CREATE_TIME].
2023-08-10 10:37:43.199 [job-0] INFO DbUtils - value for query [SHOW VARIABLES LIKE ‘ob_compatibility_mode’] is [ORACLE]
2023-08-10 10:37:43.236 [job-0] INFO OriginalConfPretreatmentUtil - table:[SERVICE_LINK_RUN] all columns:[
ID,CONSUMER_ID,CONSUMER_NAME,SERVICE_ID,SERVICE_NAME,SYSTEM_ID,SYSTEM_NAME,TOTAL_NUM,TOTAL_AVG_TIME,SUCCESS_NUM,FAILED_NUM,INSTANCE,CREATE_TIME
].
2023-08-10 10:37:43.266 [job-0] ERROR JobContainer - Exception when job run
com.alibaba.datax.common.exception.DataXException: Code:[DBUtilErrorCode-01], Description:[获取表字段相关信息失败.]. - 获取表:SERVICE_LINK_RUN 的字段的元信息时失败
. 请联系 DBA 核查该库、表信息. - java.sql.SQLSyntaxErrorException: (conn=1078730) ORA-00900: You have an error in your SQL syntax; check the manual that corresponds
to your OceanBase version for the right syntax to use near ‘from SERVICE_LINK_RUN where 1=2’ at line 1
at com.oceanbase.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:108)
at com.oceanbase.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:200)
at com.oceanbase.jdbc.OceanBaseStatement.executeExceptionEpilogue(OceanBaseStatement.java:310)
at com.oceanbase.jdbc.OceanBaseStatement.executeInternal(OceanBaseStatement.java:421)
at com.oceanbase.jdbc.OceanBaseStatement.executeQuery(OceanBaseStatement.java:691)
at com.alibaba.datax.plugin.rdbms.util.DBUtil.getColumnMetaData(DBUtil.java:566)
at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.dealColumnConf(OriginalConfPretreatmentUtil.java:125)
at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.dealColumnConf(OriginalConfPretreatmentUtil.java:140)
at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.doPretreatment(OriginalConfPretreatmentUtil.java:35)
at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Job.init(CommonRdbmsWriter.java:41)
at com.alibaba.datax.plugin.writer.oceanbasev10writer.OceanBaseV10Writer$Job.init(OceanBaseV10Writer.java:76)
at com.alibaba.datax.core.job.JobContainer.initJobWriter(JobContainer.java:704)
at com.alibaba.datax.core.job.JobContainer.init(JobContainer.java:304)
at com.alibaba.datax.core.job.JobContainer.start(JobContainer.java:113)
at com.alibaba.datax.core.Engine.start(Engine.java:86)
at com.alibaba.datax.core.Engine.entry(Engine.java:168)
at com.alibaba.datax.core.Engine.main(Engine.java:201)
jdbc关键信息隐藏了下
{
“job”: {
“setting”: {
“speed”: {
“channel”: 2
},
“errorLimit”: {
“record”: 0,
“percentage”: 0.2
}
},
“content”: [
{
“reader”: {
“where”: “”,
“readBatchSize”: 10,
“name”: “rdbmsreader”,
“parameter”: {
“username”: “",
“password”: "”,
“column”: [""],
“splitPk”: “ID”,
“connection”: [
{
“table”: [
“SERVICE_LINK_RUN”
],
“jdbcUrl”: [
“jdbc:dm://...:/”
]
}
]
}
},
“writer”: {
“name”: “oceanbasev10writer”,
“parameter”: {
“obWriteMode”: “insert”,
“username”: “",
“password”: "”,
“column”: [""],
“splitPk”: “ID”,
“connection”: [
{
“table”: [
“SERVICE_LINK_RUN”
],
“jdbcUrl”:“jdbc:oceanbase://...:/?loadBalanceAutoCommitStatementThreshold=5&allowSendParamTypes=true&allowMultiQueries=true&UseLocalSessionState=true&useServerPrepStmts=false&cachePrepStmts=true”
}
],
“batchSize”: 5,
“writerThreadCount”: 5,
“memstoreThreshold”: “0.9”
}
}
}
]
}
}
job的配置，关键信息* 号隐藏了
这里配置的是空？
如果配置的是空 就不对哈 要把字段名写上 或者 如果全部字段都需要的话使用*
使用*或者""都可以的，代表的都是全部列
不行的 你可以看下这一块代码com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil#dealColumnConf(com.alibaba.datax.common.util.Configuration, com.alibaba.datax.plugin.rdbms.util.ConnectionFactory, java.lang.String)
你配置了空字符"" 后面执行的select语句就变成了 select from xxx where 1=2 就会报语法错误
“jdbcUrl”:“jdbc:oceanbase://...:/?loadBalanceAutoCommitStatementThreshold=5&allowSendParamTypes=true&allowMultiQueries=true&UseLocalSessionState=true&useServerPrepStmts=false&cachePrepStmts=true”
这样配置肯定是错的，你在用mysql方式配置datax对oceanbase的支持。
你可以官网找到样例的。问答区都可以搜索到样例
"jdbcUrl": "||_dsc_ob10_dsc_||obdemo:oboracle||_dsc_ob10_dsc_||jdbc:oceanbase://127.0.0.1:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",