View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003171 | SymmetricDS | Bug | public | 2017-06-29 16:28 | 2020-06-24 20:39 |
| Reporter | rossenzahariev | Assigned To | JJ_Starrett | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.8.26 | ||||
| Target Version | 3.11.10 | Fixed in Version | 3.11.10 | ||
| Summary | 0003171: Trigger Rebuild: custom_before_insert_text is ignored when rebuilding triggers | ||||
| Description | I 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 Reproduce | 1. 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 | ||||
| Tags | dialect: sql-server, trigger | ||||
| related to | 0004452 | closed | JJ_Starrett | Trigger Rebuild: custom_before_insert_text is ignored when rebuilding triggers |
|
SymmetricDS: 3.11 afd414ca 2020-06-23 11:41:05 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 | ||
| 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 |