Security

How do I debug a dboutput-update error giving unique constraint violation errors?

NigelCooke
Explorer

I am trying to use dboutput to update/insert to an oracle table. If I run the search fragment below (reduced to the minimum required for testing) on an empty table I get a record inserted as expected.

...

| eval KEY_VALUE="Q1SHAR00010816"

| eval Identifier="Q1 SHAR0001 08 16"

| eval AlertGroup = "GS0120PUMASPLUNK"

| dedup KEY_VALUE

| dboutput database=T3_ADHOC_REPORTING type=update table=ALARM_COLLECTOR key=KEY_VALUE notFound=insert
Identifier as Identifier
AlertGroup as AlertGroup

If I run it again I expect the record to be updated and the search to succeed. Instead I get the following error :
command="dboutput", Unexpected error while performing dboutput: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (T3_ADHOC_REPORTING.PK_KEY_VALUE) violated

The dboutput command appears to be trying to insert instead of update, as if it can't find the key. I check the database and the value for KEY_VALUE appears to be correct. KEY_VALUE is a VARCHAR2(32 BYTE) column in the table that has been designated as the primary key. I also tried removing the unique constraint from the table and I simply got duplicate records being inserted. Definitely wants to insert instead of update.
In the log I can see a 'GETINFO' and a 'EXECUTE' record and they show the command I used but there is no information about what is actually sent to Oracle. How do I see what is being sent. Any other information about how I can find out why this error is occurring would be greatly appreciated.

0 Karma

kundeng
Path Finder

Did you solve this issue? I have the exact problem. dbx version is 1.1.6.

0 Karma

vasanthmss
Motivator

Do you have any auto increment field or other constraints? Check your constrain fields.you can query the table fields desc. Compare it wit your qry. It should work.

If possible desc your table and query.

V
0 Karma

NigelCooke
Explorer

The only constraint is the primary key (CONSTRAINT "PK_KEY_VALUE" PRIMARY KEY ("KEY_VALUE")) , no others and no auto increment.

If I change the dboutput command to

| dboutput database=T3_ADHOC_REPORTING type=sql
"UPDATE ALARM_COLLECTOR SET Identifier = $Identifier$, AlertGroup = $AlertGroup$ WHERE KEY_VALUE = $KEY_VALUE$"

it works for updating. The problem with doing it that way is that if the record doesn't exist in the table I don't get an insert. I want the search to update the record if it exists or insert it if it doesn't. My understanding of the dboutput command indicates that my search as described previously should do exactly that, but it doesn't.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...