oracle to tidb on ogg OGG-00868

1.环境说明

oracle 11.2.0.4
tidb 4.0.0
ogg on oracle 12.2.0.1
ogg on tidb 12.3 (mysql version)

2.安装参考链接:
https://book.tidb.io/session4/chapter5/from-oracle-to-tidb.html

操作步骤在### 5. 进程启动顺序 <<<<==============以上链接的这个章节中启动全量抽取进程出现以下错误
2020-09-11 14:15:00 ERROR OGG-00868 Query to retrieve Octect Length Failed for database=‘ITOM’ AND table=‘TBL_SUBMCHT_INFO01’ AND column=1.

详细日志如下:


             Oracle GoldenGate Delivery for MySQL
  Version 12.3.0.1.5 OGGCORE_12.3.0.1.0_PLATFORMS_180501.2124

Linux, x64, 64bit (optimized), MySQL Enterprise on May 2 2018 11:18:24

Copyright © 1995, 2018, Oracle and/or its affiliates. All rights reserved.

                Starting at 2020-09-11 14:14:55

Operating System Version:
Linux
Version #1 SMP Wed Jun 3 14:28:03 UTC 2020, Release 3.10.0-1127.10.1.el7.x86_64
Node: node1
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited

Process id: 58877

Description:


** Running with the following parameters **


2020-09-11 14:15:00 INFO OGG-03059 Operating system character set identified as UTF-8.

2020-09-11 14:15:00 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.

2020-09-11 14:15:00 INFO OGG-01360 REPLICAT is running in Remote Task mode.

2020-09-11 14:15:00 INFO OGG-03528 The source database character set, as determined from the table definition file, is UTF-8.
replicat rtinit
sourcedefs ./dirdef/defgen1_oracle.def
targetdb ogg@10.233.27.128:4000 userid ggs password ***
discardfile ./dirrpt/rtinit,purge
map IBXRP_BUCKLE.TBL_SUBMCHT_INFO, target ITOM.TBL_SUBMCHT_INFO01;

2020-09-11 14:15:00 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/home/appl/ogg/product/dirtmp.

Database Version:
MySQL
Server Version: 5.7.25-TiDB-v4.0.2
Client Version: 5.6.14
Host Connection: 10.233.27.128 via TCP/IP
Protocol Version: 10

Database Language and Character Set:
CLIENT CHARACTER SET = “utf8mb4”
SERVER CHARACTER SET = “utf8mb4”
DATABASE CHARACTER SET = “utf8mb4”
LOCALE INFORMATION = “en”
DATABASE COLLATION = “caseinsensitive”


** Run Time Messages **


2020-09-11 14:15:00 WARNING OGG-02761 Source definitions file, ./dirdef/defgen1_oracle.def, is ignored because trail file contains table definitions.

2020-09-11 14:15:00 INFO OGG-06505 MAP resolved (entry IBXRP_BUCKLE.TBL_SUBMCHT_INFO): map “IBXRP_BUCKLE”.“TBL_SUBMCHT_INFO”, target “ITOM”.“TBL_SUBMCHT_INFO01”.

Source Context :
SourceModule : [ggdb.mysql.db]
SourceID : [/scratch/aime/adestore/views/aime_adc4150325/oggcore/OpenSys/src/gglib/ggdbmysql/dbmysql.c]
** Run Time Messages **


2020-09-11 14:15:00 WARNING OGG-02761 Source definitions file, ./dirdef/defgen1_oracle.def, is ignored because trail file contains table definitions.

2020-09-11 14:15:00 INFO OGG-06505 MAP resolved (entry IBXRP_BUCKLE.TBL_SUBMCHT_INFO): map “IBXRP_BUCKLE”.“TBL_SUBMCHT_INFO”, target “ITOM”.“TBL_SUBMCHT_INFO01”.

Source Context :
SourceModule : [ggdb.mysql.db]
SourceID : [/scratch/aime/adestore/views/aime_adc4150325/oggcore/OpenSys/src/gglib/ggdbmysql/dbmysql.c]
SourceMethod : [get_target_col_specs]
SourceLine : [2187]
ThreadBacktrace : [19] elements
: [_ZN3ggs5gglib14MultiThreading6Thread9RunThreadEPNS2_10ThreadArgsE]
: [/home/appl/ogg/product/replicat(ggs::gglib::MultiThreading::thread::RunThread(ggs::gglib::MultiThreading::thread::Thread聽]
: [/home/appl/ogg/product/replicat(ggs::gglib::MultiThreading::thread::RunThread(ggs::gglib::MultiThreading::thread::Thread聽]
: [/home/appl/ogg/product/replicat(ggs::gglib::MultiThreading::thread::RunThread(ggs::gglib::MultiThreading::thread::Thread聽]
: []
: []
: []
: []
: []
: [/home/appl/ogg/product/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x140) [0x7ea460]]
: [/home/appl/ogg/product/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x140) [0x7ea460]]
: [/home/appl/ogg/product/replicat(ggs::gglib::Multq]
: [/home/appl/ogg/product/replicat()]
: [/home/appl/ogg/product/replicat(ggs::gglib::Multq]
: [/home/appl/ogg/product/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x140) [0x7ea460]]
: [/home/appl/ogg/product/replicat(ggs::gglib::Multq]
: [/lib64/libc.so.6(__libc_start_main)]
: [/lib64/libc.so.6(__libc_start_main)]
: [/home/appl/ogg/product/replicat()]

2020-09-11 14:15:00 ERROR OGG-00868 Query to retrieve Octect Length Failed for database=‘ITOM’ AND table=‘TBL_SUBMCHT_INFO01’ AND column=1.

全量抽取进程参数
extract etinit
userid ggs,password ggs
rmthost xxxx mgrport 7809
rmttask replicat,group rtinit
table IBXRP_BUCKLE.TBL_SUBMCHT_INFO;
全量同步进程参数
replicat rtinit
sourcedefs ./dirdef/defgen1_oracle.def
targetdb ogg@xxxxxx:4000 userid ggs password ggs
discardfile ./dirrpt/rtinit,purge
map IBXRP_BUCKLE.TBL_SUBMCHT_INFO, target ITOM.TBL_SUBMCHT_INFO01;

问题参考链接:


解决方案中设置参数
lower-case-table-names = 1
表中也有有主键

表结构如下:

CREATE TABLE ITOM.TBL_SUBMCHT_INFO01
( ID int ,
APP_CODE VARCHAR(32),
CHANNEL_MCHT_NO VARCHAR(32),
PAY_WAY VARCHAR(32),
SUB_MCHT_ID VARCHAR(32),
REMARK VARCHAR(128),
LOCAL_MCHT_NO VARCHAR(32),
CREATE_TIME VARCHAR(14),
UPDATE_TIME VARCHAR(14),
primary key(ID));

是否有同行遇到过以上问题?

同样配置在mysql没有重现,在tidb出现这个问题

麻烦确认一下 lower-case-table-names = 1 是否生效呢?可以通过 tidb 的启动参数日志来看确认一下。

这个参数不是只是骗过goldengate么,tidb我看了文档上面是只能是2,就算修改了也是2吧?改成1其实就是骗过goldengate

参考链接:https://docs.pingcap.com/zh/tidb/v3.1/tidb-configuration-file#lower-case-table-names

[2020/09/10 23:57:10.997 +08:00] [INFO] [printer.go:56] [“loaded config”] [config="{“host”:“0.0.0.0”,“advertise-address”:“tidb-poc-tidb-0.tidb-poc-tidb-peer.tidb.svc”,“port”:4000,“cors”:"",“store”:“tikv”,“path”:“tidb-poc-pd:2379”,“socket”:"",“lease”:“45s”,“run-ddl”:true,“split-table”:true,“token-limit”:4096,“oom-use-tmp-storage”:true,“tmp-storage-path”:"/tmp/0_tidb/MC4wLjAuMDo0MDAwLzAuMC4wLjA6MTAwODA=/tmp-storage",“oom-action”:“log”,“mem-quota-query”:1073741824,“tmp-storage-quota”:-1,“enable-streaming”:false,“enable-batch-dml”:false,“lower-case-table-names”:1,“server-version”:"",“log”:{“level”:“error”,“format”:“text”,“disable-timestamp”:null,“enable-timestamp”:null,“disable-error-stack”:null,“enable-error-stack”:null,“file”:{“filename”:"",“max-size”:300,“max-days”:0,“max-backups”:3},“enable-slow-log”:true,“slow-query-file”:"/var/log/tidb/slowlog",“slow-threshold”:300,“expensive-threshold”:10000,“query-log-max-len”:4096,“record-plan-in-slow-log”:1},“security”:{“skip-grant-table”:false,“ssl-ca”:"",“ssl-cert”:"",“ssl-key”:"",“require-secure-transport”:false,“cluster-ssl-ca”:"",“cluster-ssl-cert”:"",“cluster-ssl-key”:"",“cluster-verify-cn”:null},“status”:{“status-host”:“0.0.0.0”,“metrics-addr”:"",“status-port”:10080,“metrics-interval”:15,“report-status”:true,“record-db-qps”:false},“performance”:{“max-procs”:0,“max-memory”:0,“stats-lease”:“3s”,“stmt-count-limit”:5000,“feedback-probability”:0.05,“query-feedback-limit”:1024,“pseudo-estimate-ratio”:0.8,“force-priority”:“NO_PRIORITY”,“bind-info-lease”:“3s”,“txn-total-size-limit”:104857600,“tcp-keep-alive”:true,“cross-join”:true,“run-auto-analyze”:true,“agg-push-down-join”:false,“committer-concurrency”:16,“max-txn-ttl”:600000},“prepared-plan-cache”:{“enabled”:true,“capacity”:100,“memory-guard-ratio”:0.1},“opentracing”:{“enable”:false,“rpc-metrics”:false,“sampler”:{“type”:“const”,“param”:1,“sampling-server-url”:"",“max-operations”:0,“sampling-refresh-interval”:0},“reporter”:{“queue-size”:0,“buffer-flush-interval”:0,“log-spans”:false,“local-agent-host-port”:""}},“proxy-protocol”:{“networks”:"",“header-timeout”:5},“tikv-client”:{“grpc-connection-count”:4,“grpc-keepalive-time”:10,“grpc-keepalive-timeout”:3,“commit-timeout”:“41s”,“max-batch-size”:128,“overload-threshold”:200,“max-batch-wait-time”:0,“batch-wait-size”:8,“enable-chunk-rpc”:true,“region-cache-ttl”:600,“store-limit”:0,“store-liveness-timeout”:“5s”,“copr-cache”:{“enable”:false,“capacity-mb”:1000,“admission-max-result-mb”:10,“admission-min-process-ms”:5}},“binlog”:{“enable”:false,“ignore-error”:false,“write-timeout”:“15s”,“binlog-socket”:"",“strategy”:“range”},“compatible-kill-query”:false,“plugin”:{“dir”:"",“load”:""},“pessimistic-txn”:{“enable”:true,“max-retry-count”:256},“check-mb4-value-in-utf8”:true,“max-index-length”:3072,“alter-primary-key”:false,“treat-old-version-utf8-as-utf8mb4”:true,“enable-table-lock”:false,“delay-clean-table-lock”:0,“split-region-max-num”:1000,“stmt-summary”:{“enable”:true,“enable-internal-query”:false,“max-stmt-count”:200,“max-sql-length”:4096,“refresh-interval”:1800,“history-size”:24},“repair-mode”:false,“repair-table-list”:[],“isolation-read”:{“engines”:[“tikv”,“tiflash”,“tidb”]},“max-server-connections”:0,“new_collations_enabled_on_first_bootstrap”:false,“experimental”:{“allow-auto-random”:false,“allow-expression-index”:false},“enable-collect-execution-info”:false,“enable-telemetry”:true}"]

“lower-case-table-names”:1, 启动日志是1

是的,目的是为了骗过 OGG 的 check 配置的阶段。现在的问题应该还是和大小写敏感有关系,可以尝试自己新建一个测试表,验证一下是否为 Table name 的大小敏感导致的。

另外我们也有其他的同步案例比如使用 DataX 开源工具,来同步数据。

多谢,sun提供的思路
我知道是怎么回事了
我建表语句是大写表名,但是tidb只能是支持小写,所以我的配置文件里面target table name无论大小写都无法识别

tidb建表语句一定要小写,不能大写才可以识别

:+1: 找到问题就好,看来这个问题其他的小伙伴也可能会看到。感谢你的反馈,如果有其他的问题,麻烦创建新的帖子。