View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0003418 | SymmetricDS | Bug | public | 2018-02-13 17:39 | 2019-08-26 12:45 |
Reporter | bahoover | Assigned To | |||
Priority | normal | ||||
Status | closed | Resolution | open | ||
Product Version | 3.9.2 | ||||
Summary | 0003418: Syncing to named schema in Postgresql not working using a transform | ||||
Description | Syncing 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 Reproduce | 1. 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 Information | MySQL 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; | ||||
Tags | dialect: postgresql, transformation | ||||
|
|
|
Found some discussion on this here: https://sourceforge.net/p/symmetricds/discussion/739236/thread/ee8859cb/ |
|
Auto closing all issues waiting for feedback after 4 months. We don't have enough information to take any action. |
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 |