symmetricds: sym_data table in master node is filled but sym_data_event is empty

Question

We have a problem, sym_data table in master node is filled with data but sym_data_event is empty and sym_outgoing_batch is empty too. No any error in log file, symmetricds version is 3.4.2. One day ago all works fine and symmetricds config files not changed.

postgresql log:

23643 1 2016-10-18 17:20:38 MSK [unknown] [unknown]  [unknown] 00000LOG:  connection received: host=192.168.0.111 port=36888
23643 2 2016-10-18 17:20:38 MSK century symmetricds 192.168.0.111 [unknown] 00000LOG:  connection authorized: user=symmetricds database=century
23643 3 2016-10-18 17:20:38 MSK century symmetricds 192.168.0.111 [unknown] 42P01ERROR:  relation "gp_id" does not exist at character 20
23643 4 2016-10-18 17:20:38 MSK century symmetricds 192.168.0.111 [unknown] 42P01STATEMENT:  select gpname from gp_id
23643 5 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 6 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(ROUTE) already exists.
23643 7 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 8 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 9 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(PULL) already exists.
23643 10 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 11 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 12 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(PUSH) already exists.
23643 13 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 14 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 15 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(HEARTBEAT) already exists.
23643 16 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 17 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 18 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(PURGE_INCOMING) already exists.
23643 19 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 20 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 21 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(PURGE_OUTGOING) already exists.
23643 22 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 23 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 24 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(PURGE_STATISTICS) already exists.
23643 25 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 26 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 27 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(SYNCTRIGGERS) already exists.
23643 28 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 29 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"ф
23643 30 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(PURGE_DATA_GAPS) already exists.
23643 31 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 32 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 33 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(STAGE_MANAGEMENT) already exists.
23643 34 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 35 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 36 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(WATCHDOG) already exists.
23643 37 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 38 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 39 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(STATISTICS) already exists.
23643 40 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 41 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 42 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(FILE_SYNC_PULL) already exists.
23643 43 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 44 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 45 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(FILE_SYNC_PUSH) already exists.
23643 46 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 47 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 48 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(FILE_SYNC_TRACKER) already exists.
23643 49 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 50 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_lock_PK"
23643 51 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (lock_action)=(INITIAL_LOAD_EXTRACT) already exists.
23643 52 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_lock (lock_action) values($1)
23643 53 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_sequence_PK"
23643 54 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (sequence_name)=(outgoing_batch) already exists.
23643 55 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_sequence (sequence_name, current_value, increment_by, min_value, max_value, cycle, create_time, last_update_by, last_update_time) values($1,$2,$3,$4,$5,$6,current_timestamp,$7,current_timestamp)
23643 56 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505ERROR:  duplicate key value violates unique constraint "sym_sequence_PK"
23643 57 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505DETAIL:  Key (sequence_name)=(outgoing_batch_load_id) already exists.
23643 58 2016-10-18 17:20:43 MSK century symmetricds 192.168.0.111 [unknown] 23505STATEMENT:  insert into sym_sequence (sequence_name, current_value, increment_by, min_value, max_value, cycle, create_time, last_update_by, last_update_time) values($1,$2,$3,$4,$5,$6,current_timestamp,$7,current_timestamp)

score 4 · Answer 1 · answered Oct 19 '16 at 19:43

The rows in sym_data_event are generated by the SymmetricDS engine when it runs the "routing" job. It looks for new rows at the end of the sym_data table and assigns them to batches. Make sure routing is running, which you can verify in the log or the sym_lock table.

The routing uses the sym_data_gap table as a pointer into sym_data. Try stopping SymmetricDS, delete all rows from sym_data_gap, and then start it again. That will cause it go through all of sym_data and determine what data has not routed yet, and then rebuild the correct sym_data_gap.

By the way, the errors from the postgresql log are probably normal and happening during the startup of SymmetricDS, when it tries to insert rows into that table to ensure it's populated.

That reply saved my day, thanks a lot. The procedure described above worked as a charm :) — Pedro Trindade, Apr 24 '17 at 12:25

score 1 · Answer 2 · answered Oct 18 '16 at 19:14

It seems that symmetricds the engine is not working. sym_data table is populated by database triggers, symmetricds engine is not required to run. But to populate data event and outgoing batch tables symmetricds engine has to be running and routing extracted data

symmetricds: sym_data table in master node is filled but sym_data_event is empty

2 Answers2