View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0004452SymmetricDSBugpublic2020-06-24 20:062020-06-24 20:47
ReporterJJ_Starrett Assigned ToJJ_Starrett  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.8.26 
Target Version3.12.1Fixed in Version3.12.1 
Summary0004452: 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
TagsNo tags attached.

Relationships

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

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2020-06-24 20:06 JJ_Starrett New Issue
2020-06-24 20:06 JJ_Starrett Status new => assigned
2020-06-24 20:06 JJ_Starrett Assigned To => JJ_Starrett
2020-06-24 20:06 JJ_Starrett Issue generated from: 0003171
2020-06-24 20:06 JJ_Starrett Relationship added related to 0003171
2020-06-24 20:07 JJ_Starrett Status assigned => resolved
2020-06-24 20:07 JJ_Starrett Resolution open => fixed
2020-06-24 20:07 JJ_Starrett Fixed in Version => 3.12.1
2020-06-24 20:47 admin Status resolved => closed