View Issue Details

IDProjectCategoryView StatusLast Update
0003171SymmetricDSBugpublic2020-06-24 20:39
Reporterrossenzahariev Assigned ToJJ_Starrett  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.8.26 
Target Version3.11.10Fixed in Version3.11.10 
Summary0003171: Trigger Rebuild: custom_before_insert_text is ignored when rebuilding triggers
DescriptionI am working on building a migration from MSSQL to MYSQL.
As mention here:
http://www.symmetricds.org/issues/view.php?id=2457
and here http://www.symmetricds.org/issues/view.php?id=2458

there is an issue with the identity being lost on inserts. To work around the issue it should be possible to create custom before and after insert-trigger SQL in order to restore the value, so that it is properly returned.

The fields in the sym_trigger are available.

But though I have set all triggers to have:


UPDATE gp_dev.dbo.sym_trigger SET custom_before_insert_text = 'DECLARE @BackupIdentitySeederFunc VARCHAR(1000); SET @BackupIdentitySeederFunc =''DECLARE @BackupIdentity TABLE(IdentityID INT IDENTITY(''+CAST(@@IDENTITY AS VARCHAR)+'', 1)) INSERT @BackupIdentity DEFAULT VALUES'';'

UPDATE gp_dev.dbo.sym_trigger SET custom_on_insert_text = 'EXEC(@BackupIdentitySeederFunc)'

And have rebuild the triggers, the triggers will not get created.

The server reports an issue:

2017-06-29 18:22:01,334 INFO [server] [MsSqlSymmetricDialect] [server-sync-triggers-2] Failed to create trigger: create trigger SYM_ON_I_FOR_T_DSTRBTRS_SRVR on "gp_dev"."dbo"."t_distributors" 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 ("gp_dev".dbo.sym_triggers_disabled() = 0) begin
insert into "gp_dev"."dbo".sym_data
               (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)
select 't_distributors','I', 2015,
case when inserted."ID" is null then '' else ('"' + convert(varchar(40), inserted."ID",2) + '"') end+','+
case when inserted."address_1" is null then '' else '"' + replace(replace(convert(varchar(100),inserted."address_1") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."address_2" is null then '' else '"' + replace(replace(convert(varchar(100),inserted."address_2") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."city" is null then '' else '"' + replace(replace(convert(varchar(100),inserted."city") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."country" is null then '' else ('"' + convert(varchar(40), inserted."country",2) + '"') end+','+
case when inserted."company" is null then '' else '"' + replace(replace(convert(varchar(100),inserted."company") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."email" is null then '' else '"' + replace(replace(convert(varchar(100),inserted."email") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."firstname" is null then '' else '"' + replace(replace(convert(varchar(100),inserted."firstname") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."homepage" is null then '' else '"' + replace(replace(convert(varchar(100),inserted."homepage") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."lastname" is null then '' else '"' + replace(replace(convert(varchar(100),inserted."lastname") ,'\','\\'),'"','\"') + '"' end+','+
case when orig."notes" is null then '' else '"' + replace(replace(cast(orig."notes" as varchar(max)),'\','\\'),'"','\"') + '"' end+','+
case when orig."phone" is null then '' else '"' + replace(replace(cast(orig."phone" as varchar(max)),'\','\\'),'"','\"') + '"' end+','+
case when inserted."title" is null then '' else ('"' + convert(varchar(40), inserted."title",2) + '"') end+','+
case when inserted."zip" is null then '' else '"' + replace(replace(convert(varchar(50),inserted."zip") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."address_id" is null then '' else ('"' + convert(varchar(40), inserted."address_id",2) + '"') end+','+
case when inserted."currency_id" is null then '' else ('"' + convert(varchar(40), inserted."currency_id",2) + '"') end+','+
case when inserted."import_costs_share" is null then '' else ('"' + convert(varchar(40), inserted."import_costs_share",2) + '"') end+','+
case when inserted."responsible_person_id" is null then '' else ('"' + convert(varchar(40), inserted."responsible_person_id",2) + '"') end,
'default', @TransactionId, "gp_dev".dbo.sym_node_disabled(), null, current_timestamp

from inserted inner join "gp_dev"."dbo"."t_distributors" orig on orig."ID"=inserted."ID"

where 1=1
end
EXEC (@BackupIdentitySeederFunc)
if (@NCT = 0) set nocount off
end
---- go


It seems rebuilding is inserting the second part (EXEC...) but not the "before" part and so the creation of the trigger fails.

This is reproducible on all tables.

If unset the custom fields, the triggers are created properly, but obviously there is an issue with the ID returned.

Furthermore, it is not possible to set the "before" value from the WEB UI, which I am using.
Steps To Reproduce1. Set the custom trigger fields:


UPDATE gp_dev.dbo.sym_trigger SET custom_before_insert_text = 'DECLARE @BackupIdentitySeederFunc VARCHAR(1000); SET @BackupIdentitySeederFunc =''DECLARE @BackupIdentity TABLE(IdentityID INT IDENTITY(''+CAST(@@IDENTITY AS VARCHAR)+'', 1)) INSERT @BackupIdentity DEFAULT VALUES'';'

UPDATE gp_dev.dbo.sym_trigger SET custom_on_insert_text = 'EXEC(@BackupIdentitySeederFunc)'


2. Rebuild the triggers via the WEB UI
Tagsdialect: sql-server, trigger

Relationships

related to 0004452 closedJJ_Starrett Trigger Rebuild: custom_before_insert_text is ignored when rebuilding triggers 

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.11 afd414ca

2020-06-23 11:41:05

JJ_Starrett

Details Diff
0003171: Trigger Rebuild: custom_before_insert_text is ignored when rebuilding triggers

Added functionality to triggerMapper to look for the three "before" columns
Affected Issues
0003171
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterService.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterServiceSqlMap.java Diff File

Issue History

Date Modified Username Field Change
2017-06-29 16:28 rossenzahariev New Issue
2019-04-24 15:21 elong Tag Attached: trigger
2019-04-24 15:21 elong Tag Attached: dialect: sql-server
2020-06-23 15:38 JJ_Starrett Assigned To => JJ_Starrett
2020-06-23 15:38 JJ_Starrett Status new => assigned
2020-06-23 15:42 JJ_Starrett Status assigned => resolved
2020-06-23 15:42 JJ_Starrett Resolution open => fixed
2020-06-23 15:42 JJ_Starrett Fixed in Version => 3.11.10
2020-06-23 15:42 JJ_Starrett Target Version => 3.11.10
2020-06-23 16:00 JJ_Starrett Changeset attached => SymmetricDS 3.11 afd414ca
2020-06-24 20:06 JJ_Starrett Issue cloned: 0004452
2020-06-24 20:06 JJ_Starrett Relationship added related to 0004452
2020-06-24 20:39 admin Status resolved => closed