用ogg同步oracle 到 tidb，tidb报错 Duplicate entry '471606' for key 'PRIMARY'"

[bennett]

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

• 【TiDB 版本】：4.0.0
• 【问题描述】：使用ogg 初始化同步 oracle到tidb，tidb侧的表时空表，为什么刚开启extract进程，就报错了“Duplicate entry ‘471606’ for key ‘PRIMARY’"”，tidb这边表明明是空表呀？

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

[WangXiangUSTC (王相-PingCAP)]

确定表里面是空的吗，能否手动写入一条主键为471606的数据试一下

[bennett]

这个问题已经解决了，之前没有使用defen定义表结构，导致ogg解析有问题。
ogg 12c以上不需要defen文件了。 但是我源端使用的ogg11.2.0.3版本

[yilong (yi888long)]

感谢反馈

[bennett]

还有个问题，oracle到mysql初始化同步ok了。 但是增量同步时，timestamp类型的数据无法同步，一直报错。 test_time字段为什么会加\u转化，tidb就不认识了

ogg报错：2020-07-13 11:53:48 WARNING OGG-01004 Aborted grouped transaction on pps.gg_receive, Database error 1366 ([SQL error 1366]Incorrect datetime value: '2020-07-10 17:54:00
).

tidb.log 报错信息：
[2020/07/13 11:53:48.273 +08:00] [ERROR] [conn.go:728] [“command dispatched failed”] [conn=89] [connInfo=“id:89, addr:10.123.26.122:50581 status:1, collation:latin1_swedish_ci, user:ggmgr”] [command=Execute] [status=“inTxn:1, autocommit:0”] [sql=“INSERT INTO pps.gg_receive (cardid,tester,SOURCE_DATABASE,TARGET_DATABASE,test_time,TEST_TIMESTAMP) VALUES (?,?,?,?,?,?) [arguments: (2860346, 测试, oracle, tidb, 2020-07-10 17:54:00\u0005\u0000\u001f\u0000\u0000\u00002, 2020-07-10 17:54:00.023515)]”] [txn_mode=PESSIMISTIC] [err="[table:1366]Incorrect datetime value: ‘2020-07-10 17:54:00\u0005\u0000\u001f\u0000\u0000\u00002’ for column ‘test_time’ at row 1\ngithub.com/pingcap/errors.AddStack\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20190809092503-95897b64e011/errors.go:174\ github.com/pingcap/parser/terror.(*Error).GenWithStackByArgs\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20200525110646-f45c2cee1dca/terror/terror.go:243\ngithub.com/pingcap/tidb/executor.(*InsertValues).handleErr\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/executor/insert_common.go:289\ngithub.com/pingcap/tidb/executor.(*InsertValues).fastEvalRow\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/executor/insert_common.go:353\ngithub.com/pingcap/tidb/executor.insertRows\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/executor/insert_common.go:223\ github.com/pingcap/tidb/executor.(*InsertExec).Next\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/executor/insert.go:261\ngithub.com/pingcap/tidb/executor.Next\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/executor/executor.go:248\ github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/executor/adapter.go:507\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handlePessimisticDML\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/executor/adapter.go:526\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/executor/adapter.go:387\ngithub.com/pingcap/tidb/executor.(*ExecStmt).Exec\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/executor/adapter.go:357\ngithub.com/pingcap/tidb/session.runStmt\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/session/tidb.go:276\ github.com/pingcap/tidb/session.(*session).CommonExec\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/session/session.go:1210\ngithub.com/pingcap/tidb/session.(*session).ExecutePreparedStmt\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/session/session.go:1326\ngithub.com/pingcap/tidb/server.(*TiDBStatement).Execute\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/server/driver_tidb.go:76\ngithub.com/pingcap/tidb/server.(*clientConn).handleStmtExecute\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/server/conn_stmt.go:184\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/server/conn.go:912\ngithub.com/pingcap/tidb/server.(*clientConn).Run\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/server/conn.go:713\ngithub.com/pingcap/tidb/server.(*Server).onConn\ \t/home/jenkins/agent/workspace/tidb_v4.0.0/go/src/github.com/pingcap/tidb/server/server.go:415\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357\ INSERT INTO pps.gg_receive (cardid,tester,SOURCE_DATABASE,TARGET_DATABASE,test_time,TEST_TIMESTAMP) VALUES (?,?,?,?,?,?) [arguments: (2860346, 测试, oracle, tidb, 2020-07-10 17:54:00\u0005\u0000\u001f\u0000\u0000\u00002, 2020-07-10 17:54:00.023515)]"]

[yilong (yi888long)]

请问这一列的时间精度要求，能否调整为 ‘YYYY-MM-DD HH24:MI:SS’，在导出时指定到秒试试。

类似：
https://blog.csdn.net/weixin_30802171/article/details/94826271

[bennett]

但是源端oracle 是tiestamp(9), TiDB最大支持timestamp(6)，这样会截断吧，时间就不精确了

[yilong (yi888long)]

到 秒 不够吗？ 如果够的话，应该不影响。