源端ogg 版本，数据库11.2.0.4
Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Apr 16 2018 00:53:30
Operating system character set identified as UTF-8.
目标端版本tidb
Oracle GoldenGate Command Interpreter 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 10:58:16
Operating system character set identified as UTF-8.
目标端日志
2020-07-22T11:18:18.552+0800 INFO OGG-06510 Oracle GoldenGate Delivery for MySQL: Using the following key columns for target table test.hs_o_e: e_no.
2020-07-22T11:18:18.560+0800 WARNING OGG-01004 Oracle GoldenGate Delivery for MySQL: Aborted grouped transaction on test.hs_o_e, Database error 1366 ([SQL error 1366]Incorrect datetime value: ‘’ for column ‘return_date’ at row 1
).
2020-07-22T11:18:18.563+0800 ERROR OGG-02245 Oracle GoldenGate Delivery for MySQL: Repositioning was attempted as REPERROR action, but is not supported by RMTTASK.
2020-07-22T11:18:23.565+0800 ERROR OGG-01668 Oracle GoldenGate Delivery for MySQL: PROCESS ABENDING.
我测试了一下，在源端插入 create table test.t1(id int,namevarchar(10),d datetime);
insert into test.t1(id,name) values(3,‘b’)
源端
SQL> select *From test.t1;
ID NAME D
3 b
目标端
mysql> select *from t1;
±-----±-----±--------------------+
| id | name | d |
±-----±-----±--------------------+
| 3 | b | 0000-00-00 00:00:00 |
±-----±-----±--------------------+
2 rows in set (0.01 sec)
但是在目标端tidb 库直接
insert into t1 (id,name) values(2,‘a’);
mysql> select *from t1;
±-----±-----±--------------------+
| id | name | d |
±-----±-----±--------------------+
| 2 | a | NULL |
| 3 | b | 0000-00-00 00:00:00 |
±-----±-----±--------------------+
2 rows in set (0.01 sec)
当前tidb测试
mysql> select @@sql_mode;
±----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
±----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
SQL> desc lzy.t1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(10)
D DATE
在上游 datetime 字段上插入 null 值
SQL> insert into lzy.t1 values(2,'b',null);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from lzy.t1;
ID NAME D
---------- ---------- ------------
1 a 23-JUL-20
2 b
SQL> select * from lzy.t1 where D is null;
ID NAME D
---------- ---------- ------------
2 b
查看 OGG 同步进程退出
GGSCI (node4235) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REP1 00:00:00 00:02:09
查看 OGG 日志
2020-07-23T22:20:53.176+0800 WARNING OGG-01004 Oracle GoldenGate Delivery for MySQL: Aborted grouped transaction on lzy.t1, Database error 1366 ([SQL error 1366]Incorrect datetime value: '' for column '
d' at row 1
).
2020-07-23T22:20:53.176+0800 ERROR OGG-02245 Oracle GoldenGate Delivery for MySQL: Repositioning was attempted as REPERROR action, but is not supported by RMTTASK.
2020-07-23T22:20:58.183+0800 ERROR OGG-01668 Oracle GoldenGate Delivery for MySQL: PROCESS ABENDING.
下游 TiDB 查询没有数据同步过来
MySQL [lzy]> select * from t1;
+------+------+---------------------+
| id | name | d |
+------+------+---------------------+
| 1 | a | 2020-07-23 23:29:15 |
+------+------+---------------------+
1 row in set (0.00 sec)