在Tomcat Web应用程序中使用Java和MySQL进行连接池 [英] Connection pooling with Java and MySQL in Tomcat web application

问题描述

我最近写了一个Java Web应用程序并将其部署到服务器，我发现一个不寻常的问题，在开发或测试期间没有出现。

I recently wrote and deployed a Java web application to a server and I'm finding an unusual problem which didn't appear during development or testing.

用户在这么长时间后登录并转到显示数据库中的数据，该页面指示没有要查看的记录。

When a user logs in after so long and goes to display data from the database, the page indicates that there are no records to see. But upon page refresh, the first x records are shown according to the pagination rules.

检查日志，我发现：

ERROR|19 09 2009|09 28 54|http-8080-4|myDataSharer.database_access.Database_Metadata_DBA| - Error getting types of columns of tabular Dataset 12

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.io.EOFException

STACKTRACE:

java.io.EOFException
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1956)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2368)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2867)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)

上几百线。

该应用程序目前设置为大约100个用户，但尚未完全使用。它使用Apache Tomcat servlets / jsps和MySQL数据库之间的连接池，以下代码示例构成数据库操作的一般布置，其中每页通常有几个：

The application is currently set for about 100 users but is not yet in full use. It uses connection pooling between the Apache Tomcat servlets / jsps and a MySQL database with the following code example forming the general arrangement of a database operation, of which there are typically several per page:

// Gets a Dataset.
public static Dataset getDataset(int DatasetNo) {
    ConnectionPool_DBA pool = ConnectionPool_DBA.getInstance();
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;

    String query = ("SELECT * " +
                    "FROM Dataset " +
                    "WHERE DatasetNo = ?;");

    try {
        ps = connection.prepareStatement(query);
        ps.setInt(1, DatasetNo);
        rs = ps.executeQuery();
        if (rs.next()) {
            Dataset d = new Dataset();
            d.setDatasetNo(rs.getInt("DatasetNo"));
            d.setDatasetName(rs.getString("DatasetName"));
            ...

            }

            return d;
        }
        else {
            return null;
        }
    }
    catch(Exception ex) {
        logger.error("Error getting Dataset " + DatasetNo + "\n", ex);            
        return null;
    }
    finally {
        DatabaseUtils.closeResultSet(rs);
        DatabaseUtils.closePreparedStatement(ps);
        pool.freeConnection(connection);
    }
}

任何人都可以建议一种方法来纠正这个问题？

Is anyone able to advise a way of correcting this problem?

我相信这是由于MySQL将连接轮询连接打开长达8小时，但不确定。

I believe it is due to MySQL leaving connection poll connections open for up to eight hours but am not certain.

感谢

Martin O'Shea。

Martin O'Shea.

澄清一点关于我的连接池的方法，它不是Oracle，我在我的应用程序中使用，但我自己的类如下：

Just to clarify one point made about my method of connection pooling, it isn't Oracle that I'm using in my application but a class of my own as follows:

package myDataSharer.database_access;

import java.sql.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import org.apache.log4j.Logger;

public class ConnectionPool_DBA {

    static Logger logger = Logger.getLogger(ConnectionPool_DBA.class.getName());

    private static ConnectionPool_DBA pool = null;
    private static DataSource dataSource = null;


    public synchronized static ConnectionPool_DBA getInstance() {
        if (pool == null) {
            pool = new ConnectionPool_DBA();
        }
        return pool;
    }

    private ConnectionPool_DBA() {
        try {
            InitialContext ic = new InitialContext();
            dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/myDataSharer");
        }
        catch(Exception ex) {
            logger.error("Error getting a connection pool's datasource\n", ex);
        }
    }

    public void freeConnection(Connection c) {
        try {
            c.close();
        }
        catch (Exception ex) {
            logger.error("Error terminating a connection pool connection\n", ex);           
        }
    }

    public Connection getConnection() {
        try {
            return dataSource.getConnection();
        }
        catch (Exception ex) {
            logger.error("Error getting a connection pool connection\n", ex);            
            return null;
        }
    }    
}

是因为我使用了类似的名称。

I think the mention of Oracle is due to me using a similar name.

推荐答案

有几个指针，避免这种情况，从其他来源，从其他驱动程序和其他应用程序服务器的连接池实现。

There are a few pointers on avoiding this situation, obtained from other sources, especially from the connection pool implementations of other drivers and from other application servers. Some of the information is already available in the Tomcat documentation on JNDI Data Sources.

1. 建立清理/回收计划，将关闭池，如果它们在一段时间之后是不活动的。将数据库连接打开8小时（MySQL默认值）是不好的做法。在大多数应用程序服务器上，非活动连接超时值是可配置的，通常小于15分钟（即连接不能留在池中超过15分钟，除非它们被重复使用一次）。在Tomcat中，当使用JNDI DataSource时，使用removeAbandoned和removeAbandonedTimeout设置执行相同操作。


2. 当新连接从池返回到应用程序时，请确保先进行测试。例如，我知道的大多数应用程序服务器可以配置为使用执行SELECT 1 FROM dual来测试与Oracle数据库的连接。在Tomcat中，使用validationQuery属性为MySQL设置适当的查询 - 我相信这是SELECT 1（不带引号）。设置validationQuery属性的值有帮助的原因是因为如果查询无法执行，则会从池中删除连接，并创建新的连接。

1. Establish a cleanup/reaper schedule that will close connections in the pool, if they are inactive beyond a certain period. It is not good practice to leave a connection to the database open for 8 hours (the MySQL default). On most application servers, the inactive connection timeout value is configurable and is usually less than 15 minutes (i.e. connections cannot be left in the pool for more than 15 minutes unless they are being reused time and again). In Tomcat, when using a JNDI DataSource, use the removeAbandoned and removeAbandonedTimeout settings to do the same.
2. When a new connection is return from the pool to the application, ensure that it is tested first. For instance, most application servers that I know, can be configured so that connection to an Oracle database are tested with an execute of "SELECT 1 FROM dual". In Tomcat, use the validationQuery property to set the appropriate query for MySQL - I believe this is "SELECT 1" (without quotes). The reason why setting the value of the validationQuery property helps, is because if the query fails to execute, the connection is dropped from the pool, and new one is created in its place.

由于应用程序的行为远远不够，用户可能看到池的结果第一次返回到应用程序的过期连接。第二次，池可能返回一个不同的连接，可以为应用程序的查询提供服务。

As far are the behavior of your application is concerned, the user is probably seeing the result of the pool returning a stale connection to the application for the first time. The second time around, the pool probably returns a different connection that can service the application's queries.

Tomcat JNDI数据源基于Commons DBCP，因此配置属性也将应用于Tomcat。

Tomcat JNDI Data Sources are based on Commons DBCP, so the configuration properties applicable to DBCP will apply to Tomcat as well.

这篇关于在Tomcat Web应用程序中使用Java和MySQL进行连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！