View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0002240 | SymmetricDS | Bug | public | 2015-03-26 09:02 | 2023-09-13 17:25 |
Reporter | dlauwers | Assigned To | |||
Priority | normal | ||||
Status | closed | Resolution | open | ||
Product Version | 3.7.10 | ||||
Summary | 0002240: Send schema gives error on foreign key | ||||
Description | I 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 Reproduce | 1) 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 Information | Other 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 ? | ||||
Tags | ddl/schema, dialect: sql-server | ||||
|
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? |
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 |