View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0002109 | SymmetricDS | Bug | public | 2014-12-20 01:27 | 2015-01-06 22:27 |
| Reporter | spadalkar | Assigned To | chenson | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.6.15 | ||||
| Target Version | 3.7.0 | Fixed in Version | 3.7.0 | ||
| Summary | 0002109: Trigger creation fails on Sybase ASE database for CLOB,BLOB,BINARY,VARBINARY,LONGBINARY | ||||
| Description | Whenever 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 Reproduce | Create 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 Information | In 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: | ||||
| Tags | No tags attached. | ||||
|
|
Is this when a binary field is part of the pk? |
|
|
Yes this fails when field is part for PRIMARY KEY. |
|
|
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) |
|
SymmetricDS: master d2710559 2014-12-22 16:00:00 Details Diff |
0002109: Trigger creation fails on Sybase ASE database for CLOB,BLOB,BINARY,VARBINARY,LONGBINARY |
Affected Issues 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 Details Diff |
0002109: Trigger creation fails on Sybase ASE database for CLOB,BLOB,BINARY,VARBINARY,LONGBINARY |
Affected Issues 0002109 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereTriggerTemplate.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2014-12-20 01:27 | spadalkar | New Issue | |
| 2014-12-22 16:05 | chenson | Note Added: 0000639 | |
| 2014-12-22 20:29 | spadalkar | Note Added: 0000640 | |
| 2014-12-22 21:00 | chenson | Changeset attached | => SymmetricDS trunk r9099 |
| 2014-12-22 21:02 | chenson | Status | new => resolved |
| 2014-12-22 21:02 | chenson | Fixed in Version | => 3.6.17 |
| 2014-12-22 21:02 | chenson | Resolution | open => fixed |
| 2014-12-22 21:02 | chenson | Assigned To | => chenson |
| 2014-12-22 21:02 | chenson | Target Version | => 3.6.17 |
| 2014-12-22 22:00 | chenson | Changeset attached | => SymmetricDS 3.6 r9100 |
| 2014-12-26 13:37 | chenson | Status | resolved => closed |
| 2015-01-05 02:09 | spadalkar | Note Added: 0000644 | |
| 2015-01-05 02:09 | spadalkar | Status | closed => feedback |
| 2015-01-05 02:09 | spadalkar | Resolution | fixed => reopened |
| 2015-01-06 13:42 | chenson | Fixed in Version | 3.6.17 => 3.7.0 |
| 2015-01-06 13:42 | chenson | Target Version | 3.6.17 => 3.7.0 |
| 2015-01-06 13:42 | chenson | Status | feedback => resolved |
| 2015-01-06 13:42 | chenson | Resolution | reopened => fixed |
| 2015-01-06 14:00 | chenson | Changeset attached | => SymmetricDS trunk r9237 |
| 2015-01-06 22:27 | chenson | Status | resolved => closed |
| 2015-07-31 01:49 | chenson | Changeset attached | => SymmetricDS master 5e7efa35 |
| 2015-07-31 01:49 | chenson | Changeset attached | => SymmetricDS master d2710559 |