4

I am using MYSQL 5.0 and Tomcat 5.5. After 8 hours , MYSQL closes by default closes all idle connections and so I am getting an SQL Exception .Any solution to this problem

5 Answers5

7

Which connection pool are you using? Most allow a heartbeat command to be executed periodically to keep connections from idling out.

EDIT: Looking at the DBCP configuration page, I think you should set an appropriate (cheap) validationQuery. You could then either tell DBCP to test this when potentially evicting idle connections (testWhileIdle) or tell it to test just before returning the connection (testOnBorrow). The first would (hopefully!) keep your idle connections alive; the second wouldn't, but wouldn't return you broken connections (i.e. if would create a new one if necessary).

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Or to test the connection before using it, and if it's been killed, create a fresh one. – Kieron May 05 '09 at 10:53
  • Basically I am using OpenCMS, a content Management System, Which Uses MYSQL and Tomcat.It uses DBCP connection Pooling mechanism –  May 05 '09 at 10:55
  • Brilliant answer @Jon. Out of interest, which would you recommend out of these two methods? – Steve Chambers Apr 22 '15 at 12:17
  • @SteveChambers: I'm afraid I don't have nearly enough recent experience to make an educated recommendation. – Jon Skeet Apr 22 '15 at 12:30
  • Thanks Jon. Think I'll go for `testOnBorrow` for now as it seems to be completely independent of the `wait_timeout` value. The only thing that slightly concerns me is whether it might have any effect on performance but hopefully this won't be an issue. (Another solution I also considered was `autoReconnect=true` as described [here](http://stackoverflow.com/questions/2594583/mysql-connection-timeout#2595293) but have now discounted this as it isn't recommended by [MySQL Connector/J](http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html#idm140446220006144).) – Steve Chambers Apr 22 '15 at 13:54
2

Do you mean that you keep the connections alive while they are idle? I am no expert on mySql, but in the databases I have been working with it's considered good practice to keep the connection open only while it's needed, and then close it.

Fredrik Mörk
  • 155,851
  • 29
  • 291
  • 343
  • Where individual request speed is of the essence (e.g. OLTP where individual requests may often be frequent and small and consumers browse away if the page is slow) a pool holds up to a fixed number of connections (protecting the DB from infinite connections) and keeps them open as long as possible to avoid paying connection startup costs. Without a pool, or when request startup is a minor part of your per-operation cost (e.g. OLAP), one might not bother with a pool in which case yes make sure you close the connections. https://en.wikipedia.org/wiki/Connection_pool – Gus Feb 28 '20 at 14:34
1

With MySQL, by default an open connection (often waiting in a connection pool) will be closed after 8 hours of inactivity. You can change this time from 8 hours by configuring the wait_timeout variable either in your my.cnf or on our mysql command line

See: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

Danny D'Amours
  • 712
  • 8
  • 17
1

I don't know how to increase the timeout but I guess there should be a setting.. Why don't you create the connection when you need it, and close it after using it other than depending on a created connection?

Chathuranga Chandrasekara
  • 20,548
  • 30
  • 97
  • 138
  • wait_timeout , will increse the time, but i dont think so, it would be the feasible solution. –  May 05 '09 at 11:00
0

This question has been asked a few years ago, but I feel that it's good to have the correct answer. i had the same issue, to solve I had to play with the configuration a little bit. here is what works for me. Go on https://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html for more info. The code below took care of everything. There is way to optimize the values but I will take care of these later.

<Resource name="jdbc/database" 
               auth="Container" 
               type="javax.sql.DataSource"
               maxActive="100" 
               maxIdle="30" 
               maxWait="10000"
          username="username" password="password" 
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql:///"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
          testWhileIdle="true"
          testOnBorrow="true"
          testOnReturn="false"
          validationQuery="SELECT 1"
          validationInterval="30000"`enter code here`
          timeBetweenEvictionRunsMillis="30000"
          minIdle="10" `enter code here`
          initialSize="10"
          removeAbandonedTimeout="60"
          removeAbandoned="true"
          logAbandoned="true"
          minEvictableIdleTimeMillis="30000" 
          jmxEnabled="true"         
           />
Edson H Philippe
  • 3
  • 2