View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0002232 | SymmetricDS | Bug | public | 2015-03-10 20:35 | 2015-03-17 16:58 |
| Reporter | spadalkar | Assigned To | chenson | ||
| Priority | high | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.7.9 | ||||
| Target Version | 3.7.10 | Fixed in Version | 3.7.10 | ||
| Summary | 0002232: Insert fails due to error in Trigger | ||||
| Description | Whenever 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 Reproduce | 1. 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) | ||||
| Tags | No tags attached. | ||||
|
|
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); |
|
|
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? |
|
|
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" |
|
|
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 is attached for further reference. |
|
SymmetricDS: master 56a8963b 2015-03-17 08:12:07 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 | ||
| 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 |