Database server disconnects time-out connections in database connection pool applications

During the database application development process, we may encounter a problem: The application uses a database connection pool, and after each specified time, any requests made to the database server will fail, with only one failure, and no problem with normal access thereafter. Especially in Web applications, if there is no access during the night, and the first visitor experiences the next day is a database access error, if the developer of the system does not pay attention to the problem, the end user may see a heap of database exception information thrown.

In fact, the main reason for this problem is that the database connection pool in the application will save a specified number of database connection instances, and these connection instances do not regularly detect their connection to the database server is normal; The database server can configure a time-out for a database connection instance, and it will automatically disconnect after a time elapses. That is, the disconnected connection is still stored in the application's database connection pool, and the next time it is used, the database connection disconnects and one access failure occurs.

There are two recommended scenarios for resolving the above connection shutdown:

• If it is possible to provide such a detection mechanism, it is entirely possible to avoid the problems described above by periodically detecting the validity of connections in connection pooling in the application's connection pool management.

• In the application code through the exception processing mechanism, to achieve the re-processing of the business, can also be well avoided.

For example, using a Java-developed web system, tomcat as an HTTP server, MySQL as a database, throws the exception information as follows:

[http-bio-8080-exec-10] 2012-11-28 00:55:43 [org.shirdrn.wm.de.action.stataction]-[warn]    com.ibatis.dao.client.daoexception: error ending sql map transaction.   cause: java.sql.sqlexception: already closed.      at  com.ibatis.dao.engine.transaction.sqlmap.sqlmapdaotransaction.rollback (SqlMapDaoTransaction.java:51)       at  Com.ibatis.dao.engine.transaction.sqlmap.SqlMapDaoTransactionManager.rollbackTransaction ( sqlmapdaotransactionmanager.java:85)       at  Com.ibatis.dao.engine.impl.DaoContext.endTransaction (daocontext.java:112)        At com.ibatis.dao.engine.impl.daoproxy.invoke (daoproxy.java:77)       at   $Proxy 8.selectByExample (unknown source)       at  Org.shirdrn.wm.de.service.impl.StatItemsServiceImpl.countitems (Unknown source)       at  Org.shirdrn.wm.de.action.StatAction.makeStat (Unknown source)       at  Org.shirdrn.wm.de.action.StatAction.doGet (Unknown source)       at  Javax.servlet.http.HttpServlet.service (httpservlet.java:621)       at  Javax.servlet.http.HttpServlet.service (httpservlet.java:722)       at  Org.apache.catalina.core.ApplicationFilterChain.internalDoFilter (applicationfilterchain.java:305)        at org.apache.catalina.core.applicationfilterchain.dofilter ( applicationfilterchain.java:210)       at  Org.apache.catalina.core.StandardWrapperValve.invoke (standardwrappervalve.java:224)        at org.apache.catalina.core.standardcontextvalve.invoke (standardcontextvalve.java:169)    &Nbsp;   at org.apache.catalina.authenticator.authenticatorbase.invoke ( authenticatorbase.java:472)       at  Org.apache.catalina.core.StandardHostValve.invoke (standardhostvalve.java:168)        at org.apache.catalina.valves.errorreportvalve.invoke (errorreportvalve.java:98)        at org.apache.catalina.valves.accesslogvalve.invoke (accesslogvalve.java:927)        at org.apache.catalina.core.standardenginevalve.invoke ( standardenginevalve.java:118)       at  Org.apache.catalina.connector.CoyoteAdapter.service (coyoteadapter.java:407)        at org.apache.coyote.http11.abstracthttp11processor.process (abstracthttp11processor.java:987)        at org.apache.coyote.abstractprotocol$abstractconnectionhandler.process ( abstractprotocol.java:579)  &NBsp;    at org.apache.tomcat.util.net.jioendpoint$socketprocessor.run ( jioendpoint.java:307)       at java.util.concurrent.threadpoolexecutor$ Worker.runtask (threadpoolexecutor.java:886)       at  Java.util.concurrent.threadpoolexecutor$worker.run (threadpoolexecutor.java:908)        at java.lang.thread.run (thread.java:619)   caused by: java.sql.sqlexception:  Already closed.      at  Org.apache.commons.dbcp.PoolableConnection.close (poolableconnection.java:84)        at org.apache.commons.dbcp.poolingdatasource$poolguardconnectionwrapper.close ( poolingdatasource.java:181)       at  Com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransaction.close (jdbctransaction.java:81)        at com.ibatis.sqlmap.engine.transAction. Transactionmanager.end (transactionmanager.java:93)       at  Com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction (sqlmapexecutordelegate.java:734)        at com.ibatis.sqlmap.engine.impl.sqlmapsessionimpl.endtransaction ( sqlmapsessionimpl.java:176)       at  Com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction (sqlmapclientimpl.java:153)        at com.ibatis.dao.engine.transaction.sqlmap.sqlmapdaotransaction.rollback ( sqlmapdaotransaction.java:49)       ... 25 more

We solve this with the second solution given above, the logic for exception handling is as follows:

Protected void doget (httpservletrequest request, httpservletresponse response)   Throws servletexception, ioexception {      boolean retry  = false;      string type = request.getparameter ( Requestparams.item_type);       string top = request.getparameter ( Requestparams.top_n);       byte itemtype = byte.parsebyte (type);       Integer topN = super.topN;       if (top!=null)  {          try {               topn = integer.parseint (top) ;          } catch  (Exception e)  {}       }      target target = itemnames.get (ItemType);       try {          makestat (Request ,  response, itemtype, topn, target);      } catch  (exception e)  {          log.warn ("",  e);           // com.ibatis.dao.client.DaoException:  error ending sql map transaction.  cause: java.sql.sqlexception:  Already closed.          if (!retry &&  e instanceof daoexception)  {               log.warn ("Try to obtain database connection again.");               retry = true;               this.makestat (REQUEST,&NBSP;RESPONSE,&NBSP;ITEMTYPE,&NBSP;TOPN ,  target);          } else {               response.senderror (500,  E.tostring ());              return;           }      }       request.getrequestdispatcher (Target.url). Forward (request, response);   }     private void makestat (httpservletrequest request,  httpservletresponse response,           byte  itemtype, integer topn, target&Nbsp;target)  throws IOException, ServletException {       List<statitems> items = statitemsservice.countitems (Itemtype, new date (),  TopN);      for  (statitems statk : items)  {           if (Statk.getitemname ()!=null && ! " Null ". Equalsignorecase (Statk.getitemname ()))  {               piedataset.setvalue (Statk.getitemname (). Trim ()  +    ( +  statk.getpercentage ()  +  ")",  statk.getitemvalue ());           }      }      String  Imageurl = super.generateimage (piedataset, target.title, request);       request.setattribute ("ITEMs ",  items);       request.setattribute (" ImageUrl ",  imageurl);       if (Items!=null && !items.isempty ()  &&  Items.size () <topn)  {          topN =  Items.size ();       }      request.setattribute (" TopN ",  topn);   }

The above code, to determine if a connection failure occurs, save the request parameter, and then re-process the request.

Another deprecated scenario is to modify the connection timeout configuration for the database server. Because in real-world projects, it is not always the DBA who is involved in the application, and the configuration of the database server may pose a risk to other on-line business. Here's how to fix it:

Take MySQL, for example, to view the file/etc/my.cnf and query for parameters about the timeout configuration:

mysql> show variables like  '%timeout ';   +----------------------------+------- ---+  | variable_name               | value    |  +----------------------------+----------+   | connect_timeout            | 10        |  | delayed_insert_timeout      | 300      |  | innodb_lock_wait_timeout    | 50       |  | innodb_rollback_on_timeout |  OFF      |  | interactive_timeout         | 28800    |  | lock_wait_timeout           | 31536000 |  | net_read_timeout            | 30       |  |  net_write_timeout          | 60        |  | slave_net_timeout           | 3600     |  | wait_timeout                | 28800    |   +----------------------------+----------+

We can modify the following two parameters below the attribute group mysqld:

• Interactive_timeout

• Wait_timeout

The connection timeout for the MySQL database server configuration is 8 hours by default, and if the modified time-out is long enough, there will be no problem with the disconnection that occurred earlier. However, if there are many applications are using database connection pool, a large number of database connection resources have been occupied, severe words may cause the database server down, but also can cause some attackers to forge a large number of requests, the database server load overload and downtime, thereby affecting the application processing business.

Database server disconnects time-out connections in database connection pool applications