View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0002879 | SymmetricDS | Bug | public | 2016-10-26 10:43 | 2018-05-08 20:08 |
Reporter | gatnick | Assigned To | mmichalek | ||
Priority | urgent | ||||
Status | closed | Resolution | open | ||
Product Version | 3.8.7 | ||||
Summary | 0002879: using a different target catalog and schema when transfering data from psql to mssql dosen't allow identity columns | ||||
Description | When 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 Information | I 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 | ||||
Tags | No tags attached. | ||||
|
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? |
|
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 |
|
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 |
|
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 |
|
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 |
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 |