OGG复制MYSQL到ORACLE时空字符串问题

徐靖

发布于 2022-09-22 11:45:40

6070

发布于 2022-09-22 11:45:40

文章被收录于专栏：DB说

【背景】

使用OGG同步MYSQL数据到ORACLE,如果表没有主键，那么此时使用所有列作为条件来更新或者删除数据，MYSQL里面存在空字符串,同步到ORACLE数据库时转换成空(NULL)，注意这个不是在OGG层转换，而是依赖数据库的转换,那么此时更新或者删除会遇到非常经典错误1403(找不到数据)，注意触发场景是表没有主键且更新或者删除时存在空字符串。以下是模拟过程以及几种方案。

【表结构】

show create table xiaoxu\G
*************************** 1. row ***************************
       Table: xiaoxu
Create Table: CREATE TABLE `xiaoxu` (
  `RK` varchar(30) NOT NULL ,
  `QTY` decimal(9,0) DEFAULT NULL,
  `MEMO` varchar(1000) DEFAULT NULL,
  `EXT1` varchar(255) DEFAULT NULL,
  `EXT2` varchar(255) DEFAULT NULL,
  `ORDERTIME` datetime DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

【MYSQL插入构造数据】

INSERT INTO `xiaoxu` values ('20095527III20211230',-1000,
'20210104setup','',NULL,now());
Query OK, 1 row affected (0.00 sec)

select * from xiaoxu;
+---------------------+-------+---------------+------+------+---------------------+
| RK                  | QTY   | MEMO          | EXT1 | EXT2 | ORDERTIME           |
+---------------------+-------+---------------+------+------+---------------------+
| 20095527III20211230 | -1000 | 20210104setup |      | NULL | 2022-01-04 20:02:11 |
+---------------------+-------+---------------+------+------+---------------------+
1 row in set (0.00 sec)

【ORACLE OGG插入数据】

备注:通过打印SQL能够直观观察

1、参数配置很简单:

replicat repm2ox
setenv ( NLS_LANG ="AMERICAN_AMERICA.AL32UTF8")
setenv (ORACLE_HOME="/data/app/oracle/product/11.2.0/dbhome_1")
USERID ogg, PASSWORD ***
dboptions suppresstriggers
DBOPTIONS DEFERREFCONST
NOBINARYCHARS
NODYNSQL
SHOWSYNTAX
map ytmat.xiaoxu, target ytmat.xiaoxu;

2、[os层来启动否则会报错]

[oracle@ localhost goldengate]$ ./replicat paramfile ./dirprm/repm2ox.prm


INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "YTMAT"."XIAOXU" 
("RK","QTY","MEMO","EXT1","EXT2","ORDERTIME") VALUES 
('20095527III20211230','-1000','20210104setup','',
NULL,TO_DATE('2022-01-04 20:02:11','YYYY-MM-DD HH24:MI:SS'))
Statement length: 221

(S)top display, (K)eep displaying (default):
这里回车即可

3、验证数据

备注:同步oracle后，mysql里面空字符串与NULL都变成空.

查询空字符串是没有结果返回

【MYSQL模拟数据】

 update xiaoxu set QTY='10000' where RK='20095527III20211230';
Rows matched: 1  Changed: 1  Warnings: 0


 select * from xiaoxu;
+---------------------+-------+---------------+------+------+---------------------+
| RK                  | QTY   | MEMO          | EXT1 | EXT2 | ORDERTIME           |
+---------------------+-------+---------------+------+------+---------------------+
| 20095527III20211230 | 10000 | 20210104setup |      | NULL | 2022-01-04 20:02:11 |
+---------------------+-------+---------------+------+------+---------------------+
1 row in set (0.00 sec)

【ORACLE OGG更新数据】

备注:通过打印SQL能够直观观察,到这里OGG是无法正常继续下去了,提示经典错误1403.找不到数据。

UPDATE /*+  RESTRICT_ALL_REF_CONS  */ "YTMAT"."XIAOXU" x SET 
x."RK" = '20095527III20211230',x."QTY" = '10000',
x."MEMO" = '20210104setup',x."EXT1" = '',
x."EXT2" = NULL,x."ORDERTIME" = 
TO_DATE('2022-01-04 20:02:11','YYYY-MM-DD HH24:MI:SS') 
WHERE x."RK"='20095527III20211230' AND 
x."QTY"='-1000' AND x."MEMO"='20210104setup' 
AND x."EXT1"='' AND x."EXT2" IS NULL AND 
x."ORDERTIME"=TO_DATE('2022-01-04 20:02:11','YYYY-MM-DD HH24:MI:SS')
 AND ROWNUM = 1
Statement length: 446
(S)top display, (K)eep displaying (default):

从这里可以清晰看到OGG更新还是使用空字符串去匹配数据,显然无法匹配的.直接1403
错误

2022-01-04 20:15:03  WARNING OGG-01004  Aborted grouped 
transaction on YTMAT.XIAOXU, Database error 1403 
(OCI Error ORA-01403: no data found, SQL <UPDATE /*+  
RESTRICT_ALL_REF_CONS  */ "YTMAT"."XIAOXU" x SET x."RK" = :a6,
x."QTY" = :a7,x."MEMO" = :a8,x."EXT1" = :a9,x."EXT2" = :a10,
x."ORDERTIME" = :a11 WHERE x."RK" = :b0 AND x."QTY" = :b1 AND 
x."MEMO" = :b2 AND x."EXT1" = :b3 AND x."EXT2" is NULL AND 
x."ORDERTIME" = :b5 AND ROWNUM = 1>).

【针对以上解决方案】

1、使用handlecollisions来处理异常，此时会造成重复数据，会把更新转换成插入，造成数据异常，这个方案不适合

2、如果能够几个字段来确定唯一值,使用keycols(aa,bb),如果不涉及到空字符串，此时通过创建唯一索引来解决这个问题.--快速解决方案

3、如果无法通过几个字段来确定唯一值,此时需要在mysql端禁止使用空字符串来规避这个问题.因为涉及异构数据转换问题，所以我们需要前期针对不同数据库处理逻辑的不同来规范研发操作.否则同步数据与数据验证是比较麻烦的事情.--从根本解决方案

4、如果OGG层能够支持转换空字符串转换成NULL，目前这个参数我没有找到，如果大家知道有参数能够进行转换或者其他方案，欢迎交流.

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-01-04，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法