用datax迁移mysql至社区版

OceanBase
#1

用datax迁移mysql至oceanbase社区版
【 使用环境 】测试环境
【 OB or 其他组件 】datax
【 使用版本 】mysql5.6。oceanbase社区版
【问题描述】执行job时提示表不存在。但表已经建好了，删除重建或者换表名都是一样的错误提示。
【复现路径】问题出现前后相关操作
python datax.py …/job/test1job.json
【附件及日志】
{
“job”:{
“setting”:{
“speed”:{
“channel”:32
},
“errorLimit”:{
“percentage”: 0.1
}
},
“content”:[
{
“reader”:{
“name”:“mysqlreader”,
“parameter”:{
“username”: “root”,
“password”:“123456”,
“column”:[""],
“connection”: [
{
“table”: [“t1”],
“jdbcUrl”:[“jdbc:mysql://192.168.1.17:3306/test”]
}
]
}
},
“writer”:{
“name”:“oceanbasev10writer”,
“parameter”:{
“writeMode”:“insert”,
“batchSize”:5000,
“memstoreThreshold”:“90”,
“username”:“root@mq_t1”,
“password”:“123456”,
“column”:[""],
“preSql”: [
“truncate table t1”
]
“connection”: [
{
“jdbcUrl”:“jdbc:oceanbase://192.168.50.104:2883/test”,
“table”: [“t1”]
}
]
}
}
}
]
}
}

错误信息
2024-01-26 10:00:51.528 [main] INFO PerfTrace - PerfTrace traceId=job_-1, isEnable=false
2024-01-26 10:00:51.528 [main] INFO JobContainer - DataX jobContainer starts job.
2024-01-26 10:00:51.530 [main] INFO JobContainer - Set jobId = 0
2024-01-26 10:00:51.916 [job-0] INFO OriginalConfPretreatmentUtil - Available jdbcUrl:jdbc:mysql://192.168.1.17:3306/test?yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true.
2024-01-26 10:00:51.917 [job-0] WARN OriginalConfPretreatmentUtil - 您的配置文件中的列配置存在一定的风险. 因为您未配置读取数据库表的列，当您的表字段个数、类型有变动时，可能影响任务正确性甚至会运行出错。请检查您的配置并作出修改.
2024-01-26 10:00:52.224 [job-0] INFO DbUtils - value for query [SHOW VARIABLES LIKE ‘ob_compatibility_mode’] is [MYSQL]
2024-01-26 10:00:52.309 [job-0] ERROR JobContainer - Exception when job run
com.alibaba.datax.common.exception.DataXException: Code:[DBUtilErrorCode-06], Description:[执行数据庿Sql 失败, 请检查您的配置的 column/table/where/querySql或者向 DBA 寻求帮助.]. - 执行的SQL丿 select * from t1 where 1=2 具体错误信息为：com.alipay.oceanbase.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘test.t1’ doesn’t exist - com.alipay.oceanbase.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘test.t1’ doesn’t exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.alipay.oceanbase.jdbc.Util.handleNewInstance(Util.java:439)
at com.alipay.oceanbase.jdbc.Util.getInstance(Util.java:416)
at com.alipay.oceanbase.jdbc.SQLError.createSQLException(SQLError.java:1176)
at com.alipay.oceanbase.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4879)
at com.alipay.oceanbase.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4804)
at com.alipay.oceanbase.jdbc.MysqlIO.sendCommand(MysqlIO.java:2998)
at com.alipay.oceanbase.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:3164)
at com.alipay.oceanbase.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2903)
at com.alipay.oceanbase.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2855)
at com.alipay.oceanbase.jdbc.StatementImpl.executeQuery(StatementImpl.java:1445)
at com.alibaba.datax.plugin.rdbms.util.DBUtil.getTableColumnsByConn(DBUtil.java:520)
at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.dealColumnConf(OriginalConfPretreatmentUtil.java:106)
at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.dealColumnConf(OriginalConfPretreatmentUtil.java:147)
at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.doPretreatment(OriginalConfPretreatmentUtil.java:36)
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)

    at com.alibaba.datax.common.exception.DataXException.asDataXException(DataXException.java:41) ~[datax-common-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.rdbms.util.RdbmsException.asQueryException(RdbmsException.java:97) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.rdbms.util.DBUtil.getTableColumnsByConn(DBUtil.java:527) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.dealColumnConf(OriginalConfPretreatmentUtil.java:106) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.dealColumnConf(OriginalConfPretreatmentUtil.java:147) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.rdbms.writer.util.OriginalConfPretreatmentUtil.doPretreatment(OriginalConfPretreatmentUtil.java:36) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.rdbms.writer.CommonRdbmsWriter$Job.init(CommonRdbmsWriter.java:41) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.plugin.writer.oceanbasev10writer.OceanBaseV10Writer$Job.init(OceanBaseV10Writer.java:76) ~[oceanbasev10writer-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.core.job.JobContainer.initJobWriter(JobContainer.java:704) ~[datax-core-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.core.job.JobContainer.init(JobContainer.java:304) ~[datax-core-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.core.job.JobContainer.start(JobContainer.java:113) ~[datax-core-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.core.Engine.start(Engine.java:86) [datax-core-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.core.Engine.entry(Engine.java:168) [datax-core-0.0.1-SNAPSHOT.jar:na]
    at com.alibaba.datax.core.Engine.main(Engine.java:201) [datax-core-0.0.1-SNAPSHOT.jar:na]

Caused by: com.alipay.oceanbase.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘test.t1’ doesn’t exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_171]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_171]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_171]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_171]
at com.alipay.oceanbase.jdbc.Util.handleNewInstance(Util.java:439) ~[oceanbase-client-1.1.10.jar:na]
at com.alipay.oceanbase.jdbc.Util.getInstance(Util.java:416) ~[oceanbase-client-1.1.10.jar:na]
at com.alipay.oceanbase.jdbc.SQLError.createSQLException(SQLError.java:1176) ~[oceanbase-client-1.1.10.jar:na]
at com.alipay.oceanbase.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4879) ~[oceanbase-client-1.1.10.jar:na]
at com.alipay.oceanbase.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4804) ~[oceanbase-client-1.1.10.jar:na]
at com.alipay.oceanbase.jdbc.MysqlIO.sendCommand(MysqlIO.java:2998) ~[oceanbase-client-1.1.10.jar:na]
at com.alipay.oceanbase.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:3164) ~[oceanbase-client-1.1.10.jar:na]
at com.alipay.oceanbase.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2903) ~[oceanbase-client-1.1.10.jar:na]
at com.alipay.oceanbase.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2855) ~[oceanbase-client-1.1.10.jar:na]
at com.alipay.oceanbase.jdbc.StatementImpl.executeQuery(StatementImpl.java:1445) ~[oceanbase-client-1.1.10.jar:na]
at com.alibaba.datax.plugin.rdbms.util.DBUtil.getTableColumnsByConn(DBUtil.java:520) ~[plugin-rdbms-util-0.0.1-SNAPSHOT.jar:na]
… 11 common frames omitted

#3

"jdbcUrl": “||dsc_ob10_dsc||obdemo:obmysql||dsc_ob10_dsc||jdbc:oceanbase://192.168.150.116:2883/test?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true”,
url有问题把，参考下呢，然后看下官网

#4

username”:“root@mq_t1”, 还有用户这里试下root呢，是不是这个租户

#5

默认端口是 2881 ，使用2883代替 2881端口了吧 ？？

#6

下面示例供参考

cat job/bmsql_oorder_csv2ob.json
{
    "job": {
        "setting": {
            "speed": {
                "channel": 4
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.1
            }
        },
        "content": [
            {
                "reader": {
                    "name": "txtfilereader",
                    "parameter": {
                        "path": ["/tmp/tpcc/bmsql_oorder"],
                        "fileName": "bmsql_oorder",
                        "encoding": "UTF-8",
                        "column": ["*"],
                        "dateFormat": "yyyy-MM-dd hh:mm:ss" ,
                        "nullFormat": "\\N" ,
                        "fieldDelimiter": ","
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "obWriteMode": "insert",
                        "column": [
                            "*"
                        ],
                        "preSql": [
                            "truncate table bmsql_oorder"
                        ],
                        "connection": [
                            {
                                "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",
                                "table": [
                                    "bmsql_oorder"
                                ]
                            }
                        ],
                        "username": "tpcc",
                        "password":"********",
                        "writerThreadCount":10,
                        "batchSize": 1000,
                        "memstoreThreshold": "0.9"
                    }
                }
            }
        ]
    }
}

更多用法可以看看：https://mp.weixin.qq.com/s/AmqAb6XymC7gjPsC0Bishg