query.closeAll() and query.close(result) will not close the cursor anymore; it remains open as long as the database session exists.

The issue does not occur in 3.0.11 but can be reproduced reliably in 3.2.0-release.

Testcase attached.

Description

query.closeAll() and query.close(result) will not close the cursor anymore; it remains open as long as the database session exists.
The issue does not occur in 3.0.11 but can be reproduced reliably in 3.2.0-release.
Testcase attached.

Another interesting fact: when using the Merlia driver (com.inet.ora.OraDriver), earlier versions will fail as well (like m-2).
When using the orginal (oracle.jdbc.OracleDriver), everything up to m-3 is fine!

Note: with the Merlia driver, the amount of open cursors will rise until it hits 500 and then remain constant.

Gerrit Volkenborn added a comment - 22/Mar/13 04:58 PM - edited Another interesting fact: when using the Merlia driver (com.inet.ora.OraDriver), earlier versions will fail as well (like m-2).
When using the orginal (oracle.jdbc.OracleDriver), everything up to m-3 is fine!
Note: with the Merlia driver, the amount of open cursors will rise until it hits 500 and then remain constant.

Elaborating on the comment, found in the DatanucleusCursorsTest.java, "The query needs to use a different syntax each time"....

We noticed that the amount of cursors opened for a repeated query depended on how the query was structured. The following illustrates three different approaches for getting a job. Test 1 and 2 will not open a cursor for each job id. Test 3 will open a cursor for each query if the id is different. In all three tests the cursors are left open. However, the maximum open cursors exceeded exception only occurs when using Test 3 in a repeated loop because a cursor is left open for each query.

The test was shown using oracle6.jar (oracle.jdbc.OracleDriver) and DataNucleus release 3.2.0.

Rebecca Martinez added a comment - 22/Mar/13 07:26 PM Elaborating on the comment, found in the DatanucleusCursorsTest.java, "The query needs to use a different syntax each time"....
We noticed that the amount of cursors opened for a repeated query depended on how the query was structured. The following illustrates three different approaches for getting a job. Test 1 and 2 will not open a cursor for each job id. Test 3 will open a cursor for each query if the id is different. In all three tests the cursors are left open. However, the maximum open cursors exceeded exception only occurs when using Test 3 in a repeated loop because a cursor is left open for each query.
The test was shown using oracle6.jar (oracle.jdbc.OracleDriver) and DataNucleus release 3.2.0.
public JobFlowSubject getJobTest1(long id)
{
Query query = PM.newNamedQuery(JobFlowSubject.class, "JobFlowSubject.findByJobId");
query.setUnique(true);
JobFlowSubject job = (JobFlowSubject) query.execute(id);
query.closeAll();
return job;
}
public JobFlowSubject getJobTest2(long id)
{
Query query = PM.newQuery(JobFlowSubject.class);
query.setFilter("JobId == id");
query.declareParameters("long id");
query.setUnique(true);
JobFlowSubject job = (JobFlowSubject) query.execute(id);
query.closeAll();
return job;
}
public JobFlowSubject getJobTest3(long id) throws Exception
{
Query query = PM.newQuery(JobFlowSubject.class);
query.setFilter(String.format("JobId == %d", id));
query.setUnique(true);
JobFlowSubject job = (JobFlowSubject) query.execute();
query.closeAll();
return job;
}

Rebecca it sounds like your hunch makes sense: It might be that the cursor isn't being freed in all cases but because Test 2 has an identical filter containing a parameter name in each call only one cursor is ever used but Test 3, having a different filter each call, uses a new filter with each call - causing eventual depletion.

Chris Colman added a comment - 22/Mar/13 07:57 PM - edited Rebecca it sounds like your hunch makes sense: It might be that the cursor isn't being freed in all cases but because Test 2 has an identical filter containing a parameter name in each call only one cursor is ever used but Test 3, having a different filter each call, uses a new filter with each call - causing eventual depletion.

Evidently putting a link to the test case format on the front page of JIRA, referencing it in "datanucleus.org" website, and putting it on the forum is not enough (for some). A testcase is defined at http://www.datanucleus.org/project/problem_jdo_testcase.html
What is attached here wouldn't run on my Oracle 11 XE instance on Linux ("table or view doesn't exist"), and doesn't fit that standard anyway, and I've got very little spare time for such things (and that spare time for this week has just been wasted). Consequently I'll hand it back to the people who have the problem for them to develop a fix. Attach your patch to plugin "store.rdbms" SVN trunk to this issue when you've finished it.

Since you've gone to the effort of working out that your problem is not present in 3.2.0.M3 of store.rdbms, yet is in 3.2.0.M4 then you could easily enough just look at JIRA for that release for what was changed, namely http://www.datanucleus.org/servlet/jira/secure/IssueNavigator.jspa?reset=true&mode=hide&pid=10144&fixfor=11883
Perhaps something in DBCP caching of PreparedStatements is buggy ? (since that is enabled in 3.2.0.M4, but wasn't before). But then there are many other connection pools supported and trivial to use any other one. Or maybe its something else. Anyway, that's only discovered by investigating it. Thanks.

Andy Jefferson added a comment - 24/Mar/13 06:52 PM - edited Evidently putting a link to the test case format on the front page of JIRA, referencing it in "datanucleus.org" website, and putting it on the forum is not enough (for some). A testcase is defined at http://www.datanucleus.org/project/problem_jdo_testcase.html
What is attached here wouldn't run on my Oracle 11 XE instance on Linux ("table or view doesn't exist"), and doesn't fit that standard anyway, and I've got very little spare time for such things (and that spare time for this week has just been wasted). Consequently I'll hand it back to the people who have the problem for them to develop a fix. Attach your patch to plugin "store.rdbms" SVN trunk to this issue when you've finished it.
Since you've gone to the effort of working out that your problem is not present in 3.2.0.M3 of store.rdbms, yet is in 3.2.0.M4 then you could easily enough just look at JIRA for that release for what was changed, namely http://www.datanucleus.org/servlet/jira/secure/IssueNavigator.jspa?reset=true&mode=hide&pid=10144&fixfor=11883
Perhaps something in DBCP caching of PreparedStatements is buggy ? (since that is enabled in 3.2.0.M4, but wasn't before). But then there are many other connection pools supported and trivial to use any other one. Or maybe its something else. Anyway, that's only discovered by investigating it. Thanks.

In case you want to review this issue once more, I adapted your test case so it fails now, and also added both query types (JDO and SQL).

There are two preconditions to the failure:

one, the query needs to be different every time (JDOQuery obviously needs to access a member of the queried class on top of that)
two, the result set may not be fully iterated because the cursor is removed then, so you may not use a for construct, or you need to break out of it.

Gerrit Volkenborn added a comment - 25/Mar/13 02:12 PM In case you want to review this issue once more, I adapted your test case so it fails now, and also added both query types (JDO and SQL).
There are two preconditions to the failure:
one, the query needs to be different every time (JDOQuery obviously needs to access a member of the queried class on top of that)
two, the result set may not be fully iterated because the cursor is removed then, so you may not use a for construct, or you need to break out of it.

As per my suggestion on this issue, disabling "preparedStatement pooling" when using DBCP makes that test pass. Use of C3P0 or BoneCP with preparedStatement pooling unchanged makes that test pass.

Conclusion : DBCP PreparedStatement pooling is buggy, and SVN trunk now sets datanucleus.connectionPool.maxStatements to 0 as the default. The provided test passes. If some other situation have this "problem" after setting this property or using SVN trunk then raise an issue with a valid testcase

Andy Jefferson added a comment - 04/Apr/13 09:21 AM As per my suggestion on this issue, disabling "preparedStatement pooling" when using DBCP makes that test pass. Use of C3P0 or BoneCP with preparedStatement pooling unchanged makes that test pass.
Conclusion : DBCP PreparedStatement pooling is buggy, and SVN trunk now sets datanucleus.connectionPool.maxStatements to 0 as the default. The provided test passes. If some other situation have this "problem" after setting this property or using SVN trunk then raise an issue with a valid testcase