WildFly 8.2.0 and MySQL - The connection never stays on...

[Perry Terrance]

I have a brand-new WildFly 8.2.0 and local MySQL setup.

The JDBC DataSource is WildFly-managed using the "mysql-connector-java-5.1.34-bin.jar_com.mysql.jdbc.Driver_5_1" JDBC driver. In terms of the WildFly DataSource settings - everything is at default and I haven't touched anything other setting up the initial connection.

What is annoying right now is that the JDBC connection would connect promptly to my local MySQL DB - but after a few hours - it would abruptly disconnect. And then I have to end up enabling and disabling the DataSource in WildFly for the connection to be re-established?

Why is it doing this - and why can't it just stay connected to my MySQL DB forever?

Here are my WildFly DataSource settings:

Name: myTestDB JNDI: java:jboss/datasources/myTestDB Is enabled?: true Statistics enabled?: false Datasource Class: Driver: mysql-connector-java-5.1.34-bin.jar_com.mysql.jdbc.Driver_5_1 Driver Class: com.mysql.jdbc.Driver Share Prepared Statements: false Statement Cache Size: 0 Connection URL: jdbc:mysql://localhost:3306/myTestDB New Connection Sql: Transaction Isolation: Use JTA?: true Use CCM?: true Connection URL: jdbc:mysql://localhost:3306/forum New Connection Sql: Transaction Isolation: Use JTA?: true Use CCM?: true Valid Connection Checker: Check Valid Sql: Validate On Match: false Background Validation: false Validation Millis: 0 Stale Connection Checker: Exception Sorter: Use tryLock(): 0 Blocking Timeout Millis: 0 Idle Timeout Minutes: 0 Set Tx Query Timeout: false Query Timeout: 0 Allocation Retry: 0 Allocation Retry Wait Millis: 0

Is there something I am not configuring right?

[Jaikiran Pai]

It's a natural and common thing with connections. They don't stay alive forever. The DB server terminates the connection that it has leased out after a period of inactivity.

Datasource pools which pool open connections allow configuring a "idle timeout" on connections. The idle timeout setting allows the pool to release back connections from the pool, back to the DB server, when a certain connection hasn't been in use for the specified period of time (i.e. idle timeout). That way, the pool doesn't end up holding on to a terminated/stale connection and subsequently handing it out to the caller applications. You just need to specify an idle timeout on your pool configuration. If you are using the admin console to configure that datasource, I think you'll see the setting there (or else there are other ways to go about configuring this).

[Perry Terrance]

Jaikiran Pai wrote:It's a natural and common thing with connections. They don't stay alive forever. The DB server terminates the connection that it has leased out after a period of inactivity.

Datasource pools which pool open connections allow configuring a "idle timeout" on connections. The idle timeout setting allows the pool to release back connections from the pool, back to the DB server, when a certain connection hasn't been in use for the specified period of time (i.e. idle timeout). That way, the pool doesn't end up holding on to a terminated/stale connection and subsequently handing it out to the caller applications. You just need to specify an idle timeout on your pool configuration. If you are using the admin console to configure that datasource, I think you'll see the setting there (or else there are other ways to go about configuring this).

Well currently - as shown in my Config - the "Idle Timeout Minutes" is set at 0 - doesn't that mean "indefinitely"? Or is it recommended I actually set a value there - like "60000 Minutes"?

[Jaikiran Pai]

Perry Terrance wrote:
Well currently - as shown in my Config - the "Idle Timeout Minutes" is set at 0 - doesn't that mean "indefinitely"? Or is it recommended I actually set a value there - like "60000 Minutes"?

I believe that's what the problem is. Leaving unused connections in the pool indefinitely is going to be a problem, for the reason I explained in my previous post. What you should be doing is setting a reasonable idle timeout (like 5 minutes) so that if a connection in the pool hasn't been used (by any clients of the pool, like your application) for 5 minutes, then the pool will close that connection (before the DB server forcefully terminates the connection).

P.S: 60000 minutes isn't a good value for idle timeout.