719734 – "ORA-01795: maximum number of expressions in a list is 1000" SQLException when trying to import 300 platforms and their child servers via autodiscovery queue view (error occurs in query executed by DiscoveryBossBean.checkStatus())

Bug 719734 - "ORA-01795: maximum number of expressions in a list is 1000" SQLException when trying to import 300 platforms and their child servers via autodiscovery queue view (error occurs in query executed by DiscoveryBossBean.checkStatus())

Summary: "ORA-01795: maximum number of expressions in a list is 1000" SQLException whe...

Keywords:
Status:	CLOSED CURRENTRELEASE
Alias:	None
Product:	RHQ Project
Classification:	Other
Component:	Core Server
Sub Component:
Version:	4.0.1
Hardware:	Unspecified
OS:	Unspecified
Priority:	high
Severity:	medium
Target Milestone:	---
Target Release:	---
Assignee:	Ian Springer
QA Contact:	Mike Foley
Docs Contact:
URL:
Whiteboard:
Depends On:
Blocks:	rhq-ora-in-clause jon3
TreeView+	depends on / blocked

Reported:	2011-07-07 19:34 UTC by Ian Springer
Modified:	2013-08-06 00:39 UTC (History)
CC List:	2 users (show)
Fixed In Version:
Clone Of:
Environment:
Last Closed:	2012-02-07 19:25:23 UTC
Embargoed:

Attachments	(Terms of Use)

Description Ian Springer 2011-07-07 19:34:17 UTC

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
	at org.rhq.enterprise.server.discovery.DiscoveryBossBean.checkStatus(DiscoveryBossBean.java:918)
	at org.rhq.enterprise.server.discovery.DiscoveryBossBean.importResources(DiscoveryBossBean.java:892)

<snip>

Caused by: java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1203)
	at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:90)
	at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:187)

Comment 1 Ian Springer 2011-07-11 19:26:53 UTC

This is fixed by [master cc69251] and [master 902a31c]. 

Note, though importing a large number of Resources now works, it takes a very long time. For example, to import 1500 Resources (300 platforms and 1200 servers), it took 10 minutes.

Comment 2 Mike Foley 2011-07-11 20:22:10 UTC

discussed and agreed upon a verification approach with ips:

1) use the perftest plugin  ... add the plugin, plugins update, etc...

2) http://rhq-project.org/display/RHQ/Test+Using+the+Perftest+Plugin

3) begin by using the sample scenario specification

Example Scenario Specification

<resource type="server-a">
<simpleResourceGenerator numberOfResources="1"/>
<simpleNumericMeasurementGenerator/>
<simpleArtifactGenerator artifactType="artifact2" numberOfArtifacts="20">
<simpleArtifactRevisionGenerator numberOfRevisions="5"/>
</simpleArtifactGenerator>
</resource>


4) ... and change the numberOfResources="1000"

5) additionally consider changing the numberOfRevisions and numberOfArtifacts to do some additional testing in this area...

Comment 3 Mike Foley 2011-07-27 20:03:27 UTC

verified.  used the perftest plugin as described above.

Comment 4 Mike Foley 2012-02-07 19:25:23 UTC

changing status of VERIFIED BZs for JON 2.4.2 and JON 3.0 to CLOSED/CURRENTRELEASE

Note You need to log in before you can comment on or make changes to this bug.