MySQL database connection dropping to JBoss application

Question

I'm running a Java web application on a JBoss 7 server with a MySQL 5.1 datastore. I use JDBC to communicate with my database. There is a particular method which is rather database query intensive. It runs fine when I run it once or twice, but after running it 5 times (even if it's 5 times spread apart) I lose my database connection resulting in the following error:

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

I did some searching about the issue and I suspect that I'm not closing a connection properly somewhere. I commented out any of the methods making database calls until I found the one that seems to be causing the issue. It has several PreparedStatements being run inside loops, but I'm fairly certain I've caught each call inside a try-finally that closes any open PreparedStatements and ResultSets. Here is the method in question:

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;
}

The connection to the database is initialized to the user when they log in, since different users can be assigned different databases. If it helps, here is the configuration of my datastore inside my standalone.xml (it is the same for each database):

<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>

I'm not sure what is causing the issue and I've tried a lot of solutions. Any help would be much appreciated!

SOLVED:

I found the leak through some debugging. It turns out it was in a different method that kept opening new connections, but never closed them. I surrounded it in a try-finally that ensures that new connection is closed right after it's done being used.

Answer 1

You can try below

Add <cached-connection-manager debug="true"/> in <subsystem xmlns="urn:jboss:domain:jca:1.1">

JBoss will close leaked connection

You need to find leaked connection by checking logs after enabling JCA logging. I resolved connection leak by this way.

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

You will get entries like below in logs

DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (MSC service thread 1-4) {JNDI_NAME}: getConnection(null, null) [1/100] --> It means taking connection from pool.

DEBUG [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (MSC service thread 1-4) {JNDI_NAME}: returnConnection(607e334, false) [1/99] --> It means returning connection to pool

You can check which query is not returning connetion to pool and check your application from where that query is being executed. Fix it.

Answer 2

try using a connection pool in application start-up or configure it in jboss, for example :

 ComboPooledDataSource connectionPoolDatasource = new ComboPooledDataSource();
  connectionPoolDatasource.setDriverClass("com.mysql.jdbc.Driver");
  connectionPoolDatasource.setJdbcUrl("jdbc:mysql://localhost:3306/mydatabase");
  connectionPoolDatasource.setUser("root");
  connectionPoolDatasource.setPassword("");
  connectionPoolDatasource.setMinPoolSize(1);
  connectionPoolDatasource.setAcquireIncrement(5);
  connectionPoolDatasource.setMaxPoolSize(20);
  Connection con = connectionPoolDatasource.getConnection();

you can also configure it in jboss, please refer to this link.