Created attachment 495006 [details] RHQ Log file showing SQL Exception Description of problem: SQL Exception with monitoring Apache Http server resource. Version-Release number of selected component (if applicable): RHQ 4.0 CR1 How reproducible: 100% Steps to Reproduce: 1. Inventory --> Apache Http Server 2. Monitoring --> Traits 3. Observe log file Actual results: Expected results: SQL exception in RHQ log file due to invalid SQL, missing parenthesis Additional info: log files are attached. Oracle database.
Created attachment 495282 [details] DEBUG level server logging to illuminate the issue
I think this is an Oracle bug that shows up in my version of Oracle and probably Mike's. My version: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production From this: http://www.dba-oracle.com/sf_ora_00907_missing_right_parenthesis.htm he states, "Oracle offers information regarding ORA-00907 in conjunction with FULL/LEFT OUTER joins in association with Bug 4433936. This bug causing an ORA-00907 has been fixed in 10.2.0.3 and future versions of 11g" See this: http://www.anysql.net/doc/bug10203.html specifically "10.2.0.3 Bug Fixes by Category" 4433936 ORA-907 from FULL / LEFT OUTER joins
here's a simple SQL to try: select count((m.TIME_STAMP, m.SCHEDULE_ID)) as c from RHQ_MEASUREMENT_DATA_TRAIT m this results in the ORA 907 exception "missing right parens"
documenting my oracle version: Database Product Name : Oracle Database Product Version : Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Database Driver Name : Oracle JDBC driver Database Driver Version : 10.2.0.5.0
I'm not sure if this is a bug or not, if it is I think it may be in Hibernate. It has to do with the fact that we are using Criteria queries to pull the trait data. The CriteriaQueryGenerator has a standard way of generating count queries like: Select count(entity) From Entity entity Where ... But because MeaasurementDataTrait extends MeasurmentData and MeasurementData has a composite key, this is translating into something unusual. This is the root cause. Now need to figure out what to do.
D'oh! Looks like I was right, this is a Hibernate issue: http://lists.jboss.org/pipermail/hibernate-issues/2010-April/022211.html Investigating...
It seems like we could generate our count queries slightly differently to avoid this issue. I don't think there is really a difference between: Select Count( entity ) From Entity entity Where ... And Select Count( * ) From Entity entity Where ... I made this change locally and things seem to be working as expected. Unless someone knows of any subtleties I think this is a valid workaround.
I pushed this potential workaround to master for further eval: commit 15a53e58c10ba129ce0dd5b569aea1944de6836a Author: Jay Shaughnessy <jshaughn> Date: Wed Apr 27 16:06:30 2011 -0400
cherry picked over to release-4.0.0 branch as commit: 64fd52bb1fb072f79f8e5f02793d11fcd31e6225
verified RHQ 4.0.1 by following the steps to reproduce.
Bulk closing of issues that were VERIFIED, had no target release and where the status changed more than a year ago.