UPDATE_INSERT Error java.sql.SQLException "Cannot insert duplicate key"

[ng82si]

Hi Experts,

I have proxy-->JDBC Scenario by using Update_Insert statement.

Sometimes I got the following error:

Error during JDBC-Message-Processing; Reason: Error processing request in sax parser: Error when executing statement for table/stored proc. 'DISPO_Unit' (structure 'Statement'): com.microsoft.sqlserver.jdbc.SQLServerException: ... cannot insert duplicate key. The double key value is (143801).

I searched and get the following SAP-Note 831162 Q & A 19:

19. Using UPDATE_INSERT with MS SQL Server:java.sql.SQLException Cannot insert duplicate key

• Q: When I construct payload using UPDATE_INSERT, I get Cannot insert duplicate key in object XXX. How do I resolve this?

           A: You need to create UNIQUE INDEX for the table with 'IGNORE_DUP_KEY' option.
           For instance, if the table name is TEST and its primary key is id, then following statement may be executed using Query analyzer tool.
           CREATE UNIQUE INDEX TEST_INDEX ON TEST(id) WITH IGNORE_DUP_KEY
           After executing the above command, please re-run the scenario.

Could anyone explain how to create the unique index?

Thanks a lot!

Regards

Christine

[Former Member · Accepted solution]

>>>Could anyone explain how to create the unique index?

It's already explained right? What exactly you are looking for?

CREATE UNIQUE INDEX <<index name>> ON <<table name>(<<primary key coloumn) WITH IGNORE_DUP_KEY

you just need to execute this on the database.

[ng82si · Accepted solution]

Hi Hareesh,

I've set up my message structure as what Aaron said:

MT_ItemUnit

     Statement

          dbTableName

               @action=UPDATE_INSERT

               table

               access

                    [lots of fields you have mapped]

               key

                    [primary keys of your destination table should go here]

How could I add the parameter like: with Ignor_dup_key?

Thanks

Christine

[JaySchwendemann · Accepted solution]

This is a long shot cause I'm no way an expert when it comes to JDBC but I think what meant is to execute this statement to the database.

This has nothing to do with any mappings or whatsovever, I suppose

Excerpt from note http://service.sap.com/sap/support/notes/831162

A: You need to create UNIQUE INDEX for the table with 'IGNORE_DUP_KEY' option. For instance, if the table name is TEST and its primary key is id, then following statement may be executed using Query analyzer tool.

However, as I already stated, I'm not very skilled in this area, so I could very well be wrong here.

Cheers

Jens

[naveen_chichili · Accepted solution]

Hi Christine,

You need not handle anything like ignoring the duplicate key if you use UPDATE_INSERT if the same records exist in DB it will update and if the new record come it will insert.

Thanks,

Naveen

[Former Member · Accepted solution]

Yes. Your assumption is correct.

This query has to be executed on database and this has got nothing to do with PI mappings.

@ Christine - As i mentioned, create this unique index on the database table. You can request your DBA to create the unique key.

[Former Member · Accepted solution]

To me it looks like there is already 1 field which is a primary key, that is what SQL server is sometimes reporting back as in her example above.

Christine, please post a picture of your message layout. Have you defined the "key" as part of your datatype? This will just be a constant string of the name for this field which the DBA has already defined as primary key in the database. (e.g. just look at your payload, which field had a value of "143801" in this case).

If your message had it defined properly then the UPDATE_INSERT option would first query the table to see if 143801 exists. If yes it updates, if no, then it inserts.

[ng82si · Accepted solution]

Hi Aaron,

this is the screenshots of Message-Mapping:

and here is the configuration of JDBC Receiver Channel:

In addition, I noticed that the messages, that had the duplicate key error, will not have the same error, when I send them again.

see, last night there were 52 Message Artikelstamm sent. 49 were OK and 3 had this error. Today I sent this 3 messages again and get no more error.

By the way this is sync JDBC Call.

every Query-statement get a response:

Weird!

Any idea?

Thanks!

Christine

[JaySchwendemann · Accepted solution]

Again, long shot but could you try setting isolation level to "repeatable read"? Could be that you have a set of records that affect the same row and while first transaction succeeds, second transaction fails because it did not get the correct information because transaction one is not completed on every isolation level (Isolation (database systems) - Wikipedia, the free encyclopedia)

Cheers

Jens

[Former Member · Accepted solution]

Thanks for the info Christine, seems like you have the basics setup correctly...with Artikel_ID being the primary key.

Perhaps since you can successfully retry these messages, then increase that retry on DB setting to something greater than zero?

[ng82si · Accepted solution]

Hi Jens,

I tried changing the isolation level on "repeatable read".

But the error comes as before.

cheers.

christine

[ng82si · Accepted solution]

Hi Aaron,

u r great!

Thanks

Christine

[ng82si · Accepted solution]

Hi Aaron,

I don't why, this weird error comes again, although the parameter "Number of Retries of Database Transaction on SQL Error" is already arisen to 5.

Do u have idea?

Thanks and Regards

Christine

[Former Member · Accepted solution]

When this error comes, is the "double key value" changing each time? For example, what was the value this time...originally you posted "The double key value is (143801)."

If the number is different, can you check with the DBA or application person to see if that artikel_id value is in the table already? If yes, take note of when the record inserted / modified. Then reprocess your message I'm curious if the record gets updated.

We use this function all the time and it has been pretty reliable for us.

[ng82si · Accepted solution]

it is really weird.

The job runs from 23:00 to 01:00 and normally around 3000 thousand of message with master data are written in the SQL DB.

Just the first 2 or 3 message have this error. After that it doesn't appear.

Here are some screenshots:

Item Master data:

Unit Master data:

I should create a OSS-Request?

Regards

Christine

[JaySchwendemann · Accepted solution]

Hi Christine,

my guts feeling would be that opening an OSS / incident wouldn't be a bad idea. However, this may take a while to bubble up to a person who will have deep enough knowledge to solve, depending on the complexnes of the error involved.

Are you able to reproduce the issue? Sounds like you are. If so, you maybe could use XPI Inspector to get a deeper look into the problem. At least that's my "standard procedure" when tackling a problem. Of course increasing the respecting tracing level would also yield to similar output.

Be aware though: If you only can reproduce in production environment using XPI Inspector or increase trace level might significantly interfere system performance or stabilitiy, especially when dealing with 3000 thousand (so thats 3 million?) records.

Apart from that maybe could help you narrow down the problem. He asked some questions about values of duplicate keys above 🙂

Cheers

Jens

[Former Member · Accepted solution]

Hi Christine, you could open OSS of course but they might ask you more about the data.

If you have an application expert or dba who could help you collect this first it may be helpful.

I suggest knowing about this data which is being written to the "DISPO_Unit" table.

1) In these 3k records do you really have duplicate Artikel_ID values of 143801 for instance?

2) When you get the error each day is this number/value changing in the error message you find in RWB?

This could help you figure out if there is really a problem with the behavior of the JDBC receiver adapter, or if there is something about the data that you could perhaps fix with parameters or a better key.

[Former Member]

Hi Christine, in your PI message structure did you define the key fields to match how the database was built?

You may have something like:

[Your_Message_Type]

     Statement

          dbTableName

               @action=UPDATE_INSERT

               table

               access

                    [lots of fields you have mapped]

               key

                    [primary keys of your destination table should go here]

This way PI knows whether to insert, or update the record if it is already existing.

This works well when inserting / updating directly to a table.

I've never tried this against a stored procedure so using the two methods may not be compatible.