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!

Solution 1

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="..."/>

Solution 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

Solution 3

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)

Comments

[NikolayGS]

I tried this(i have to put url in hibernata cfg file right?) and connection was lost again this morning, but as i mentioned when i restart tomcat everything was OK(earlier had to restart first mysqld.exe first)

[NikolayGS]

In this case i`m the admin, but i am not so advanced and familiar with connection pooling, but i will read your article and try to search solution

[Steve Chambers]

Worth noting this method isn't recommended by the MySQL Connector/J documentation "because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly".