View Issue Details

IDProjectCategoryView StatusLast Update
0002232SymmetricDSBugpublic2015-03-17 16:58
Reporterspadalkar Assigned Tochenson  
Priorityhigh 
Status closedResolutionfixed 
Product Version3.7.9 
Target Version3.7.10Fixed in Version3.7.10 
Summary0002232: Insert fails due to error in Trigger
DescriptionWhenever we setup SymmetricDS 3.7.9 for tables like shown below; we can not insert data to source table.

Insert fails due to invalid trigger.

DBD::SQLAnywhere::st execute failed: Correlation name 'orig' not found (DBD: execute failed)


mysql> desc test_car;
+--------------------+------------------+------+-----+------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+------------------+-------+
| car_uuid | binary(16) | NO | | | |
| car_vin_uuid | binary(16) | NO | | | |
| car_builder_uuid | binary(16) | YES | | NULL | |
| car_product_time | int(10) unsigned | NO | | 0 | |
+--------------------+------------------+------+-----+------------------+-------+
Steps To Reproduce1. Create table in Sybase SQL Anywhere & Mysql

CREATE TABLE `test_car` (`car_uuid` BINARY(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `car_vin_uuid` BINARY(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `car_builder_uuid` BINARY(16) DEFAULT NULL, `car_product_time` UNSIGNED INT NOT NULL DEFAULT 0)

2. Setup SymmetricDS to sync from Sybase to Mysql

3. Try to insert data to Sybase copy; it would fail.

INSERT INTO test_car (car_uuid, car_vin_uuid, car_builder_uuid, car_product_time) VALUES (STRTOBIN('e9aa3560c41c11e4a49bdb15e4accc86'), STRTOBIN('e9aa3560c41c11e4a49bdb15e4accc86'), STRTOBIN('e9aa3560c41c11e4a49bdb15e4accc86') , 0);



DBD::SQLAnywhere::st execute failed: Correlation name 'orig' not found (DBD: execute failed)

TagsNo tags attached.

Activities

spadalkar

2015-03-10 20:39

reporter   ~0000682

insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time,use_capture_lobs,use_stream_lobs)
values('test_car_I','test_car','sybase_to_mysql',current_timestamp,current_timestamp,0, 0);

insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('test_car_I','test_car_router', 200, current_timestamp, current_timestamp);

chenson

2015-03-11 00:02

administrator   ~0000683

What version of sql anywhere? Is this error because of the binary column types? Is there something special about the way the table is defined? Do you get this error on other tables?

spadalkar

2015-03-16 20:47

reporter   ~0000684

It's SQL Anywhere Network Server Version 16.0.0.1948

I think the trigger clauses are wrong. Please view attached text file for further information. E.g Insert trigger reference orig table which is undefined.

join "vms"."DBA". "test_car" as "orig"

spadalkar

2015-03-16 20:48

reporter  

test_car_3_7_9.txt (12,911 bytes)   
mysql>
==================================

CREATE TABLE `test_car` (`car_uuid` BINARY(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `car_vin_uuid` BINARY(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `car_builder_uuid` BINARY(16) DEFAULT NULL, `car_product_time` INT UNSIGNED  NOT NULL DEFAULT 0);

sybase > 
==================================
CREATE TABLE `test_car` (`car_uuid` BINARY(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `car_vin_uuid` BINARY(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', `car_builder_uuid` BINARY(16) DEFAULT NULL, `car_product_time` UNSIGNED INT NOT NULL DEFAULT 0);


mysql>
==================================

insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time,use_capture_lobs,use_stream_lobs) values('test_car_I','test_car','sybase_to_mysql',current_timestamp,current_timestamp,0, 0);

   
mysql>
==================================

insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('test_car_I','csm_2_ims' , 200, current_timestamp, current_timestamp);


#RESTART SymmetricDS 

sybase >
==================================

INSERT INTO test_car (car_uuid, car_vin_uuid, car_builder_uuid, car_product_time) VALUES (STRTOBIN('e9aa3560c41c11e4a49bdb15e4accc86'), STRTOBIN('e9aa3560c41c11e4a49bdb15e4accc86'), STRTOBIN('e9aa3560c41c11e4a49bdb15e4accc86') , 0);


sybase >
===================================
select trigger_defn from systrigger where table_id=(select table_id from systab where table_name='test_car');


sybase > 3.7.8 INSERT TRIGER 
===================================

create trigger "SYM_ON_I_FOR_TST_CR__CSM" on "vms"."DBA"."test_car" for insert as begin declare @DataRow varchar(16384) declare @newpk0 bigint
  declare @ChannelId varchar(20) if("vms"."DBA"."sym_triggers_disabled"(0) = 0) begin
      declare "DataCursor" dynamic scroll cursor for select case when "inserted"."car_product_time" is null then '' else('"'+convert(varchar,"inserted"."car_product_time")+'"') end+','
          +case when "orig"."car_uuid" is null then '' else '"'+"base64_encode"("orig"."car_uuid")+'"' end+','
          +case when "orig"."car_vin_uuid" is null then '' else '"'+"base64_encode"("orig"."car_vin_uuid")+'"' end+','
          +case when "orig"."car_builder_uuid" is null then '' else '"'+"base64_encode"("orig"."car_builder_uuid")+'"' end,"inserted"."car_product_time",'sybase_to_mysql' from "inserted" join "vms"."DBA"."test_car" as "orig" on "orig"."car_product_time" = "inserted"."car_product_time" 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( 'test_car','I',59,@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 cursor "DataCursor"
    end
end

sybase > 3.7.9 INSERT TRIGER 
===================================

 create trigger "SYM_ON_I_FOR_TST_CR__CSM" on "vms"."DBA"."test_car" for insert as begin declare @DataRow varchar(16384) declare @newpk0 varbinary(16384)
  declare @newpk1 varbinary(16384)
  declare @newpk2 varbinary(16384)
  declare @newpk3 bigint
  declare @ChannelId varchar(20) if("vms"."DBA"."sym_triggers_disabled"(0) = 0) begin
      declare "DataCursor" dynamic scroll cursor for select case when "orig"."car_uuid" is null then '' else '"'+"base64_encode"("orig"."car_uuid")+'"' end+','
          +case when "orig"."car_vin_uuid" is null then '' else '"'+"base64_encode"("orig"."car_vin_uuid")+'"' end+','
          +case when "orig"."car_builder_uuid" is null then '' else '"'+"base64_encode"("orig"."car_builder_uuid")+'"' end+','
          +case when "inserted"."car_product_time" is null then '' else('"'+convert(varchar,"inserted"."car_product_time")+'"') end,"inserted"."car_uuid","inserted"."car_vin_uuid","inserted"."car_builder_uuid","inserted"."car_product_time",'sybase_to_mysql' from "inserted" where 1 = 1
      open "DataCursor"
      fetch next "DataCursor" into @DataRow,@newpk0,@newpk1,@newpk2,@newpk3,@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( 'test_car','I',59,@DataRow,@ChannelId,"vms"."DBA"."sym_txid"(0),"vms"."DBA"."sym_node_disabled"(0),null,"getdate"() ) 
          fetch next "DataCursor" into @DataRow,@newpk0,@newpk1,@newpk2,@newpk3,@ChannelId
        end
      close "DataCursor"
      deallocate cursor "DataCursor"
    end
end



sybase > 3.7.8 UPDATE TRIGER 
===================================

create trigger "SYM_ON_U_FOR_TST_CR__CSM" on "vms"."DBA"."test_car" for update as begin declare @DataRow varchar(16384) declare @OldPk varchar(2000) declare @OldDataRow varchar(16384) declare @ChannelId varchar(20) declare @oldpk0 bigint
  declare @newpk0 bigint
  if("vms"."DBA"."sym_triggers_disabled"(0) = 0) begin
      declare "DataCursor" dynamic scroll cursor for select case when "inserted"."car_product_time" is null then '' else('"'+convert(varchar,"inserted"."car_product_time")+'"') end+','
          +case when "orig"."car_uuid" is null then '' else '"'+"base64_encode"("orig"."car_uuid")+'"' end+','
          +case when "orig"."car_vin_uuid" is null then '' else '"'+"base64_encode"("orig"."car_vin_uuid")+'"' end+','
          +case when "orig"."car_builder_uuid" is null then '' else '"'+"base64_encode"("orig"."car_builder_uuid")+'"' end,
          case when "deleted"."car_product_time" is null then '' else('"'+convert(varchar,"deleted"."car_product_time")+'"') end,
          case when "deleted"."car_product_time" is null then '' else('"'+convert(varchar,"deleted"."car_product_time")+'"') end+','
          +''+','
          +''+','
          +'',"deleted"."car_product_time","inserted"."car_product_time",'sybase_to_mysql' from "inserted" join "vms"."DBA"."test_car" as "orig" on "orig"."car_product_time" = "inserted"."car_product_time" join "deleted" on "deleted"."car_product_time" = "inserted"."car_product_time" where 1 = 1
      open "DataCursor"
      fetch next "DataCursor" into @DataRow,@OldPk,@OldDataRow,@oldpk0,@newpk0,@ChannelId
      while @@FETCH_STATUS = 0 begin insert into "vms"."DBA"."sym_data"( "table_name","event_type","trigger_hist_id","row_data","pk_data","old_data","channel_id","transaction_id","source_node_id","external_data","create_time" ) values( 'test_car','U',59,@DataRow,@OldPk,@OldDataRow,@ChannelId,"vms"."DBA"."sym_txid"(0),"vms"."DBA"."sym_node_disabled"(0),null,"getdate"() ) 
          fetch next "DataCursor" into @DataRow,@OldPk,@OldDataRow,@oldpk0,@newpk0,@ChannelId
        end
      close "DataCursor"
      deallocate cursor "DataCursor"
    end
end


sybase > 3.7.9 UPDATE TRIGER 
===================================

create trigger "SYM_ON_U_FOR_TST_CR__CSM" on "vms"."DBA"."test_car" for update as begin declare @DataRow varchar(16384) declare @OldPk varchar(2000) declare @OldDataRow varchar(16384) declare @ChannelId varchar(20) declare @oldpk0 varbinary(16384)
  declare @oldpk1 varbinary(16384)
  declare @oldpk2 varbinary(16384)
  declare @oldpk3 bigint
  declare @newpk0 varbinary(16384)
  declare @newpk1 varbinary(16384)
  declare @newpk2 varbinary(16384)
  declare @newpk3 bigint
  if("vms"."DBA"."sym_triggers_disabled"(0) = 0) begin
      declare "DataCursor" dynamic scroll cursor for select case when "orig"."car_uuid" is null then '' else '"'+"base64_encode"("orig"."car_uuid")+'"' end+','
          +case when "orig"."car_vin_uuid" is null then '' else '"'+"base64_encode"("orig"."car_vin_uuid")+'"' end+','
          +case when "orig"."car_builder_uuid" is null then '' else '"'+"base64_encode"("orig"."car_builder_uuid")+'"' end+','
          +case when "inserted"."car_product_time" is null then '' else('"'+convert(varchar,"inserted"."car_product_time")+'"') end,
          ''+','
          +''+','
          +''+','
          +case when "deleted"."car_product_time" is null then '' else('"'+convert(varchar,"deleted"."car_product_time")+'"') end,
          ''+','
          +''+','
          +''+','
          +case when "deleted"."car_product_time" is null then '' else('"'+convert(varchar,"deleted"."car_product_time")+'"') end,"deleted"."car_uuid","deleted"."car_vin_uuid","deleted"."car_builder_uuid","deleted"."car_product_time","inserted"."car_uuid","inserted"."car_vin_uuid","inserted"."car_builder_uuid","inserted"."car_product_time",'sybase_to_mysql' from "inserted" join "deleted" on "deleted"."car_uuid" = "inserted"."car_uuid" and "deleted"."car_vin_uuid" = "inserted"."car_vin_uuid" and "deleted"."car_builder_uuid" = "inserted"."car_builder_uuid" and "deleted"."car_product_time" = "inserted"."car_product_time" where 1 = 1
      open "DataCursor"
      fetch next "DataCursor" into @DataRow,@OldPk,@OldDataRow,@oldpk0,@oldpk1,@oldpk2,@oldpk3,@newpk0,@newpk1,@newpk2,@newpk3,@ChannelId
      while @@FETCH_STATUS = 0 begin insert into "vms"."DBA"."sym_data"( "table_name","event_type","trigger_hist_id","row_data","pk_data","old_data","channel_id","transaction_id","source_node_id","external_data","create_time" ) values( 'test_car','U',59,@DataRow,@OldPk,@OldDataRow,@ChannelId,"vms"."DBA"."sym_txid"(0),"vms"."DBA"."sym_node_disabled"(0),null,"getdate"() ) 
          fetch next "DataCursor" into @DataRow,@OldPk,@OldDataRow,@oldpk0,@oldpk1,@oldpk2,@oldpk3,@newpk0,@newpk1,@newpk2,@newpk3,@ChannelId
        end
      close "DataCursor"
      deallocate cursor "DataCursor"
    end
end


sybase > 3.7.8 DELETE TRIGER 
===================================

create trigger "SYM_ON_D_FOR_TST_CR__CSM" on "vms"."DBA"."test_car" for delete as begin declare @OldPk varchar(2000) declare @OldDataRow varchar(16384) declare @ChannelId varchar(20) declare @oldpk0 bigint
  if("vms"."DBA"."sym_triggers_disabled"(0) = 0) begin
      declare "DataCursor" dynamic scroll cursor for select case when "deleted"."car_product_time" is null then '' else('"'+convert(varchar,"deleted"."car_product_time")+'"') end,
          case when "deleted"."car_product_time" is null then '' else('"'+convert(varchar,"deleted"."car_product_time")+'"') end+','
          +''+','
          +''+','
          +'',"deleted"."car_product_time",'sybase_to_mysql' from "deleted" where 1 = 1
      open "DataCursor"
      fetch next "DataCursor" into @OldPk,@OldDataRow,@oldpk0,@ChannelId
      while @@FETCH_STATUS = 0 begin insert into "vms"."DBA"."sym_data"( "table_name","event_type","trigger_hist_id","pk_data","old_data","channel_id","transaction_id","source_node_id","external_data","create_time" ) values( 'test_car','D',59,@OldPk,@OldDataRow,@ChannelId,"vms"."DBA"."sym_txid"(0),"vms"."DBA"."sym_node_disabled"(0),null,"getdate"() ) 
          fetch next "DataCursor" into @OldPk,@OldDataRow,@oldpk0,@ChannelId
        end
      close "DataCursor"
      deallocate cursor "DataCursor"
    end
end   


sybase > 3.7.9 DELETE TRIGER 
===================================

create trigger "SYM_ON_D_FOR_TST_CR__CSM" on "vms"."DBA"."test_car" for delete as begin declare @OldPk varchar(2000) declare @OldDataRow varchar(16384) declare @ChannelId varchar(20) declare @oldpk0 varbinary(16384)
  declare @oldpk1 varbinary(16384)
  declare @oldpk2 varbinary(16384)
  declare @oldpk3 bigint
  if("vms"."DBA"."sym_triggers_disabled"(0) = 0) begin
      declare "DataCursor" dynamic scroll cursor for select ''+','
          +''+','
          +''+','
          +case when "deleted"."car_product_time" is null then '' else('"'+convert(varchar,"deleted"."car_product_time")+'"') end,
          ''+','
          +''+','
          +''+','
          +case when "deleted"."car_product_time" is null then '' else('"'+convert(varchar,"deleted"."car_product_time")+'"') end,"deleted"."car_uuid","deleted"."car_vin_uuid","deleted"."car_builder_uuid","deleted"."car_product_time",'sybase_to_mysql' from "deleted" where 1 = 1
      open "DataCursor"
      fetch next "DataCursor" into @OldPk,@OldDataRow,@oldpk0,@oldpk1,@oldpk2,@oldpk3,@ChannelId
      while @@FETCH_STATUS = 0 begin insert into "vms"."DBA"."sym_data"( "table_name","event_type","trigger_hist_id","pk_data","old_data","channel_id","transaction_id","source_node_id","external_data","create_time" ) values( 'test_car','D',59,@OldPk,@OldDataRow,@ChannelId,"vms"."DBA"."sym_txid"(0),"vms"."DBA"."sym_node_disabled"(0),null,"getdate"() ) 
          fetch next "DataCursor" into @OldPk,@OldDataRow,@oldpk0,@oldpk1,@oldpk2,@oldpk3,@ChannelId
        end
      close "DataCursor"
      deallocate cursor "DataCursor"
    end
end

test_car_3_7_9.txt (12,911 bytes)   

spadalkar

2015-03-16 20:48

reporter   ~0000685

test_car_3_7_9.txt is attached for further reference.

Related Changesets

SymmetricDS: master 56a8963b

2015-03-17 08:12:07

chenson

Details Diff
0002232: Insert fails due to error in Trigger Affected Issues
0002232
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql2000/MsSql2000TriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/sqlanywhere/SqlAnywhereTriggerTemplate.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java Diff File

Issue History

Date Modified Username Field Change
2015-03-10 20:35 spadalkar New Issue
2015-03-10 20:39 spadalkar Note Added: 0000682
2015-03-11 00:02 chenson Note Added: 0000683
2015-03-16 20:47 spadalkar Note Added: 0000684
2015-03-16 20:48 spadalkar File Added: test_car_3_7_9.txt
2015-03-16 20:48 spadalkar Note Added: 0000685
2015-03-17 00:40 chenson Assigned To => chenson
2015-03-17 00:40 chenson Status new => assigned
2015-03-17 00:41 chenson Fixed in Version => 3.7.10
2015-03-17 00:41 chenson Target Version => 3.7.10
2015-03-17 12:12 chenson Status assigned => resolved
2015-03-17 12:12 chenson Resolution open => fixed
2015-03-17 13:00 chenson Changeset attached => SymmetricDS trunk r9411
2015-03-17 16:58 chenson Status resolved => closed
2015-07-31 01:49 chenson Changeset attached => SymmetricDS master 56a8963b