Proper way to keey always alive database connection

Question

I'm working on a backend web server using Mybatis to query MySQL DB.

The web server only has requested in limited (but not fixed) time periods, so nearly every day I will receive the warning log like below.

pooled.PooledDataSource: Execution of ping query 'SELECT 1' failed: The last packet successfully received from the server was 51,861,027 milliseconds ago. The last packet sent successfully to the server was 51,861,027 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

autoReconnect=true (Why does autoReconnect=true not seem to work?) looks close to my problem but it's not what I expected.

I'm wondering is that possible to make connections NEVER timeout by ping it when it has been in an idle state for some time (maybe 1 hour).

Since Mybatis are using a connection pool, it's hard to force ping a specified idle connection and make it never timeout.

I've done some search on Google but looks like it's not easy to hack into Mybatis.

My questions are:

1. Are there any suggestions, references, or alternative libraries for this problem?

Or

2. Are there reasons that I should not try to keep always alive connections? (potential risks? violate best practices? etc)

Answer 1

Use connection pool manager like C3P0. You will be able to configure permanent connections. It works just like you have described - "pings" connection with example query like SELEC 1 to keep them alive if the connection is idle for some N seconds (thats configurable).

Some guidence here http://gbif.blogspot.com/2011/08/using-c3p0-with-mybatis.html or here http://edwin.baculsoft.com/2012/09/connecting-mybatis-orm-to-c3p0-connection-pooling/. Configuration options of C3P0 can be googled out.

Answer 2

Periodically execute a "select" simply for keeping db connection alive.

Answer 3

When connected to MySQL server default "wait_timeout" is 8 hours(28800 seconds), which means that a connection is idle more than eight hours, Mysql will automatically disconnect the connection. Can be checked using show variables like '%timeout%';

But java connection does not know about this connection being closed from DB side.

To address this issue you have multiple options:

1. Increase the value of wait_timeout properties. This will for all applications connecting the database and might not be the best solution

set interactive_timeout=432000;
set wait_timeout=432000;

2. Use connection pool manager like C3P0. It will ping DB using preferredTestQuery after some intervals for you and then you will have permanent connections.