View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0007021 | SymmetricDS | Bug | public | 2025-08-18 07:12 | 2025-08-20 17:30 |
| Reporter | klippit | Assigned To | |||
| Priority | normal | ||||
| Status | acknowledged | Resolution | open | ||
| Product Version | 3.15.15 | ||||
| Summary | 0007021: unable to create trigger with sync_on_delete_condition | ||||
| Description | sync on delete condition creats error on sql server 2019 after update from symmetricds 3.10.4 to 3.15.15. after updating symmetricds version all triggers are recreated but on trigger fails to recreate. it seems like sync_on_delete_condition is unable to handle string replace for $(curTriggerValue). $(curTriggerValue) is replaced to inserted and should be replaced to deleted in delete part of new trigger. i fixed the error by changing the sync on delete condition with statement below update sym_trigger set sync_on_delete_condition='deleted.identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)' where trigger_id='ll2_identity' now the trigger is created as it should be. Error also exits on a new database. | ||||
| Steps To Reproduce | let symmetricds upgrade on old trigger or create a new trigger with sync_on_delete conditon. old insert statement with error: insert into SYM_TRIGGER (trigger_id, source_table_name,channel_id,sync_on_delete, last_update_time, create_time,sync_on_insert_condition,sync_on_delete_condition,sync_on_update_condition) values ('ll2_identity', 'll2_identity', 'll2',1, GETDATE(), GETDATE(),'$(curTriggerValue).identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)','$(curTriggerValue).identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)','$(curTriggerValue).identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)'); insert into SYM_TRIGGER_ROUTER(trigger_id, router_id, initial_load_order, create_time,last_update_time,initial_load_select) values ('ll2_identity', 'server-2-allshops', 300, GETDATE(),GETDATE(),'t.identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)'); new insert statement is running: insert into SYM_TRIGGER (trigger_id, source_table_name,channel_id,sync_on_delete, last_update_time, create_time,sync_on_insert_condition,sync_on_delete_condition,sync_on_update_condition) values ('ll2_identity', 'll2_identity', 'll2',1, GETDATE(), GETDATE(),'$(curTriggerValue).identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)','deleted.identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)','$(curTriggerValue).identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)'); insert into SYM_TRIGGER_ROUTER(trigger_id, router_id, initial_load_order, create_time,last_update_time,initial_load_select) values ('ll2_identity', 'server-2-allshops', 300, GETDATE(),GETDATE(),'t.identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)'); | ||||
| Tags | sync-trigger, trigger, upgrade | ||||
|
|
symmetric-error.log (22,347 bytes)
2025-08-18 09:03:03,000 INFO [server] [TriggerRouterService] [server-job-15] Synchronizing 1 triggers
2025-08-18 09:03:03,016 INFO [server] [MsSql2016SymmetricDialect] [server-job-15] Creating SYM_ON_I_FOR_LL2_DNTTY_SRVR trigger for postest.dbo.ll2_identity
2025-08-18 09:03:03,032 INFO [server] [MsSql2016SymmetricDialect] [server-job-15] Creating SYM_ON_U_FOR_LL2_DNTTY_SRVR trigger for postest.dbo.ll2_identity
2025-08-18 09:03:03,032 INFO [server] [MsSql2016SymmetricDialect] [server-job-15] Failed to create trigger: create or alter trigger "dbo".SYM_ON_U_FOR_LL2_DNTTY_SRVR on "postest"."dbo"."ll2_identity"
with execute as caller after update as
begin
declare @LOCALROWCOUNT int
declare @LOCALPKCHANGED int
set @LOCALROWCOUNT=@@ROWCOUNT
set @LOCALPKCHANGED = 0
if (1=1)
begin
select @LOCALPKCHANGED = count(*) from inserted, deleted where deleted."id"=inserted."id"
end
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 ("postest".dbo.sym_triggers_disabled() = 0)
begin
if (1=1 AND ( UPDATE("id") ) AND @LOCALROWCOUNT <> @LOCALPKCHANGED)
begin
if (@LOCALROWCOUNT = 1)
begin
insert into "postest"."dbo".sym_data
(table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id,
transaction_id, source_node_id,
external_data, create_time)
select 'll2_identity','U', 358,
case when inserted."id" is null then '' else ('"' + convert(varchar(40), inserted."id", 3) + '"') end+','+
case when inserted."identitytype_id" is null then '' else ('"' + convert(varchar(40), inserted."identitytype_id", 3) + '"') end+','+
case when inserted."data" is null then '' else '"' + replace(replace(convert(varchar(50),inserted."data") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."valid_from" is null then '' else ('"' + convert(varchar,inserted."valid_from",121) + '"') end+','+
case when inserted."valid_until" is null then '' else ('"' + convert(varchar,inserted."valid_until",121) + '"') end+','+
case when inserted."invalid" is null then '' when inserted."invalid" = 1 then '"1"' else '"0"' end,
case when deleted."id" is null then '' else ('"' + convert(varchar(40), deleted."id", 3) + '"') end,
case when deleted."id" is null then '' else ('"' + convert(varchar(40), deleted."id", 3) + '"') end+','+
case when deleted."identitytype_id" is null then '' else ('"' + convert(varchar(40), deleted."identitytype_id", 3) + '"') end+','+
case when deleted."data" is null then '' else '"' + replace(replace(convert(varchar(50),deleted."data") ,'\','\\'),'"','\"') + '"' end+','+
case when deleted."valid_from" is null then '' else ('"' + convert(varchar,deleted."valid_from",121) + '"') end+','+
case when deleted."valid_until" is null then '' else ('"' + convert(varchar,deleted."valid_until",121) + '"') end+','+
case when deleted."invalid" is null then '' when deleted."invalid" = 1 then '"1"' else '"0"' end,
'll2',
@TransactionId, "postest".dbo.sym_node_disabled(), null,
current_timestamp
from inserted
inner join deleted on 1=1
where inserted.identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null) and (((inserted."id" IS NOT NULL AND deleted."id" IS NOT NULL AND inserted."id"<>deleted."id") or (inserted."id" IS NULL AND deleted."id" IS NOT NULL) or (inserted."id" IS NOT NULL AND deleted."id" IS NULL)) or ((inserted."identitytype_id" IS NOT NULL AND deleted."identitytype_id" IS NOT NULL AND inserted."identitytype_id"<>deleted."identitytype_id") or (inserted."identitytype_id" IS NULL AND deleted."identitytype_id" IS NOT NULL) or (inserted."identitytype_id" IS NOT NULL AND deleted."identitytype_id" IS NULL)) or ((inserted."data" IS NOT NULL AND deleted."data" IS NOT NULL AND inserted."data"<>deleted."data") or (inserted."data" IS NULL AND deleted."data" IS NOT NULL) or (inserted."data" IS NOT NULL AND deleted."data" IS NULL)) or ((inserted."valid_from" IS NOT NULL AND deleted."valid_from" IS NOT NULL AND inserted."valid_from"<>deleted."valid_from") or (inserted."valid_from" IS NULL AND deleted."valid_from" IS NOT NULL) or (inserted."valid_from" IS NOT NULL AND deleted."valid_from" IS NULL)) or ((inserted."valid_until" IS NOT NULL AND deleted."valid_until" IS NOT NULL AND inserted."valid_until"<>deleted."valid_until") or (inserted."valid_until" IS NULL AND deleted."valid_until" IS NOT NULL) or (inserted."valid_until" IS NOT NULL AND deleted."valid_until" IS NULL)) or ((inserted."invalid" IS NOT NULL AND deleted."invalid" IS NOT NULL AND inserted."invalid"<>deleted."invalid") or (inserted."invalid" IS NULL AND deleted."invalid" IS NOT NULL) or (inserted."invalid" IS NOT NULL AND deleted."invalid" IS NULL)))
end
else
begin
insert into "postest"."dbo".sym_data
(table_name, event_type, trigger_hist_id, pk_data, channel_id, transaction_id,
source_node_id, external_data, create_time)
select 'll2_identity','D', 358,
case when deleted."id" is null then '' else ('"' + convert(varchar(40), deleted."id", 3) + '"') end,
'll2', @TransactionId,
"postest".dbo.sym_node_disabled(),
null, current_timestamp
from deleted
where inserted.identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)
insert into "postest"."dbo".sym_data
(table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id,
source_node_id, external_data, create_time)
select 'll2_identity','I', 358,
case when inserted."id" is null then '' else ('"' + convert(varchar(40), inserted."id", 3) + '"') end+','+
case when inserted."identitytype_id" is null then '' else ('"' + convert(varchar(40), inserted."identitytype_id", 3) + '"') end+','+
case when inserted."data" is null then '' else '"' + replace(replace(convert(varchar(50),inserted."data") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."valid_from" is null then '' else ('"' + convert(varchar,inserted."valid_from",121) + '"') end+','+
case when inserted."valid_until" is null then '' else ('"' + convert(varchar,inserted."valid_until",121) + '"') end+','+
case when inserted."invalid" is null then '' when inserted."invalid" = 1 then '"1"' else '"0"' end,
'll2', @TransactionId,
"postest".dbo.sym_node_disabled(),
null, current_timestamp
from inserted
where inserted.identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null)
end
end
else
begin
insert into "postest"."dbo".sym_data
(table_name, event_type, trigger_hist_id, row_data, pk_data, old_data, channel_id,
transaction_id, source_node_id,
external_data, create_time)
select 'll2_identity','U', 358,
case when inserted."id" is null then '' else ('"' + convert(varchar(40), inserted."id", 3) + '"') end+','+
case when inserted."identitytype_id" is null then '' else ('"' + convert(varchar(40), inserted."identitytype_id", 3) + '"') end+','+
case when inserted."data" is null then '' else '"' + replace(replace(convert(varchar(50),inserted."data") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."valid_from" is null then '' else ('"' + convert(varchar,inserted."valid_from",121) + '"') end+','+
case when inserted."valid_until" is null then '' else ('"' + convert(varchar,inserted."valid_until",121) + '"') end+','+
case when inserted."invalid" is null then '' when inserted."invalid" = 1 then '"1"' else '"0"' end,
case when deleted."id" is null then '' else ('"' + convert(varchar(40), deleted."id", 3) + '"') end,
case when deleted."id" is null then '' else ('"' + convert(varchar(40), deleted."id", 3) + '"') end+','+
case when deleted."identitytype_id" is null then '' else ('"' + convert(varchar(40), deleted."identitytype_id", 3) + '"') end+','+
case when deleted."data" is null then '' else '"' + replace(replace(convert(varchar(50),deleted."data") ,'\','\\'),'"','\"') + '"' end+','+
case when deleted."valid_from" is null then '' else ('"' + convert(varchar,deleted."valid_from",121) + '"') end+','+
case when deleted."valid_until" is null then '' else ('"' + convert(varchar,deleted."valid_until",121) + '"') end+','+
case when deleted."invalid" is null then '' when deleted."invalid" = 1 then '"1"' else '"0"' end,
'll2',
@TransactionId, "postest".dbo.sym_node_disabled(), null,
current_timestamp
from inserted
inner join deleted on deleted."id"=inserted."id"
where inserted.identitytype_id in (select id from ll2_identitytype where flags&1=0 OR flags is null) and (((inserted."id" IS NOT NULL AND deleted."id" IS NOT NULL AND inserted."id"<>deleted."id") or (inserted."id" IS NULL AND deleted."id" IS NOT NULL) or (inserted."id" IS NOT NULL AND deleted."id" IS NULL)) or ((inserted."identitytype_id" IS NOT NULL AND deleted."identitytype_id" IS NOT NULL AND inserted."identitytype_id"<>deleted."identitytype_id") or (inserted."identitytype_id" IS NULL AND deleted."identitytype_id" IS NOT NULL) or (inserted."identitytype_id" IS NOT NULL AND deleted."identitytype_id" IS NULL)) or ((inserted."data" IS NOT NULL AND deleted."data" IS NOT NULL AND inserted."data"<>deleted."data") or (inserted."data" IS NULL AND deleted."data" IS NOT NULL) or (inserted."data" IS NOT NULL AND deleted."data" IS NULL)) or ((inserted."valid_from" IS NOT NULL AND deleted."valid_from" IS NOT NULL AND inserted."valid_from"<>deleted."valid_from") or (inserted."valid_from" IS NULL AND deleted."valid_from" IS NOT NULL) or (inserted."valid_from" IS NOT NULL AND deleted."valid_from" IS NULL)) or ((inserted."valid_until" IS NOT NULL AND deleted."valid_until" IS NOT NULL AND inserted."valid_until"<>deleted."valid_until") or (inserted."valid_until" IS NULL AND deleted."valid_until" IS NOT NULL) or (inserted."valid_until" IS NOT NULL AND deleted."valid_until" IS NULL)) or ((inserted."invalid" IS NOT NULL AND deleted."invalid" IS NOT NULL AND inserted."invalid"<>deleted."invalid") or (inserted."invalid" IS NULL AND deleted."invalid" IS NOT NULL) or (inserted."invalid" IS NOT NULL AND deleted."invalid" IS NULL)))
end
end
if (@NCT = 0) set nocount off
end
---- go
2025-08-18 09:03:03,032 INFO [server] [TriggerRouterService] [server-job-15] Cleaning up trigger hist row of 358 after failing to create trigger for ll2_identity
2025-08-18 09:03:03,032 ERROR [server] [TriggerRouterService] [server-job-15] Failed to create triggers for ll2_identity StackTraceKey.init [SqlException:3833551641] org.jumpmind.db.sql.SqlException: Der mehrteilige Bezeichner "inserted.identitytype_id" konnte nicht gebunden werden.
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:2535)
at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessary(TriggerRouterService.java:2411)
at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:2365)
at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTrigger(TriggerRouterService.java:2205)
at org.jumpmind.symmetric.service.impl.TriggerRouterService.syncTriggers(TriggerRouterService.java:2290)
at org.jumpmind.symmetric.common.ConfigurationChangedHelper.syncTriggers(ConfigurationChangedHelper.java:246)
at org.jumpmind.symmetric.common.ConfigurationChangedHelper.contextComplete(ConfigurationChangedHelper.java:218)
at org.jumpmind.symmetric.common.ConfigurationChangedHelper.contextCommittedAndComplete(ConfigurationChangedHelper.java:230)
at org.jumpmind.symmetric.route.ConfigurationChangedDataRouter.contextCommitted(ConfigurationChangedDataRouter.java:392)
at org.jumpmind.symmetric.service.impl.RouterService.completeBatchesAndCommit(RouterService.java:698)
at org.jumpmind.symmetric.service.impl.RouterService.routeDataForChannel(RouterService.java:650)
at org.jumpmind.symmetric.service.impl.RouterService.routeDataForEachChannel(RouterService.java:305)
at org.jumpmind.symmetric.service.impl.RouterService.routeData(RouterService.java:222)
at org.jumpmind.symmetric.job.RouterJob.doJob(RouterJob.java:47)
at org.jumpmind.symmetric.job.AbstractJob.invoke(AbstractJob.java:202)
at org.jumpmind.symmetric.job.AbstractJob.run(AbstractJob.java:268)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:358)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Der mehrteilige Bezeichner "inserted.identitytype_id" konnte nicht gebunden werden.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:270)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1735)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:929)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:823)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7745)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4391)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:276)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:796)
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)
... 25 more
2025-08-18 09:03:03,047 INFO [server] [MsSql2016SymmetricDialect] [server-job-15] Dropping SYM_ON_I_FOR_LL2_DNTTY_SRVR trigger for ll2_identity
2025-08-18 09:03:03,047 INFO [server] [MsSql2016SymmetricDialect] [server-job-15] Dropping SYM_ON_D_FOR_LL2_DNTTY_SRVR trigger for ll2_identity
2025-08-18 09:03:03,047 INFO [server] [MsSql2016SymmetricDialect] [server-job-15] Dropping SYM_ON_U_FOR_LL2_DNTTY_SRVR trigger for ll2_identity
2025-08-18 09:03:03,047 INFO [server] [TriggerRouterService] [server-job-15] DropTriggers: it took 15 ms to drop triggers for trigger ID ll2_identity
2025-08-18 09:03:03,047 INFO [server] [TriggerRouterService] [server-job-15] SyncTriggers: fix multiple active trigger histories took 0 ms |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2025-08-18 07:12 | klippit | New Issue | |
| 2025-08-18 07:12 | klippit | Tag Attached: sync-trigger | |
| 2025-08-18 07:12 | klippit | Tag Attached: trigger | |
| 2025-08-18 07:12 | klippit | Tag Attached: upgrade | |
| 2025-08-18 07:12 | klippit | File Added: symmetric-error.log | |
| 2025-08-20 17:30 | mdrouard | Status | new => acknowledged |
| 2025-08-20 17:30 | mdrouard | Relationship added | related to 0004201 |