Java and MySQL connection pool: avoid timeouts

Question

I've been trying to Tomcat's native conncetion pooling features to avoid connection timeouts in my Java web project, but it seems I'm still out of luck.

I've put mysql-connector-java-5.1.23-bin.jar in the WEB-INF/lib folder, created a META-INF/context.xml like this:

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <Resource name="jdbc/TheDatabase" auth="Container"
            type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
            maxActive="100" maxIdle="30" maxWait="1000"
            poolPreparedStatements="true" maxOpenPreparedStatements="100"
            username="user" password="pass"
            url="jdbc:mysql://localhost:3306/my_database"/>
</Context>

Here's what I do:

public static init() {
    ...
    sqlDataSource = (DataSource) new InitialContext().lookup("java:comp/env/jdbc/TheDatabase");
    ...
}

public static ArrayList<ResultSet> dbRead() {
    Connection conn = sqlDataSource.getConnection();
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
        ResultSet res = stmt.executeQuery("SELECT * FROM the_table");
        ...
        res.close();

        return out;
    } catch (SQLException e) {
        // Logging stuff
        return null;
    } finally {
        if (stmt != null)
            try {if (!stmt.isClosed()) stmt.close();}
            catch (SQLException e) {/* Logging stuff */}
        if (conn != null)
            try {if (!conn.isClosed()) conn.close();}
            catch (SQLException e) {/* Logging stuff */}
    }
}

Whenever I call the function dbRead after the wait_timeout has passed, I get:

Communications link failure

The last packet successfully received from the server was 1.818.697 milliseconds
ago. The last packet sent successfully to the server was 0 milliseconds ago.

with a SQL state 08S01.

I thought it was because of some uncorrect closings, but it doesn't seem the case.

@duffymo It *was* actually answered there, even though the answer wasn't accepted. Too bad I couldn't find that question in my searches (that maybe were too refined). I'll do some additional tests, then add the answer, then this question can be closed. — MaxArt, Oct 01 '13 at 13:06

score 1 · Answer 1 · edited Oct 03 '13 at 04:18

Try adding a validation query and let me now.I hope this will solve the problem.
If not then change the localhost to the IP of your machine(you may also test 127.0.0.1)

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <Resource name="jdbc/TheDatabase" auth="Container"
            type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
            maxActive="100" maxIdle="30" maxWait="1000"
            poolPreparedStatements="true" maxOpenPreparedStatements="100"
            username="user" password="pass" validationQuery="select now()" 
            url="jdbc:mysql://192.168.1.1:3306/my_database"/>
</Context>

jdev · Answer 2 · 2013-10-01T11:57:25.817

set the autoReconnect to true the default is false. for more inforamtion or help

autoReconnect Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, as a last option, investigate setting the MySQL server variable "wait_timeout" to a high value, rather than the default of 8 hours. false 1.1

autoReconnectForPools Use a reconnection strategy appropriate for connection pools (defaults to 'false') false 3.1.3

see mysql documentation

It doesn't seem to work, I get the exact same error. That's odd, it doesn't seem that those properties have any effect. — MaxArt, Oct 01 '13 at 12:01

score 0 · Answer 3 · answered Oct 01 '13 at 11:32

0

Try to use this Stuff:

<Resource name="jdbc/myDataSource" auth="Container" type="javax.sql.DataSource"
            maxActive="100" maxIdle="30" maxWait="10000"
            username="user" password="pass" 
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost/my_database?autoReconnect=true"
            logAbandoned="true" removeAbandoned="true" 
            removeAbandonedTimeout="60" />

answered Oct 01 '13 at 11:32

Harmeet Singh Taara

6,483
20
73
126

Tried, but with no luck. I've tried using the attribute `autoReconnect` instead of the parameter, and using `autoReconnectForPools` too, but the result is the same. – MaxArt Oct 01 '13 at 12:02
the same error generate ? – Harmeet Singh Taara Oct 01 '13 at 12:07
Yes, the same `Communications link failure` error. – MaxArt Oct 01 '13 at 12:13

score 0 · Accepted Answer · answered Oct 01 '13 at 14:01

It seemed that all I needed to add to the <Resource> definition was the validationQuery attribute. No autoReconnect, no autoReconnectForPools, nothing. This just works:

<Resource name="jdbc/TheDatabase" auth="Container"
        type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
        maxActive="100" maxIdle="30" maxWait="1000"
        poolPreparedStatements="true" maxOpenPreparedStatements="100"
        username="user" password="pass" validationQuery="SELECT 1"
        url="jdbc:mysql://localhost:3306/my_database"/>

That attribute isn't even in the MySQL documentation, so I had no idea of its existence. Now I doubt some of the other attributes have any effect too...

It's not a MySQL property, that's why you didn't find it there. It's a property of Apache DBCP. — user207421, Nov 04 '15 at 06:05

Java and MySQL connection pool: avoid timeouts

4 Answers4