View Issue Details

IDProjectCategoryView StatusLast Update
0003418SymmetricDSBugpublic2019-08-26 12:45
Reporterbahoover Assigned To 
Prioritynormal 
Status closedResolutionopen 
Product Version3.9.2 
Summary0003418: Syncing to named schema in Postgresql not working using a transform
DescriptionSyncing to a Postgresql named schema does not work correctly with a transformed table.

When using the default (public) schema, symmetric behaves as it should. However, when syncing to a named schema, symmetric creates the original table and syncs to it instead of syncing to the transformed table.
Steps To Reproduce1. Start with clean install
   bin/symadmin --engine loggernet-001 uninstall
   bin/symadmin --engine lndb-000 uninstall
   bin/symadmin --engine lndb-000 create-sym-tables

2. create transform table in schema (change schema name depending on test):
   script in Additonal Info

3. Run below script to setup symmetricds

4. Start symmetricds

-----------------------------------------------------------------------
-- Script to setup symmetric (using public schema)
-----------------------------------------------------------------------

-- Clear out tables
delete from sym_transform_column ;
delete from sym_transform_table ;
delete from sym_trigger_router;
delete from sym_trigger;
delete from sym_router;
delete from sym_channel where channel_id in ('mux_tables', 'mts_tables');
delete from sym_node_group_link;
delete from sym_node_group;
delete from sym_node_host;
delete from sym_node_identity;
delete from sym_node_security;
delete from sym_node;

-- Define Nodes and setup security
insert into sym_node (node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version,database_type,database_version,heartbeat_time,timezone_offset,batch_to_send_count,batch_in_error_count,created_at_node_id)
 values ('000','lndb','000',1,null,null,null,null,null,current_timestamp,null,0,0,'000');
 
insert into sym_node (node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version,database_type,database_version,heartbeat_time,timezone_offset,batch_to_send_count,batch_in_error_count,created_at_node_id)
 values ('001','loggernet','001',1,null,null,null,null,null,current_timestamp,null,0,0,'000');

insert into sym_node_security (node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time,created_at_node_id)
 values ('000','f96606b78a54ed89e88d9aa9b48ea4e8',0,current_timestamp,0,current_timestamp,'000');
 
insert into sym_node_security (node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time,created_at_node_id)
 values ('001','f96606b78a54ed89e88d9aa9b48ea4e8',1,null,1,null,'000');

insert into sym_node_identity values ('000');

-- Groups and link
insert into sym_node_group (node_group_id, description) values ('lndb', 'Loggernet (CR1000) source database');
insert into sym_node_group (node_group_id, description) values ('loggernet', 'BioLogic raw data store');

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('lndb', 'loggernet', 'P');

-- Routers
-- default router, send all captured data to target
insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('lndb_2_loggernet', 'lndb', 'loggernet', 'default', current_timestamp, current_timestamp);

insert into sym_router(router_id, source_node_group_id, target_node_group_id, router_type, create_time, last_update_time)
values('lndb_audit', 'lndb', 'audit', 'audit', current_timestamp, current_timestamp);

-- Channels
insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values('mux_tables', 1, 100000, 1, 'Mux tables');

-- Triggers
insert into sym_trigger(trigger_id,source_table_name,channel_id,sync_on_update_condition, last_update_time,create_time)
values('vc1_leveltemp_trigger','VC1_LevelTemp','mux_tables', 'new.TmStamp > ''2008-01-01'' and new.TmStamp <= now()',current_timestamp,current_timestamp);

-- trigger/router
insert into sym_trigger_router(trigger_id,router_id,initial_load_order, last_update_time,create_time)
values('vc1_leveltemp_trigger','lndb_2_loggernet', 100, current_timestamp, current_timestamp);

insert into sym_transform_table(transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name, target_table_name, delete_action, column_policy)
values('extract_vc1_leveltemp_xform', 'lndb', 'loggernet', 'EXTRACT', 'VC1_LevelTemp', 'mux_leveltemp', 'DEL_ROW', 'IMPLIED');

insert into sym_transform_column (transform_id, include_on, target_column_name, pk, transform_type, transform_expression, transform_order, last_update_time,last_update_by, create_time)
values ('extract_vc1_leveltemp_xform', '*', 'site', 1,'const', 'VC1', 1, current_timestamp, 'BAH',current_timestamp);

insert into sym_transform_column (transform_id, include_on, target_column_name, source_column_name, pk,transform_type, transform_expression, transform_order, last_update_time,last_update_by, create_time)
values ('extract_vc1_leveltemp_xform', '*', 'TmStamp', 'TmStamp', 1,'copy', '', 2, current_timestamp, 'BAH',current_timestamp);

insert into sym_transform_column (transform_id, include_on, target_column_name, source_column_name, pk,transform_type, transform_expression, transform_order, last_update_time,last_update_by, create_time)
values ('extract_vc1_leveltemp_xform', '*', 'RecNum', 'RecNum', 1,'copy', '', 3, current_timestamp, 'BAH', current_timestamp);

-----------------------------------------------------------------------
-- For named schema, use above script, except change inserts into sym_router and sym_transform_table
-----------------------------------------------------------------------
insert into sym_router (router_id,source_node_group_id,target_node_group_id,target_schema_name,router_type,create_time,last_update_time)
values('lndb_2_loggernet', 'lndb', 'loggernet', 'loggernet', 'default', current_timestamp, current_timestamp);

insert into sym_transform_table(transform_id, source_node_group_id, target_node_group_id, target_schema_name, transform_point, source_table_name, target_table_name, delete_action, column_policy)
values('extract_vc1_leveltemp_xform', 'lndb', 'loggernet', 'loggernet', 'EXTRACT', 'VC1_LevelTemp', 'mux_leveltemp', 'DEL_ROW', 'IMPLIED');


Additional InformationMySQL 5.6
Postgresql 9.5



-- Create transform table on postgresql
CREATE TABLE loggernet.mux_leveltemp
(
    id SERIAL,
    site character varying(3) COLLATE pg_catalog."default" NOT NULL,
    tmstamp timestamp without time zone NOT NULL,
    recnum bigint NOT NULL,
    teg_status integer,
    water_level real,
    water_temp real,
    air_temp real,
    in_volt real,
    tagcount integer,
    CONSTRAINT mux_leveltemp_pkey PRIMARY KEY (site, tmstamp, recnum)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE loggernet.mux_leveltemp
    OWNER to dmuser;

-- Source table
CREATE TABLE `VC1_LevelTemp` (
  `TmStamp` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `RecNum` bigint(20) NOT NULL DEFAULT '0',
  `TEG_status` int(11) DEFAULT NULL,
  `Water_level` float DEFAULT NULL,
  `Water_temp` float DEFAULT NULL,
  `Air_temp` float DEFAULT NULL,
  `In_Volt` float DEFAULT NULL,
  `TagCount` int(11) DEFAULT NULL,
  PRIMARY KEY (`TmStamp`,`RecNum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Tagsdialect: postgresql, transformation

Activities

bahoover

2018-02-13 17:39

reporter  

lndb-000.properties (3,203 bytes)

elong

2018-02-14 16:50

developer   ~0001126

Found some discussion on this here: https://sourceforge.net/p/symmetricds/discussion/739236/thread/ee8859cb/

admin

2019-08-26 12:45

administrator   ~0001581

Auto closing all issues waiting for feedback after 4 months. We don't have enough information to take any action.

Issue History

Date Modified Username Field Change
2018-02-13 17:39 bahoover New Issue
2018-02-13 17:39 bahoover File Added: lndb-000.properties
2018-02-13 17:39 bahoover File Added: loggernet-001.properties
2018-02-14 16:50 elong Note Added: 0001126
2018-10-03 14:24 hanes Status new => feedback
2019-04-24 17:32 elong Tag Attached: transformation
2019-04-24 17:32 elong Tag Attached: dialect: postgresql
2019-08-26 12:45 admin Note Added: 0001581
2019-08-26 12:45 admin Status feedback => closed