Talend community forum / tMySQLOutput Update problem

Slum · 2007-07-19 14:53:49

Hi,

I have a problem using tMySQLOutput component : I make a transformation with tMap component. On differents Outputs, I want to update data or insert if they are non-existent. One of them generate an error when data with same primary key values are already inserted :

Code:

Starting job Job5a at 14:54 19/07/2007.
[statistics] connecting to socket on port 4321
[statistics] connected
Exception in component tMysqlOutput_5
Exception in component tMysqlOutput_5
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ID_NUM_NATIONAL=9 AND ID_NUM_NATIONAL_2=9' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[statistics] disconnected
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:353)
    at com.mysql.jdbc.Util.getInstance(Util.java:336)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2938)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1601)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1710)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2436)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1402)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1694)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1608)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1593)
    at pmi_stat.job5a.Job5a.tMysqlInput_1Process(Job5a.java:5503)
    at pmi_stat.job5a.Job5a.tFileList_1Process(Job5a.java:464)
    at pmi_stat.job5a.Job5a.main(Job5a.java:9098)
Job Job5a ended at 14:54 19/07/2007. [exit code=0]

Why does the job stops ?
What's the difference betwween "insert or update" and "update or insert" ?

Thank you.

sologlobe · 2007-07-19 15:27:55

can't help you for your error, we need more info about what you are actually doing.

But the difference between update or insert and insert or update is the following :
update or insert : Will first try to update, if it can't (because record doesn't exist), it will try to insert.
insert or update : Will first try to insert, if records already exists (primary key already exists), it will try to update the record.

Slum · 2007-07-19 15:50:00

Thank you.

Project details :
I try to bring data from temporary database into statistical database (both are under MySQL 5).
Into temporary space, I just import fields since last transaction. Then, I update statistical space using preceding data.

You can find a pic of my job here :

[img]http://img170.imageshack.us/img170/7991/jobdn0.jpg[/img]

If necessary I can post tMap print screen.

Antoine · 2007-07-19 16:02:11

Hi,

I can't answer you,
but just a question:

how do you display the green statistics ?

Thanks

Slum · 2007-07-19 16:09:41

Hi, into "Run" tab you just have to check "Statistics" box.

mhirt · 2007-07-19 23:39:23

Slum,

Update is done on the key selected in the schema of the tMySQLOutput_5
Is she well setted ?

Concerning "Insert or update" and "Update or insert" options, they will have exactly the same results.
But you can choose between them for better performance if you know your process.

Regards,
Michaël.

Slum · 2007-07-20 08:50:29

Hi Mhirt,

I have two keys into tMySQLOutput. How can I avoid my problem ?

Here's a pic of tMap :

[img]http://img101.imageshack.us/img101/4105/jobea0.th.jpg[/img]

It's really strange ; it works fine for others link but not for this one. Even if it's the same architecture.

mhirt · 2007-07-21 00:58:57

That's exactly your problem : if you have two columns and if all of them are keys, you have nothing to update...
Imagine two columns : x and y
if x='a' and y='b' in flow.
If x is key in TOS : generated request will be "update table myTable set y='b' where x='a'"
If y is key in TOS : generated request will be "update table myTable set x='a' where y='b'"

but if x and y are keys, what would you like the request to be ? "update table myTable set x='a' and y='b" where x='a' and y='b'" ?
This won't change anything in the db and might be considered as a business error.
That's why we have chose to generate "update table myTable set where x='a' and y='b'" which generates an error... !!!

Hope it helps,

Regards,
Michaël.

Slum · 2007-07-23 11:27:44

I have deleted primary keys and now it works.

Thank you for your support.

Volker Brehm · 2008-02-26 14:35:16

Hello together,

this is a very old post but very actual for me...

I've a table with two columns, both keys. Removing primary keys is not a solution for me. I changed the Action on data to "Insert", but now I get a long list of duplicated keys. Is there a way to suppress them without loading the data into memory (=> join in tMap)?

Bye
Volker

mhirt · 2008-02-26 22:42:05

Hello Volker,

Since 2.3.0, db output components have a "field option" in Advanced Settings configuration.
In this table, you can choose some different keys in update.
This will also allow you to generate all update request you might want.

Regards,
Mike.

Volker Brehm · 2008-02-27 08:09:16

Hello Mike,

thanks for the information. It works (even if I do not understand it) with "Key in update" and "Updatable".

Bye
Volker

Announcement

Unanswered posts

#1 2007-07-19 14:53:49

tMySQLOutput Update problem

Code:

#2 2007-07-19 15:27:55

Re: tMySQLOutput Update problem

#3 2007-07-19 15:50:00

Re: tMySQLOutput Update problem

#4 2007-07-19 16:02:11

Re: tMySQLOutput Update problem

#5 2007-07-19 16:09:41

Re: tMySQLOutput Update problem

#6 2007-07-19 23:39:23

Re: tMySQLOutput Update problem

#7 2007-07-20 08:50:29

Re: tMySQLOutput Update problem

#8 2007-07-21 00:58:57

Re: tMySQLOutput Update problem

#9 2007-07-23 11:27:44

Re: tMySQLOutput Update problem

#10 2008-02-26 14:35:16

Re: tMySQLOutput Update problem

#11 2008-02-26 22:42:05

Re: tMySQLOutput Update problem

#12 2008-02-27 08:09:16

Re: tMySQLOutput Update problem

Board footer