[dev.icinga.com #2319] ORA-00913 too many values when MERGE INTO eventhandlers #861

Closed
icinga-migration opened this Issue Feb 14, 2012 · 8 comments

Projects

None yet

1 participant

@icinga-migration
Member

This issue has been migrated from Redmine: https://dev.icinga.com/issues/2319

Created by chaen on 2012-02-14 08:45:46 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2012-03-30 15:58:27 +00:00)
Target Version: 1.7
Last Update: 2014-12-08 14:37:32 +00:00 (in Redmine)

Icinga Version: 1.10.0
OS Version: any

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

Changesets

2012-02-18 10:38:06 +00:00 by mfriedrich e586b34

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

refs #2319

2012-02-22 19:58:53 +00:00 by Tommi 1997b5b

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

2012-04-28 09:16:30 +00:00 by mfriedrich dd9a85f

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

refs #2319

Conflicts:

	Changelog

2012-04-28 09:16:43 +00:00 by Tommi 037a91c

idoutils: fix wrong datatype in eventhandler data #2319
refs #2319
@icinga-migration
Member

Updated by mfriedrich on 2012-02-18 10:39:45 +00:00

  • Category set to 57
  • Status changed from New to Feedback
  • Assigned to set to mfriedrich
  • 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, "
@icinga-migration
Member

Updated by chaen on 2012-02-21 11:50:10 +00:00

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.

@icinga-migration
Member

Updated by Tommi on 2012-02-22 19:05:19 +00:00

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

@icinga-migration
Member

Updated by Tommi on 2012-02-22 20:03:18 +00:00

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

@icinga-migration
Member

Updated by chaen on 2012-02-28 08:00:55 +00:00

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)",

@icinga-migration
Member

Updated by Tommi on 2012-03-02 19:28:55 +00:00

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

@icinga-migration
Member

Updated by Tommi on 2012-03-30 15:58:27 +00:00

  • Status changed from Feedback to Resolved

no more feedback within last 4 weeks, assume resolved

@icinga-migration
Member

Updated by mfriedrich on 2014-12-08 14:37:32 +00:00

  • Project changed from 18 to Core, Classic UI, IDOUtils
  • Category changed from 57 to IDOUtils
  • Icinga Version set to 1
  • OS Version set to any
@icinga-migration icinga-migration added this to the 1.7 milestone Jan 17, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment