tMySQL Output not checking keys

Highlighted
One Star

tMySQL Output not checking keys

Hello.

Using tMySQLOutput, I'd like to drop a record into a database if and only if it is not already there. There are two features in "action on data" that I tried, insert and insert ignore. Neither work using several different techniques. There seems to be no other problems anywhere within my job, meaning that all data is inserted, all prints to screen correctly, etc.

The environment is not all too complicated, other than this table does not 100% match the schema defined in the tMySQLOutput component. The difference, other than a few 'auto timestamp' type database records is simple. Though the 'primary key' in my talend schema is set to a unique integer in the table, it is not the primary auto incrementing key as definied the database. I am trying to map other data into an existing table where my talend jobs consider this a primary key, but my data model cannot.

The primary key talend job is not the primary key in the database.

I have tried...
1. Setting the tMySqlOutput->edit schema -> set a primary key feature. I assumed this is all I would have to do, because this is what the docs said it should do. My guess is that it would query the database field for that talend-defined key and ignore if it was there. It definitely does not do that, it simply inserts another copy of the record. Unfortunaly, there is no SQL transaction log in my talend stream for me to figure out what it's doing, otherwise I'd add in a bunch of "echo my last query" statements.

2. Setting up a "unique" field in the database and using tMySqlOutpout insert/ignore. I figured that the database would nicely just fail and move on to the next record. Talend does not do that, it simply stops inserting records, like it tries them all and does not commit changes, or it simply stops trying. (or at least that's what I think it does, again, no SQL transaction log for me to check, and I can't find a way to troubleshoot my queries.)

I tried troubleshooting with System.out.println(((String)globalMap.get("tMysqlOutput_1_QUERY"))), but that doesn't seem to work. I have absolutely no clue how to print my SQL statements to a screen.


Please assist. It's a simple problem, don't insert a record if it is already there.
And this job is so high priority and due yesterday it is scary.

Thanks.

Tony Fraser
Community Manager

Re: tMySQL Output not checking keys

tonythor wrote:
2. Setting up a "unique" field in the database and using tMySqlOutpout insert/ignore. I figured that the database would nicely just fail and move on to the next record. Talend does not do that, it simply stops inserting records, like it tries them all and does not commit changes, or it simply stops trying. (or at least that's what I think it does, again, no SQL transaction log for me to check, and I can't find a way to troubleshoot my queries.)

Please assist. It's a simple problem, don't insert a record if it is already there.
And this job is so high priority and due yesterday it is scary.

Thanks.

Tony Fraser

There should be a primary key in the table. With insert ignore option, the inserting will be ignored if the key already exists, and the job continues to insert next record. If there has a tMysqlConnection to create a db connection in the job, and tMysqlOutput use an already existing connection, there should also a tMysqlCommit at the end of job to commit the change and close the db connection.

Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: tMySQL Output not checking keys

In the most simplest of forms -- it looks like this..

(my implementation has nothing to do with remote systems, this is the easiest possible way I can show what I'd like to do)

Record is
id = 1
remotesystemid = 123456788
name = tony
lname = fraser



check for [remotesystemid = 123456788]
If [remotesystemid=123456788] exists
then don't insert record

if [remotesystemid=123456788] does not exist
then insert
remotesystemid=123456788
name=tony
lname=fraser



The database primary key is "1"

Is there any way to make this happen?!



tony
One Star

Re: tMySQL Output not checking keys

Or what my job is already attempting to do rather... It's just a lot more complicated.
One Star

Re: tMySQL Output not checking keys

Ok, so I hacked my way into a solution levering my DB.

1. Set a unique key on the DB field for "remotesystemid".
2. Reset my number of rows per insert to be 1. (this is going to make it the slowest job in the history of time, but that's fine.

Bang.
Community Manager

Re: tMySQL Output not checking keys

Hi tony

The insert ignore option is based on the primary key in table, in your case, remotesystemid should defined as a primary key. Fortunately, you have figured out the problem. That's fine!

Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Enabling Data Governance

Learn how to enable Data Governance

Watch Now

The Definitive Guide to Government Data Quality

Take a peek at the definitive guide to Government Data Quality

Read