2

I have a long-running method which executes a large number of native SQL queries through the EntityManager (TopLink Essentials). Each query takes only milliseconds to run, but there are many thousands of them. This happens within a single EJB transaction. After 15 minutes, the database closes the connection which results in following error:

Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b02-p04 (04/12/2010))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Closed Connection
Error Code: 17008
Call: select ...
Query: DataReadQuery()
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
.
.
.
RAR5031:System Exception.
javax.resource.ResourceException: This Managed Connection is not valid as the phyiscal connection is not usable
at com.sun.gjc.spi.ManagedConnection.checkIfValid(ManagedConnection.java:612)

In the JDBC connection pool I set is-connection-validation-required="true" and connection-validation-method="table" but this did not help .

I assumed that JDBC connection validation is there to deal with precisely this kind of errors. I also looked at TopLink extensions (http://www.oracle.com/technetwork/middleware/ias/toplink-jpa-extensions-094393.html) for some kind of timeout settings but found nothing. There is also the TopLink session configuration file (http://download.oracle.com/docs/cd/B14099_19/web.1012/b15901/sessions003.htm) but I don't think there is anything useful there either.

I don't have access to the Oracle DBA tables, but I think that Oracle closes connections after 15 minutes according to the setting in CONNECT_TIME profile variable.

Is there any other way to make TopLink or the JDBC pool to reestablish a closed connection?

The database is Oracle 10g, application server is Sun Glassfish 2.1.1.

Dmitry Chornyi
  • 1,821
  • 4
  • 25
  • 33
  • 1
    is there a reason why you cant increase the connect_timeout in the oracle user profile used by your connection pool? – happymeal May 27 '11 at 13:15
  • I don't have DBA rights. I am going to create a ticket so that someone on the Oracle side looks into this. Since this is a huge and bureaucratic organization I want to make sure there is nothing I can do myself before going the DBA way. – Dmitry Chornyi May 27 '11 at 13:25
  • Related question: if you are working through JPA and the connection is closed... how would it be linked to the previous Session profile? – SJuan76 May 27 '11 at 13:32
  • @dima, are you using Glassfish as the Java EE container? – Vineet Reynolds May 27 '11 at 13:35
  • @SJuan76, This interests me as well. I am not sure I understand how JDBC and Toplink work with regard to transactions and this validation/reconnection business. Maybe it is in fact not possible to continue when a connection is closed and hence the exception. But then I don't understand of what use connection validation is if I can't reconnect. – Dmitry Chornyi May 27 '11 at 13:42

3 Answers3

3

All JPA implementations (running on a Java EE container) use a datasource with an associated connection pool to manage connectivity with the database.

The persistence context itself is associated with the datasource via an appropriate entry in persistence.xml. If you wish to change the connection timeout settings on the client-side, then the associated connection pool must be re-configured.

In Glassfish, the timeout settings associated with the connection pool can be reconfigured by editing the pool settings, as listed in the following links:

On the server-side (whose settings if lower than the client settings, would be more important), the Oracle database can be configured to have database profiles associated with user accounts. The session idle_time and connect_time parameters of a profile would constitute the timeout settings of importance in this aspect of the client-server interaction. If no profile has been set, then by default, the timeout is unlimited.

Vineet Reynolds
  • 76,006
  • 17
  • 150
  • 174
  • I can not yet change the Oracle settings, but on the Glassfish side there are only two parameters. `Max Wait Time` is probably irrelevant. Do you think that I need to set `Idle Timeout`? My understanding is that connection is not idle, but it is just closed after 15 minutes (in the middle of work) by the database server. – Dmitry Chornyi May 27 '11 at 15:47
  • Oracle is generally built for long running connections, even for days. While the DBA may have set a CONNECT_TIME limit, it is unusual. – Gary Myers May 28 '11 at 00:45
2

Unless you've got some sort of RAC failover, when the connection is terminated, it will end the session and transaction.

The admins may have set into some limits to prevent runaway transactions or a single job 'hogging' a connection in a pool. You generally don't want to lock a connection in a pool for an extended period.

If these queries aren't necessarily part of the same transaction, then you could try terminating and restarting a new connection.

Are you able to restructure your code so that it completes in under 15 minutes. A stored procedure in the background may be able to do the job a lot quicker than dragging the results of thousands of operations over the network.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
2

I see you set your connection-validation-method="table" and is-connection-validation-required="true", but you do not mention that you specified the table you were validating on; did you set validation-table-name="any_table_you_know_exists" and provide any existing table-name? validation-table-name="existing_table_name" is required.

See this article for more details on connection validation.

Related StackOverflow article with similar problem - he wants to flush the entire invalid connection pool.

Community
  • 1
  • 1
JoshDM
  • 4,939
  • 7
  • 43
  • 72