[JUDDI-146] Hitting Oracle In Clause Limit Error during Find request

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 2.0rc5
• Fix Version/s: 2.0
• Environment: Glassfish application server on JDK 1.5. Oracle 10g backend.
• Assignee: Kurt Stam
• Reporter: Michael Pilone
• Created: 15/Oct/08 12:25
• Updated: 01/Feb/09 04:04
• Resolved: 01/Feb/09 04:04

Description

I'm using JUDDI in a project where we publish our application's internal taxonomies as TModels. I'm hitting a problem when I try to get some of our TModels back out of JUDDI using a find TModel operation. I've looked at the code and the problem is in the implementation of the FindTModelByNameQuery around line 76. The IN clause generated in this case has more than 1000 entries which is more than is allowed by Oracle. I've attached a stacktrace below.

I'm using an maxRows value of 100 on my find request, but JUDDI doesn't apply this limit until after all the items have been found and loaded internally. I have the feeling from looking through the other Find functions that this is going to be a limit on everything in JUDDI, not just TModels.

I would suggest that the in clause be broken down into reasonable chunks (100 - 500?) and then ORed together to avoid this problem in Oracle and other DBs. The other option would be to execute multiple independent queries with chunks of keys in the single IN clause.

Logs:

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

WARN - An Exception was encountered while attempting to close the Find TModel ResultSet: null
java.lang.NullPointerException
at org.apache.juddi.datastore.jdbc.FindTModelByNameQuery.select(FindTModelByNameQuery.java:94)
at org.apache.juddi.datastore.jdbc.JDBCDataStore.findTModel(JDBCDataStore.java:1610)
at org.apache.juddi.function.FindTModelFunction.execute(FindTModelFunction.java:165)
at org.apache.juddi.registry.RegistryEngine.execute(RegistryEngine.java:234)
at org.apache.juddi.registry.AbstractService.doPost(AbstractService.java:168)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:411)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:290)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:271)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:202)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:94)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:206)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:571)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1080)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:150)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:632)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:577)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:571)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1080)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:272)
at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.invokeAdapter(DefaultProcessorTask.java:637)
at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.doProcess(DefaultProcessorTask.java:568)
at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.process(DefaultProcessorTask.java:813)
at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.executeProcessorTask(DefaultReadTask.java:341)
at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask(DefaultReadTask.java:263)
at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask(DefaultReadTask.java:214)
at com.sun.enterprise.web.portunif.PortUnificationPipeline$PUTask.doTask(PortUnificationPipeline.java:380)
at com.sun.enterprise.web.connector.grizzly.TaskBase.run(TaskBase.java:265)
at com.sun.enterprise.web.connector.grizzly.ssl.SSLWorkerThread.run(SSLWorkerThread.java:106)

Issue Links

• duplicates: JUDDI-89 JDBC Datastore performance improvements