Skip to content
This repository has been archived by the owner on Jan 15, 2019. It is now read-only.

[dev.icinga.com #3965] SELECT DISTINCT on CLOBs breaks Oracle support #1097

Closed
icinga-migration opened this issue Apr 13, 2013 · 11 comments
Closed

[dev.icinga.com #3965] SELECT DISTINCT on CLOBs breaks Oracle support #1097

icinga-migration opened this issue Apr 13, 2013 · 11 comments
Labels
bug Database & Queries Urgent
Milestone
1.9

Comments

@icinga-migration
Copy link

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

Created by mfriedrich on 2013-04-13 20:09:33 +00:00

Assignee: mfrosch
Status: Resolved (closed on 2013-04-22 16:20:58 +00:00)
Target Version: 1.9
Last Update: 2013-04-22 16:20:58 +00:00 (in Redmine)

Icinga Version: 1.9.0dev
Icinga Web Version: 1.9.0dev
IDO Version: 1.9.0dev
OS Version: Debian 6.0.7 x64
DB Type: Oracle
DB Version: 11.2
Browser Version: chromium 25

Like evaluated in https://dev.icinga.org/issues/3855#note-13

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}
The fix for [\#3921](https://dev.icinga.com/issues/3921) also introduced DISTINCT, which breaks oracle support again.

Possible solutions:

rsim/oracle-enhanced#112
http://www.dbforums.com/oracle/1683783-how-retrieve-clob-data-when-using-distinct-keyword-other-columns-sql.html

columns included will be output, longoutput, perfdata causing the problems.

So this can be isolated into the following queries

  • histories.xml
            SELECT DISTINCT
                h.icon_image AS HOST_ICON_IMAGE,
                i.instance_name AS INSTANCE_NAME,
                hsh.statehistory_id AS STATEHISTORY_ID,
                h.host_object_id AS HOST_OBJECT_ID,
                oh.name1 AS HOST_NAME,
                h.alias AS HOST_ALIAS,
                h.display_name AS HOST_DISPLAY_NAME,
                hsh.state AS STATEHISTORY_STATE,
                hsh.state_time AS STATEHISTORY_STATE_TIME,
                hsh.output AS STATEHISTORY_OUTPUT,
                hsh.long_output AS STATEHISTORY_LONG_OUTPUT,
                hsh.current_check_attempt AS STATEHISTORY_CURRENT_CHECK_ATTEMPT,
                hsh.max_check_attempts AS STATEHISTORY_MAX_CHECK_ATTEMPTS,
                hsh.current_check_attempt AS state_check_attempt,
                hsh.max_check_attempts AS state_max_check_attempts
            FROM IcingaStatehistory hsh
            INNER JOIN hsh.object oh WITH oh.objecttype_id = 1
            INNER JOIN oh.host h
            INNER JOIN hsh.instance i
            WHERE h.config_type = '${retained_flag}'
  • host.xml
            SELECT DISTINCT
                h.icon_image AS HOST_ICON_IMAGE,
                i.instance_name AS INSTANCE_NAME,
                h.host_object_id AS HOST_OBJECT_ID,
                oh.name1 AS HOST_NAME,
                h.alias AS HOST_ALIAS,
                h.display_name AS HOST_DISPLAY_NAME,
                COALESCE(hs.current_state,0) AS HOST_CURRENT_STATE,
                COALESCE(hs.current_state,0) AS HOST_STATUS,
                hs.last_hard_state AS HOST_HARD_STATE,
                (CASE WHEN hs.last_state_change<='1970-01-01 00:00:00' THEN ps.program_start_time ELSE hs.last_state_change END) as DURATION_START,
                hs.last_check AS HOST_LAST_CHECK,
                hs.next_check AS HOST_NEXT_CHECK,
                ps.program_start_time AS HOST_PROGRAM_START_TIME,
                hs.output AS HOST_OUTPUT,
                hs.long_output AS HOST_LONG_OUTPUT,
                hs.current_check_attempt AS HOST_CURRENT_CHECK_ATTEMPT,
                hs.max_check_attempts AS HOST_MAX_CHECK_ATTEMPT,
                hs.process_performance_data AS HOST_PROCESS_PERFORMANCE_DATA,
                hs.max_check_attempts AS max_attempt,
                hs.perfdata AS HOST_PERFORMANCE_DATA,
                i.instance_id AS HOST_INSTANCE_ID,
                h.host_id AS HOST_ID,
                h.action_url AS HOST_ACTION_URL,
                h.notes as HOST_NOTES,
                h.notes_url AS HOST_NOTES_URL,
                ((COALESCE(hs.has_been_checked,0)-1)*-1) as HOST_IS_PENDING,
                hs.notifications_enabled as notifications_enabled,
                hs.problem_has_been_acknowledged as problem_acknowledged,
                hs.scheduled_downtime_depth as scheduled_downtime_depth,
                COALESCE(hs.has_been_checked,0) AS host_has_been_checked
            FROM IcingaHosts h
            LEFT JOIN h.instance i
            LEFT JOIN h.status hs
            LEFT JOIN h.object oh
            LEFT JOIN i.programstatus as ps

            WHERE h.config_type = '${retained_flag}'

            SELECT DISTINCT
                   h.icon_image AS SERVICE_ICON_IMAGE,
                i.instance_name AS INSTANCE_NAME,
                h.host_object_id AS HOST_OBJECT_ID,
                oh.name1 AS HOST_NAME,
                h.alias AS HOST_ALIAS,
                h.display_name AS HOST_DISPLAY_NAME,
                hs.current_state AS HOST_CURRENT_STATE,
                hs.last_hard_state AS HOST_HARD_STATE,
                hs.process_performance_data AS HOST_PROCESS_PERFORMANCE_DATA,
                (CASE WHEN hs.last_state_change<='1970-01-01 00:00:00' THEN ps.program_start_time ELSE hs.last_state_change END) as DURATION_START,
                hs.last_check AS HOST_LAST_CHECK,
                hs.next_check AS HOST_NEXT_CHECK,
                ps.program_start_time AS HOST_PROGRAM_START_TIME,
                hs.perfdata AS HOST_PERFORMANCE_DATA,
                hs.output AS HOST_OUTPUT,
                hs.long_output AS HOST_LONG_OUTPUT,
                hs.current_check_attempt AS HOST_CURRENT_CHECK_ATTEMPT,
                hs.max_check_attempts AS HOST_MAX_CHECK_ATTEMPT,
                i.instance_id AS HOST_INSTANCE_ID,
                h.host_id AS HOST_ID,
                h.action_url AS HOST_ACTION_URL,
                h.notes_url AS HOST_NOTES_URL,
                hs.notifications_enabled as notifications_enabled,
                hs.problem_has_been_acknowledged as problem_acknowledged,
                hs.scheduled_downtime_depth as scheduled_downtime_depth,
                hs.has_been_checked as host_has_been_checked
            FROM IcingaHosts h
            INNER JOIN h.instance i
            INNER JOIN h.status hs WITH
            (
                hs.current_state != 0 AND
                hs.problem_has_been_acknowledged = 0 AND
                hs.scheduled_downtime_depth = 0
            )
            INNER JOIN h.object oh
            INNER JOIN i.programstatus as ps
            WHERE h.config_type = '${retained_flag}'
  • services.xml
            SELECT DISTINCT
                s.icon_image AS SERVICE_ICON_IMAGE,
                i.instance_name AS INSTANCE_NAME,
                h.host_object_id AS HOST_OBJECT_ID,
                s.service_object_id AS SERVICE_OBJECT_ID,
                oh.name1 AS HOST_NAME,
                h.alias AS HOST_ALIAS,
                h.display_name AS HOST_DISPLAY_NAME,
                os.name2 AS SERVICE_NAME,
                s.display_name AS SERVICE_DISPLAY_NAME,
                ss.process_performance_data AS SERVICE_PROCESS_PERFORMANCE_DATA,
                COALESCE(ss.current_state,0) AS SERVICE_CURRENT_STATE,
                COALESCE(ss.current_state,0) AS SERVICE_STATUS,
                ss.last_hard_state AS SERVICE_HARD_STATE,
                hs.last_hard_state AS HOST_HARD_STATE,
                COALESCE(hs.current_state,0) AS HOST_CURRENT_STATE,
                (CASE WHEN ss.last_state_change<='1970-01-01 00:00:00' THEN ps.program_start_time ELSE ss.last_state_change END) as DURATION_START,
                ps.program_start_time AS SERVICE_PROGRAM_START_TIME,
                ss.last_check AS SERVICE_LAST_CHECK,
                ss.next_check AS SERVICE_NEXT_CHECK,
                ss.output AS SERVICE_OUTPUT,
                ss.long_output AS SERVICE_LONG_OUTPUT,
                ss.perfdata AS SERVICE_PERFORMANCE_DATA,
                ss.current_check_attempt AS SERVICE_CURRENT_CHECK_ATTEMPT,
                ss.max_check_attempts AS service_max_attempt,
                i.instance_id AS SERVICE_INSTANCE_ID,
                s.service_id AS SERVICE_ID,
                ss.notifications_enabled as notifications_enabled,
                ss.problem_has_been_acknowledged as problem_acknowledged,
                ss.scheduled_downtime_depth as scheduled_downtime_depth,
                s.action_url AS SERVICE_ACTION_URL,
                h.notes as HOST_NOTES,
                h.notes_url AS HOST_NOTES_URL,
                s.notes as SERVICE_NOTES,
                s.notes_url AS SERVICE_NOTES_URL,
                COALESCE(ss.has_been_checked,0) as service_has_been_checked
            FROM IcingaServices s
            INNER JOIN s.instance i
            LEFT JOIN s.status ss
            INNER JOIN s.host h
            LEFT JOIN h.status hs
            INNER JOIN h.object oh
            INNER JOIN s.object os
            INNER JOIN i.programstatus as ps

            WHERE s.config_type = '${retained_flag}'

Proposed fix: select the clobs seperatedly, and do not use distinct there at all cost.

and before you ask - #3855 is only partly related to this, as it only adds output column as clob, long_output is already there. long_output has been added to statehistory in #3918

Attachments

Changesets

2013-04-15 13:15:45 +00:00 by mhein 0f0b310

SELECT DISTINCT on CLOBs breaks Oracle support

Add test to test regression of AppKitArrayUtil because
it is important that this tool works.

refs #3965

2013-04-15 13:15:46 +00:00 by mhein f0a306a

SELECT DISTINCT on CLOBs breaks Oracle support

Remove distinct from api views. This is made now with PHP on
application server side.

fixes #3965

2013-04-15 13:15:46 +00:00 by mhein b195821

SELECT DISTINCT on CLOBs breaks Oracle support

Add php style distinct system

refs #3965

2013-04-16 08:01:10 +00:00 by mhein f6876e7

SELECT DISTINCT on CLOBs breaks Oracle support

Set default distinct to false.

fixes #3965

2013-04-21 21:07:09 +00:00 by mfrosch 95beee9

Revert "SELECT DISTINCT on CLOBs breaks Oracle support"

This reverts commit f6876e7fb3569b9b151cf6bbe6310bcef7412a91.

refs #3965

2013-04-21 21:07:33 +00:00 by mfrosch db11592

Revert "SELECT DISTINCT on CLOBs breaks Oracle support"

This reverts commit f0a306ab6d5d9cb99b6307c294a3ac9eb65dbb66.

refs #3965

2013-04-21 21:07:56 +00:00 by mfrosch 4991c66

Revert "SELECT DISTINCT on CLOBs breaks Oracle support"

This reverts commit b19582188121cd229570017e3a40094932f3f1cc.

refs #3965

2013-04-21 21:09:20 +00:00 by mfrosch 116ad9d

Added Merge View to views including BLOB data

This allows to merge any BLOB field in a second query.

Which should allow us to use DISTINCT where required.

We need DISTINCT to avoid duplication when multiple credentials match.

refs #3965

2013-04-22 07:25:42 +00:00 by mfrosch 041e5d9

Added BLOB field merging to Api

The new code avoids selecting blob fields in the original
query and creates a second query to select all requested
blob fields.

After the second query the results are merged, based on
the primary key row (we pull this field from the first
element of the columnsDefault data).

refs #3965

2013-04-22 12:14:13 +00:00 by mfrosch c460f00

Forced no DISTINCT on selecting of BLOB fields

Fix for Oracle

refs #3965

Relations:
@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-04-13 20:17:08 +00:00

#3855 hasn't been merged to 'next' yet, as it's unclear how to proceed here. for the time being, long_output and perfdata columns are broken, #3855 would add the output column to that problem set then.

we need to discuss a proper solution here.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-04-13 20:24:32 +00:00

https://blogs.oracle.com/rammenon/entry/dbadapter\_javasqlsqlexception

You'll get the same error if you use "GROUP BY clobcolumn" as well. Took me a long time to figure that out after one of our VARCHAR2 columns got changed to a CLOB.

@icinga-migration
Copy link
Author

Updated by mhein on 2013-04-15 13:16:12 +00:00

  • Status changed from Assigned to Resolved
  • Done % changed from 0 to 100

Applied in changeset f0a306a.

@icinga-migration
Copy link
Author

Updated by mhein on 2013-04-15 13:17:43 +00:00

Add php distinct system, please test if everything works as expected.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-04-15 14:55:15 +00:00

  • Status changed from Resolved to Assigned
  • Done % changed from 100 to 80

the status and history grids are fixed, but there's more to do here - when clicking on e.g. 'service details' you will get a generic query where output/long_output/perfdata gets selected with DISTINCT and then breaks again. So basically the PHP-ish distinct maker should be applied here as well (if this is not already the case).

best would be to entirely wipe DISTINCT to stay safe for future fixes. and to update web dev guidelines to forbid to use DISTINCT queries in regards of Oracle CLOBs.

Request URL:http://foo/icinga-web/modules/web/api/json
Request Method:POST
Status Code:500 Internal Server Error
Request Headersview source
Accept:*/*
Accept-Charset:ISO-8859-1,utf-8;q=0.7,*;q=0.3
Accept-Encoding:gzip,deflate,sdch
Accept-Language:en-US,en;q=0.8
Connection:keep-alive
Content-Length:2010
Content-Type:application/x-www-form-urlencoded; charset=UTF-8
Cookie:icinga-web=foo; icinga-web-loginname=root
Host:foo
Origin:http://foo
Referer:http://foo/icinga-web/modules/web/portal
User-Agent:Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.160 Safari/537.22
X-Requested-With:XMLHttpRequest
Form Dataview sourceview URL encoded
db:
target:service
connection:icinga
enableRewrite:true
filters_json:{"type":"AND","field":[{"type":"atom","field":["SERVICE_OBJECT_ID"],"method":["="],"value":["27"]}]}
columns[0]:SERVICE_ID
columns[1]:SERVICE_INSTANCE_ID
columns[2]:SERVICE_CONFIG_TYPE
columns[3]:SERVICE_IS_ACTIVE
columns[4]:SERVICE_OBJECT_ID
columns[5]:SERVICE_NAME
columns[6]:SERVICE_DISPLAY_NAME
columns[7]:SERVICE_NOTIFICATIONS_ENABLED
columns[8]:SERVICE_FLAP_DETECTION_ENABLED
columns[9]:SERVICE_PASSIVE_CHECKS_ENABLED
columns[10]:SERVICE_EVENT_HANDLER_ENABLED
columns[11]:SERVICE_ACTIVE_CHECKS_ENABLED
columns[12]:SERVICE_RETAIN_STATUS_INFORMATION
columns[13]:SERVICE_RETAIN_NONSTATUS_INFORMATION
columns[14]:SERVICE_OBSESS_OVER_SERVICE
columns[15]:SERVICE_FAILURE_PREDICTION_ENABLED
columns[16]:SERVICE_NOTES
columns[17]:SERVICE_NOTES_URL
columns[18]:SERVICE_ACTION_URL
columns[19]:SERVICE_ICON_IMAGE
columns[20]:SERVICE_ICON_IMAGE_ALT
columns[21]:SERVICE_OUTPUT
columns[22]:SERVICE_LONG_OUTPUT
columns[23]:SERVICE_PERFDATA
columns[24]:SERVICE_PROCESS_PERFORMANCE_DATA
columns[25]:SERVICE_CURRENT_STATE
columns[26]:SERVICE_CURRENT_CHECK_ATTEMPT
columns[27]:SERVICE_MAX_CHECK_ATTEMPTS
columns[28]:SERVICE_LAST_CHECK
columns[29]:SERVICE_LAST_STATE_CHANGE
columns[30]:SERVICE_CHECK_TYPE
columns[31]:SERVICE_LATENCY
columns[32]:SERVICE_EXECUTION_TIME
columns[33]:SERVICE_NEXT_CHECK
columns[34]:SERVICE_HAS_BEEN_CHECKED
columns[35]:SERVICE_LAST_HARD_STATE
columns[36]:SERVICE_LAST_HARD_STATE_CHANGE
columns[37]:SERVICE_LAST_NOTIFICATION
columns[38]:SERVICE_STATE_TYPE
columns[39]:SERVICE_IS_FLAPPING
columns[40]:SERVICE_PROBLEM_HAS_BEEN_ACKNOWLEDGED
columns[41]:SERVICE_SCHEDULED_DOWNTIME_DEPTH
columns[42]:SERVICE_SHOULD_BE_SCHEDULED
columns[43]:SERVICE_STATUS_UPDATE_TIME
Response Headersview source
Access-Control-Allow-Headers:x-requested-with
Access-Control-Allow-Methods:POST GET UPDATE
Access-Control-Allow-Origin:*
Access-Control-Max-Age:720000
Cache-Control:no-store, no-cache, must-revalidate, post-check=0, pre-check=0
Connection:close
Content-Encoding:gzip
Content-Length:1897
Content-Type:text/html
Date:Mon, 15 Apr 2013 14:24:35 GMT
Expires:Thu, 19 Nov 1981 08:52:00 GMT
Pragma:no-cache
Server:Apache/2.2.16 (Debian)
Vary:Accept-Encoding
X-Powered-By:PHP/5.3.3-7+squeeze15

        
            
                
            
            
                
                    A critical exception occured!
                    

                        Uncaught Doctrine_Connection_Oracle_Exception thrown:
                        
                                                        ORA-00932: inconsistent datatypes: expected - got CLOB : SELECT DISTINCT o.id AS f_0, o.is_active AS f_1, o.name2 AS f_2, s.service_object_id AS f_3, s.id AS f_4, s.instance_id AS f_5, s.config_type AS f_6, s.display_name AS f_7, s.event_handler_enabled AS f_8, s.retain_status_information AS f_9, s.retain_nonstatus_information AS f_10, s.obsess_over_service AS f_11, s.failure_prediction_enabled AS f_12, s.notes AS f_13, s.notes_url AS f_14, s.action_url AS f_15, s.icon_image AS f_16, s.icon_image_alt AS f_17, s.process_performance_data AS f_18, s2.id AS f_19, s2.notifications_enabled AS f_20, s2.flap_detection_enabled AS f_21, s2.passive_checks_enabled AS f_22, s2.active_checks_enabled AS f_23, s2.output AS f_24, s2.long_output AS f_25, s2.perfdata AS f_26, s2.current_check_attempt AS f_27, s2.max_check_attempts AS f_28, s2.last_check AS f_29, s2.last_state_change AS f_30, s2.check_type AS f_31, s2.latency AS f_32, s2.execution_time AS f_33, s2.next_check AS f_34, s2.last_hard_state AS f_35, s2.last_hard_state_change AS f_36, s2.last_notification AS f_37, s2.state_type AS f_38, s2.is_flapping AS f_39, s2.problem_has_been_acknowledged AS f_40, s2.scheduled_downtime_depth AS f_41, s2.should_be_scheduled AS f_42, s2.status_update_time AS f_43, s.id AS f_44, s.instance_id AS f_45, s.config_type AS f_46, o.is_active AS f_47, o.id AS f_48, o.name2 AS f_49, s.display_name AS f_50, s2.notifications_enabled AS f_51, s2.flap_detection_enabled AS f_52, s2.passive_checks_enabled AS f_53, s.event_handler_enabled AS f_54, s2.active_checks_enabled AS f_55, s.retain_status_information AS f_56, s.retain_nonstatus_information AS f_57, s.obsess_over_service AS f_58, s.failure_prediction_enabled AS f_59, s.notes AS f_60, s.notes_url AS f_61, s.action_url AS f_62, s.icon_image AS f_63, s.icon_image_alt AS f_64, s2.output AS f_65, s2.long_output AS f_66, s2.perfdata AS f_67, s.process_performance_data AS f_68, COALESCE(s2.current_state, 0) AS f_69, COALESCE(s2.current_state, 0) AS f_70, s2.current_check_attempt AS f_71, s2.max_check_attempts AS f_72, s2.last_check AS f_73, s2.last_state_change AS f_74, s2.check_type AS f_75, s2.latency AS f_76, s2.execution_time AS f_77, s2.next_check AS f_78, COALESCE(s2.has_been_checked, 0) AS f_79, COALESCE(s2.has_been_checked, 0) AS f_80, s2.last_hard_state AS f_81, s2.last_hard_state_change AS f_82, s2.last_notification AS f_83, s2.state_type AS f_84, s2.is_flapping AS f_85, s2.problem_has_been_acknowledged AS f_86, s2.scheduled_downtime_depth AS f_87, s2.should_be_scheduled AS f_88, s2.status_update_time AS f_89, (CASE WHEN s2.has_been_checked IS NULL THEN 1 ELSE (s2.has_been_checked-1)*-1 END) AS f_90, (CASE WHEN s2.has_been_checked IS NULL THEN 1 ELSE (s2.has_been_checked-1)*-1 END) AS f_91 FROM objects o INNER JOIN services s ON o.id = s.service_object_id LEFT 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 LEFT JOIN servicestatus s2 ON s.service_object_id = s2.service_object_id WHERE (o.id = '27' AND s.config_type = :oci_b_var_1 AND o.is_active = 1)                        
                        Stacktrace:#0 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Connection/Statement.php(274): Doctrine_Connection->rethrowException(Object(Doctrine_Adapter_Exception), Object(Doctrine_Connection_Statement))

#1 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Connection.php(1014): Doctrine_Connection_Statement->execute(Array)
#2 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Query/Abstract.php(976): Doctrine_Connection->execute('SELECT DISTINCT...', Array)
#3 /usr/share/icinga-web/app/modules/Api/lib/database/IcingaDoctrine_Query.class.php(116): Doctrine_Query_Abstract->_execute(NULL)
#4 /usr/share/icinga-web/lib/doctrine/lib/Doctrine/Query/Abstract.php(1026): IcingaDoctrine_Query->_execute(NULL)
#5 /usr/share/icinga-web/app/modules/Api/models/Store/LegacyLayer/IcingaApiModel.class.php(129): Doctrine_Query_Abstract->execute(NULL, 5)
#6 /usr/share/icinga-web/app/modules/Api/models/Store/LegacyLayer/IcingaApiModel.class.php(148): Api_Store_LegacyLayer_IcingaApiModel->execRead()
#7 /usr/share/icinga-web/app/modules/Api/actions/ApiSearchAction.class.php(101): Api_Store_LegacyLayer_IcingaApiModel->fetch()
#8 /usr/share/icinga-web/app/modules/Api/actions/ApiSearchAction.class.php(329): Api_ApiSearchAction->executeRead(Object(AgaviWebRequestDataHolder))
#9 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(949): Api_ApiSearchAction->executeWrite(Object(AgaviWebRequestDataHolder))
#10 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1463): AgaviExecutionContainer->runAction()
#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/app/modules/Api/lib/auth/IcingaApiAuthentificationLogoutFilter.class.php(33): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#14 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): IcingaApiAuthentificationLogoutFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#15 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#16 /usr/share/icinga-web/lib/agavi/src/filter/AgaviSecurityFilter.class.php(61): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#17 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviSecurityFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#18 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#19 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(870): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#20 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1266): AgaviExecutionContainer->execute()
#21 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1255): AgaviDispatchFilter->execute(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#22 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#23 /usr/share/icinga-web/lib/agavi/src/filter/AgaviFormPopulationFilter.class.php(78): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#24 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(1700): AgaviFormPopulationFilter->executeOnce(Object(AgaviFilterChain), Object(AgaviExecutionContainer))
#25 /var/cache/icinga-web/config/compile.xml_production__d41bc4e7416d79a2859fb497054ab4f5308e2df1.php(579): AgaviFilterChain->execute(Object(AgaviExecutionContainer))
#26 /usr/share/icinga-web/pub/index.php(49): AgaviController->dispatch()
#27 {main}

@icinga-migration
Copy link
Author

Updated by mhein on 2013-04-16 08:09:00 +00:00

  • Status changed from Assigned to Resolved
  • Done % changed from 80 to 100

Applied in changeset f6876e7.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-04-16 09:10:53 +00:00

  • File added icinga_web_1.9_oracle_fixed_clobs_distinct_select.png

Ok finally, removing the default DISTINCT solves the common queries, especially the generic info popup.

thanks for fixing. the conclusion to that - never use DISTINCT again, but make sure that a) the application knows how to handle duplicates b) the joins are correct

icinga_web_1.9_oracle_fixed_clobs_distinct_select.png

@icinga-migration
Copy link
Author

Updated by mfrosch on 2013-04-21 21:05:49 +00:00

  • Status changed from Resolved to Assigned
  • Assigned to changed from mhein to mfrosch
  • Done % changed from 100 to 50

As discussed on Friday this causes different problems...

New plan is:

@icinga-migration
Copy link
Author

Updated by mfrosch on 2013-04-22 07:30:12 +00:00

  • Status changed from Assigned to 7
  • Done % changed from 50 to 90

The latest changes should not cause any DISTINCT query including a CLOB field on Oracle.

dnsmichi: Could you please test this?

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-04-22 07:47:41 +00:00

  • Status changed from 7 to Assigned
  • Done % changed from 90 to 70

with all the reverts, there's still the issue with the dynamic status information you will query when clicking on "host details" or "service details" popup with the expander.

afaik this is somehwat dynamic collected data and not directly based on a data view template. please ask marius, he already debugged the issue last week.

the error is the exact same as described here: https://dev.icinga.org/issues/3965#note-5

the grids for status and history seem to be fixed.

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2013-04-22 16:20:58 +00:00

  • Status changed from Assigned to Resolved
  • Done % changed from 70 to 100

current next fixes the object info popup with the disabled select. so once and for all, this can be resolved fow now.

@icinga-migration icinga-migration added this to the 1.9 milestone Jan 17, 2017
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
bug Database & Queries Urgent
Projects
None yet
Milestone
1.9
Development

No branches or pull requests
1 participant
@icinga-migration