SymmetricDS 总结

[TOC]

基本知识

configure Sync

node

nodes defined in sym_node

trigger

router

trigger router

conflict

相关的classes

symmetrict-io: org.jumpmind.io.data.writer
AbstractDatabaseWriter.java
AbstractDataBaseWrtierConflictResolver.java
AbstractDatabaseWriter.java
DefaultDatabaseWriter.java

transform

常用配置

配置client

创建node_group

创建node_group_link

常见问题

1. 如果需要同步的Table的结构更改, 需要重新启动 SymmServer

2. 如果报JDBC Driver问题, 要保证停掉了所有的Server, 查看是否有Server的进程没有被停掉.

3. fallback using PK_data

问题来源于ee_pawnitems表, 在ddt里的pk是entityid, 而在platform里的pk是 billid + itemnum.
如果platform里也定义成 EntityID, 则会导致重复数据的问题

在insert时出现失败, 就会触发conflict 处理

• conflict detection:
  如果conflict detection 选择 USE_PK_DATA
  如果a row already exists during an insert then conflict has been detected.

• resolve type
  Fallback: if the source operation was an insert, then an update will be attempted.

 Preparing dml: insert into `pawn_plat_all`.`ee_pawnitems` (`ENTITYID`, `BILLID`, `ITEMNUM`, `CATEGORYID`, `ITEMNAME`, `SPECIFICATION`, `QUANTITY`, `EVALUATEPRICE`, `PAWNMONEY`, `CONTINUEID`, `note`, `corporationId`, `sumCharge`, `realPay`, `monthRate`, `interestRate`, `businessStatus`, `linkBill`, `sumInterest`, `stockStatus`, `fineness`, `weight`, `unitPrice`) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
2016-01-21 13:46:40,096 DEBUG [corp-000] [DefaultDatabaseWriter] [http-bio-8580-exec-9] Submitting data [8a22527852364c4f0152623201fd15a5, 00067866201508190001, 1, 4092, 小汽车, 粤AS281W, 1.00, 630000.00, 630000.00, 00067866201511170001, , 67866, 6300, 623700, 0.01, 0, 2, null, null, 0, 0, 0.000, 0.00] with types [12, 12, 4, 4, 12, 12, 3, 3, 3, 12, 12, -5, 3, 3, 3, 3, 4, 12, 3, 4, 4, 3, 3]

// conflict detected
2016-01-21 13:46:40,097 DEBUG [corp-000] [AbstractDatabaseWriterConflictResolver] [http-bio-8580-exec-9] Conflict detected: conflict_ee_pawnitems02 in batch 531124 at line 193 for table pawn_plat_all.ee_pawnitems
2016-01-21 13:46:40,097 DEBUG [corp-000] [AbstractDatabaseWriterConflictResolver] [http-bio-8580-exec-9] Row data: "8a22527852364c4f0152623201fd15a5","00067866201508190001","1","4092","小汽车
","粤AS281W","1.00","630000.00","630000.00","00067866201511170001","","67866","6300","623700","0.01","0","2",,,"0","0","0.000","0.00"

// resolve conflict
2016-01-21 13:46:40,099 DEBUG [corp-000] [DefaultDatabaseWriter] [http-bio-8580-exec-9] Preparing dml: update `pawn_plat_all`.`ee_pawnitems` set `BILLID` = ?, `ITEMNUM` = ?, `CATEGORYID` = ?, `ITEMNAME` = ?, `SPECIFICATION` = ?, `QUANTITY` = ?, `EVALUATEPRICE` = ?, `PAWNMONEY` = ?, `CONTINUEID` = ?, `note` = ?, `corporationId` = ?, `sumCharge` = ?, `realPay` = ?, `monthRate` = ?, `interestRate` = ?, `businessStatus` = ?, `linkBill` = ?, `sumInterest` = ?, `stockStatus` = ?, `fineness` = ?, `weight` = ?, `unitPrice` = ? where `ENTITYID` = ?
2016-01-21 13:46:40,099 DEBUG [corp-000] [DefaultDatabaseWriter] [http-bio-8580-exec-9] Submitting data [00067866201508190001, 1, 4092, 小汽车, 粤AS281W, 1.00, 630000.00, 630000.00, 00067866201511170001, , 67866, 6300, 623700, 0.01, 0, 2, null, null, 0, 0, 0.000, 0.00, 8a22527852364c4f0152623201fd15a5] with types [12, 4, 4, 12, 12, 3, 3, 3, 12, 12, -5, 3, 3, 3, 3, 4, 12, 3, 4, 4, 3, 3, 12]

2016-01-21 13:46:40,102 INFO [corp-000] [DefaultDatabaseWriter] [http-bio-8580-exec-9] Failed to process a insert event in batch 531124.
Failed row data was: "8a22527852364c4f0152623201fd15a5","00067866201508190001","1","4092","小汽车","粤AS281W","1.00","630000.00","630000.00","00067866201511170001","","67866","6300","623700","0.01","0","2",,,"0","0","0.000","0.00"

4. 同步数据传递时遇到的问题

目标:
同步数据从SWB数据库到plat数据库, 再同步到ddt数据库:

swb client => plat_all => ddt db

• 发现的问题: 在从SWB 同步数据到plat_all后, 并不能激发同步数据到ddt.
• 如何处理这个问题, 需要定时触发reload data from plat_all to ddt_db

在crontab 中加入定期激发重新load数据的任务

###### reload area table data from plat_all to ddt_db dairly ############
05 23  * * * /usr/bin/mysql -h rdsnfiaaqnfiaaq.mysql.rds.aliyuncs.com -u pawn_client_user -pSoudang_518 --database pawn_plat_all < /opt/sql_scripts/reload_area.sql

-- reload_area.sql 内容:
INSERT INTO sym_data (table_name, event_type, row_data, pk_data, old_data, trigger_hist_id, channel_id, transaction_id, source_node_id, external_data, create_time)
SELECT  t.source_table_name ,        'R',     '1=1',   NULL,   NULL,  h.trigger_hist_id , t.channel_id , '1',    NULL,    NULL,    CURRENT_TIMESTAMP
  FROM sym_trigger t
 INNER JOIN sym_trigger_router tr  ON  t.trigger_id = tr .trigger_id
 INNER JOIN sym_trigger_hist h   ON   h.trigger_hist_id = (
  SELECT
       MAX(trigger_hist_id)
   FROM
       sym_trigger_hist
   WHERE
       trigger_id= t .trigger_id
 )
WHERE
  channel_id = 'plat_2_ddt' AND  tr.router_id  LIKE  'corp_2_client02_ddt'  AND (t.source_table_name = 'area')
ORDER BY tr.initial_load_order ASC;

5. 如何重新load 数据

如果数据改变没有被捕获, 可能有时需要重新发送或重新同步数据. 这种情况

执行下列SQL 在数据来源库(例如 DDTService DB).

需要替换channel_id, router_id 和 数据来源表名称

INSERT INTO sym_data (table_name, event_type, row_data, pk_data, old_data, trigger_hist_id, channel_id, transaction_id, source_node_id, external_data, create_time)
SELECT  t.source_table_name ,        'R',     '1=1',   NULL,   NULL,  h.trigger_hist_id , t.channel_id , '1',    NULL,    NULL,    CURRENT_TIMESTAMP
  FROM sym_trigger t
  INNER JOIN sym_trigger_router tr  ON  t.trigger_id = tr .trigger_id
  INNER JOIN sym_trigger_hist h   ON   h.trigger_hist_id = (
    SELECT
          MAX(trigger_hist_id)
    FROM
         sym_trigger_hist
    WHERE
         trigger_id= t .trigger_id
   )
WHERE
   channel_id = 'business_ddt' AND  tr.router_id  LIKE  'client02_2_corp_ddt'  AND (t.source_table_name = 'mb_paymentinfo')
ORDER BY tr.initial_load_order ASC;

-- 查找 data_id
select data_id, event_type, table_name, channel_id from sym_data where event_type = 'R' order by create_time desc limit 1;

-- 根据data_id查找执行状态
select o.batch_id, o.status, o.data_event_count, d.data_id, d.table_name, d.event_type from sym_data d 
join sym_data_event de 
on d.data_id = de.data_id  
join sym_outgoing_batch o 
on de.batch_id = o.batch_id 
where d.data_id = 16197111;