View Issue Details

IDProjectCategoryView StatusLast Update
0002109SymmetricDSBugpublic2015-01-06 17:27
ReporterspadalkarAssigned Tochenson 
PrioritynormalSeveritycrashReproducibilityalways
Status closedResolutionfixed 
Product Version3.6.15 
Target Version3.7.0Fixed in Version3.7.0 
Summary0002109: Trigger creation fails on Sybase ASE database for CLOB,BLOB,BINARY,VARBINARY,LONGBINARY
DescriptionWhenever SymmetricDS is configured to use Sybase ASE (my setup is 16.0) any trigger creation for tables fails for following datatypes

case Types.CLOB:
case Types.BLOB:
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
case Types.OTHER:
Steps To ReproduceCreate table with any of above datatype e.g:

create table test_table (name binary(16));

Try to setup sync for this table on Sybase ASE. Exception would be thrown,


org.jumpmind.db.sql.SqlException: SQL Anywhere Error -131: Syntax error near 'max' on line 1
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:288)


Additional InformationIn AseTriggerTemplate.java file we need to remove "max" word and put actual MAXIMUM value 32767.

spadalka@wolverine:/media/sourcefire/symmetricds/branches/3.6$ svn diff
Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java
===================================================================
--- symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java (revision 9086)
+++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java (working copy)
@@ -233,7 +233,7 @@
                     text += "bit\n";
                     break;
                 case Types.CLOB:
- text += "varchar(max)\n";
+ text += "varchar(32767)\n";
                     break;
                 case Types.BLOB:
                 case Types.BINARY:
@@ -240,10 +240,10 @@
                 case Types.VARBINARY:
                 case Types.LONGVARBINARY:
                 case -10: // SQL-Server ntext binary type
- text += "varbinary(max)\n";
+ text += "varbinary(32767)\n";
                     break;
                 case Types.OTHER:
- text += "varbinary(max)\n";
+ text += "varbinary(32767)\n";
                     break;
                 default:
TagsNo tags attached.

Activities

chenson

2014-12-22 11:05

administrator   ~0000639

Is this when a binary field is part of the pk?

spadalkar

2014-12-22 15:29

reporter   ~0000640

Yes this fails when field is part for PRIMARY KEY.

spadalkar

2015-01-04 21:09

reporter   ~0000644

On 3.6.17 this issue is reproducible. SQL Anywhere Error -131: Syntax error near 'max' on line due to "declare @newpk0 varbinary(max)". I am not sure from where this entry is being generated.

Extra Info : server node (corp-000) is Sybase & client node (store-001) is MYSQL.

[corp-000] - SqlAnywhereSymmetricDialect - Failed to create trigger: create trigger SYM_ON_I_FOR_PET_CNTRL_NF__CORP on "vms"."DBA"."pet_control_info" for insert as begin declare @DataRow varchar(16384) declare @newpk0 varbinary(max)
                                                                                                                                                                              declare @ChannelId varchar(20) if ("vms"."DBA". sym_triggers_disabled(0) = 0) begin declare DataCursor cursor for select
          case when orig."pet_uuid" is null then '' else '"' + base64_encode(orig."pet_uuid") + '"' end+','+
          case when inserted."pet_name" is null then '' else '"' + replace(replace(inserted."pet_name",'\','\\'),'"','\"') + '"' end+','+
          case when orig."parent_uuid" is null then '' else '"' + base64_encode(orig."parent_uuid") + '"' end+','+
          case when inserted."pet_num" is null then '' else ('"' + cast(inserted."pet_num" as varchar) + '"') end+','+
          case when inserted."pet_limit" is null then '' else ('"' + cast(inserted."pet_limit" as varchar) + '"') end+','+
          case when inserted."deleted" is null then '' else ('"' + cast(inserted."deleted" as varchar) + '"') end ,inserted."pet_uuid", 'sybase_to_mysql' from inserted inner join "vms"."DBA". "pet_control_info" orig on orig."pet_uuid"=inserted."pet_uuid" where 1=1 open DataCursor fetch next DataCursor into @DataRow ,@newpk0, @ChannelId while @@ FETCH_STATUS = 0 begin insert into "vms"."DBA".sym_data (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) values('pet_control_info','I', 15, @DataRow, @ChannelId, vms."DBA".sym_txid(0), "vms"."DBA".sym_node_disabled(0), null, getdate()) fetch next DataCursor into @DataRow ,@newpk0, @ ChannelId end close DataCursor deallocate DataCursor end end
2015-01-05 01:16:34,779 WARN [corp-000] [TriggerRouterService] [symmetric-engine-startup-1] Cleaning up trigger hist row of 15 after failing to create the associated trigger
[corp-000] - TriggerRouterService - Cleaning up trigger hist row of 15 after failing to create the associated trigger
2015-01-05 01:16:34,782 ERROR [corp-000] [TriggerRouterService] [symmetric-engine-startup-1]


Failed to create triggers for pet_control_info
org.jumpmind.db.sql.SqlException: SQL Anywhere Error -131: Syntax error near 'max' on line 1
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:288)
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:279)
    at org.jumpmind.db.sql.JdbcSqlTransaction.executeCallback(JdbcSqlTransaction.java:338)
    at org.jumpmind.db.sql.JdbcSqlTransaction.execute(JdbcSqlTransaction.java:262)
    at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createTrigger(AbstractSymmetricDialect.java:317)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService.rebuildTriggerIfNecessary(TriggerRouterService.java:1639)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:1509)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTrigger(TriggerRouterService.java:1389)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService.updateOrCreateDatabaseTriggers(TriggerRouterService.java:1379)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService.syncTriggers(TriggerRouterService.java:1099)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService.syncTriggers(TriggerRouterService.java:1046)
    at org.jumpmind.symmetric.service.impl.TriggerRouterService.syncTriggers(TriggerRouterService.java:1042)
    at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:533)
    at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:499)
    at org.jumpmind.symmetric.web.SymmetricEngineHolder$EngineStarter.run(SymmetricEngineHolder.java:440)

Related Changesets

SymmetricDS: master d2710559

2014-12-22 16:00:00

chenson

Details Diff
0002109: Trigger creation fails on Sybase ASE database for CLOB,BLOB,BINARY,VARBINARY,LONGBINARY
0002109
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java Diff File

SymmetricDS: master 5e7efa35

2015-01-06 08:42:11

chenson

Details Diff
0002109: Trigger creation fails on Sybase ASE database for CLOB,BLOB,BINARY,VARBINARY,LONGBINARY
0002109
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereTriggerTemplate.java Diff File

Issue History

Date Modified Username Field Change
2014-12-19 20:27 spadalkar New Issue
2014-12-22 11:05 chenson Note Added: 0000639
2014-12-22 15:29 spadalkar Note Added: 0000640
2014-12-22 16:00 chenson Changeset attached => SymmetricDS trunk r9099
2014-12-22 16:02 chenson Status new => resolved
2014-12-22 16:02 chenson Fixed in Version => 3.6.17
2014-12-22 16:02 chenson Resolution open => fixed
2014-12-22 16:02 chenson Assigned To => chenson
2014-12-22 16:02 chenson Target Version => 3.6.17
2014-12-22 17:00 chenson Changeset attached => SymmetricDS 3.6 r9100
2014-12-26 08:37 chenson Status resolved => closed
2015-01-04 21:09 spadalkar Note Added: 0000644
2015-01-04 21:09 spadalkar Status closed => feedback
2015-01-04 21:09 spadalkar Resolution fixed => reopened
2015-01-06 08:42 chenson Fixed in Version 3.6.17 => 3.7.0
2015-01-06 08:42 chenson Target Version 3.6.17 => 3.7.0
2015-01-06 08:42 chenson Status feedback => resolved
2015-01-06 08:42 chenson Resolution reopened => fixed
2015-01-06 09:00 chenson Changeset attached => SymmetricDS trunk r9237
2015-01-06 17:27 chenson Status resolved => closed
2015-07-30 21:49 chenson Changeset attached => SymmetricDS master 5e7efa35
2015-07-30 21:49 chenson Changeset attached => SymmetricDS master d2710559