View Issue Details

IDProjectCategoryView StatusLast Update
0002879SymmetricDSBugpublic2018-05-08 20:08
Reportergatnick Assigned Tommichalek  
Priorityurgent 
Status closedResolutionopen 
Product Version3.8.7 
Summary0002879: using a different target catalog and schema when transfering data from psql to mssql dosen't allow identity columns
DescriptionWhen transferring data from a psql database to a mssql database together with using a different catalog and schema (different database to the default). Identity columns will not transfer and instead will produce the following error "org.jumpmind.db.sql.SqlException: Cannot insert the value NULL into column"
Additional InformationI have looked around for a solution without success.
the following tables are configured to use both a target schema and target catalog:
sym_router
sym_transform_table
TagsNo tags attached.

Activities

elong

2016-10-26 16:33

developer   ~0000890

There is probably something in the transform causing the error. Did you mark the primary key column in the transform and select the "identity" transform type?

gatnick

2016-10-27 09:24

reporter   ~0000896

Thank you for the response.

The column is not a primary key so is marked with a 0 in the 'pk' field in the sym_transform_column table. I did however try marking the 'pk' column with the value of 1 to see if this works but it did not.

The 'transform_type' column has the value of 'identity' in the sym_transform_column table.

If you would like any further information please let me know.

Thanks, Nick

mmichalek

2016-11-16 21:11

administrator   ~0000908

Hi Nick, I have not been able to reproduce the issue so far. Also, are you using a SERIAL column on postgres and identity on MSSQL? Would it be possible for you to provide a simple sample configuration that reproduces it (along with the DDL to create the tables)?

Thanks,
Mark

gatnick

2016-11-21 10:55

reporter   ~0000911

Last edited: 2016-11-21 11:10

View 2 revisions

Thanks for the response Mark.
The psql column is not SERIAL.

Below is most of the failing configuration: (please let me know if I’ve missed anything)

--MSSQL table set up
USE [WORLD]
GO

/****** Object: Table [dbo].[PlanetEnd] Script Date: 11/18/2016 12:04:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PlanetEnd](
    [PlanetEndURN] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [TestRecord] [bit] NOT NULL,
    [ActivityID] [int] NOT NULL,
    [URN] [varchar](50) NOT NULL,
    [SegmentLetter] [varchar](10) NOT NULL,
    [Source] [varchar](50) NULL,
    [DateLoaded] [datetime] NOT NULL,
    [Status] [int] NOT NULL,
    [LastStatus] [int] NOT NULL,
    [PriorityAdjustment] [int] NULL,
 CONSTRAINT [PK_PlanetEnd] PRIMARY KEY CLUSTERED
(
    [TestRecord] ASC,
    [ActivityID] ASC,
    [URN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PlanetEnd] ADD CONSTRAINT [DF_PlanetEnd_DateLoaded] DEFAULT (getdate()) FOR [DateLoaded]
GO

ALTER TABLE [dbo].[PlanetEnd] ADD CONSTRAINT [DF_PlanetEnd_Status] DEFAULT ((0)) FOR [Status]
GO

ALTER TABLE [dbo].[PlanetEnd] ADD CONSTRAINT [DF_PlanetEnd_LastStatus] DEFAULT ((0)) FOR [LastStatus]
GO

ALTER TABLE [dbo].[PlanetEnd] ADD DEFAULT ((0)) FOR [PriorityAdjustment]
GO

--psql table set up
SET statement_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

CREATE TABLE planetend (
    testrecord boolean NOT NULL,
    activityid integer NOT NULL,
    urn character varying(50) NOT NULL,
    planetendurn integer,
    dateloaded timestamp without time zone NOT NULL,
    laststatus integer NOT NULL,
    segmentletter character varying(10) NOT NULL,
    priorityadjustment integer,
    status integer NOT NULL,
    source character varying(50)
);

ALTER TABLE public.planetend OWNER TO mint;


ALTER TABLE ONLY planetend
    ADD CONSTRAINT planetend_pkey PRIMARY KEY (testrecord, activityid, urn);

--psql is the master node, so here are the symmetricds table configurations
INSERT INTO sym_router ( router_id, source_node_group_id, target_node_group_id, router_type, router_expression, sync_on_update, sync_on_insert, sync_on_delete, create_time, last_update_time ,use_source_catalog_schema, target_schema_name, target_catalog_name) VALUES ( 'psql_to_mssql_planetend', 'sync-psql', 'sync-mssql', 'bsh', 'PLANETENDURN == null', 0, 1, 0, current_timestamp, current_timestamp, 0, 'dbo', 'world');
INSERT INTO sym_trigger ( trigger_id, source_table_name, channel_id, sync_on_update, sync_on_insert, sync_on_delete, create_time, last_update_time ) VALUES ( 'planetend_inbound', 'planetend', 'onitfhi-to-mi', 0, 1, 0, current_timestamp, current_timestamp );
INSERT INTO sym_trigger_router ( trigger_id, router_id, create_time, last_update_time ) VALUES ( 'planetend_inbound', 'psql_to_mssql_planetend', 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, column_policy, delete_action,target_catalog_name, target_schema_name ) VALUES ( 'planetend_inbound_insert', 'sync-psql', 'sync-mssql', 'LOAD', 'planetend', 'planetend', 'SPECIFIED', 'NONE' ,'WORLD','dbo');
INSERT INTO sym_transform_column( transform_id, include_on, target_column_name, source_column_name, transform_type, transform_expression, pk) VALUES ( 'planetend_inbound_insert', 'I', 'TestRecord', 'TestRecord', 'copy', null, 1 ), ( 'planetend_inbound_insert', 'I', 'ActivityID', 'ActivityID', 'copy', null , 1), ('planetend_inbound_insert', 'I', 'URN', 'URN', 'copy', null, 1 ), ( 'planetend_inbound_insert', 'I', 'PlanetEndURN', null, 'identity', null, 0 ), ('planetend_inbound_insert', 'I', 'SegmentLetter', 'SegmentLetter', 'copy', null, 0 ), ( 'planetend_inbound_insert', 'I', 'DateLoaded', null, 'variable', 'system_timestamp',0), ( 'planetend_inbound_insert', 'I', 'Status', null, 'const', '0', 0 ), ('planetend_inbound_insert', 'I', 'LastStatus', null, 'const', '0', 0 );
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, deployment_type) VALUES ('sync-mssql', 'sync-mssql', 'sync-mssql', 1, 'http://10.10.2.150:31410/sync/sync-mssql', '?', '3.8.6', 'Microsoft SQL Server', '10.50', '2016-10-26 09:53:30.145', '+01:00', 0, 0, 'sync-psql', NULL);
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, deployment_type) VALUES ('sync-psql', 'sync-psql', 'sync-psql', 1, 'http://10.10.2.150:31420/sync/sync-psql', '?', '3.8.7', 'PostgreSQL', '9.1', '2016-10-26 11:10:43.661', '+01:00', -1, -1, NULL, 'server');

INSERT INTO sym_node_host (node_id, host_name, ip_address, os_user, os_name, os_arch, os_version, available_processors, free_memory_bytes, total_memory_bytes, max_memory_bytes, java_version, java_vendor, jdbc_version, symmetric_version, timezone_offset, heartbeat_time, last_restart_time, create_time) VALUES ('sync-psql', 'localhost', '10.10.2.150', 'root', 'Linux', 'amd64', '3.2.0-4-amd64', 8, 174165864, 241172480, 241172480, '1.7.0_111', 'Oracle Corporation', 'PostgreSQL 9.4 JDBC4 (build 1204)', '3.8.7', '+00:00', '2016-11-21 10:26:14', '2016-11-21 10:11:14.709', '2016-10-25 16:06:55.518859');
INSERT INTO sym_channel (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, queue, max_network_kbps, create_time, last_update_by, last_update_time) VALUES ('onitfhi-to-mi', 1, 100000, 60, 100000, 0, 1, 1, 1, 1, 0, 0, 0, 'default', 'default', 'onitfhi-to-mi', 'default', 0.000, NULL, NULL, NULL);

--once that is all set up if you do an psql insert like below you will encounter the error.
INSERT INTO planetend values (false,1234, 4321,null,now(),0,'A',0,0,null) ;

If you need any more information please let me know.

Thanks, Nick

josh-a-hicks

2016-11-30 17:01

developer   ~0000915

Nick,

Can you remove the identity transform for your PlanetEndURN column. By default SDS will recognize an identity column on the target and turn identity insert on so that your value will be used rather then having the database generate one.

Thanks
Josh

Issue History

Date Modified Username Field Change
2016-10-26 10:43 gatnick New Issue
2016-10-26 16:33 elong Note Added: 0000890
2016-10-27 09:24 gatnick Note Added: 0000896
2016-11-09 17:46 mmichalek Assigned To => mmichalek
2016-11-09 17:46 mmichalek Status new => assigned
2016-11-16 21:11 mmichalek Note Added: 0000908
2016-11-21 10:55 gatnick Note Added: 0000911
2016-11-21 11:10 gatnick Note Edited: 0000911 View Revisions
2016-11-30 17:01 josh-a-hicks Note Added: 0000915
2018-05-08 20:08 mmichalek Status assigned => closed