Red Hat Bugzilla – Full Text Bug Listing
|Summary:||ORA-00907: missing right parenthesis|
|Product:||[Other] RHQ Project||Reporter:||Mike Foley <mfoley>|
|Component:||Database||Assignee:||Jay Shaughnessy <jshaughn>|
|Status:||CLOSED CURRENTRELEASE||QA Contact:||Mike Foley <mfoley>|
|Version:||4.0.0.Beta2||CC:||hrupp, jshaughn, mazz|
|Fixed In Version:||4.0.1||Doc Type:||Bug Fix|
|Doc Text:||Story Points:||---|
|Last Closed:||2013-09-02 03:22:13 EDT||Type:||---|
|oVirt Team:||---||RHEL 7.3 requirements from Atomic Host:|
|Bug Depends On:|
Description Mike Foley 2011-04-26 14:35:25 EDT
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.
Comment 1 Mike Foley 2011-04-27 11:56:56 EDT
Created attachment 495282 [details] DEBUG level server logging to illuminate the issue
Comment 2 John Mazzitelli 2011-04-27 13:59:35 EDT
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
Comment 3 John Mazzitelli 2011-04-27 14:02:35 EDT
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"
Comment 4 Mike Foley 2011-04-27 14:05:13 EDT
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
Comment 5 Jay Shaughnessy 2011-04-27 14:33:13 EDT
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.
Comment 6 Jay Shaughnessy 2011-04-27 14:34:22 EDT
D'oh! Looks like I was right, this is a Hibernate issue: http://lists.jboss.org/pipermail/hibernate-issues/2010-April/022211.html Investigating...
Comment 7 Jay Shaughnessy 2011-04-27 15:54:08 EDT
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.
Comment 8 Jay Shaughnessy 2011-04-27 16:08:11 EDT
I pushed this potential workaround to master for further eval: commit 15a53e58c10ba129ce0dd5b569aea1944de6836a Author: Jay Shaughnessy <email@example.com> Date: Wed Apr 27 16:06:30 2011 -0400
Comment 9 John Mazzitelli 2011-05-10 12:33:15 EDT
cherry picked over to release-4.0.0 branch as commit: 64fd52bb1fb072f79f8e5f02793d11fcd31e6225
Comment 10 Mike Foley 2011-05-11 11:55:13 EDT
verified RHQ 4.0.1 by following the steps to reproduce.
Comment 11 Heiko W. Rupp 2013-09-02 03:22:13 EDT
Bulk closing of issues that were VERIFIED, had no target release and where the status changed more than a year ago.