SymmetricDS 總結(jié)
[TOC]
基本知識
configure Sync
node
nodes defined in sym_node
trigger
router
trigger router
conflict
相關(guān)的classes
symmetrict-io: org.jumpmind.io.data.writer
AbstractDatabaseWriter.java
AbstractDataBaseWrtierConflictResolver.java
AbstractDatabaseWriter.java
DefaultDatabaseWriter.java
transform
常用配置
配置client
創(chuàng)建node_group
創(chuàng)建node_group_link
常見問題
1. 如果需要同步的Table的結(jié)構(gòu)更改, 需要重新啟動 SymmServer
2. 如果報JDBC Driver問題, 要保證停掉了所有的Server, 查看是否有Server的進程沒有被停掉.
3. fallback using PK_data
問題來源于ee_pawnitems表, 在ddt里的pk是entityid, 而在platform里的pk是 billid + itemnum.
如果platform里也定義成 EntityID, 則會導致重復數(shù)據(jù)的問題
在insert時出現(xiàn)失敗, 就會觸發(fā)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. 同步數(shù)據(jù)傳遞時遇到的問題
目標:
同步數(shù)據(jù)從SWB數(shù)據(jù)庫到plat數(shù)據(jù)庫, 再同步到ddt數(shù)據(jù)庫:
swb client => plat_all => ddt db
- 發(fā)現(xiàn)的問題: 在從SWB 同步數(shù)據(jù)到plat_all后, 并不能激發(fā)同步數(shù)據(jù)到ddt.
- 如何處理這個問題, 需要定時觸發(fā)reload data from plat_all to ddt_db
在crontab 中加入定期激發(fā)重新load數(shù)據(jù)的任務(wù)
###### 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 內(nèi)容:
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 數(shù)據(jù)
如果數(shù)據(jù)改變沒有被捕獲, 可能有時需要重新發(fā)送或重新同步數(shù)據(jù). 這種情況
執(zhí)行下列SQL 在數(shù)據(jù)來源庫(例如 DDTService DB).
需要替換channel_id, router_id 和 數(shù)據(jù)來源表名稱
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;
-- 根據(jù)data_id查找執(zhí)行狀態(tài)
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;
