java - MySQL 数据库连接下降到 JBoss 应用程序

Question

我正在带有 MySQL 5.1 数据存储的 JBoss 7 服务器上运行 Java Web 应用程序。我使用 JDBC 与我的数据库进行通信。有一种特殊的方法，它需要大量的数据库查询。当我运行一两次时它运行良好，但运行 5 次后(即使它分散 5 次)我失去了数据库连接，导致以下错误:

WARN  [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (http-localhost-127.0.0.1-9091-3) IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: Could not create connection
    at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:277)
    at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:235)
    at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.createConnectionEventListener(SemaphoreArrayListManagedConnectionPool.java:706)
    at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:321)
    at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:392)
    at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:357)
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:351)
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:326)
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:467)
    at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:129)
    ...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [:1.7.0_45]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) [:1.7.0_45]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) [:1.7.0_45]
    at java.lang.reflect.Constructor.newInstance(Unknown Source) [:1.7.0_45]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1128)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2336)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2369)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2153)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.GeneratedConstructorAccessor9.newInstance(Unknown Source) [:1.7.0_45]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) [:1.7.0_45]
    at java.lang.reflect.Constructor.newInstance(Unknown Source) [:1.7.0_45]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
    at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:249)
    ... 86 more

我对这个问题进行了一些搜索，我怀疑我没有在某处正确关闭连接。我注释掉了任何进行数据库调用的方法，直到找到似乎导致问题的方法。它有几个在循环内运行的PreparedStatements，但我相当确定我已经在关闭所有打开的PreparedStatements 和ResultSets 的try-finally 中捕获了每个调用。这是有问题的方法:

private List<SupplierDisclosureDto> getSupplierDisclosureListInClientDB(UserDto user, List<SupplierDto> coreSuppliers) throws SQLException{
    List<SupplierDisclosureDto> sdList = new ArrayList<SupplierDisclosureDto>();
    Connection connection = user.getConnection();

    PreparedStatement psAllFragrances = null;
    ResultSet rsAllFragrances = null;
    PreparedStatement psFormulations = null;
    ResultSet rsFormulations = null;
    PreparedStatement psIfra = null;
    ResultSet rsIfra = null;
    PreparedStatement psDocuments = null;
    ResultSet rsDocuments = null;

    try{

        //find all fragrances and its corresponding values
        psAllFragrances = connection.prepareStatement(
            "SELECT " +
                "c.child_trade_name AS tradeName, c.child_supplier_name AS supplier, r.raw_material_number AS rmNo, " +
                "r.id AS rid, d.id AS did, o.supplier_id AS contentRole, o.id AS oid, s.short_name AS supplierShortname " +
            "FROM object o " +
                "left join composition c on c.id = (SELECT cx.id FROM composition AS cx WHERE cx.child_object_id = o.id LIMIT 1) " +
                "left join raw_material r on r.id = (SELECT rx.id FROM raw_material AS rx WHERE rx.object_id = o.id LIMIT 1) " +
                "left join document_status d on d.id = (SELECT dx.id FROM document_status AS dx WHERE dx.raw_material_id = r.id LIMIT 1) " +
                "left join supplier s on s.id = o.supplier_id " +
            "WHERE o.type<>0 AND r.is_fragrance=1");
        rsAllFragrances = psAllFragrances.executeQuery();
        while(rsAllFragrances.next()){
            SupplierDisclosureDto sd = new SupplierDisclosureDto();
            sd.setContentRole(rsAllFragrances.getInt("contentRole"));

            List<FormulaDto> formulas = new ArrayList<FormulaDto>();

            try{
                psFormulations = connection.prepareStatement(
                    "SELECT " +
                        "c.child_supplier_name AS supplier, c.child_overall_percent , ca.category , s.subcategory , p.brand , p.sub_brand , p.notebook_number , " +
                        "fo.status AS fStatus , f.unique_id, f.name , f.formula_number , f.version , f.date_modified_provided , f.id AS fid " +
                    "FROM composition c " +
                        "left join formula f on f.id = (SELECT fx.id FROM formula AS fx WHERE fx.object_id = c.parent_object_id LIMIT 1) " +
                        "left join object fo on fo.id = (SELECT fox.id FROM object AS fox WHERE fox.id = c.parent_object_id LIMIT 1) " +
                        "left join product p on p.id = (SELECT px.id FROM product AS px WHERE px.id = f.product_id LIMIT 1) " +
                        "left join subcategory s on s.id = (SELECT sx.id FROM subcategory AS sx WHERE sx.id = p.subcategory_id LIMIT 1) " +
                        "left join category ca on ca.id = (SELECT cax.id FROM category AS cax WHERE cax.id = s.category_id LIMIT 1) " +
                    "WHERE c.child_object_id=?");
                psFormulations.setInt(1, sd.getParentObjectId());
                rsFormulations = psFormulations.executeQuery();
                while(rsFormulations.next()){

                    CategoryDto category = new CategoryDto();
                    category.setCategory(rsFormulations.getString("category"));

                    SubcategoryDto subcategory = new SubcategoryDto();
                    subcategory.setSubcategory(rsFormulations.getString("subcategory"));
                    subcategory.setCategory(category);

                    ProductDto product = new ProductDto();
                    product.setBrand(rsFormulations.getString("brand"));
                    product.setSubcategory(subcategory);

                    ObjectDto formulaObject = new ObjectDto();
                    formulaObject.setStatus(rsFormulations.getString("fStatus"));

                    FormulaDto formula = new FormulaDto();
                    formula.setFormulaNumber(rsFormulations.getString("formula_number"));
                    formula.setObject(formulaObject);
                    formula.setProduct(product);

                    try{
                        psIfra = connection.prepareStatement("SELECT i.category FROM ifra_category i WHERE i.formula_id = ?");
                        psIfra.setInt(1, rsFormulations.getInt("fid"));
                        rsIfra = psIfra.executeQuery();
                        List<String> ifraCategories = new ArrayList<String>();
                        while(rsIfra.next()){
                            ifraCategories.add(rsIfra.getString("category"));
                        }
                        rsIfra.close();
                        psIfra.close();
                        formula.setIfraCategories(ifraCategories.toArray(new String[ifraCategories.size()]));
                    }finally{
                        if(rsIfra!=null)
                            rsIfra.close();
                        if(psIfra!=null)
                            psIfra.close();
                    }

                    formulas.add(formula);
                }
                rsFormulations.close();
                psFormulations.close();
            }finally{
                if(rsFormulations!=null)
                    rsFormulations.close();
                if(psFormulations!=null)
                    psFormulations.close();
            }

            sd.setFormulas(formulas);

            try{
                psDocuments = connection.prepareStatement(
                    "SELECT " +
                        "d.id AS d_id, d.document_status_id, d.document_type, d.is_active AS d_is_active, d.uploaded_by, " +
                        "d.last_uploaded, d.previous_version_id, d.extension, d.filename, u.id AS u_id, u.user_name, u.email, u.role_id, " +
                        "u.content_role_id, u.client_id, u.name, u.address, u.company, u.phone_number, u.fax_number, c.short_name " +
                    "FROM document_status_detail d " +
                        "left join irw.user u on u.id = (SELECT ux.id FROM irw.user AS ux WHERE ux.id = d.uploaded_by LIMIT 1) " +
                        "left join irw.client c on c.id = (SELECT cx.id FROM irw.client AS cx WHERE cx.id = u.client_id LIMIT 1) " +
                    "WHERE d.document_status_id = ? " +
                    "ORDER BY document_type, last_uploaded ASC");
                psDocuments.setInt(1, rsAllFragrances.getInt("did"));
                rsDocuments = psDocuments.executeQuery();
                SupplierDocumentDto previousDocument = null;
                HashMap<String, SupplierDocumentDto> documents = new HashMap<String, SupplierDocumentDto>();
                while(rsDocuments.next()){
                    SupplierDocumentDto currentDocument = new SupplierDocumentDto();
                    currentDocument.setIsActive((rsDocuments.getInt("d_is_active")==0)?false:true);

                    UserDto userDto = new UserDto();
                    userDto.setId(rsDocuments.getInt("u_id"));
                    currentDocument.setUploadedByUser(userDto);

                    if(previousDocument!=null && previousDocument.getType().equalsIgnoreCase(currentDocument.getType())){
                        currentDocument.setPreviousVersion(previousDocument);
                    }

                    documents.put(currentDocument.getType(), currentDocument);

                    previousDocument = currentDocument;
                }
                rsDocuments.close();
                psDocuments.close();

                sd.setDocuments(documents);
                sdList.add(sd);
            }finally{
                if(rsDocuments!=null)
                    rsDocuments.close();
                if(psDocuments!=null)
                    psDocuments.close();
            }

        }
        rsAllFragrances.close();
        psAllFragrances.close();
    }finally{
        if(rsAllFragrances!=null)
            rsAllFragrances.close();
        if(psAllFragrances!=null)
            psAllFragrances.close();
    }
    return sdList;
}

与数据库的连接在用户登录时初始化，因为不同的用户可以分配不同的数据库。如果有帮助，这里是我的standalone.xml中数据存储的配置(每个数据库都是相同的):

<datasource jndi-name="java:jboss/datasources/DATABASEDS" pool-name="DATABASEDS-Pool" enabled="true" jta="false" use-java-context="true" use-ccm="true">
    <connection-url>
        jdbc:mysql://localhost:3306/DATABASE?zeroDateTimeBehavior=convertToNull
    </connection-url>
    <driver>
        mysql-connector-java-5.1.18-bin.jar
    </driver>
    <transaction-isolation>
        TRANSACTION_READ_COMMITTED
    </transaction-isolation>
    <pool>
        <min-pool-size>
            10
        </min-pool-size>
        <max-pool-size>
            500
        </max-pool-size>
        <prefill>
            true
        </prefill>
        <use-strict-min>
            false
        </use-strict-min>
        <flush-strategy>
            FailingConnectionOnly
        </flush-strategy>
    </pool>
    <security>
        <user-name>
            USERNAME
        </user-name>
        <password>
            PASSWORD
        </password>
    </security>
    <validation>
        <check-valid-connection-sql>
            SELECT 1
        </check-valid-connection-sql>
        <validate-on-match>
            false
        </validate-on-match>
        <background-validation>
            false
        </background-validation>
        <use-fast-fail>
            false
        </use-fast-fail>
    </validation>
    <timeout>
        <blocking-timeout-millis>
            60000
        </blocking-timeout-millis>
        <idle-timeout-minutes>
            1
        </idle-timeout-minutes>
    </timeout>
    <statement>
        <track-statements>
            TRUE
        </track-statements>
        <prepared-statement-cache-size>
            0
        </prepared-statement-cache-size>
        <share-prepared-statements/>
    </statement>
</datasource>

我不确定是什么原因导致了这个问题，我已经尝试了很多解决方案。任何帮助将不胜感激!

已解决:

我通过一些调试发现了泄漏。事实证明，它是通过一种不同的方法不断打开新的连接，但从未关闭它们。我将其包围在 try-finally 中，以确保新连接在使用完毕后立即关闭。

Answer 1

您可以尝试以下

添加<cached-connection-manager debug="true"/>在<subsystem xmlns="urn:jboss:domain:jca:1.1">

JBoss 将关闭泄漏的连接

启用 JCA 日志记录后，您需要通过检查日志来查找泄漏的连接。我通过这种方式解决了连接泄漏。

<logger category="org.jboss.jca">
                <level name="DEBUG"/>
</logger>

您将在日志中获得如下所示的条目

DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (MSC service thread 1-4) {JNDI_NAME}: getConnection(null, null) [1/100] --> 这意味着从池中获取连接。

DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (MSC service thread 1-4) {JNDI_NAME}: returnConnection(607e334, false) [1/99] --> 表示将连接返回到池

您可以检查哪个查询未将连接返回到池，并从执行该查询的位置检查您的应用程序。修复它。