[dev.icinga.com #3855] ORA-22835 causes blank status data cronks #1050

icinga-migration · 2013-03-13T21:04:11Z

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

Created by Tommi on 2013-03-13 21:04:11 +00:00

Assignee: Tommi
Status: Resolved (closed on 2013-04-16 09:25:17 +00:00)
Target Version: 1.9
Last Update: 2013-04-16 09:25:17 +00:00 (in Redmine)

Icinga Version: 1.9.0dev
Icinga Web Version: 1.9.0dev
IDO Version: 1.9.0dev
OS Version: CentOS 6.4
DB Type: Oracle
DB Version: 11.2
Browser Version: FF18

with #3412 we introduced clob instead of varchar for serveral long columns as suggested by gunnar in #3325. With the current git master i got a lot of internal errors regarding oci fetch errors causing status screens and others to be blank

[Wed Mar 13 21:37:18 2013] [fatal] Uncaught AppKitPHPError: PHP Error oci_fetch_array() [function.oci-fetch-array]: ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 29441, maximum: 4000) (/opt/icinga-web/lib/doctrine/lib/Doctrine/Adapter/Statement/IcingaOracle.php:503) (/opt/icinga-web/app/modules/AppKit/lib/logging/AppKitExceptionHandler.class.php:52)

This means you have to bind a clob data type or truncate data. see http://www.php.net/manual/de/function.oci-bind-by-name.php
You can check this with the sample check from #3324. similar i found already in #2584

Changesets

2013-04-07 18:34:42 +00:00 by Tommi 68f0fdc3c175158a5447788b10ec50c920855117

IcingaOracle: fix ORA-22835 when output clob>4000 bytes by replacing to_char cast with lob read function #3855
Refs #3412 #3855

2013-04-13 18:55:39 +00:00 by Tommi 9360d4a1276b4a694a096187fcb915ed3b0a27d9

Oracle: hoststatus and servicestatus: fix ORA-00932 caused by distinct on a clob field by removing distinct in host.xml and service.xml.
refs #3855

2013-04-15 13:35:48 +00:00 by Tommi bd883d0

IcingaOracle: fix ORA-22835 when output clob>4000 bytes by replacing to_char cast with lob read function #3855
Refs #3412 #3855

Relations:

relates #3412
relates #3325
relates #3855
relates #3855

The text was updated successfully, but these errors were encountered:

icinga-migration · 2013-04-06T22:19:31Z

Updated by mfriedrich on 2013-04-06 22:19:31 +00:00

Target Version set to 1.9

is this fixable for 1.9? the proposed core/ido code lives now in git branch mfriedrich/clob - https://git.icinga.org/?p=icinga-core.git;a=shortlog;h=refs/heads/mfriedrich/clob

if not, #3412 can't be merged for core 1.9 release.

icinga-migration · 2013-04-07T09:37:08Z

Updated by Tommi on 2013-04-07 09:37:08 +00:00

the problem comes from direct fetching lob contents (this is was OCI_RETURN_LOBS does)instead of fetching the descriptor afterwards via OCI-Lob calls(read or load) using the descriptor.

#IcingaOracle.php:503
$result = oci_fetch_array($this->statement, OCI_ASSOC + OCI_RETURN_NULLS + OCI_RETURN_LOBS);

It should be mentioned we have already long_output column as clob atleast for a year.
Solution might be

fetch data without OCI_RETURN_LOBS,
check fieldtypes or define it in advance
if returned fieldtype is a LOB

**** read clob value in smaller chunks

while(!$lob->eof()){
    $lobvalue .= $lob->read(2000);
}

**** replace field value with $lobvalue

icinga-migration · 2013-04-07T19:22:23Z

Updated by Tommi on 2013-04-07 19:22:23 +00:00

Status changed from New to Feedback
Done % changed from 0 to 80

after some hours of digging into this funny icingaOracle.php stuff i modified it by adding a new fetchlob function and dropped the existing to_char query modification. It lives on top of the current next branch in tdressler/issue_3855. Now the error is gone and i can see the service status data even with a very long output. Pls test it too.

Unfortunally, no change applied to hoststatus and status_map cronk. There are "internal errors" popping up, but i couldn't found any error messages regarding this into the logs or syslog, even after applying debug setings suggested in https://wiki.icinga.org/display/testing/Icinga+Web+Testing#IcingaWebTesting-DatabaseConnection. Here i suspect another issue not related to this one. Only one problem logged

PHP Fatal error:  Call to a member function evictAll() on a non-object in /opt/icinga-web/lib/doctrine/lib/Doctrine/Connection.php on line 1247

which looks more like a cleanup issue and shouldn't stop only the data esp. for this particular type. I would like to handover this problem to the web developers. Its not an Oracle issue (or someone point me to this) . If needed, i can open a new issue.

icinga-migration · 2013-04-08T19:12:55Z

Updated by mfriedrich on 2013-04-08 19:12:55 +00:00

Status changed from Feedback to Assigned
Assigned to set to mfriedrich

i'll try to test it this week.

icinga-migration · 2013-04-12T19:11:28Z

Updated by mfriedrich on 2013-04-12 19:11:28 +00:00

Status changed from Assigned to Feedback
Assigned to deleted ~~mfriedrich~~

for some reason i cannot resolve right now, i am unable to connect to my local oracle XE install, while all other tests work fine.

$ cat oratest.php

works when called via webbrowser.

databases.xml

            
                    icingaOracle://icinga:icinga@127.0.0.1/XE
                
                utf8
                YYYY-MM-DD HH24:MI:SS
                
                    CONSERVATIVE
                
                true

                %core.module_dir%/Api/lib/database/models/generated
                %core.module_dir%/Api/lib/database/models
                
                    false
                    apc
                    true

output on the dev console with the json 500

                    A critical exception occured!
                    

                        Uncaught Doctrine_Adapter_Exception thrown:
                        
                                                        Unable to Connect to :'XE' as 'icinga' : Array
(
    [code] => 12154
    [message] => ORA-12154: TNS:could not resolve the connect identifier specified
    [offset] => 0
    [sqltext] => 
)
                        
                        Stacktrace:#0 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Connection.php(492): Doctrine_Adapter_IcingaOracle->__construct('icingaOracle:ho...', 'icinga', 'icinga', Array)

#1 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Connection/IcingaOracle.php(87): Doctrine_Connection->connect()
#2 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Connection.php(1009): Doctrine_Connection_IcingaOracle->connect()
#3 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Query/Abstract.php(976): Doctrine_Connection->execute('SELECT a.* FROM...', Array)
#4 /usr/share/icinga-web/app/modules/Api/lib/database/IcingaDoctrine_Query.class.php(116): Doctrine_Query_Abstract->_execute(NULL)
#5 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Query/Abstract.php(1026): IcingaDoctrine_Query->_execute(NULL)
#6 /usr/share/icinga-web/app/modules/Api/models/Views/ApiDQLViewModel.class.php(92): Doctrine_Query_Abstract->execute(NULL, 5)
#7 /usr/share/icinga-web/app/modules/Cronks/lib/template/TemplateWorker/DQLCronkTemplateWorker.class.php(82): API_Views_ApiDQLViewModel->getResult()
#8 /usr/share/icinga-web/app/modules/Cronks/views/System/ViewProcSuccessView.class.php(141): DQLCronkTemplateWorker->fetchDataArray()
#9 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1672): Cronks_System_ViewProcSuccessView->executeJson(Object(AgaviWebRequestDataHolder))
#10 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1533): AgaviExecutionFilter->executeView(Object(AgaviExecutionContainer))
#11 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviExecutionFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#12 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#13 /usr/share/icinga-web/lib/agavi/src/filter/AgaviSecurityFilter.class.php(73): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#14 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviSecurityFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#15 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#16 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(870): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#17 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1266): AgaviExecutionContainer->execute()
#18 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviDispatchFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#19 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#20 /usr/share/icinga-web/lib/agavi/src/filter/AgaviFormPopulationFilter.class.php(78): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#21 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFormPopulationFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#22 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(579): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#23 /usr/share/icinga-web/pub/index.php(49): AgaviController->dispatch()
#24 {main}

so i am leaving this for someone else to test, and provide feedback. if there's none, we shouldn't add this to 1.9 imho.

icinga-migration · 2013-04-12T20:31:24Z

Updated by Tommi on 2013-04-12 20:31:24 +00:00

your doctrine dsn doesn't work. try simailar this. note the count of slashes for each section.

icingaOracle://icinga:icinga@localhost:1521///localhost/xe

icinga-migration · 2013-04-13T10:42:39Z

Updated by mfriedrich on 2013-04-13 10:42:39 +00:00

Status changed from Feedback to Assigned
Assigned to set to mfriedrich

buha. thanks a lot. i already read about the SID for XE being //localhost/XE and not just XE in case of such resolution errors, but passing that in that way onto the dsn is ugly.

on the icingaOracle.php it's already proven that db_host and db_port are fully ignored and just placeholders here (like ido2db does). so having the correct sid seems pretty damn hard for test cases. i'll add it to my personal documentation then, thanks again.

icinga-migration · 2013-04-13T10:53:37Z

Updated by mfriedrich on 2013-04-13 10:53:37 +00:00

ok, reproducing the error now.

                    icingaOracle://icinga:icinga@127.0.0.1///localhost/XE
                
                utf8
                YYYY-MM-DD HH24:MI:SS
                
                    CONSERVATIVE
                
                true

                %core.module_dir%/Api/lib/database/models/generated
                %core.module_dir%/Api/lib/database/models
                
                    false
                    apc
                    true
                

            

A critical exception occured!
Uncaught Doctrine_Connection_Oracle_Exception thrown:
ORA-00932: inconsistent datatypes: expected - got CLOB : SELECT a.* FROM ( SELECT DISTINCT s.icon_image AS f_0, i.instance_name AS f_1, h.host_object_id AS f_2, s.service_object_id AS f_3, o.name1 AS f_4, h.alias AS f_5, h.display_name AS f_6, o2.name2 AS f_7, s.display_name AS f_8, s2.process_performance_data AS f_9, COALESCE(s2.current_state, 0) AS f_10, COALESCE(s2.current_state, 0) AS f_11, s2.last_hard_state AS f_12, h2.last_hard_state AS f_13, COALESCE(h2.current_state, 0) AS f_14, (CASE WHEN s2.last_state_change<='1970-01-01 00:00:00' THEN p.program_start_time ELSE s2.last_state_change END) AS f_15, p.program_start_time AS f_16, s2.last_check AS f_17, s2.next_check AS f_18, s2.output AS f_19, TO_CHAR(s2.long_output) AS f_20, TO_CHAR(s2.perfdata) AS f_21, s2.current_check_attempt AS f_22, s2.max_check_attempts AS f_23, i.id AS f_24, s.id AS f_25, s2.notifications_enabled AS f_26, s2.problem_has_been_acknowledged AS f_27, s2.scheduled_downtime_depth AS f_28, s.action_url AS f_29, h.notes AS f_30, h.notes_url AS f_31, s.notes AS f_32, s.notes_url AS f_33, COALESCE(s2.has_been_checked, 0) AS f_34 FROM services s INNER JOIN instances i ON s.instance_id = i.id LEFT JOIN servicestatus s2 ON s.service_object_id = s2.service_object_id INNER JOIN hosts h ON s.host_object_id = h.host_object_id LEFT JOIN hoststatus h2 ON h.host_object_id = h2.host_object_id INNER JOIN objects o ON h.host_object_id = o.id INNER JOIN objects o2 ON s.service_object_id = o2.id INNER JOIN programstatus p ON i.id = p.instance_id WHERE (s.config_type = '1') ORDER BY o.name1 ASC ) a WHERE ROWNUM <= 25. Failing Query: "SELECT a.* FROM ( SELECT DISTINCT s.icon_image AS s__0, i.instance_name AS i__1, h.host_object_id AS h__2, s.service_object_id AS s__3, o.name1 AS o__4, h.alias AS h__5, h.display_name AS h__6, o2.name2 AS o2__7, s.display_name AS s__8, s2.process_performance_data AS s2__9, COALESCE(s2.current_state, 0) AS s2__10, COALESCE(s2.current_state, 0) AS s2__11, s2.last_hard_state AS s2__12, h2.last_hard_state AS h2__13, COALESCE(h2.current_state, 0) AS h2__14, (CASE WHEN s2.last_state_change<='1970-01-01 00:00:00' THEN p.program_start_time ELSE s2.last_state_change END) AS s__15, p.program_start_time AS p__16, s2.last_check AS s2__17, s2.next_check AS s2__18, s2.output AS s2__19, s2.long_output AS s2__20, s2.perfdata AS s2__21, s2.current_check_attempt AS s2__22, s2.max_check_attempts AS s2__23, i.instance_id AS i__24, s.service_id AS s__25, s2.notifications_enabled AS s2__26, s2.problem_has_been_acknowledged AS s2__27, s2.scheduled_downtime_depth AS s2__28, s.action_url AS s__29, h.notes AS h__30, h.notes_url AS h__31, s.notes AS s__32, s.notes_url AS s__33, COALESCE(s2.has_been_checked, 0) AS s2__34 FROM services s INNER JOIN instances i ON s.instance_id = i.instance_id LEFT JOIN servicestatus s2 ON s.service_object_id = s2.service_object_id INNER JOIN hosts h ON s.host_object_id = h.host_object_id LEFT JOIN hoststatus h2 ON h.host_object_id = h2.host_object_id INNER JOIN objects o ON h.host_object_id = o.object_id INNER JOIN objects o2 ON s.service_object_id = o2.object_id INNER JOIN programstatus p ON i.instance_id = p.instance_id WHERE (s.config_type = '1') ORDER BY o.name1 ASC ) a WHERE ROWNUM <= 25"
Stacktrace:

#0 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Connection.php(1033): Doctrine_Connection->rethrowException(Object(Doctrine_Adapter_Exception), Object(Doctrine_Connection_IcingaOracle), 'SELECT a.* FROM...')
#1 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Query/Abstract.php(976): Doctrine_Connection->execute('SELECT a.* FROM...', Array)
#2 /usr/share/icinga-web/app/modules/Api/lib/database/IcingaDoctrine_Query.class.php(116): Doctrine_Query_Abstract->_execute(NULL)
#3 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Query/Abstract.php(1026): IcingaDoctrine_Query->_execute(NULL)
#4 /usr/share/icinga-web/app/modules/Api/models/Views/ApiDQLViewModel.class.php(92): Doctrine_Query_Abstract->execute(NULL, 5)
#5 /usr/share/icinga-web/app/modules/Cronks/lib/template/TemplateWorker/DQLCronkTemplateWorker.class.php(82): API_Views_ApiDQLViewModel->getResult()
#6 /usr/share/icinga-web/app/modules/Cronks/views/System/ViewProcSuccessView.class.php(141): DQLCronkTemplateWorker->fetchDataArray()
#7 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1672): Cronks_System_ViewProcSuccessView->executeJson(Object(AgaviWebRequestDataHolder))
#8 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1533): AgaviExecutionFilter->executeView(Object(AgaviExecutionContainer))
#9 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviExecutionFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#10 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#11 /usr/share/icinga-web/lib/agavi/src/filter/AgaviSecurityFilter.class.php(73): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#12 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviSecurityFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#13 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#14 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(870): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#15 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1266): AgaviExecutionContainer->execute()
#16 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviDispatchFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#17 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#18 /usr/share/icinga-web/lib/agavi/src/filter/AgaviFormPopulationFilter.class.php(78): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#19 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFormPopulationFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#20 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(579): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#21 /usr/share/icinga-web/pub/index.php(49): AgaviController->dispatch()
#22 {main}

icinga-migration · 2013-04-13T11:01:01Z

Updated by mfriedrich on 2013-04-13 11:01:01 +00:00

now trying your fixed branch.

Throws the same errors on e.g.

/icinga-web/modules/cronks/viewproc/icinga-service-template/json

A critical exception occured!
Uncaught Doctrine_Connection_Oracle_Exception thrown:
ORA-00932: inconsistent datatypes: expected - got CLOB : SELECT a.* FROM ( SELECT DISTINCT s.icon_image AS f_0, i.instance_name AS f_1, h.host_object_id AS f_2, s.service_object_id AS f_3, o.name1 AS f_4, h.alias AS f_5, h.display_name AS f_6, o2.name2 AS f_7, s.display_name AS f_8, s2.process_performance_data AS f_9, COALESCE(s2.current_state, 0) AS f_10, COALESCE(s2.current_state, 0) AS f_11, s2.last_hard_state AS f_12, h2.last_hard_state AS f_13, COALESCE(h2.current_state, 0) AS f_14, (CASE WHEN s2.last_state_change<='1970-01-01 00:00:00' THEN p.program_start_time ELSE s2.last_state_change END) AS f_15, p.program_start_time AS f_16, s2.last_check AS f_17, s2.next_check AS f_18, s2.output AS f_19, s2.perfdata AS f_20, s2.current_check_attempt AS f_21, s2.max_check_attempts AS f_22, i.id AS f_23, s.id AS f_24, s2.notifications_enabled AS f_25, s2.problem_has_been_acknowledged AS f_26, s2.scheduled_downtime_depth AS f_27, s.action_url AS f_28, h.notes AS f_29, h.notes_url AS f_30, s.notes AS f_31, s.notes_url AS f_32, COALESCE(s2.has_been_checked, 0) AS f_33 FROM services s INNER JOIN instances i ON s.instance_id = i.id LEFT JOIN servicestatus s2 ON s.service_object_id = s2.service_object_id INNER JOIN hosts h ON s.host_object_id = h.host_object_id LEFT JOIN hoststatus h2 ON h.host_object_id = h2.host_object_id INNER JOIN objects o ON h.host_object_id = o.id INNER JOIN objects o2 ON s.service_object_id = o2.id INNER JOIN programstatus p ON i.id = p.instance_id WHERE (s.config_type = '1') ORDER BY o.name1 ASC ) a WHERE ROWNUM <= 25. Failing Query: "SELECT a.* FROM ( SELECT DISTINCT s.icon_image AS s__0, i.instance_name AS i__1, h.host_object_id AS h__2, s.service_object_id AS s__3, o.name1 AS o__4, h.alias AS h__5, h.display_name AS h__6, o2.name2 AS o2__7, s.display_name AS s__8, s2.process_performance_data AS s2__9, COALESCE(s2.current_state, 0) AS s2__10, COALESCE(s2.current_state, 0) AS s2__11, s2.last_hard_state AS s2__12, h2.last_hard_state AS h2__13, COALESCE(h2.current_state, 0) AS h2__14, (CASE WHEN s2.last_state_change<='1970-01-01 00:00:00' THEN p.program_start_time ELSE s2.last_state_change END) AS s__15, p.program_start_time AS p__16, s2.last_check AS s2__17, s2.next_check AS s2__18, s2.output AS s2__19, s2.perfdata AS s2__20, s2.current_check_attempt AS s2__21, s2.max_check_attempts AS s2__22, i.instance_id AS i__23, s.service_id AS s__24, s2.notifications_enabled AS s2__25, s2.problem_has_been_acknowledged AS s2__26, s2.scheduled_downtime_depth AS s2__27, s.action_url AS s__28, h.notes AS h__29, h.notes_url AS h__30, s.notes AS s__31, s.notes_url AS s__32, COALESCE(s2.has_been_checked, 0) AS s2__33 FROM services s INNER JOIN instances i ON s.instance_id = i.instance_id LEFT JOIN servicestatus s2 ON s.service_object_id = s2.service_object_id INNER JOIN hosts h ON s.host_object_id = h.host_object_id LEFT JOIN hoststatus h2 ON h.host_object_id = h2.host_object_id INNER JOIN objects o ON h.host_object_id = o.object_id INNER JOIN objects o2 ON s.service_object_id = o2.object_id INNER JOIN programstatus p ON i.instance_id = p.instance_id WHERE (s.config_type = '1') ORDER BY o.name1 ASC ) a WHERE ROWNUM <= 25"
Stacktrace:

#0 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Connection.php(1033): Doctrine_Connection->rethrowException(Object(Doctrine_Adapter_Exception), Object(Doctrine_Connection_IcingaOracle), 'SELECT a.* FROM...')
#1 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Query/Abstract.php(976): Doctrine_Connection->execute('SELECT a.* FROM...', Array)
#2 /usr/share/icinga-web/app/modules/Api/lib/database/IcingaDoctrine_Query.class.php(116): Doctrine_Query_Abstract->_execute(NULL)
#3 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Query/Abstract.php(1026): IcingaDoctrine_Query->_execute(NULL)
#4 /usr/share/icinga-web/app/modules/Api/models/Views/ApiDQLViewModel.class.php(92): Doctrine_Query_Abstract->execute(NULL, 5)
#5 /usr/share/icinga-web/app/modules/Cronks/lib/template/TemplateWorker/DQLCronkTemplateWorker.class.php(82): API_Views_ApiDQLViewModel->getResult()
#6 /usr/share/icinga-web/app/modules/Cronks/views/System/ViewProcSuccessView.class.php(141): DQLCronkTemplateWorker->fetchDataArray()
#7 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1672): Cronks_System_ViewProcSuccessView->executeJson(Object(AgaviWebRequestDataHolder))
#8 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1533): AgaviExecutionFilter->executeView(Object(AgaviExecutionContainer))
#9 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviExecutionFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#10 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#11 /usr/share/icinga-web/lib/agavi/src/filter/AgaviSecurityFilter.class.php(73): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#12 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviSecurityFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#13 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#14 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(870): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#15 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1266): AgaviExecutionContainer->execute()
#16 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviDispatchFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#17 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#18 /usr/share/icinga-web/lib/agavi/src/filter/AgaviFormPopulationFilter.class.php(78): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#19 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFormPopulationFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#20 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(579): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#21 /usr/share/icinga-web/pub/index.php(49): AgaviController->dispatch()
#22 {main}

this happens with all status and history grids. strangely enough, it works with open problems.

icinga-migration · 2013-04-13T11:12:28Z

Updated by mfriedrich on 2013-04-13 11:12:28 +00:00

Assigned to changed from mfriedrich to Tommi

I've got no idea in this field (php and oci fetching) so i'll reassign it to you. and since this might be a showstopper - if this cannot be solved til next week tuesday evening, we'll reschedule it for 1.10

icinga-migration · 2013-04-13T13:15:57Z

Updated by Tommi on 2013-04-13 13:15:57 +00:00

OS Version changed from CentOS 6.3 to CentOS 6.4

This error i doesn't had before, but dont tried all screens.

This is not related to php/oci but pure sql error. Cant believe this works well in a current version.

you can't do a distinct over a dataset containing a clob field. This distinct query doesnt make sense anyway because of the nature of the sum of all selected fields there are no duplicates expected. Remove the distinct and the ora-930 is gone. BTW: perfdata is already a clob sine 1.7., means this problem is still in place since a year.

i am still looking for the log which provides deeper information about "internal Errors". It is neither in "../log/icinga-web**.log", nor in ".../log/debug**.log" nor in /var/log/messages. It is very annoying to get error popups but not being able to find any details about the reason

icinga-migration · 2013-04-13T13:23:06Z

Updated by mfriedrich on 2013-04-13 13:23:06 +00:00

i've only used your git branch for testing the web, so you may reproduce the error easily with your install.

for testing icinga web and the asynchronous json request 500, try the wiki - https://wiki.icinga.org/display/testing/Icinga+Web+Testing#IcingaWebTesting-DebugRequestsandResponses

icinga-migration · 2013-04-13T19:08:15Z

Updated by Tommi on 2013-04-13 19:08:15 +00:00

Status changed from Assigned to Feedback

OK, could find the source of the query causing the ORA-00932 and removed distinct in the xml.
So far,it works. Only status map cronk doesnt work, but this looks not related to the clob change i did or someone points me into.

                    A critical exception occured!
                    

                        Uncaught Doctrine_Exception thrown:
                        
                                                        Couldn't find class hp                        
                        Stacktrace:#0 /opt/icinga-web/lib/doctrine/lib/Doctrine/Table.php(256): Doctrine_Table->initDefinition()

#1 /opt/icinga-web/lib/doctrine/lib/Doctrine/Connection.php(1134): Doctrine_Table->__construct('hp', Object(Doctrine_Connection_IcingaOracle), true)
#2 /opt/icinga-web/lib/doctrine/lib/Doctrine/Query.php(1951): Doctrine_Connection->getTable('hp')
#3 /opt/icinga-web/lib/doctrine/lib/Doctrine/Query.php(1747): Doctrine_Query->loadRoot('hp', 'hp')
#4 /opt/icinga-web/lib/doctrine/lib/Doctrine/Query.php(721): Doctrine_Query->load('hp')
#5 /opt/icinga-web/lib/doctrine/lib/Doctrine/Query.php(829): Doctrine_Query->parseClause('hp.host_object_...')
#6 /opt/icinga-web/lib/doctrine/lib/Doctrine/Query.php(705): Doctrine_Query->parseFunctionExpression('COALESCE(hp.hos...')
#7 /opt/icinga-web/lib/doctrine/lib/Doctrine/Query.php(638): Doctrine_Query->parseClause('COALESCE(hp.hos...')
#8 /opt/icinga-web/lib/doctrine/lib/Doctrine/Query/Select.php(37): Doctrine_Query->parseSelect('COALESCE(hp.hos...')
#9 /opt/icinga-web/lib/doctrine/lib/Doctrine/Query/Abstract.php(2077): Doctrine_Query_Select->parse('COALESCE(hp.hos...')
#10 /opt/icinga-web/lib/doctrine/lib/Doctrine/Query.php(1174): Doctrine_Query_Abstract->_processDqlQueryPart('select', Array)
#11 /opt/icinga-web/lib/doctrine/lib/Doctrine/Query.php(1138): Doctrine_Query->buildSqlQuery(true)
#12 /opt/icinga-web/app/modules/Api/models/Views/ApiDQLViewModel.class.php(294): Doctrine_Query->getSqlQuery()
#13 /opt/icinga-web/app/modules/Api/models/Views/ApiDQLViewModel.class.php(190): API_Views_ApiDQLViewModel->applyDQLCalls(Object(IcingaDoctrine_Query), Array)
#14 /opt/icinga-web/app/modules/Api/models/Views/ApiDQLViewModel.class.php(83): API_Views_ApiDQLViewModel->parseDQLExtensions()
#15 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(441): API_Views_ApiDQLViewModel->initialize(Object(AppKitAgaviContext), Array)
#16 /opt/icinga-web/app/modules/Cronks/models/System/StatusMapModel.class.php(69): AgaviContext->getModel('Views.ApiDQLVie...', 'Api', Array)
#17 /opt/icinga-web/app/modules/Cronks/views/System/StatusMapSuccessView.class.php(41): Cronks_System_StatusMapModel->getParentChildStructure()
#18 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(1672): Cronks_System_StatusMapSuccessView->executeJson(Object(AgaviWebRequestDataHolder))
#19 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(1533): AgaviExecutionFilter->executeView(Object(AgaviExecutionContainer))
#20 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(1255): AgaviExecutionFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#21 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#22 /opt/icinga-web/lib/agavi/src/filter/AgaviSecurityFilter.class.php(73): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#23 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(1255): AgaviSecurityFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#24 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#25 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(870): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#26 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(1266): AgaviExecutionContainer->execute()
#27 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(1255): AgaviDispatchFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#28 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#29 /opt/icinga-web/lib/agavi/src/filter/AgaviFormPopulationFilter.class.php(78): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#30 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(1700): AgaviFormPopulationFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#31 /opt/icinga-web/app/cache/config/compile.xml_production__27510727f020c0daff3f34bc2ce961f1002632b7.php(579): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#32 /opt/icinga-web/pub/index.php(49): AgaviController->dispatch()
#33 {main}

icinga-migration · 2013-04-13T19:50:41Z

Updated by mfriedrich on 2013-04-13 19:50:41 +00:00

the problem with your distinct fix is that this was done for another fix (at least for the services recently).

commit f5807588a02d8ea9f98c9c1c78d40c67714a83ec
Author: Marius Hein 
Date:   Thu Apr 4 11:57:40 2013 +0200

    Duplicates on services

    Added DISTINCT to service querys. With various credentials
    this will resolve double service entries in grid.

    fixes #3921

there are more distinct selects around in this area.

icinga-migration · 2013-04-13T19:57:06Z

Updated by mfriedrich on 2013-04-13 19:57:06 +00:00

the statusmap bug was fixed in recent next commits, where your branch isn't rebased against right now.

icinga-migration · 2013-04-13T20:11:42Z

Updated by mfriedrich on 2013-04-13 20:11:42 +00:00

regarding the clobs and distinct - since this adds a larger historical tree, i've opened #3965 as release critical bug.

i'll wait for marius to discuss what to do further.

icinga-migration · 2013-04-16T09:12:07Z

Updated by mfriedrich on 2013-04-16 09:12:07 +00:00

resolving #3965 will allow a proper merge then. already rebased the branch against next, will merge after the core rebase-merge is done. thanks for fixing.

icinga-migration · 2013-04-16T09:25:17Z

Updated by mfriedrich on 2013-04-16 09:25:17 +00:00

Status changed from Feedback to Resolved
Done % changed from 80 to 100

merged to 'next'.

icinga-migration closed this as completed Apr 16, 2013

icinga-migration added bug Database & Queries labels Jan 17, 2017

icinga-migration added this to the 1.9 milestone Jan 17, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[dev.icinga.com #3855] ORA-22835 causes blank status data cronks #1050

[dev.icinga.com #3855] ORA-22835 causes blank status data cronks #1050

icinga-migration commented Mar 13, 2013

icinga-migration commented Apr 6, 2013

icinga-migration commented Apr 7, 2013

icinga-migration commented Apr 7, 2013

icinga-migration commented Apr 8, 2013

icinga-migration commented Apr 12, 2013

icinga-migration commented Apr 12, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 16, 2013

icinga-migration commented Apr 16, 2013

[dev.icinga.com #3855] ORA-22835 causes blank status data cronks #1050

[dev.icinga.com #3855] ORA-22835 causes blank status data cronks #1050

Comments

icinga-migration commented Mar 13, 2013

icinga-migration commented Apr 6, 2013

icinga-migration commented Apr 7, 2013

icinga-migration commented Apr 7, 2013

icinga-migration commented Apr 8, 2013

icinga-migration commented Apr 12, 2013

icinga-migration commented Apr 12, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 13, 2013

icinga-migration commented Apr 16, 2013

icinga-migration commented Apr 16, 2013