[dev.icinga.com #1362] ORA-00600 while executing merge

[icinga-migration]

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

Created by Tommi on 2011-03-28 14:27:35 +00:00

Assignee: Tommi
Status: Resolved (closed on 2011-08-02 09:17:26 +00:00)
Target Version: 1.5
Last Update: 2014-12-08 14:35:47 +00:00 (in Redmine)

Icinga Version: 1.10.0
OS Version: any

---

reported by Morbid Angel http://www.nagios-portal.org/wbb/index.php?page=Thread&postID=148340#post148340
Full message:
ERROR: MSG 'ORA-00600: internal error code, arguments: [koklGetLocAndFlag: bndpos],...
Afterwards, ido2db throws disconnected messages ERROR: MSG 'ORA-03135: connection lost contact
System:
client: 11.2.0.2.0 (oracle-instantclient11.2-basic-11.2.0.2.0-1 rpm)
server: 10.2.0.4.0 - 64bit
ocilib: 3.8.1
os: centos 5.5, db auf rhel 5.5

env vars:
ORACLE_BASE="/usr/lib"
ORACLE_HOME="/usr/lib/oracle/11.2/client64"
ORA_HOME="/usr/lib/oracle/11.2/client64"

Changesets

2011-07-24 16:12:25 +00:00 by Tommi 8d076e1c33c976757436fba577e09a9128a1a90a

idoutils: large oracle parts updated
new function for binding CLOBs to fix #1362
fixes missed long_output column in eventhandlers oracle column #1748
change perfdata columns to CLOB #1749
change logentry_data column to CLOB #1750
fixes usage of unsigned long values #1751
rewrite SQL and formatting #1377
refs #1362, #1748, #1749, #1750, #1751, #1377

2011-07-24 18:26:59 +00:00 by Tommi 9a8f61c

idoutils: large oracle parts updated

new function for binding CLOBs to fix #1362
fixes missed long_output column in eventhandlers oracle column #1748
change perfdata columns to CLOB #1749
change logentry_data column to CLOB #1750
fixes usage of unsigned long values #1751
rewrite SQL and formatting #1377
refs #1362, #1748, #1749, #1750, #1751, #1377
refs #1110

2011-08-03 13:40:29 +00:00 by Tommi 2caa20e0c825cc73011d2257d641f2f1e2bc1d3f

idoutils: fix SEGV in ido2db_query_statehistory caused by wrong index introduced with new clob functions for #1362
refs #1362

2011-08-03 17:56:54 +00:00 by Tommi c22fc22

idoutils: fix SEGV in ido2db_query_statehistory caused by wrong index introduced with new clob functions for #1362
refs #1362

---

Relations:

• relates #1362

[icinga-migration]

Updated by Tommi on 2011-03-28 14:38:45 +00:00

First analysis:
Maybe he hit an unfixed oracle bug(Bug 10102731 If a duplicate long bind occurs for a LOB then an ORA-600 may be reported. ). This occurs if we have the same lob column bind twice into one statement, which is true for merge statements (1.insert,2.update)
Oracle planned fix for 12.1 is to raise a different error code :-((
Oracle suggested Solution: Change the code so as not to issue duplicate long binds.

my proposed solution: Do merge without lob column as first step and return id, second update clob column only using returned id.

[icinga-migration]

Updated by mfriedrich on 2011-03-28 17:31:52 +00:00

• Status changed from New to Assigned
• Assigned to set to Tommi

feel free to implement a fix (but make sure it won't affect the current tree which works). i can't reproduce the problem, i had other segfaults oci, but not on startup. that is why there's a config output to disable such output in syslog ...

[icinga-migration]

Updated by Tommi on 2011-03-28 20:02:40 +00:00

• Category set to 57

nice hint:"but make sure it won't affect the current tree which works". I counted 49 merge statements in db.c to check and probably to fix. This will be a really huge change.
I would like to do this when we go to through the oracle code review. To time it's not this urgent from my perspective, because it "may happen", but dont happens for me at all and we got no other messages about this issue until now.

[icinga-migration]

Updated by mfriedrich on 2011-03-28 20:23:38 +00:00

i'm just saying, as you figured - small fixes might end in a full rewrite. in order to prevent such mistakes probably slowing your enthusiasm, i'm pointing such things out in the first place. and it's more than 49 queries, it's 60 to 70 all over the code. some to rewrite into insert instead of merge, some needing a proper implementation. an older changelog from 1.0rc or later on should give more information on my recent work.

maybe thinking about upsert procedures for those instead of the merge? then you'd only have to bind once, depending on the unique constraint violation, either insert or update.

[icinga-migration]

Updated by Tommi on 2011-03-30 16:13:05 +00:00

The problems are the lobs. Currently they are bond as normal strings like a varchar. It works for now, but not necessary in future if we will have bigger entries. The correct way can be first using the merge sql or insert/violation/update (or reverse depending whats more often) sequence within oci to fill the conventional fields. Aftwards we use offered lob functions (OCI_LOBWrite etc) to write the long content similar writing a file to the current row using id we have certainly after the first step.
It makes no sense for me to code hundreds of SP to make plsql upserts.

[icinga-migration]

Updated by mfriedrich on 2011-03-30 16:59:48 +00:00

coding such upserts will be the only perspective when it comes to postgresql support, so taking this into account is imho a valid option.

we could use the oci_lobwrite straight up on the bindings, or isn't that possible?

[icinga-migration]

Updated by Tommi on 2011-03-30 18:42:01 +00:00

i would like to suggest not to have such dependencies from other database types. I can't speak for pgsql, but doesnt matter. If we have in the code a call to the main function handle_xyz, this will extract the fields to insert/update and now using #ifdef call a special function for the configured database handle_xyz_oci or handle_xyz_pgsql. Then you can do the steps which are fine for pgsql and if needed a complete other implementation using special oracle features.
handling lobs is not a single call (see OCI_LOB* samples). We can implement this as one-for-all function similar to the null binding function but bind and execute.

[icinga-migration]

Updated by Tommi on 2011-06-26 10:15:56 +00:00

Unfortunalely merge doesnt offer a returning clause so we cant take use of a pk(id) to update lob afterwards without an additional query. I will go to split merge into seperate insert/updates, bind lobs as CLOB, first try update, if none updated then executing insert

[icinga-migration]

Updated by mfriedrich on 2011-06-26 10:53:22 +00:00

that's true on the returning clause - but catching up with 2 seperate queries might have been too much performance issues. although with the current knowledge it might be possible to do the update then insert trick again. this is the current implementation in pgsql too. although it might become handy to use stored procedures instead, having only one server transaction call.

[icinga-migration]

Updated by Tommi on 2011-06-26 11:43:22 +00:00

If you moving the logic to the SP, then you will move to load, but not really reduce it, because then the same querys are executed from SP, but you need for each "upsert" a seperate function. Bindings are the same. It will be no noticable performance degration if the majority of transaction will be done with the first call, either update or insert. status tables are updated in general, history tables inserted. An unsuccessfully update can be detected if "affectedRows" are zero(already part of statement handle after update has been sucessfully executed). As opposite a duplicate insert needs to throw an exception over an seperate unique index with the duplicate condition. we cannot use the PK (id), because its a sequence value new for each insert. unfortunately within the current ido2db implementation exceptions cant be used from the application side: they are already trapped by the oci error handler which do not return into calling code part. Means we can only use an "Exception-Free" solution, that's the "upgrade first" methode as implemetation with the lowest effort. Regarding the possible performance degration i am prefering to take your method: lets see if someone complains and can proof this:-))

[icinga-migration]

Updated by mfriedrich on 2011-06-26 15:18:21 +00:00

i got the idea and problems about stored procedures and their movement of upsert already thanks to postgresql (that's why i decided to stick with the current algorithm i had to implement in the first place).

as you have mentioned, making the queries logically ordered how they would happen in the first place, this will reduce the overall load. also regarding the current virtual merge trick being used. and even more, the returning into for the sequence id will be possible without any further selections.

one major problem from recent implementation already went away - *checks are now only inserted due to the processed flag.

the main targets might be changed/achieved by taking the pgsql queries from dbqueries.c and rebuilding them into the prepared statement functions, adding the binds. probably db.c is the wrong place, but adding dbstatements.c would fit better?

@method taking - lol. but true.

[icinga-migration]

Updated by mfriedrich on 2011-07-23 22:43:30 +00:00

i've removed various merge statements for the multiline inserts in #1110

[icinga-migration]

Updated by Tommi on 2011-07-24 16:55:54 +00:00

• Done % changed from 0 to 80

introduced new ido2db_oci_bind_clob functions. Clobs will be bound to 2 different bind names for insert and update and should now be handled seperate from oracle perspective and therefore resolve this issue

[icinga-migration]

Updated by Tommi on 2011-08-02 09:17:26 +00:00

• Status changed from Assigned to Resolved
• Target Version set to 1.5
• Done % changed from 80 to 100

[icinga-migration]

Updated by mfriedrich on 2014-12-08 14:35:47 +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