2

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!

CResults
  • 5,100
  • 1
  • 22
  • 28
NikolayGS
  • 300
  • 2
  • 5
  • 13
  • What are you using to connect to your database? Are you using a connection pool that's leaving db connections open all night (and isn't smart enough to close them when done, or at least reopen connections that go away)? – Seth Apr 07 '10 at 17:51
  • Ops, i missed that i`m using Hibernate API to access database – NikolayGS Apr 07 '10 at 18:20
  • Can you please paste the exact error messages? How do you fix your system after this? Do you restart your tomcat and everything is fine afterwards? – Michael Konietzka Apr 07 '10 at 19:04
  • Well, i fix the problem with restart mysqld.exe and tomcat. But as i was suggested i add jdbc:mysql://localhost/mydb?autoreconnect=true in hibernate cfg file this morning connection was lost again, but when i restart tomcat(without mysqld) everything was OK. – NikolayGS Apr 08 '10 at 07:42

3 Answers3

10

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="..."/>
Maurice Perry
  • 32,610
  • 9
  • 70
  • 97
9

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

nos
  • 223,662
  • 58
  • 417
  • 506
  • 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 Apr 08 '10 at 07:43
  • 2
    Worth noting this method isn't recommended by the [MySQL Connector/J documentation](http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html#idm140446220006144) *"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"*. – Steve Chambers Apr 22 '15 at 13:44
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)

nos
  • 223,662
  • 58
  • 417
  • 506
stacker
  • 68,052
  • 28
  • 140
  • 210
  • 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 – NikolayGS Apr 08 '10 at 07:48