Bug #2319

ORA-00913 too many values when MERGE INTO eventhandlers

Added by chaen about 2 years ago. Updated about 2 years ago.

Status:ResolvedStart date:02/14/2012
Priority:NormalDue date:
Assignee:dnsmichi% Done:

100%

Category:Queries
Target version:Icinga 1.x - 1.7
Icinga Version: DB Type:
IDO Version: DB Version:

Description

Hello,

I am running Icinga 1.5.1 with ido2db 1.5.1 and an Oracle backend

Our logs are full of errors like this one :

ido2db: OCIERROR - MSG ORA-00913: too many values#012 at pos 11 in QUERY 'MERGE INTO eventhandlers USING DUAL ON (instance_id=:X1 AND start_time=unixts2date(:X6)AND start_time_usec=:X7) WHEN MATCHED THEN UPDATE SET eventhandler_type=:X2, object_id=:X3, state=:X4, state_type=:X5, end_time=:X8, end_time_usec=:X9, command_object_id=:X10, command_args=:X11, command_line=:X12, timeout=:X13, early_timeout=:X14, execution_time=:X15, return_code=:X16, output=:X17, long_output=:X18u WHEN NOT MATCHED THEN INSERT (instance_id, eventhandler_type, object_id, state, state_type, start_time, start_time_usec, end_time, end_time_usec, command_object_id, command_args, command_line, timeout, early_timeout, execution_time, return_code, output, long_output) VALUES (seq_eventhandlers.nextval, :X1, :X2, :X3, :X4, :X5, unixts2date(:X6), :X7, unixts2date(:X8), :X9, :X10, :X11, :X12, :X13, :X14, :X15, :X16, :X17, :X18i)' -->19 BindVars -->[Name:':X1',Type:uInt,Val:'1'][Name:':X2',Type:Int,Val:'0'][Name:':X3',Type:uInt,Val:'1859'][Name:':X4',Type:Int,Val:'0'][Name:':X5',Type:Int,Val:'0'][Name:':X6',Type:uInt,Val:'1329208063'][Name:':X7',Type:uInt,Val:'318087'][Name:':X8',Type:uInt,Val:'1329208063'][Name:':X9',Type:uInt,Val:'483480'][Name:':X10',Type:uInt,Val:'34'][Name:':X11',Type:Text Size:0,Val:''][Name:':X12',Type:Text Size:745,Val:'/usr/bin/printf "%b" "CONTACTNAME:$CONTACTNAME$\nCONTACTALIAS:$CONTACTALIAS$\nCONTACTEMAIL:$CONTACTEMAIL$\nCONTACTPAGER:$CONTACTPAGER$\nHOSTNAME:s2a01\nHOSTALIAS:s2a01\nHOSTADDRESS:s2a01\nHOSTSTATE:UP\nSERVICEDESC:ddn_disk_f_03\nSERVICESTATE:OK\nSERVICEOUTPUT:SNMP OK - healthy(1)\nSERVICEPERFDATA:\nSERVICEEXECUTIONTIME:0.020\nSERVICELATENCY:183.330\nNOTIFICATIONTYPE:\nNOTIFICATIONNUMBER:\nLONGDATETIME:Tue Feb 14 09:27:43 CET 2012\nSHORTDATETIME:02-14-2012 09:27:43\nDATE:02-14-2012\nTIME:09:27:43\nTIMET:1329208063\nLASTSERVICECHECK:1329207851\nLASTSERVICESTATECHANGE:1329207851\nADMINEMAIL:nagios@localhost\nADMINPAGER:pagenagios@localhost\nSERVICEACKAUTHOR:\nSERVICEACKCOMMENT:" | /usr/loc

I am far to be an SQL guru, but I can imagine 2 reasons for this :

- if you have a look at the insert statement, the values group has 19 members, whereas the insert group has only 18. The one extra being I guess "seq_eventhandlers.nextval". This would match exactly the oracle error number.
- on the other hand, I also notice that all the log messages concerning this issue are trunkated at the same size. So I don't know if only the log message itself is trunkated, in which case in does not matter, but if the request itself is trunkated, then I am quite conviced it will trigger an error (but different from the 00913).

I would guess that the problem only appears with Oracle, since the other DBs offer an autoincrement function, and you don't need sequences.

Also, even though it does not prevent us to run, I suspect this error to be responsible for the latency we observe.

I hope it is not a duplicate of another bug, I could not find such.

Cheers,
Chris

Associated revisions

Revision e586b345
Added by dnsmichi about 2 years ago

idoutils: fix ORA-00913 too many values when MERGE INTO eventhandlers #2319

refs #2319

Revision 1997b5bc
Added by Tommi about 2 years ago

idoutils: fix wrong datatype in eventhandler data #2319
refs #2319

Revision dd9a85f1
Added by dnsmichi almost 2 years ago

idoutils: fix ORA-00913 too many values when MERGE INTO eventhandlers #2319

refs #2319

Conflicts:

Changelog

Revision 037a91cd
Added by Tommi almost 2 years ago

idoutils: fix wrong datatype in eventhandler data #2319
refs #2319

History

#1 Updated by dnsmichi about 2 years ago

  • Category set to Queries
  • Status changed from New to Feedback
  • Assignee set to dnsmichi
  • Target version set to 1.7
  • % Done changed from 0 to 100

you are totally right, this is a copy paste bug possibly. the sequence_name.nextval is required to be inserted as well and therefore the "id" column is missing.

can you test that diff or the current dev/ido branch?

diff --git a/module/idoutils/src/db.c b/module/idoutils/src/db.c
index 9d8290e..3f2e0e2 100644
--- a/module/idoutils/src/db.c
+++ b/module/idoutils/src/db.c
@@ -4712,7 +4712,7 @@ int ido2db_oci_prepared_statement_eventhandlerdata(ido2db_idi *idi) {
                     "timeout=:X13, early_timeout=:X14, execution_time=:X15, " 
                     "return_code=:X16, output=:X17, long_output=:X18u " 
                     "WHEN NOT MATCHED THEN " 
-                    "INSERT (instance_id, eventhandler_type, object_id, " 
+                    "INSERT (id, instance_id, eventhandler_type, object_id, " 
                     "state, state_type, start_time, start_time_usec, " 
                     "end_time, end_time_usec, command_object_id, " 
                     "command_args, command_line, " 

#2 Updated by chaen about 2 years ago

Hi, thanks for the patch.

I applied, and a new error appeared, on the same request :

ido2db: OCIERROR - MSG ORA-00932: inconsistent datatypes: expected DATE got NUMBER#012 at pos 216 in QUERY

The query is

MERGE INTO eventhandlers USING DUAL ON (instance_id=:X1 AND start_time=unixts2date(:X6)AND start_time_usec=:X7) WHEN MATCHED THEN UPDATE SET eventhandler_type=:X2, object_id=:X3, state=:X4, state_type=:X5, end_time=:X8, end_time_usec=:X9, command_object_id=:X10, command_args=:X11, command_line=:X12, timeout=:X13, early_timeout=:X14, execution_time=:X15, return_code=:X16, output=:X17, long_output=:X18u WHEN NOT MATCHED THEN INSERT (id, instance_id, eventhandler_type, object_id, state, state_type, start_time, start_time_usec, end_time, end_time_usec, command_object_id, command_args, command_line, timeout, early_timeout, execution_time, return_code, output, long_output) VALUES (seq_eventhandlers.nextval, :X1, :X2, :X3, :X4, :X5, unixts2date(:X6), :X7, unixts2date(:X8), :X9, :X10, :X11, :X12, :X13, :X14, :X15, :X16, :X17, :X18i)' -->19 BindVars -->[Name:':X1',Type:uInt,Val:'1'][Name:':X2',Type:Int,Val:'0'][Name:':X3',Type:uInt,Val:'1860'][Name:':X4',Type:Int,Val:'0'][Name:':X5',Type:Int,Val:'0'][Name:':X6',Type:uInt,Val:'1329824959'][Name:':X7',Type:uInt,Val:'212660'][Name:':X8',Type:uInt,Val:'1329824959'][Name:':X9',Type:uInt,Val:'342508'][Name:':X10',Type:uInt,Val:'34'][Name:':X11',Type:Text Size:0,Val:''][Name:':X12',Type:Text Size:746,Val:'blablalba

So I guess the value with problem is X8.

#3 Updated by Tommi about 2 years ago

i can see 2 errors here. First one, missing id column when updating, was already fixed by dnsmichi, second one relates to the endtime in matched branch,should be ...:X5,*end_time=unix2date(:X8)*,...

one more case which should be added to the tests

#4 Updated by Tommi about 2 years ago

patch for 1.6+ added. Here we have to use unixts2localts instead of unix2date

#5 Updated by chaen about 2 years ago

Great, thanks. Can you please confirm that the proper sql request now is (I patch the 1.5 we are running) :

"MERGE INTO %s USING DUAL " 
"ON (instance_id=:X1 "
"AND start_time=unixts2date(:X6)"
"AND start_time_usec=:X7) "
"WHEN MATCHED THEN "
"UPDATE SET eventhandler_type=:X2, object_id=:X3, "
"state=:X4, state_type=:X5, end_time=unixts2localts(:X8), "
"end_time_usec=:X9, command_object_id=:X10, "
"command_args=:X11, command_line=:X12, "
"timeout=:X13, early_timeout=:X14, execution_time=:X15, "
"return_code=:X16, output=:X17, long_output=:X18u "
"WHEN NOT MATCHED THEN "
"INSERT (id, instance_id, eventhandler_type, object_id, "
"state, state_type, start_time, start_time_usec, "
"end_time, end_time_usec, command_object_id, "
"command_args, command_line, "
"timeout, early_timeout, execution_time, "
"return_code, output, long_output) "
"VALUES (seq_eventhandlers.nextval, :X1, :X2, "
":X3, :X4, :X5, unixts2date(:X6), :X7, "
"unixts2date(:X8), :X9, :X10, :X11, :X12, "
":X13, :X14, :X15, :X16, :X17, :X18i)",

#6 Updated by Tommi about 2 years ago

For 1.5 please replace all unix2localts() calls with unix2date(). for 1.6+ opposite.

#7 Updated by Tommi about 2 years ago

  • Status changed from Feedback to Resolved

no more feedback within last 4 weeks, assume resolved

Also available in: Atom PDF