View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003422SymmetricDSBugpublic2018-02-14 14:522024-04-10 17:38
Reporteranthony.vitale@sonymusic.com Assigned To 
Priorityhigh 
Status closedResolutionno change required 
Product Version3.9.2 
Summary0003422: Using sym_table_reload_request in 3.9.2 does not work as it did in version 3.7.2
DescriptionHello

We have used Version 3.7.2 successfully for quite some time.

We Just Upgraded to version 3.9.2

In version 3.7.2 I used the sym_table_reload_request table quite alot to re-sync data from source to target when I found some imbalance in a table.

An Example of what I would do in 3.7.2 to as example reload a table called client_object_map which has a trigger_id of gras_trig_client_object_map

I Would Run this in the Source Database.

insert into sym_table_reload_request (target_node_id, source_node_id, trigger_id, router_id, reload_select, reload_delete_stmt, reload_enabled, create_time, last_update_time)
values ('001','000','gras_trig_client_object_map','gras_router',null,null,1,current_timestamp,current_timestamp);

Source_node_id = 000 is my Source DB.
target_node_id = 001 is my Target DB
Trigger_id = gras_trig_client_object_map which is my client_object_map table.
router_id = gras_router (this is an established Router.
reload_enabled = 1 would start the process.

And The Normal Action is I would insert it, and it would immediately begin to process (I would see it in the logs) and the Row within the sym_table_reload_request would be deleted and Life was Great.

Within 3.9.2 I see that the sym_table_reload_request has gone thru some changes.

So I tried this with the following Sql.

insert into sym_table_reload_request
(target_node_id, source_node_id, trigger_id, router_id, create_time, last_update_time)
values ('001','000','gras_trig_client_object_map','gras_router', current_timestamp, current_timestamp);


Yet This Time, The Row Remains within the sym_table_reload_request , and The processed bit immediately Goes to a 1

In The Log I see this within the log (Notate the Unable to resolve messages), And Yes within the Source DB the table client_object_map has the parents client_systems, request_entities and requests)

Yet, All 3 of them are in Sync between Source and Target, NOR have I asked to Reload them.

I See No added activity within the sym_data table except for activity related to the actual sym_table_reload_request itself which is going across on the config channel

And I do not see any activity to the Target DB related to inserts on the missing nor any data.

So I am Perplexed, This Worked Great in 3.7.2, Now I cannot seem to get it to work in 3.9.2.

Is this a Bug, Or an I doing it wrong.

Please let me know what you see related to what I write as Using the table_reload_request facility for a table is very important to me.

[amp-000] - RouterService - Found 1 table reload requests to process.
[amp-000] - DataService - Queueing up a load to node 001
[amp-000] - Database - Unable to resolve foreign keys for table client_object_map because the following dependent tables were not included [client_systemsrequest_entitiesrequests].
[amp-000] - Database - Unable to resolve foreign keys for table client_object_map because the following dependent tables were not included [client_systemsrequest_entitiesrequests].
[amp-000] - Database - Unable to resolve foreign keys for table client_object_map because the following dependent tables were not included [client_systemsrequest_entitiesrequests].
[amp-000] - DataService - Table reload request(s) for load id 4 have been processed.
[amp-000] - PurgeService - Purged all 0 incoming batch for node 001

Steps To ReproducePlease See Description.
Tagsinitial/partial load

Activities

elong

2018-02-14 17:46

developer   ~0001127

Did it create a batch in the sym_outgoing_batch table?

select * from sym_outgoing_batch where load_id = 4

If it did, check the statistics on the batch, like data_row_count.

anthony.vitale@sonymusic.com

2018-02-14 22:12

reporter   ~0001129

Hi

I Checked, And it made me even more confused.

I Actually Tried using other tables (the tables that where within the unable to resolve fk messages which have no parent tables and still I see nothing occuring.

From the sym_outgoing_batch table where the load_id = the load_id of the reload table request All I see is within the text file which has the Sql and the results.

Within this you see that the tables have data (at least more then 1 row) and the trigger_id's are accurate.



Again what baffles me is that if in 3.7.2 (which I still run in other Replications) I ran this

 insert into sym_table_reload_request (target_node_id, source_node_id, trigger_id, router_id, reload_select, reload_delete_stmt, reload_enabled, create_time, last_update_time)
 values ('001','000','gras_trig_client_object_map','gras_router',null,null,1,current_timestamp,current_timestamp);

It works Great

But in 3.9.2 when I run this.

  insert into sym_table_reload_request
  (target_node_id, source_node_id, trigger_id, router_id, create_time, last_update_time)
  values ('001','000','gras_trig_client_object_map','gras_router', current_timestamp, current_timestamp);

I Get nothing.

I Hope All that is happening is that I just have to put the sym_table_reload_request in Differently Unless there is some configuration messed up.

Maybe It is in the way the reload channel is configured as I am pretty sure in 3.7.2 it would reload using the gras_channel like everything we run.
SymDS_Query_Sym_tables_for_reload_request.txt (19,686 bytes)    
aoma=# select count(*) from aoma.amp.client_object_map;
 count
--------
 129944
(1 row)

aoma=# select count(*) from aoma.amp.request_entities;
 count
-------
    47
(1 row)

aoma=# select count(*) from aoma.amp.requests;
 count
-------
 11627
(1 row)

aoma=# select count(*) from aoma.amp.client_systems;
 count
-------
    11
(1 row)

aoma=# select * from aoma.amp.sym_table_reload_request order by last_update_time;

 target_node_id | source_node_id |         trigger_id          |  router_id  | reload_select |        reload_time        |        create_time        | last_update_by |    last_update_time    | create_table | delete_first | before_custom_sql | load_id | processed |  channel_id
----------------+----------------+-----------------------------+-------------+---------------+---------------------------+---------------------------+----------------+------------------------+--------------+--------------+-------------------+---------+-----------+--------------
 001            | 000            | gras_trig_client_object_map | gras_router |               | 13-FEB-18 23:30:19.214176 | 13-FEB-18 23:00:21.08906  |                | 13-FEB-18 23:00:22.286 |            0 |            0 |                   |       5 |         1 | gras_channel
 001            | 000            | gras_trig_request_entities  | gras_router |               | 13-FEB-18 23:30:19.214176 | 13-FEB-18 23:28:13.35091  |                | 13-FEB-18 23:28:13.935 |            0 |            0 |                   |       6 |         1 | gras_channel
 001            | 000            | gras_trig_requests          | gras_router |               | 13-FEB-18 23:30:19.214176 | 13-FEB-18 23:28:13.352735 |                | 13-FEB-18 23:28:13.977 |            0 |            0 |                   |       7 |         1 | gras_channel
 001            | 000            | gras_trig_client_systems    | gras_router |               | 13-FEB-18 23:30:19.214176 | 13-FEB-18 23:28:13.34328  |                | 13-FEB-18 23:28:14.012 |            0 |            0 |                   |       8 |         1 | gras_channel
 001            | 000            | gras_trig_client_object_map | gras_router |               | 13-FEB-18 23:30:19.214176 | 13-FEB-18 23:28:13.354527 |                | 13-FEB-18 23:28:14.023 |            0 |            0 |                   |       9 |         1 | gras_channel
(5 rows)



aoma=# select * from aoma.amp.sym_outgoing_batch where load_id in (select load_id from aoma.amp.sym_table_reload_request) ;

 batch_id | node_id | channel_id | status | error_flag | sql_state | sql_code | sql_message |         last_update_hostname         |     last_update_time      |        create_time        |      summary      | ignore_count | byte_count | load_flag | extract_count | sent_count | load_count | reload_row_count | other_row_count | data_row_count | extract_row_count | load_row_count | data_insert_row_count | data_update_row_count | data_delete_row_count | extract_insert_row_count | extract_update_row_count | extract_delete_row_count | load_insert_row_count | load_update_row_count | load_delete_row_count | network_millis | filter_millis | load_millis | router_millis | extract_millis | transform_extract_millis | transform_load_millis | load_id | common_flag | fallback_insert_count | fallback_update_count | ignore_row_count | missing_delete_count | skip_count | total_extract_millis | total_load_millis | extract_job_flag |   extract_start_time   |  transfer_start_time   |    load_start_time     | failed_data_id | failed_line_number | create_by
----------+---------+------------+--------+------------+-----------+----------+-------------+--------------------------------------+---------------------------+---------------------------+-------------------+--------------+------------+-----------+---------------+------------+------------+------------------+-----------------+----------------+-------------------+----------------+-----------------------+-----------------------+-----------------------+--------------------------+--------------------------+--------------------------+-----------------------+-----------------------+-----------------------+----------------+---------------+-------------+---------------+----------------+--------------------------+-----------------------+---------+-------------+-----------------------+-----------------------+------------------+----------------------+------------+----------------------+-------------------+------------------+------------------------+------------------------+------------------------+----------------+--------------------+-----------
    13702 | 001     | reload     | OK     |          0 |           |        0 |             | degtlun5843.server.arvato-systems.de | 13-FEB-18 23:00:25.150748 | 13-FEB-18 23:00:22.28216  | sym_node_security |            0 |        484 |         1 |             1 |          1 |          1 |                0 |               0 |              1 |                 1 |              1 |                     0 |                     1 |                     0 |                        0 |                        1 |                        0 |                     0 |                     1 |                     0 |              1 |             0 |           3 |             0 |              5 |                        0 |                     0 |       5 |           0 |                     0 |                     0 |                0 |                    0 |          0 |                    0 |                 0 |                0 | 13-FEB-18 23:00:25.105 | 13-FEB-18 23:00:25.111 | 13-FEB-18 23:00:25.123 |              0 |                  0 |
    13703 | 001     | reload     | OK     |          0 |           |        0 |             | degtlun5843.server.arvato-systems.de | 13-FEB-18 23:00:25.203313 | 13-FEB-18 23:00:22.285696 | sym_node_security |            0 |        484 |         1 |             1 |          1 |          1 |                0 |               0 |              1 |                 1 |              1 |                     0 |                     1 |                     0 |                        0 |                        1 |                        0 |                     0 |                     1 |                     0 |              0 |             1 |           2 |             0 |              4 |                        0 |                     0 |       5 |           0 |                     0 |                     0 |                0 |                    0 |          0 |                    0 |                 0 |                0 | 13-FEB-18 23:00:25.169 | 13-FEB-18 23:00:25.173 | 13-FEB-18 23:00:25.181 |              0 |                  0 |
    13713 | 001     | reload     | OK     |          0 |           |        0 |             | degtlun5843.server.arvato-systems.de | 13-FEB-18 23:28:15.39967  | 13-FEB-18 23:28:13.930891 | sym_node_security |            0 |        484 |         1 |             1 |          1 |          1 |                0 |               0 |              1 |                 1 |              1 |                     0 |                     1 |                     0 |                        0 |                        1 |                        0 |                     0 |                     1 |                     0 |              0 |             3 |          10 |             0 |              8 |                        0 |                     0 |       6 |           0 |                     0 |                     0 |                0 |                    0 |          0 |                    0 |                 0 |                0 | 13-FEB-18 23:28:15.346 | 13-FEB-18 23:28:15.354 | 13-FEB-18 23:28:15.37  |              0 |                  0 |
    13714 | 001     | reload     | OK     |          0 |           |        0 |             | degtlun5843.server.arvato-systems.de | 13-FEB-18 23:28:15.455605 | 13-FEB-18 23:28:13.935247 | sym_node_security |            0 |        484 |         1 |             1 |          1 |          1 |                0 |               0 |              1 |                 1 |              1 |                     0 |                     1 |                     0 |                        0 |                        1 |                        0 |                     0 |                     1 |                     0 |              0 |             0 |           3 |             0 |              4 |                        0 |                     0 |       6 |           0 |                     0 |                     0 |                0 |                    0 |          0 |                    0 |                 0 |                0 | 13-FEB-18 23:28:15.41  | 13-FEB-18 23:28:15.414 | 13-FEB-18 23:28:15.439 |              0 |                  0 |
    13715 | 001     | reload     | OK     |          0 |           |        0 |             | degtlun5843.server.arvato-systems.de | 13-FEB-18 23:28:15.496813 | 13-FEB-18 23:28:13.973486 | sym_node_security |            0 |        484 |         1 |             1 |          1 |          1 |                0 |               0 |              1 |                 1 |              1 |                     0 |                     1 |                     0 |                        0 |                        1 |                        0 |                     0 |                     1 |                     0 |              0 |             0 |           4 |             0 |              3 |                        0 |                     0 |       7 |           0 |                     0 |                     0 |                0 |                    0 |          0 |                    0 |                 0 |                0 | 13-FEB-18 23:28:15.466 | 13-FEB-18 23:28:15.469 | 13-FEB-18 23:28:15.478 |              0 |                  0 |
    13716 | 001     | reload     | OK     |          0 |           |        0 |             | degtlun5843.server.arvato-systems.de | 13-FEB-18 23:28:15.535395 | 13-FEB-18 23:28:13.976653 | sym_node_security |            0 |        484 |         1 |             1 |          1 |          1 |                0 |               0 |              1 |                 1 |              1 |                     0 |                     1 |                     0 |                        0 |                        1 |                        0 |                     0 |                     1 |                     0 |              0 |             0 |           3 |             0 |              3 |                        0 |                     0 |       7 |           0 |                     0 |                     0 |                0 |                    0 |          0 |                    0 |                 0 |                0 | 13-FEB-18 23:28:15.506 | 13-FEB-18 23:28:15.51  | 13-FEB-18 23:28:15.519 |              0 |                  0 |
    13717 | 001     | reload     | OK     |          0 |           |        0 |             | degtlun5843.server.arvato-systems.de | 13-FEB-18 23:28:15.575468 | 13-FEB-18 23:28:14.008192 | sym_node_security |            0 |        484 |         1 |             1 |          1 |          1 |                0 |               0 |              1 |                 1 |              1 |                     0 |                     1 |                     0 |                        0 |                        1 |                        0 |                     0 |                     1 |                     0 |              0 |             0 |           2 |             0 |              3 |                        0 |                     0 |       8 |           0 |                     0 |                     0 |                0 |                    0 |          0 |                    0 |                 0 |                0 | 13-FEB-18 23:28:15.546 | 13-FEB-18 23:28:15.549 | 13-FEB-18 23:28:15.558 |              0 |                  0 |
    13718 | 001     | reload     | OK     |          0 |           |        0 |             | degtlun5843.server.arvato-systems.de | 13-FEB-18 23:28:15.613084 | 13-FEB-18 23:28:14.011799 | sym_node_security |            0 |        484 |         1 |             1 |          1 |          1 |                0 |               0 |              1 |                 1 |              1 |                     0 |                     1 |                     0 |                        0 |                        1 |                        0 |                     0 |                     1 |                     0 |              0 |             0 |           3 |             0 |              2 |                        0 |                     0 |       8 |           0 |                     0 |                     0 |                0 |                    0 |          0 |                    0 |                 0 |                0 | 13-FEB-18 23:28:15.584 | 13-FEB-18 23:28:15.586 | 13-FEB-18 23:28:15.593 |              0 |                  0 |
    13719 | 001     | reload     | OK     |          0 |           |        0 |             | degtlun5843.server.arvato-systems.de | 13-FEB-18 23:28:15.654577 | 13-FEB-18 23:28:14.020722 | sym_node_security |            0 |        484 |         1 |             1 |          1 |          1 |                0 |               0 |              1 |                 1 |              1 |                     0 |                     1 |                     0 |                        0 |                        1 |                        0 |                     0 |                     1 |                     0 |              0 |             0 |           3 |             0 |              2 |                        0 |                     0 |       9 |           0 |                     0 |                     0 |                0 |                    0 |          0 |                    0 |                 0 |                0 | 13-FEB-18 23:28:15.625 | 13-FEB-18 23:28:15.627 | 13-FEB-18 23:28:15.637 |              0 |                  0 |
    13720 | 001     | reload     | OK     |          0 |           |        0 |             | degtlun5843.server.arvato-systems.de | 13-FEB-18 23:28:15.695883 | 13-FEB-18 23:28:14.023122 | sym_node_security |            0 |        484 |         1 |             1 |          1 |          1 |                0 |               0 |              1 |                 1 |              1 |                     0 |                     1 |                     0 |                        0 |                        1 |                        0 |                     0 |                     1 |                     0 |              0 |             0 |           3 |             0 |              3 |                        0 |                     0 |       9 |           0 |                     0 |                     0 |                0 |                    0 |          0 |                    0 |                 0 |                0 | 13-FEB-18 23:28:15.665 | 13-FEB-18 23:28:15.668 | 13-FEB-18 23:28:15.677 |              0 |                  0 |

(10 rows)


aoma=# select * from aoma.amp.sym_channel order by 1;

   channel_id    | processing_order | max_batch_size | max_batch_to_send | max_data_to_route | extract_period_millis | enabled | use_old_data_to_route | use_row_data_to_route | use_pk_data_to_route | reload_flag | file_sync_flag | contains_big_lob | batch_algorithm  | data_loader_type |   description    |        create_time        | last_update_by |     last_update_time      |  queue  | max_network_kbps | data_event_action
-----------------+------------------+----------------+-------------------+-------------------+-----------------------+---------+-----------------------+-----------------------+----------------------+-------------+----------------+------------------+------------------+------------------+------------------+---------------------------+----------------+---------------------------+---------+------------------+-------------------
 config          |                0 |           2000 |               100 |             10000 |                     0 |       1 |                     1 |                     1 |                    1 |           0 |              0 |                1 | default          | default          |                  | 05-FEB-18 17:20:55.04     |                | 05-FEB-18 17:20:55.032    | default |            0.000 |
 default         |            99999 |           1000 |               100 |             10000 |                     0 |       1 |                     1 |                     1 |                    1 |           0 |              0 |                0 | default          | default          |                  | 05-FEB-18 17:20:55.047    |                | 05-FEB-18 17:20:55.046    | default |            0.000 |
 dynamic         |            99999 |           1000 |               100 |             10000 |                     0 |       1 |                     1 |                     1 |                    1 |           0 |              0 |                0 | default          | default          |                  | 05-FEB-18 17:20:55.049    |                | 05-FEB-18 17:20:55.047    | default |            0.000 |
 filesync        |                3 |            100 |               100 |             10000 |                     0 |       1 |                     1 |                     1 |                    1 |           0 |              1 |                0 | nontransactional | default          |                  | 05-FEB-18 17:20:55.05     |                | 05-FEB-18 17:20:55.049    | default |            0.000 |
 filesync_reload |                1 |            100 |               100 |             10000 |                     0 |       1 |                     1 |                     1 |                    1 |           1 |              1 |                0 | nontransactional | default          |                  | 05-FEB-18 17:20:55.051    |                | 05-FEB-18 17:20:55.051    | default |            0.000 |
 gras_channel    |                1 |         100000 |                60 |            100000 |                     0 |       1 |                     1 |                     1 |                    1 |           0 |              0 |                0 | transactional    | default          | AMP GRAS Channel | 05-FEB-18 17:25:06.434179 | svangal        | 05-FEB-18 17:25:06.434179 | default |            0.000 |
 monitor         |                2 |            100 |               100 |             10000 |                     0 |       1 |                     1 |                     1 |                    1 |           0 |              0 |                1 | default          | default          |                  | 12-FEB-18 12:41:06.32     |                | 12-FEB-18 12:41:06.316    | default |            0.000 |
 heartbeat       |                2 |            100 |               100 |             10000 |                     0 |       1 |                     1 |                     1 |                    1 |           0 |              0 |                0 | default          | default          |                  | 05-FEB-18 17:20:55.044    |                | 05-FEB-18 17:20:55.043    | default |            0.000 |
 reload          |                1 |              1 |                 1 |             10000 |                     0 |       1 |                     1 |                     1 |                    1 |           1 |              0 |                0 | default          | default          |                  | 05-FEB-18 17:20:55.042    |                | 05-FEB-18 17:20:55.042    | default |            0.000 |

(9 rows)
SymDS_Query_Sym_tables_for_reload_request.txt (19,686 bytes)   

elong

2018-02-21 17:23

developer   ~0001130

Can you run this query?

select t.trigger_id, r.router_id, l.source_node_group_id, l.target_node_group_id, l.data_event_action
from sym_trigger t
inner join sym_trigger_router tr on tr.trigger_id = t.trigger_id
inner join sym_router r on r.router_id = tr.router_id
inner join sym_node_group_link l on l.source_node_group_id = r.source_node_group_id
and l.target_node_group_id = r.target_node_group_id
where t.trigger_id = 'gras_trig_requests' and r.router_id = 'gras_router'

Does it return the source and target node groups for 000 and 001?

anthony.vitale@sonymusic.com

2018-02-21 18:11

reporter   ~0001132

Hi

I Slightly Changed your sql to include the actual node_id's

But YES it appears correct.

And Just to be clear

1) The Normal Replication via the triggers is working 100% in 3.9.2 for all of the tables.
2) When this was 3.7.2 the sym_table_reload_request worked 100% when I placed the request

Anyway, Here is my modified sql from your sql with the results.

\x on
Expanded display is on.

# select t.trigger_id, r.router_id, l.source_node_group_id, node_source.node_id source_node_id, l.target_node_group_id, node_target.node_id target_node_id, l.data_event_action
# from sym_trigger t
# inner join sym_trigger_router tr on tr.trigger_id = t.trigger_id
# inner join sym_router r on r.router_id = tr.router_id
# inner join sym_node_group_link l on l.source_node_group_id = r.source_node_group_id
# and l.target_node_group_id = r.target_node_group_id
# inner join sym_node node_source on node_source.node_group_id = l.source_node_group_id
# inner join sym_node node_target on node_target.node_group_id = l.target_node_group_id
# where t.trigger_id = 'gras_trig_requests' and r.router_id = 'gras_router';

-[ RECORD 1 ]--------+-------------------
trigger_id | gras_trig_requests
router_id | gras_router
source_node_group_id | amp
source_node_id | 000
target_node_group_id | gras
target_node_id | 001
data_event_action | W

Thanks
Tony V

jvanmeter

2024-04-10 17:38

developer   ~0002427

Version is End of Life

Issue History

Date Modified Username Field Change
2018-02-14 14:52 anthony.vitale@sonymusic.com New Issue
2018-02-14 14:52 anthony.vitale@sonymusic.com Tag Attached: table_reload_request
2018-02-14 17:46 elong Note Added: 0001127
2018-02-14 22:12 anthony.vitale@sonymusic.com File Added: SymDS_Query_Sym_tables_for_reload_request.txt
2018-02-14 22:12 anthony.vitale@sonymusic.com Note Added: 0001129
2018-02-21 17:23 elong Note Added: 0001130
2018-02-21 18:11 anthony.vitale@sonymusic.com Note Added: 0001132
2018-10-03 18:47 hanes Assigned To => hanes
2018-10-03 18:47 hanes Status new => assigned
2019-04-12 16:33 admin Tag Renamed table_reload_request => initial/partial load
2019-05-14 12:56 elong Category Task => Bug
2019-09-18 13:39 hanes Assigned To hanes =>
2024-04-10 17:38 jvanmeter Status assigned => closed
2024-04-10 17:38 jvanmeter Resolution open => no change required
2024-04-10 17:38 jvanmeter Note Added: 0002427