WildFly24 - "ORA-01000 maximum open cursors exceeded" encountered in the production

[Aymen Mankari]

Hello there,

We’re trying to migrate an application from Jboss EAP6 to Wildfly24. Recently, we decided to go in the production after finalizing our tests in staging environments UAT and PP. The behavior of the application is just fine for about an hour or so, but after that the "ORA-01000 maximum open cursors exceeded" issue is thrown by the application and most of the incoming requests cannot be served.

Note that we have not encountered such issue neither on the Jboss 6 nor on the staging environment UAT and PP for Wildfly.

What we did to investigate.

• We checked the application code and overall, the connections, statements and resultsets are closed in the finally block.
• setting <track-statements> to true to monitor for un-closed statements and resultsets.Regarding to the following log message wildfly is taking care of closing some resultsets that are left open somewhere in the code. ==> 2024-04-12 16:25:54,733 WARN  [org.jboss.jca.adapters.jdbc.WrappedConnection] (default task-1) IJ030042: Closing a result set you left open, please do your own housekeeping for: oracle.jdbc.driver.ForwardOnlyResultSet @758e9fe7: java.lang.Throwable: STACKTRACE.
• I run the JProfiler and executed the application locally to monitor the execution at the JDBC level and everything seems fine, no connection leaks has been shown by the tool or weird stuff noticed on this side. 

My point of view :

I think that the connections that are returned to the pool continue to accumulate open cursors, even if they are closed by the application. As the application runs for a while, it eventually reaches its limit of open cursors and throws the exception. Maybe we should think about removing the opened connections from the pool to release the opened resources that are left open and create new ones, but it comes at a cost as Wildfly has to create each time a new physical connection after releasing/removing the used one from the pool and this could cause a performance impact.

Tech stack : 

Wildfly 24 + JRE 11

Oracle 11g database

Ojdbc6 (jdbc driver for oracle)

Hibernate 3.2.6

Spring 3.1.1

Any help would be appreciated

Thanks in advance.

[Scott Marlow]

Responding again due to failure from googlegroups...

On Wednesday, April 17, 2024 at 11:37:55 AM UTC-4 Aymen Mankari wrote:

Hello there,

We’re trying to migrate an application from Jboss EAP6 to Wildfly24. Recently, we decided to go in the production after finalizing our tests in staging environments UAT and PP. The behavior of the application is just fine for about an hour or so, but after that the "ORA-01000 maximum open cursors exceeded" issue is thrown by the application and most of the incoming requests cannot be served.

Based on the information that you supplied, it sounds like some cursors are not being closed on the database server.

Note that we have not encountered such issue neither on the Jboss 6 nor on the staging environment UAT and PP for Wildfly.

What we did to investigate.

• We checked the application code and overall, the connections, statements and resultsets are closed in the finally block.
• setting <track-statements> to true to monitor for un-closed statements and resultsets.Regarding to the following log message wildfly is taking care of closing some resultsets that are left open somewhere in the code. ==> 2024-04-12 16:25:54,733 WARN  [org.jboss.jca.adapters.jdbc.WrappedConnection] (default task-1) IJ030042: Closing a result set you left open, please do your own housekeeping for: oracle.jdbc.driver.ForwardOnlyResultSet @758e9fe7: java.lang.Throwable: STACKTRACE.
• I run the JProfiler and executed the application locally to monitor the execution at the JDBC level and everything seems fine, no connection leaks has been shown by the tool or weird stuff noticed on this side. 

+1 and I agree that means the application side is not leaking any memory at all.  The database server side is leaking cursors for some reason.

My point of view :

I think that the connections that are returned to the pool continue to accumulate open cursors, even if they are closed by the application. As the application runs for a while, it eventually reaches its limit of open cursors and throws the exception. Maybe we should think about removing the opened connections from the pool to release the opened resources that are left open and create new ones, but it comes at a cost as Wildfly has to create each time a new physical connection after releasing/removing the used one from the pool and this could cause a performance impact.

It sounds like you might have a workaround to keep using your tech stack for a bit longer.  I'd try to make this as graceful as possible (e.g. review the different datasource options).

Scott