0003845: Primary key constraint violation while replicating single table from MSSQL 2017 into PostgreSQL 9.6 - SymmetricDS

Summary	0003845: Primary key constraint violation while replicating single table from MSSQL 2017 into PostgreSQL 9.6

Revision	2022-09-19 18:41 by elong
Description	This bug does NOT appear in SymmetricDS 3.8.41 version and is due to erroneous MSSQL database trigger definition in 3.9.16 version. Consider the following MSSQL table: CREATE TABLE [dbo].[rightsholder_invoice]( [rightsholder_invoice_id] [int] NOT NULL, [rightsholder_invoice_datetime] [datetime] NOT NULL, [rightsholder_id] [int] NOT NULL, [number] [varchar](128) NOT NULL, [income_date] [date] NOT NULL, [invoice_date] [date] NOT NULL, [sale_date] [date] NOT NULL, [rightsholder_invoice_type_id] [int] NOT NULL, [status_description] [varchar](128) NULL, [comment] [varchar](max) NULL, [correcting_rightshaolder_invoice_id] [int] NULL, [GUID] [uniqueidentifier] NOT NULL, [LAST_UPDATE_DATE_TIME] [datetime2](7) NOT NULL, CONSTRAINT [PK_rightsholder_invoice] PRIMARY KEY CLUSTERED ( [rightsholder_invoice_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO Initial table definition creation and initial loading of data is performed correctly. After performing simple update on multiple records at once exception occurs (mutiple records changed in single transaction are a must for this error to occur): UPDATE [zol].[dbo].[rightsholder_message] SET [message_content] = 'some_text'; (1252 rows affected) Error in changes replication: 2019-01-03 18:10:51,796 INFO [zapaonline-001] [DefaultDatabaseWriter] [zapaonline-001-dataloader-6] Failed to process update event in batch 000-32 on channel 'zol_to_zapaonline'. Failed sql was: update "public"."rightsholder_invoice" set "rightsholder_invoice_id" = 1, "rightsholder_invoice_datetime" = {ts '2012-12-05 12:24:17.433'}, "rightsholder_id" = 5982, "number" = '11/2012', "income_date" = '2012-11-16', "invoice_date" = '2012-11-09', "sale_date" = '2012-11-09', "rightsholder_invoice_type_id" = 1, "status_description" = 'Zaakceptowana', "comment" = 'wZjKbXąJŚźHyżtohmoĄei9ćTQpLłŻ7ŁNxDuęqFóg', "correcting_rightsholder_invoice_id" = null, "guid" = 'EE49EA84-105E-435B-9D79-06558EBEA744', "last_update_date_time" = {ts '2018-11-06 14:43:39.606'} where "rightsholder_invoice_id" = 4285 Failed raw sql was: update "public"."rightsholder_invoice" set "rightsholder_invoice_id" = ?, "rightsholder_invoice_datetime" = ?, "rightsholder_id" = ?, "number" = ?, "income_date" = ?, "invoice_date" = ?, "sale_date" = ?, "rightsholder_invoice_type_id" = ?, "status_description" = ?, "comment" = ?, "correcting_rightsholder_invoice_id" = ?, "guid" = ?, "last_update_date_time" = ? where "rightsholder_invoice_id" = ? Failed sql parameters: [1, {ts '2012-12-05 12:24:17.433'}, 5982, '11/2012', '2012-11-16', '2012-11-09', '2012-11-09', 1, 'Zaakceptowana', 'wZjKbXąJŚźHyżtohmoĄei9ćTQpLłŻ7ŁNxDuęqFóg', null, 'EE49EA84-105E-435B-9D79-06558EBEA744', {ts '2018-11-06 14:43:39.606'}, 4285] Failed sql parameters types: [INTEGER, TIMESTAMP, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, INTEGER, VARCHAR, LONGVARCHAR, INTEGER, CHAR, TIMESTAMP, INTEGER] Failed sql state and code: 23505 (0) Failed pk data was: "4285" Failed row data was: "1","2012-12-05 12:24:17.433","5982","11/2012","2012-11-16","2012-11-09","2012-11-09","1","Zaakceptowana","wZjKbXąJŚźHyżtohmoĄei9ćTQpLłŻ7ŁNxDuęqFóg",,"EE49EA84-105E-435B-9D79-06558EBEA744","2018-11-06 14:43:39.6066667" Failed old data was: "4285","2018-10-26 14:29:28.987","1469","12/2018","2018-10-26","2018-10-15","2018-10-15","1","Zaakceptowana",,,"0A16CC16-1216-4C59-A3EE-6521517E52F2","2018-11-06 14:43:39.6066667" StackTraceKey.init [UniqueKeyException:2026926579] org.jumpmind.db.sql.UniqueKeyException: org.postgresql.util.PSQLException: BŁĄD: podwójna wartość klucza narusza ograniczenie unikalności "rightsholder_invoice_pkey" Detail: Klucz (rightsholder_invoice_id)=(1) już istnieje. at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:296) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:466) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:904) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.update(DefaultDatabaseWriter.java:488) at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:188) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:201) at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:210) at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:177) at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:123) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1039) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1016) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: org.postgresql.util.PSQLException: BŁĄD: podwójna wartość klucza narusza ograniczenie unikalności "rightsholder_invoice_pkey" Detail: Klucz (rightsholder_invoice_id)=(1) już istnieje. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:485) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:463) ... 16 more Full log attached. The problem is due to the error on ordering changes while inserting them into [sym_data] mssql table in [SYM_ON_U_FOR_RGHTSHLDR_NVC_ZL] trigger (after update trigger on [dbo].[rightsholder_invoice] table). The pk_data and respecting row_data in [sym_data] are inserted in REVERSE order - please see attached screenshot.

Revision	2019-01-03 17:30 by lukasz.krawczyk
Description	This bug does NOT appear in SymmetricDS 3.8.41 version and is due to erroneous MSSQL database trigger definition in 3.9.16 version. Consider the following MSSQL table: CREATE TABLE [dbo].[rightsholder_invoice]( [rightsholder_invoice_id] [int] NOT NULL, [rightsholder_invoice_datetime] [datetime] NOT NULL, [rightsholder_id] [int] NOT NULL, [number] [varchar](128) NOT NULL, [income_date] [date] NOT NULL, [invoice_date] [date] NOT NULL, [sale_date] [date] NOT NULL, [rightsholder_invoice_type_id] [int] NOT NULL, [status_description] [varchar](128) NULL, [comment] [varchar](max) NULL, [correcting_rightshaolder_invoice_id] [int] NULL, [GUID] [uniqueidentifier] NOT NULL, [LAST_UPDATE_DATE_TIME] [datetime2](7) NOT NULL, CONSTRAINT [PK_rightsholder_invoice] PRIMARY KEY CLUSTERED ( [rightsholder_invoice_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO Initial table definition creation and initial loading of data is performed correctly. After performing simple update on multiple records at once exception occurs (mutiple records changed in single transaction are a must for this error to occur): UPDATE [zol].[dbo].[rightsholder_message] SET [message_content] = 'some_text'; (1252 rows affected) Error in changes replication: 2019-01-03 18:10:51,796 INFO [zapaonline-001] [DefaultDatabaseWriter] [zapaonline-001-dataloader-6] Failed to process update event in batch 000-32 on channel 'zol_to_zapaonline'. Failed sql was: update "public"."rightsholder_invoice" set "rightsholder_invoice_id" = 1, "rightsholder_invoice_datetime" = {ts '2012-12-05 12:24:17.433'}, "rightsholder_id" = 5982, "number" = '11/2012', "income_date" = '2012-11-16', "invoice_date" = '2012-11-09', "sale_date" = '2012-11-09', "rightsholder_invoice_type_id" = 1, "status_description" = 'Zaakceptowana', "comment" = 'wZjKbXąJŚźHyżtohmoĄei9ćTQpLłŻ7ŁNxDuęqFóg', "correcting_rightsholder_invoice_id" = null, "guid" = 'EE49EA84-105E-435B-9D79-06558EBEA744', "last_update_date_time" = {ts '2018-11-06 14:43:39.606'} where "rightsholder_invoice_id" = 4285 Failed raw sql was: update "public"."rightsholder_invoice" set "rightsholder_invoice_id" = ?, "rightsholder_invoice_datetime" = ?, "rightsholder_id" = ?, "number" = ?, "income_date" = ?, "invoice_date" = ?, "sale_date" = ?, "rightsholder_invoice_type_id" = ?, "status_description" = ?, "comment" = ?, "correcting_rightsholder_invoice_id" = ?, "guid" = ?, "last_update_date_time" = ? where "rightsholder_invoice_id" = ? Failed sql parameters: [1, {ts '2012-12-05 12:24:17.433'}, 5982, '11/2012', '2012-11-16', '2012-11-09', '2012-11-09', 1, 'Zaakceptowana', 'wZjKbXąJŚźHyżtohmoĄei9ćTQpLłŻ7ŁNxDuęqFóg', null, 'EE49EA84-105E-435B-9D79-06558EBEA744', {ts '2018-11-06 14:43:39.606'}, 4285] Failed sql parameters types: [INTEGER, TIMESTAMP, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, INTEGER, VARCHAR, LONGVARCHAR, INTEGER, CHAR, TIMESTAMP, INTEGER] Failed sql state and code: 23505 (0) Failed pk data was: "4285" Failed row data was: "1","2012-12-05 12:24:17.433","5982","11/2012","2012-11-16","2012-11-09","2012-11-09","1","Zaakceptowana","wZjKbXąJŚźHyżtohmoĄei9ćTQpLłŻ7ŁNxDuęqFóg",,"EE49EA84-105E-435B-9D79-06558EBEA744","2018-11-06 14:43:39.6066667" Failed old data was: "4285","2018-10-26 14:29:28.987","1469","12/2018","2018-10-26","2018-10-15","2018-10-15","1","Zaakceptowana",,,"0A16CC16-1216-4C59-A3EE-6521517E52F2","2018-11-06 14:43:39.6066667" StackTraceKey.init [UniqueKeyException:2026926579] org.jumpmind.db.sql.UniqueKeyException: org.postgresql.util.PSQLException: BŁĄD: podwójna wartość klucza narusza ograniczenie unikalności "rightsholder_invoice_pkey" Detail: Klucz (rightsholder_invoice_id)=(1) już istnieje. at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:296) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:466) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:904) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.update(DefaultDatabaseWriter.java:488) at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:188) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:201) at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:210) at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:177) at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:123) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1039) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1016) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: org.postgresql.util.PSQLException: BŁĄD: podwójna wartość klucza narusza ograniczenie unikalności "rightsholder_invoice_pkey" Detail: Klucz (rightsholder_invoice_id)=(1) już istnieje. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2422) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2167) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:485) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:463) ... 16 more Full log attached. The problem is due to the error on ordering changes while inserting them into [sym_data] mssql table in [SYM_ON_U_FOR_RGHTSHLDR_NVC_ZL] trigger (after update trigger on [dbo].[rightsholder_invoice] table). The pk_data and respecting row_data in [sym_data] are inserted in REVERSE order - please see attached screenshot.

View Revisions: Issue #3845