MySQL connection timeout

Question

I'm running program at apache tomcat server, that should be on permanently, but every morning(the client part isn't accessible at night) i receive error message (in apache tomcat console) that MySQL server is off. So is there any way to prevent this? Thanks in advance!

Answer 1

I suppose you open the connections directly in your web apps code. You could try to introduce a connection pool using a timeout the connection should be reestablished.

If this doesn't help, ask the network admin whether he has stateful filters which detect timeouts (while your client is idle) and close the connection.

If you're using a database pool, like c3p0 or dbcp, look in its documentation, there should be settings to configure idle timeouts or periodic checking of the connection (which will keep the connection open, and not time it out on the server side)

Answer 2

the mysql server times out a connection after a while, by default it's 28800 seconds, it's likely this timeout you're hitting.

You can just instruct the mysql driver to reconnect in case of a lost connection by adding the autoreconnect=true parameter to the jdbc url, e.g. jdbc:mysql://localhost/mydb?autoreconnect=true

Answer 3

Yes, I have had the same problem. Try to add a validationQuery to your DataSource.

Here is how I have defined mine:

<Resource auth="Container"
          type="javax.sql.DataSource"
          name="jdbc/gporder"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost/mydb"
          maxActive="10"
          maxIdle="5"
          validationQuery="SELECT 1"
          testOnBorrow="true"
          testWhileIdle="true"
          timeBetweenEvictionRunsMillis="10000"
          minEvictableIdleTimeMillis="60000"
          username="..." password="..."/>