View Issue Details

IDProjectCategoryView StatusLast Update
0002240SymmetricDSBugpublic2023-09-13 17:25
Reporterdlauwers Assigned To 
Prioritynormal 
Status closedResolutionopen 
Product Version3.7.10 
Summary0002240: Send schema gives error on foreign key
DescriptionI have a simple table that exists in both databases (MS-SQL). Configuration is OK, data is synchronized bi-directional.

When I want to change the schema and use the symadmin send-schema an error occurs.
I have added 1 column (Job Title) to the central database, I followed the steppes in the manual.
1) alter table
2) sync triggers
3) Send schema

On step 3 I get the following error:

2015-03-26 08:47:58,882 ERROR [Danny] [DataLoaderService] [danny-pull-1] Failed to load batch DA-SERVER-CENTRAL-7225 because: The constraint 'PK_Employees' is being referenced by table 'ServicesEmployees', foreign key constraint 'FK_ServicesEmployees_Employee'.
org.jumpmind.db.sql.SqlException: The constraint 'PK_Employees' is being referenced by table 'ServicesEmployees', foreign key constraint 'FK_ServicesEmployees_Employee'.

What I find odd that the script is trying to change the column ID also, that has not changed ?
I would guess that regardless of PK en FK, simple changes to the table should be able to be distributed to the nodes via the send-schema ?


More details below:

2015-03-26 08:47:58,868 INFO [Danny] [DefaultDatabaseWriter] [danny-pull-1] About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="Employee">
        <column name="ID" primaryKey="true" required="true" type="CHAR" size="36" default="newid()">
            <platform-column name="mssql2008" type="uniqueidentifier" default="newid()"/>
        </column>
        <column name="FirstName" required="true" type="VARCHAR" size="50">
            <platform-column name="mssql2008" type="nvarchar" size="50"/>
        </column>
        <column name="LastName" required="true" type="VARCHAR" size="50">
            <platform-column name="mssql2008" type="nvarchar" size="50"/>
        </column>
        <column name="LoginID" type="CHAR" size="36">
            <platform-column name="mssql2008" type="uniqueidentifier"/>
        </column>
        <column name="Title" type="VARCHAR" size="50">
            <platform-column name="mssql2008" type="nvarchar" size="50"/>
        </column>
        <column name="Gender" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2008" type="int" size="10"/>
        </column>
        <column name="LastChanged" type="TIMESTAMP" size="23,3">
            <platform-column name="mssql2008" type="datetime" size="23" decimalDigits="3"/>
        </column>
        <column name="RoleID" type="CHAR" size="36">
            <platform-column name="mssql2008" type="uniqueidentifier"/>
        </column>
        <column name="Job Title" type="VARCHAR" size="50">
            <platform-column name="mssql2008" type="nvarchar" size="50"/>
        </column>
        <index name="IX_Employee">
            <index-column name="LastName"/>
            <index-column name="FirstName"/>
        </index>
    </table>
</database>
2015-03-26 08:47:58,874 INFO [Danny] [MsSql2008DatabasePlatform] [danny-pull-1] Running alter sql:
SET quoted_identifier on;
BEGIN
  DECLARE @tn4654926b_14c51ca0409__7feb nvarchar(256), @cn4654926b_14c51ca0409__7fea nvarchar(256)
  DECLARE refcursor CURSOR FOR
  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
    FROM sysobjects objs
    WHERE objs.xtype = 'PK' AND object_name(objs.parent_obj) = 'Employee' OPEN refcursor
  FETCH NEXT FROM refcursor INTO @tn4654926b_14c51ca0409__7feb, @cn4654926b_14c51ca0409__7fea
  WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC ('ALTER TABLE '+@tn4654926b_14c51ca0409__7feb+' DROP CONSTRAINT '+@cn4654926b_14c51ca0409__7fea)
      FETCH NEXT FROM refcursor INTO @tn4654926b_14c51ca0409__7feb, @cn4654926b_14c51ca0409__7fea
    END
  CLOSE refcursor
  DEALLOCATE refcursor
END;
ALTER TABLE "DA"."dbo"."Employee" ADD "id" uniqueidentifier DEFAULT 'newid()' NOT NULL;
ALTER TABLE "DA"."dbo"."Employee" ADD "Job Title" nvarchar(50);
BEGIN
DECLARE @sql NVARCHAR(2000)
SELECT TOP 1 @sql = N'alter table "Employee" drop constraint ['+dc.NAME+N']'
FROM sys.default_constraints dc
JOIN sys.columns c
    ON c.default_object_id = dc.object_id
WHERE
    dc.parent_object_id = OBJECT_ID('Employee')
AND c.name = N'ID'
IF @@ROWCOUNT > 0
  EXEC (@sql)
END
;
ALTER TABLE "DA"."dbo"."Employee" DROP COLUMN "ID";
ALTER TABLE "DA"."dbo"."Employee"
    ADD CONSTRAINT "Employee_PK" PRIMARY KEY ("id");

2015-03-26 08:47:58,881 WARN [Danny] [JdbcSqlTemplate] [danny-pull-1] The constraint 'PK_Employees' is being referenced by table 'ServicesEmployees', foreign key constraint 'FK_ServicesEmployees_Employee'.. Failed to execute: BEGIN
  DECLARE @tn4654926b_14c51ca0409__7feb nvarchar(256), @cn4654926b_14c51ca0409__7fea nvarchar(256)
  DECLARE refcursor CURSOR FOR
  SELECT object_name(objs.parent_obj) tablename, objs.name constraintname
    FROM sysobjects objs
    WHERE objs.xtype = 'PK' AND object_name(objs.parent_obj) = 'Employee' OPEN refcursor
  FETCH NEXT FROM refcursor INTO @tn4654926b_14c51ca0409__7feb, @cn4654926b_14c51ca0409__7fea
  WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC ('ALTER TABLE '+@tn4654926b_14c51ca0409__7feb+' DROP CONSTRAINT '+@cn4654926b_14c51ca0409__7fea)
      FETCH NEXT FROM refcursor INTO @tn4654926b_14c51ca0409__7feb, @cn4654926b_14c51ca0409__7fea
    END
  CLOSE refcursor
  DEALLOCATE refcursor
END
2015-03-26 08:47:58,882 ERROR [Danny] [DefaultDatabaseWriter] [danny-pull-1] Failed to alter table using the following xml: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="Employee">
        <column name="ID" primaryKey="true" required="true" type="CHAR" size="36" default="newid()">
            <platform-column name="mssql2008" type="uniqueidentifier" default="newid()"/>
        </column>
        <column name="FirstName" required="true" type="VARCHAR" size="50">
            <platform-column name="mssql2008" type="nvarchar" size="50"/>
        </column>
        <column name="LastName" required="true" type="VARCHAR" size="50">
            <platform-column name="mssql2008" type="nvarchar" size="50"/>
        </column>
        <column name="LoginID" type="CHAR" size="36">
            <platform-column name="mssql2008" type="uniqueidentifier"/>
        </column>
        <column name="Title" type="VARCHAR" size="50">
            <platform-column name="mssql2008" type="nvarchar" size="50"/>
        </column>
        <column name="Gender" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2008" type="int" size="10"/>
        </column>
        <column name="LastChanged" type="TIMESTAMP" size="23,3">
            <platform-column name="mssql2008" type="datetime" size="23" decimalDigits="3"/>
        </column>
        <column name="RoleID" type="CHAR" size="36">
            <platform-column name="mssql2008" type="uniqueidentifier"/>
        </column>
        <column name="Job Title" type="VARCHAR" size="50">
            <platform-column name="mssql2008" type="nvarchar" size="50"/>
        </column>
        <index name="IX_Employee">
            <index-column name="LastName"/>
            <index-column name="FirstName"/>
        </index>
    </table>
</database>
2015-03-26 08:47:58,882 ERROR [Danny] [DataLoaderService] [danny-pull-1] Failed to load batch DA-SERVER-CENTRAL-7225 because: The constraint 'PK_Employees' is being referenced by table 'ServicesEmployees', foreign key constraint 'FK_ServicesEmployees_Employee'.
org.jumpmind.db.sql.SqlException: The constraint 'PK_Employees' is being referenced by table 'ServicesEmployees', foreign key constraint 'FK_ServicesEmployees_Employee'.
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:288)
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:279)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:434)
    at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:344)
    at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:107)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterTables(AbstractDatabasePlatform.java:204)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterDatabase(AbstractDatabasePlatform.java:177)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.create(DefaultDatabaseWriter.java:508)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:167)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:65)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:204)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:200)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:170)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:116)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener.end(DataLoaderService.java:820)
    at org.jumpmind.symmetric.io.data.writer.StagingDataWriter.notifyEndBatch(StagingDataWriter.java:75)
    at org.jumpmind.symmetric.io.data.writer.AbstractProtocolDataWriter.end(AbstractProtocolDataWriter.java:220)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:130)
    at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:430)
    at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPull(DataLoaderService.java:267)
    at org.jumpmind.symmetric.service.impl.PullService.execute(PullService.java:135)
    at org.jumpmind.symmetric.service.impl.NodeCommunicationService$2.run(NodeCommunicationService.java:317)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: The constraint 'PK_Employees' is being referenced by table 'ServicesEmployees', foreign key constraint 'FK_ServicesEmployees_Employee'.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2894)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2334)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:649)
    at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:614)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:573)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:810)
    at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1290)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:358)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:344)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:432)
    ... 24 more



Steps To Reproduce1) Central DB and Node are in 2 way replication

Employee TABLE:

CREATE TABLE [dbo].[Employee](
    [ID] [UNIQUEIDENTIFIER] NOT NULL CONSTRAINT [DF_Employees_ID] DEFAULT (NEWID()),
    [FirstName] [NVARCHAR](50) NOT NULL,
    [LastName] [NVARCHAR](50) NOT NULL,
    [LoginID] [UNIQUEIDENTIFIER] NULL,
    [Title] [NVARCHAR](50) NULL,
    [Gender] [INT] NOT NULL,
    [LastChanged] [DATETIME] NULL,
    [RoleID] [UNIQUEIDENTIFIER] NULL,
    [Job Title] [NVARCHAR](50) NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
    [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]


ServicesEmployees TABLE

CREATE TABLE [dbo].[ServicesEmployees](
    [ID] [UNIQUEIDENTIFIER] NOT NULL CONSTRAINT [DF_ServicesEmployees_ID] DEFAULT (NEWID()),
    [EmployeeID] [UNIQUEIDENTIFIER] NOT NULL,
    [ServicesID] [UNIQUEIDENTIFIER] NULL,
    [LogOndt] [DATETIME] NULL CONSTRAINT [DF_ServicesEmployees_LogOndt] DEFAULT (GETDATE()),
    [LogOffdt] [DATETIME] NULL,
    [Type] [INT] NULL,
    [ResidentID] [UNIQUEIDENTIFIER] NULL,
    [LastChanged] [DATETIME] NULL,
 CONSTRAINT [PK_ServicesEmployees] PRIMARY KEY CLUSTERED
(
    [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]

GO

ALTER TABLE [dbo].[ServicesEmployees] WITH CHECK ADD CONSTRAINT [FK_ServicesEmployees_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([ID])
GO

ALTER TABLE [dbo].[ServicesEmployees] CHECK CONSTRAINT [FK_ServicesEmployees_Employee]
GO

ALTER TABLE [dbo].[ServicesEmployees] WITH CHECK ADD CONSTRAINT [FK_ServicesEmployees_Service] FOREIGN KEY([ServicesID])
REFERENCES [dbo].[Service] ([ID])
GO

ALTER TABLE [dbo].[ServicesEmployees] CHECK CONSTRAINT [FK_ServicesEmployees_Service]
GO

2) We added the column

[Job Title] [NVARCHAR](50) NULL

to the Employee table

3) Run Symadmin Sync-trigers
4) Then used symadmin send-schema to trigger the change to the nodes
5) Error in the Node trying to apply the schema changes
Additional InformationOther related things about schema synchronization:
- Is it possible to automatically sync the schema changes when they happen on the central DB to all other nodes ?
- Is it also possible to sync the stored procedures and also when they are created/alterd in the central DB, that SymmetricDS would pick up those changes and apply them to the nodes, this all to also keep the schema and stored procedures identical between central DB and nodes ?
Tagsddl/schema, dialect: sql-server

Activities

emiller

2022-09-01 18:06

developer   ~0002177

I followed your steps to reproduce this issue using SymmetricDS 3.14.1 and SQL Server 2019. When I sent the schema to the target node, SymmetricDS successfully altered the target table by running the following SQL:

SET quoted_identifier on;
ALTER TABLE "client"."dbo"."Employee" ADD "Job Title" nvarchar(50);

It's possible that this issue has been resolved since version 3.7.10. Can you upgrade SymmetricDS to the latest version and check whether the issue still occurs?

Issue History

Date Modified Username Field Change
2015-03-26 09:02 dlauwers New Issue
2019-04-23 16:51 elong Tag Attached: ddl/schema
2019-04-23 16:51 elong Tag Attached: mssql
2019-04-23 20:49 admin Tag Renamed mssql => dialect: mssql
2019-04-24 12:50 admin Tag Renamed dialect: mssql => dialect: sql-server
2022-09-01 18:06 emiller Note Added: 0002177
2022-09-01 18:06 emiller Status new => feedback
2023-09-13 17:25 emiller Status feedback => closed