Bug on initial_load 3.9.15?

[jmdiazlr]

Good day.

I have a server and clien in a test environment. I use then next command to make a reload of client node ( node id = 003):

./bin/sadmin reload-node --properties ./sync_pruebas/server.properties 003

But data from server->client does not arrive, If I go to server db, I saw next info on batch_outgoing and corresponding data

batch_id	status	node_id	data_id	table_name	event_type	row_data	pk_data
271	OK	003	148	sym_node_security	U	"003","h5AB4IYclnzeSnqAoSmGVvH0nBkiCY","0","2018-11-24 08:42:47.509975","1",,"-1","symadmin","0","2018-11-24 08:42:51.675000","2","registration","vps"	"003"
272	OK	003	149	sym_node	S	"update sym_incoming_batch set status='OK', error_flag=0 where node_id='vps' and status != 'OK'"
273	OK	003	150	ta	R	1=1
274	OK	003	151	ta	R	1=1
275	OK	003	152	ta	R	1=1
276	OK	003	154	sym_node_security	U	"003","h5AB4IYclnzeSnqAoSmGVvH0nBkiCY","0","2018-11-24 08:42:47.509975","0","2018-11-25 09:37:49.939000","4","symadmin","0","2018-11-24 08:42:51.675000","2","registration","vps"	"003"

I do not understand row_data for batch id's 273, 274 and 275, they seem strage for me.

I have to say that I am trying to implement a multitenant replication environment, where on server there is a schema for each client, and on client the corresponding tables are on diferent schema (public). On all nodes I am using postgrsql. This is the third node, and testing initial and reverse loading, on registration both loads where requested, but only reverse load was done correctly, initial load was marked as done in node_security but data did not arrive to client node, so I tried to do it manually as explained, but data did not arrive to client node either.

Inspecting trigger_history_id, I deduced than Symmetricds is scheduling initial load from the three schemas I have, but finally is unable to get data to send because not using schema name on final extract query (this last statement is a though). So the quetions are:

1. Is this a bug ?
2. Or how to invoke and initial load in this context from a special server schema to corresponding node ?
3. Maybe the problem is schema name being diferent on server and client ?

NOTE: sym_trigger for table "ta" have source_shema_name = '*', router server->client3 use schema_name of ta's trigger dml to route to correct node (schema name are same to group_id).

Thanks with any help.

[jmdiazlr]

After last comment, I tried table_reload_request with no sucess:

INSERT INTO sym_table_reload_request (source_node_id, target_node_id, trigger_id, router_id, create_time, last_update_time)
VALUES  ('server', '003', 'trg_ta', 'router_gvps_cliente3', current_timestamp, current_timestamp);

Querying batch and data generated I got almost the same info as previous comment:

batch_id	status	node_id	data_id	table_name	event_type	row_data	pk_data	old_data	trigger_hist_id	channel_id	transaction_id	source_node_id	external_data	node_list
299	OK	003	163	ta	R	1=1			34					003
300	OK	003	164	ta	R	1=1			33					003
301	OK	003	165	ta	R	1=1			32					003

So I conclude this is a bug, because table_reload_request have all information to select correct schema (because routing dml's are done well with this same info).

Any workaround ?

Thanks.

[jmdiazlr]

Actualization:

I activated debugging for sql statements, and found that data extraction for initial load is working properly (but for the three db schemas I have), I extracted the queries and executed externally and they generated resultset for each schema, so the problem is not in data extraction for initial load, but on routing, transporting or receiving.

The queries on the log were:

select case when t."id" is null then '' else '"' || cast(cast(t."id" as numeric) as varchar) || '"' end as x__0,case when t."name" is null then '' else '"' || replace(replace(cast(t."name" as varchar),$$\$$,$$\\$$),'"',$$\"$$) || '"' end as x__1 from "cliente1"."ta" t where 1=1

select case when t."id" is null then '' else '"' || cast(cast(t."id" as numeric) as varchar) || '"' end as x__0,case when t."name" is null then '' else '"' || replace(replace(cast(t."name" as varchar),$$\$$,$$\\$$),'"',$$\"$$) || '"' end as x__1 from "cliente2"."ta" t where 1=1

select case when t."id" is null then '' else '"' || cast(cast(t."id" as numeric) as varchar) || '"' end as x__0,case when t."name" is null then '' else '"' || replace(replace(cast(t."name" as varchar),$$\$$,$$\\$$),'"',$$\"$$) || '"' end as x__1 from "cliente3"."ta" t where 1=1

curiosly, 2 quieres after the third, the logs shows this

update sym_outgoing_batch set status='NE', load_id=6, extract_job_flag=1, load_flag=1, error_flag=0, byte_count=61, extract_count=1, sent_count=0, load_count=0, data_row_count=0, reload_row_count=1, data_insert_row_count=0, data_update_row_count=0, data_delete_row_count=0, other_row_count=0, ignore_count=0, router_millis=0, network_millis=0, filter_millis=0, load_millis=0, extract_millis=10, extract_start_time={ts '2018-11-25 18:11:21.317'}, transfer_start_time=null, load_start_time=null, sql_state=null, sql_code=0, sql_message=null, failed_data_id=0, last_update_hostname='jmdiazlr-Latitude-E6430', last_update_time=current_timestamp, summary='ta', load_row_count=0, load_insert_row_count=0, load_update_row_count=0, load_delete_row_count=0, fallback_insert_count=0, fallback_update_count=0, ignore_row_count=0, missing_delete_count=0, skip_count=0, extract_row_count=0, extract_insert_row_count=0, extract_update_row_count=0, extract_delete_row_count=0, transform_extract_millis=0, transform_load_millis=0 where batch_id=301 and node_id='003'

as you can see, it updates reload_row_count=1 but when I executed then extract query I am sure it produces more than one row.

I am really struggled with this. Maybe misunderstanding something but all works ok when server and client tables are on same schema.

[Mark Michalek]

Hi jmdiazlr, the extracted data will also get put through your router (sym_router) - so it's possible the router is reducing the number of rows. Also it's possible to reduce the number of rows through a transform (sym_trasnform) so also check that.