topic Re: tMysqlOutput 'Action on Data' options in Talend Studio

tMysqlOutput 'Action on Data' options

Anonymous — Sat, 16 Nov 2024 13:32:20 GMT

Hi!
In tMysqlOutput, what's the difference between the following options:
1) Action On Data: Insert
Advanced settings: Check 'Use duplicate key update mode insert'
2) Action On Data: Insert or update on duplicate key or unique index.
I checked the syntax generated and they seem to be exactly the same except 1) insert into and 2) insert IGNORE into. I am not sure if IGNORE helps much here since the purpose is not to ignore update record silently (we do want to update the record). What would be the reason to choose one over another?
Also in what scenario would you choose 'Insert or Update' over 'Update or Insert' option?
Thanks

Re: tMysqlOutput 'Action on Data' options

Anonymous — Tue, 23 Feb 2010 10:10:20 GMT

Hello
1)Action On Data: Insert or update on duplicate key or unique index:
Gnenerally, the job will throws an java exception as below when you insert a duplicate row into table which have one or more columns are pk column.
Exception in component tMysqlOutput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 1
if you choose this action on data' insert or update on duplicate key or unique index', it will ignore the exception, if the pk column exists, update the non-pk columns, otherwise, insert new row.
Let's take an exmaple?
here is a table, id is a pk :
CREATE TABLE `person` (
`id` int(11) NOT NULL,
`name` varchar(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=104 DEFAULT CHARSET=latin1
there are two rows in the table now:
id;name
1;mike
2;shong
I will load the three rows as below into table:
id;name
1;mike1
2;shong1
3;elise
result will be:
1;mike1
2;shong1
3;elise
2) Action On Data: Insert
Advanced settings: Check 'Use duplicate key update mode insert
it is similar with action on data 'insert or update on duplicate key or unique index', but there is a little difference, it will update the specified non-pk column with fixed value if the pk column exist. For exmaple
there are two rows in the table now:
id;name
1;mike
2;shong
I will load the three rows as below into table:
id;name
1;mike1
2;shong1
3;elise
I choose action on data 'insert' and check 'Use duplicate key update mode insert' option, specify a non-pk column with a fixed value as screenshot shows.
result will be:
id;name
1;a fixed value
2;a fixed value
3;elise
Best regards
shong

Re: tMysqlOutput 'Action on Data' options

Anonymous — Fri, 12 Aug 2016 13:28:18 GMT

Hi All,
I am using tDB2Input component to read data from one database and to save the data in another database, I use tDB2Output component.
This job runs daily in Task Scheduler. There will be scenarios where New data should be Inserted and the already existing data in the destination database should be Updated.
So, I have used the Action on Data as 'Update or Insert' in my tDB2Output Component. But, the performace is very slow for this Action. But, If I am using Action on Data as 'Insert' for Insertion alone, the performance is good. What is the problem here?
Kindly help me to resolve this Issue.
Thanks,