View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0006509 | SymmetricDS | Bug | public | 2024-06-21 12:08 | 2024-07-29 14:30 |
Reporter | otaburiaux | Assigned To | |||
Priority | normal | ||||
Status | acknowledged | Resolution | open | ||
Product Version | 3.15.7 | ||||
Summary | 0006509: Issue with trigger on SQLServer with table names using reserved keywords | ||||
Description | Create or alter of trigger fails on some tables that have specific names: Role and Rule (maybe more ?) Exceptions: 2024-06-20 17:11:00,960 INFO [client-S101] [MsSql2016SymmetricDialect] [client-s101-sync-triggers-1] Failed to create trigger: create or alter trigger "dbo".SYM_ON_I_FOR_RL_CLNT123 on "SimuProductS101"."dbo"."Rule" with execute as caller after insert as begin declare @NCT int set @NCT = @@OPTIONS & 512 set nocount on declare @TransactionId varchar(1000) if (@@TRANCOUNT > 0) begin select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 end if ("SimuProductS101".dbo.sym_triggers_disabled() != 2) begin insert into "SimuProductS101"."dbo".sym_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) select 'Rule','I', 400, XXX, 'default', @TransactionId, "SimuProductS101".dbo.sym_node_disabled(), null, current_timestamp from inserted inner join "SimuProductS101"."dbo"."Rule" orig on orig."Id"=inserted."Id" where 1=1 end if (@NCT = 0) set nocount off end ---- go 2024-06-20 17:11:00,961 INFO [client-S101] [TriggerRouterService] [client-s101-sync-triggers-1] Cleaning up trigger hist row of 400 after failing to create trigger for Rule 2024-06-20 17:11:00,962 ERROR [client-S101] [TriggerRouterService] [client-s101-sync-triggers-1] Failed to create triggers for XXX StackTraceKey.init [SqlException:1974324067] org.jumpmind.db.sql.SqlException: Cannot alter trigger 'dbo.SYM_ON_I_FOR_RL_CLNT123' on 'SimuProductS101.dbo.Rule' because this trigger does not belong to this object. Specify the correct trigger name or the correct target object name. at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:377) at org.jumpmind.db.sql.JdbcSqlTransaction.execute(JdbcSqlTransaction.java:284) at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createTrigger(AbstractSymmetricDialect.java:419) at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessaryWithException(TriggerRouterService.java:2509) at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessary(TriggerRouterService.java:2385) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:2336) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTrigger(TriggerRouterService.java:2193) at org.jumpmind.symmetric.service.impl.TriggerRouterService$2.run(TriggerRouterService.java:2047) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:842) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot alter trigger 'dbo.SYM_ON_I_FOR_RL_CLNT123' on 'SimuProductS101.dbo.Rule' because this trigger does not belong to this object. Specify the correct trigger name or the correct target object name. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:261) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1752) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:946) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:840) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7748) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4410) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263) at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:813) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193) at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:291) at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:284) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:375) ... 12 more 2024-06-20 17:11:05,444 INFO [client-S201] [MsSql2016SymmetricDialect] [client-s201-sync-triggers-1] Failed to create trigger: create or alter trigger "dbo".SYM_ON_I_FOR_RL_CLNT123 on "SimuProductS201"."dbo"."Role" with execute as caller after insert as begin declare @NCT int set @NCT = @@OPTIONS & 512 set nocount on declare @TransactionId varchar(1000) if (@@TRANCOUNT > 0) begin select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0 end if ("SimuProductS201".dbo.sym_triggers_disabled() != 2) begin insert into "SimuProductS201"."dbo".sym_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) select 'Role','I', 387, XXX, 'default', @TransactionId, "SimuProductS201".dbo.sym_node_disabled(), null, current_timestamp from inserted inner join "SimuProductS201"."dbo"."Role" orig on orig."Id"=inserted."Id" where 1=1 end if (@NCT = 0) set nocount off end ---- go 2024-06-20 17:11:05,445 INFO [client-S201] [TriggerRouterService] [client-s201-sync-triggers-1] Cleaning up trigger hist row of 387 after failing to create trigger for Role 2024-06-20 17:11:05,447 ERROR [client-S201] [TriggerRouterService] [client-s201-sync-triggers-1] Failed to create triggers for XXX StackTraceKey.init [SqlException:1539047956] org.jumpmind.db.sql.SqlException: Cannot alter trigger 'dbo.SYM_ON_I_FOR_RL_CLNT123' on 'SimuProductS201.dbo.Role' because this trigger does not belong to this object. Specify the correct trigger name or the correct target object name. at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:377) at org.jumpmind.db.sql.JdbcSqlTransaction.execute(JdbcSqlTransaction.java:284) at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createTrigger(AbstractSymmetricDialect.java:419) at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessaryWithException(TriggerRouterService.java:2509) at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessary(TriggerRouterService.java:2385) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:2336) at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTrigger(TriggerRouterService.java:2193) at org.jumpmind.symmetric.service.impl.TriggerRouterService$2.run(TriggerRouterService.java:2047) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:842) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot alter trigger 'dbo.SYM_ON_I_FOR_RL_CLNT123' on 'SimuProductS201.dbo.Role' because this trigger does not belong to this object. Specify the correct trigger name or the correct target object name. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:261) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1752) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:946) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:840) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7748) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4410) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263) at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:813) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193) at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:291) at org.jumpmind.db.sql.JdbcSqlTransaction$3.execute(JdbcSqlTransaction.java:284) at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:375) ... 12 more | ||||
Additional Information | Probably the same problem that I reported previously for DbCompare: https://issues.symmetricds.org/view.php?id=6348 | ||||
Tags | dialect: sql-server, trigger | ||||
|
The table name is already quoted, so there shouldn't be a problem with that table name. Are you using Microsoft SQL-Server 2016 without any service packs? There was an bug early on in SQL-Server 2016 where "create or alter" didn't work properly and gave a misleading error. If you set trigger.allow.create.or.replace=false in the engine file for SymmetricDS, it won't use the "or alter" syntax and it avoids the error. Or, you could apply the latest service packs to SQL-Server, which is probably a good idea anyway. |
|
I have this version installed: Microsoft SQL Server 2017 (RTM-GDR) (KB5040942) - 14.0.2056.2 (X64) Trigger creations seems fine on other tables, only tables named Rule and Role are provoking errors. 2024-07-03 16:00:50,215 INFO [server-2] [MsSql2016SymmetricDialect] [server-2-sync-triggers-1] Creating SYM_ON_I_FOR_CTNRYSPPRTLNKD_SRVR trigger for Sbb_Release.dbo.CatenarySupportLinked 2024-07-03 16:00:50,224 INFO [server-2] [MsSql2016SymmetricDialect] [server-2-sync-triggers-1] Creating SYM_ON_U_FOR_CTNRYSPPRTLNKD_SRVR trigger for Sbb_Release.dbo.CatenarySupportLinked 2024-07-03 16:00:50,224 INFO [server-2] [MsSql2016SymmetricDialect] [server-2-sync-triggers-1] Creating SYM_ON_D_FOR_CTNRYSPPRTLNKD_SRVR trigger for Sbb_Release.dbo.CatenarySupportLinked 2024-07-03 16:00:50,233 INFO [server-2] [MsSql2016SymmetricDialect] [server-2-sync-triggers-1] Creating SYM_ON_I_FOR_CTNRYLMNT_SRVR trigger for Sbb_Release.dbo.CatenaryElement 2024-07-03 16:00:50,233 INFO [server-2] [MsSql2016SymmetricDialect] [server-2-sync-triggers-1] Creating SYM_ON_U_FOR_CTNRYLMNT_SRVR trigger for Sbb_Release.dbo.CatenaryElement 2024-07-03 16:00:50,242 INFO [server-2] [MsSql2016SymmetricDialect] [server-2-sync-triggers-1] Creating SYM_ON_D_FOR_CTNRYLMNT_SRVR trigger for Sbb_Release.dbo.CatenaryElement |
|
Also using this version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Same problem |
|
trigger.allow.create.or.replace=false did the trick, thanks. 2024-07-29 16:24:07,644 INFO [server-C101] [MsSql2016SymmetricDialect] [server-C101-job-3] Dropping SYM_ON_I_FOR_RL_SRVR trigger for Rule 2024-07-29 16:24:07,649 INFO [server-C101] [MsSql2016SymmetricDialect] [server-C101-job-3] Creating SYM_ON_I_FOR_RL_SRVR trigger for SimuProductC.dbo.Rule |
Date Modified | Username | Field | Change |
---|---|---|---|
2024-06-21 12:08 | otaburiaux | New Issue | |
2024-06-21 12:08 | otaburiaux | Tag Attached: dialect: sql-server | |
2024-06-21 12:08 | otaburiaux | Tag Attached: trigger | |
2024-06-26 18:00 | cquamme | Status | new => acknowledged |
2024-07-02 17:15 | elong | Note Added: 0002474 | |
2024-07-02 17:16 | elong | Status | acknowledged => feedback |
2024-07-15 14:59 | otaburiaux | Note Added: 0002476 | |
2024-07-15 14:59 | otaburiaux | Status | feedback => new |
2024-07-16 07:32 | otaburiaux | Note Added: 0002479 | |
2024-07-24 17:09 | cquamme | Status | new => acknowledged |
2024-07-29 14:30 | otaburiaux | Note Added: 0002484 |