View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005877SymmetricDSBugpublic2023-06-13 11:032023-06-23 07:49
Reporterachintya.de@uk.daiwacm.com Assigned Topmarzullo  
Priorityhigh 
Status assignedResolutionopen 
Product Version3.14.7 
Summary0005877: Failing to create SYM_ON_I_% trigger throwing "Length or precision specification 32767 is not within the range of 1 to 16384"
Descriptionhi all,
I was setting up a SymDS replication for a database from Sybase To SQL Server but due to some reason SymDS process is failing to create a SYM_ON_I_ trigger on a particular table.

Ive setup SymDS replication for quite a few databases where a similar type table exists but all worked well so far in terms of SYM_ON triggers creation

The log file shows the trigger sql it was trying to create , it has declared/defined a variable called
declare @newpk28 varbinary(32767)

and in the end it couldnt create the trigger with a SQL Exception:

2023-06-07 00:00:08,775 ERROR [Node Name] [TriggerRouterService] [node_name-sync-triggers-1] Failed to create triggers for trade StackTraceKey.init [SqlException:1184073736] org.jumpmind.db.sql.SqlException: Length or precision specification 32767 is not within the range of 1 to 16384

the total column length of that table is : 2189
the table has: 5 indexes

please let me know whether any one of you has encountered similar issue and if so what action did you to rectify it

many thanks

Achintya
Steps To ReproduceI tried again and its the same error for the same table
TagsNo tags attached.

Activities

achintya.de@uk.daiwacm.com

2023-06-13 11:03

reporter  

sym_on_trigger_creation_error.txt (16,704 bytes)    
Error while creating bd_trading_data.trade table SYM trigger
2023-06-07 00:00:08,712 INFO [CBFP_LN_BTD] [AseSymmetricDialect] [cbfp_ln_btd-sync-triggers-1] Creating SYM_ON_I_FOR_TRD_CBFP_LN_BTD trigger for bd_trading_data.dbo.trade
2023-06-07 00:00:08,752 INFO [CBFP_LN_BTD] [AseSymmetricDialect] [cbfp_ln_btd-sync-triggers-1] Failed to create trigger: create trigger SYM_ON_I_FOR_TRD_CBFP_LN_BTD on bd_trading_data.dbo.trade for insert order 1 as
                                begin                                                                                                                                                                  
                                  set nocount on      
                                  declare @txid varchar(50)             
                                  if (@@TRANCOUNT > 0) begin                                                                                                                                         
                                      select @txid = bintostr(xactkey) from master.dbo.systransactions where spid = @@spid                              
                                  end                                                                                                                                                                
                                  declare @clientapplname varchar(50)  
                                  declare @clientname varchar(50)    
                                  select @clientapplname = clientapplname, @clientname = case when clientapplname = 'SymmetricDS' then clientname else null end from master.dbo.sysprocesses where spid = @@spid     
                                  declare @DataRow varchar(16384)  
                                  declare @ChannelId varchar(128)   
                                  declare @newpk0 NUMERIC(18,0)
declare @newpk1 varchar(1000)
declare @newpk2 NUMERIC(18,0)
declare @newpk3 NUMERIC(18,0)
declare @newpk4 NUMERIC(18,0)
declare @newpk5 datetime
declare @newpk6 NUMERIC(18,0)
declare @newpk7 NUMERIC(18,0)
declare @newpk8 NUMERIC(18,0)
declare @newpk9 NUMERIC(18,0)
declare @newpk10 datetime
declare @newpk11 datetime
declare @newpk12 NUMERIC(18,0)
declare @newpk13 varchar(1000)
declare @newpk14 varchar(1000)
declare @newpk15 varchar(1000)
declare @newpk16 NUMERIC(18,0)
declare @newpk17 NUMERIC(18,0)
declare @newpk18 varchar(1000)
declare @newpk19 NUMERIC(18,0)
declare @newpk20 varchar(1000)
declare @newpk21 NUMERIC(18,0)
declare @newpk22 NUMERIC(18,0)
declare @newpk23 NUMERIC(18,0)
declare @newpk24 NUMERIC(18,0)
declare @newpk25 varchar(1000)
declare @newpk26 NUMERIC(18,0)
declare @newpk27 NUMERIC(18,0)
declare @newpk28 varbinary(32767)
declare @newpk29 varchar(1000)
declare @newpk30 NUMERIC(18,0)
declare @newpk31 datetime
declare @newpk32 NUMERIC(18,0)
declare @newpk33 NUMERIC(18,0)
declare @newpk34 NUMERIC(18,0)
declare @newpk35 NUMERIC(18,0)
declare @newpk36 NUMERIC(18,0)
declare @newpk37 varchar(1000)
                                                                                                                                            
                                   
                                  if (@clientapplname <> 'SymmetricDS') begin                                                                                                                           
                                    declare DataCursor cursor for                                                                                                                                      
                                                                                                                                                                        
                                       select 
          case when inserted.id is null then null else ('"' + convert(varchar,inserted.id) + '"') end+','+
          case when inserted.contract is null then null else '"' + str_replace(str_replace(inserted.contract,'\','\\'),'"','\"') + '"' end+','+
          case when inserted.revno is null then null else ('"' + convert(varchar,inserted.revno) + '"') end+','+
          case when inserted.state is null then null else ('"' + convert(varchar,inserted.state) + '"') end+','+
          case when inserted.spn is null then null else ('"' + convert(varchar,inserted.spn) + '"') end+','+
          case when inserted.time_entered is null then null else ('"' + str_replace(convert(varchar,inserted.time_entered,102),'.','-') + ' ' + right('00'+convert(varchar,datepart(HOUR,inserted.time_entered)),2)+':'+right('00'+convert(varchar,datepart(MINUTE,inserted.time_entered)),2)+':'+right('00'+convert(varchar,datepart(SECOND,inserted.time_entered)),2)+'.'+right('000'+convert(varchar,datepart(MILLISECOND,inserted.time_entered)),3) + '"') end+','+
          case when inserted.user_entered is null then null else ('"' + convert(varchar,inserted.user_entered) + '"') end+','+
          case when inserted.book is null then null else ('"' + convert(varchar,inserted.book) + '"') end+','+
          case when inserted.cparty is null then null else ('"' + convert(varchar,inserted.cparty) + '"') end+','+
          case when inserted.contact is null then null else ('"' + convert(varchar,inserted.contact) + '"') end+','+
          case when inserted.trade_time is null then null else ('"' + str_replace(convert(varchar,inserted.trade_time,102),'.','-') + ' ' + right('00'+convert(varchar,datepart(HOUR,inserted.trade_time)),2)+':'+right('00'+convert(varchar,datepart(MINUTE,inserted.trade_time)),2)+':'+right('00'+convert(varchar,datepart(SECOND,inserted.trade_time)),2)+'.'+right('000'+convert(varchar,datepart(MILLISECOND,inserted.trade_time)),3) + '"') end+','+
          case when inserted.value_date is null then null else ('"' + str_replace(convert(varchar,inserted.value_date,102),'.','-') + ' ' + right('00'+convert(varchar,datepart(HOUR,inserted.value_date)),2)+':'+right('00'+convert(varchar,datepart(MINUTE,inserted.value_date)),2)+':'+right('00'+convert(varchar,datepart(SECOND,inserted.value_date)),2)+'.'+right('000'+convert(varchar,datepart(MILLISECOND,inserted.value_date)),3) + '"') end+','+
          case when inserted.initiate is null then null else ('"' + convert(varchar,inserted.initiate) + '"') end+','+
          case when inserted.fees is null then null else '"' + str_replace(str_replace(inserted.fees,'\','\\'),'"','\"') + '"' end+','+
          case when inserted.instructions is null then null else '"' + str_replace(str_replace(inserted.instructions,'\','\\'),'"','\"') + '"' end+','+
          case when inserted.comment is null then null else '"' + str_replace(str_replace(inserted.comment,'\','\\'),'"','\"') + '"' end+','+
          case when inserted.entity is null then null else ('"' + convert(varchar,inserted.entity) + '"') end+','+
          case when inserted.agent is null then null else ('"' + convert(varchar,inserted.agent) + '"') end+','+
          case when inserted.execution_id is null then null else '"' + str_replace(str_replace(inserted.execution_id,'\','\\'),'"','\"') + '"' end+','+
          case when inserted.executor_entity is null then null else ('"' + convert(varchar,inserted.executor_entity) + '"') end+','+
          case when inserted.fx_cur is null then null else '"' + str_replace(str_replace(inserted.fx_cur,'\','\\'),'"','\"') + '"' end+','+
          case when inserted.exchange_id is null then null else ('"' + convert(varchar,inserted.exchange_id) + '"') end+','+
          case when inserted.trader_id is null then null else ('"' + convert(varchar,inserted.trader_id) + '"') end+','+
          case when inserted.order_id is null then null else ('"' + convert(varchar,inserted.order_id) + '"') end+','+
          case when inserted.trade_type is null then null else ('"' + convert(varchar,inserted.trade_type) + '"') end+','+
          case when inserted.allocated_from is null then null else '"' + str_replace(str_replace(inserted.allocated_from,'\','\\'),'"','\"') + '"' end+','+
          case when inserted.organization_id is null then null else ('"' + convert(varchar,inserted.organization_id) + '"') end+','+
          case when inserted.broker_id is null then null else ('"' + convert(varchar,inserted.broker_id) + '"') end+','+
          case when inserted.timestamp is null then null else '"' + bintostr(convert(varbinary(16384),inserted.timestamp)) + '"' end+','+
          case when inserted.method is null then null else '"' + str_replace(str_replace(inserted.method,'\','\\'),'"','\"') + '"' end+','+
          case when inserted.trade_amend_id is null then null else ('"' + convert(varchar,inserted.trade_amend_id) + '"') end+','+
          case when inserted.time_adjusted is null then null else ('"' + str_replace(convert(varchar,inserted.time_adjusted,102),'.','-') + ' ' + right('00'+convert(varchar,datepart(HOUR,inserted.time_adjusted)),2)+':'+right('00'+convert(varchar,datepart(MINUTE,inserted.time_adjusted)),2)+':'+right('00'+convert(varchar,datepart(SECOND,inserted.time_adjusted)),2)+'.'+right('000'+convert(varchar,datepart(MILLISECOND,inserted.time_adjusted)),3) + '"') end+','+
          case when inserted.inst_currency_spn is null then null else ('"' + convert(varchar,inserted.inst_currency_spn) + '"') end+','+
          case when inserted.value_currency_spn is null then null else ('"' + convert(varchar,inserted.value_currency_spn) + '"') end+','+
          case when inserted.priceType is null then null else ('"' + convert(varchar,inserted.priceType) + '"') end+','+
          case when inserted.strategy_id is null then null else ('"' + convert(varchar,inserted.strategy_id) + '"') end+','+
          case when inserted.b2b_id is null then null else ('"' + convert(varchar,inserted.b2b_id) + '"') end+','+
          case when inserted.b2b_parent is null then null else '"' + str_replace(str_replace(inserted.b2b_parent,'\','\\'),'"','\"') + '"' end+','+
          case when inserted.quantity is null then null else ('"' + convert(varchar,inserted.quantity) + '"') end+','+
          case when inserted.price is null then null else ('"' + convert(varchar,inserted.price) + '"') end+','+
          case when inserted.fx_rate is null then null else ('"' + convert(varchar,inserted.fx_rate) + '"') end+','+
          case when inserted.priceMult is null then null else ('"' + convert(varchar,inserted.priceMult) + '"') end+','+
          case when inserted.quantMult is null then null else ('"' + convert(varchar,inserted.quantMult) + '"') end+','+
          case when inserted.tickunit is null then null else ('"' + convert(varchar,inserted.tickunit) + '"') end+','+
          case when inserted.value is null then null else ('"' + convert(varchar,inserted.value) + '"') end ,inserted.id,inserted.contract,inserted.revno,inserted.state,inserted.spn,inserted.time_entered,inserted.user_entered,inserted.book,inserted.cparty,inserted.contact,inserted.trade_time,inserted.value_date,inserted.initiate,inserted.fees,inserted.instructions,inserted.comment,inserted.entity,inserted.agent,inserted.execution_id,inserted.executor_entity,inserted.fx_cur,inserted.exchange_id,inserted.trader_id,inserted.order_id,inserted.trade_type,inserted.allocated_from,inserted.organization_id,inserted.broker_id,inserted.timestamp,inserted.method,inserted.trade_amend_id,inserted.time_adjusted,inserted.inst_currency_spn,inserted.value_currency_spn,inserted.priceType,inserted.strategy_id,inserted.b2b_id,inserted.b2b_parent, 'bd_trading_data' from inserted where 1=1                                                                                  
                                                                                                                                                                         
                                       open DataCursor                                                                                                                                                 
                                       fetch DataCursor into @DataRow ,@newpk0, @newpk1, @newpk2, @newpk3, @newpk4, @newpk5, @newpk6, @newpk7, @newpk8, @newpk9, @newpk10, @newpk11, @newpk12, @newpk13, @newpk14, @newpk15, @newpk16, @newpk17, @newpk18, @newpk19, @newpk20, @newpk21, @newpk22, @newpk23, @newpk24, @newpk25, @newpk26, @newpk27, @newpk28, @newpk29, @newpk30, @newpk31, @newpk32, @newpk33, @newpk34, @newpk35, @newpk36, @newpk37, @ChannelId                                                                                                     
                                       while @@sqlstatus = 0 begin                                                                                                                                  
                                           insert into bd_trading_data.dbo.sym_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) 
                                             values('trade','I', 41, @DataRow, @ChannelId, @txid, @clientname, null, getdate())                                   
                                           fetch DataCursor into @DataRow ,@newpk0, @newpk1, @newpk2, @newpk3, @newpk4, @newpk5, @newpk6, @newpk7, @newpk8, @newpk9, @newpk10, @newpk11, @newpk12, @newpk13, @newpk14, @newpk15, @newpk16, @newpk17, @newpk18, @newpk19, @newpk20, @newpk21, @newpk22, @newpk23, @newpk24, @newpk25, @newpk26, @newpk27, @newpk28, @newpk29, @newpk30, @newpk31, @newpk32, @newpk33, @newpk34, @newpk35, @newpk36, @newpk37, @ChannelId                                                                                                 
                                       end                                                                                                                                                             
                                       close DataCursor                                                                                                                                                
                                       deallocate cursor DataCursor                                                                                                                                           
                                  end                                                                                                                                                                  
                                   
                                  set nocount off      
                                end                                                                                                                                                                    
2023-06-07 00:00:08,753 INFO [CBFP_LN_BTD] [TriggerRouterService] [cbfp_ln_btd-sync-triggers-1] Cleaning up trigger hist row of 41 after failing to create the associated trigger
2023-06-07 00:00:08,775 ERROR [CBFP_LN_BTD] [TriggerRouterService] [cbfp_ln_btd-sync-triggers-1] Failed to create triggers for trade StackTraceKey.init [SqlException:1184073736] org.jumpmind.db.sql.SqlException: Length or precision specification 32767 is not within the range of 1 to 16384.

	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:393)
	at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessary(TriggerRouterService.java:2390)
	at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:2231)
	at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTrigger(TriggerRouterService.java:2083)
	at org.jumpmind.symmetric.service.impl.TriggerRouterService$2.run(TriggerRouterService.java:1937)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Length or precision specification 32767 is not within the range of 1 to 16384.
sym_on_trigger_creation_error.txt (16,704 bytes)   

pmarzullo

2023-06-14 17:24

developer   ~0002339

reference 0002109 for original change.
sql anywhere uses 32767 for max length.
sybase ase uses 16384 for max length.

achintya.de@uk.daiwacm.com

2023-06-15 08:05

reporter   ~0002341

thanks pmarzullo
so, what do you suggest to overcome this issue ?
schema change on Sybase side ?
ive found that the table has a column of varbinary datatype, i take that's causing this issue ?
any comment to resolve the issue will be appreciated

pmarzullo

2023-06-21 17:27

developer   ~0002343

The code will be changed in the future.

For now you can change the varbinary column data type to something else.

achintya.de@uk.daiwacm.com

2023-06-23 07:49

reporter   ~0002344

thanks but as a daily SymDS internal scheduler type process has managed to create the triggers of that table and it is replicating as expected without any schema change was required
even i created the table in another test database and setit up and it created the trigger there also, so, may be some kind of bug etc??
so, this call can be closed now and thanks again

Issue History

Date Modified Username Field Change
2023-06-13 11:03 achintya.de@uk.daiwacm.com New Issue
2023-06-13 11:03 achintya.de@uk.daiwacm.com File Added: sym_on_trigger_creation_error.txt
2023-06-14 17:24 pmarzullo Assigned To => pmarzullo
2023-06-14 17:24 pmarzullo Status new => acknowledged
2023-06-14 17:24 pmarzullo Note Added: 0002339
2023-06-15 08:05 achintya.de@uk.daiwacm.com Note Added: 0002341
2023-06-21 17:27 pmarzullo Status acknowledged => feedback
2023-06-21 17:27 pmarzullo Note Added: 0002343
2023-06-23 07:49 achintya.de@uk.daiwacm.com Note Added: 0002344
2023-06-23 07:49 achintya.de@uk.daiwacm.com Status feedback => assigned