[dev.icinga.com #2319] ORA-00913 too many values when MERGE INTO eventhandlers #861
Comments
Updated by mfriedrich on 2012-02-18 10:39:45 +00:00
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?
|
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)' - So I guess the value with problem is X8. |
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 |
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 |
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 " |
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. |
Updated by Tommi on 2012-03-30 15:58:27 +00:00
no more feedback within last 4 weeks, assume resolved |
Updated by mfriedrich on 2014-12-08 14:37:32 +00:00
|
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)
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/locI am far to be an SQL guru, but I can imagine 2 reasons for this :
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
2012-02-22 19:58:53 +00:00 by Tommi 1997b5b
2012-04-28 09:16:30 +00:00 by mfriedrich dd9a85f
2012-04-28 09:16:43 +00:00 by Tommi 037a91c
The text was updated successfully, but these errors were encountered: