View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0006509SymmetricDSBugpublic2024-06-21 12:082024-07-29 14:30
Reporterotaburiaux Assigned To 
Prioritynormal 
Status acknowledgedResolutionopen 
Product Version3.15.7 
Summary0006509: Issue with trigger on SQLServer with table names using reserved keywords
DescriptionCreate 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 InformationProbably the same problem that I reported previously for DbCompare: https://issues.symmetricds.org/view.php?id=6348
Tagsdialect: sql-server, trigger

Activities

elong

2024-07-02 17:15

developer   ~0002474

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.

otaburiaux

2024-07-15 14:59

reporter   ~0002476

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

otaburiaux

2024-07-16 07:32

reporter   ~0002479

Also using this version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Same problem

otaburiaux

2024-07-29 14:30

reporter   ~0002484

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

Issue History

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