tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Pid *" <...@pidster.com>
Subject Re: Connection pooling issue with MySQLNonTransientConnectionException and Java webapp
Date Mon, 21 Nov 2011 09:46:38 GMT
On 21 Nov 2011, at 09:28, "appy74@dsl.pipex.com" <appy74@dsl.pipex.com> wrote:

> Hello
>
> I don't think this is a Tomcat issue but I thought I would post here just in case.
>
> I have a Java, JSP and MySQL application running under Apache Tomcat 6.0.26 which I've
been testing with JMeter.
> What I find is that for a simple test having 10 users logging in and displaying a JSP
which is populated from
> several database reads, everything is fine.
>
> But when I increase the number of users in JMeter to 20+, logging in starts to fail with
HTTP request 500. Checking
> the logs I find that I am getting large numbers of MySQLNonTransientConnectionExceptions
as follows:
> com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create
connection to database
> server. Attempted reconnect 3 times. Giving up.
>
> So this indicates that there's an issue either with the connection pooling through Apache
Tomcat 6.0.26 or MySQL
> Community Server 5.X.
>
> The relevant part of the application's context.xml file in the project reads as follows:
> <Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
> logAbandoned="true" maxActive="-1" maxIdle="30"
> maxWait="10000" minEvictableIdleTimeMillis="30000"
> name="jdbc/myApp" numTestsPerEvictionRun="5"
> password="XXXXXXXXXX" removeAbandoned="true"
> removeAbandonedTimeout="120" testOnBorrow="true"
> testOnReturn="false" testWhileIdle="true"
> timeBetweenEvictionRunsMillis="-1"
> type="javax.sql.DataSource"
> url="jdbc:mysql://localhost:3306/myApp?autoReconnect=true"
> username="XXXXXXXXXX" validationQuery="select 1"/>
>
> I've set maxActive above to -1 on the understanding that this is unlimited. I think that
the MySQL error is because
> MySQL is refusing the connections.
>
> Am I correct here? Can anyone suggest a workaround or advise?
>
> Typically, my database operations are as follows:
>
> // Gets an ArrayList of Datasets.
> public static ArrayList<Dataset> getDatasets() {
>    ConnectionPool_DB pool = ConnectionPool_DB.getInstance();
>    Connection connection = pool.getConnection();

Usually the connection is initialised as null and then assigned inside
the try block. What happens if the method above throws an error after
a connection is removed from the pool?


p

>    PreparedStatement ps = null;
>    ResultSet rs = null;
>
>    String query = ("SELECT * " +
>                    "FROM Dataset " +
>                    "WHERE Active = '" + Valid.TRUE + "';");
>
>    try {
>        ps = connection.prepareStatement(query);
>        rs = ps.executeQuery();
>        ArrayList<Dataset> datasets = new ArrayList<Dataset>();
>        while (rs.next()) {
>            datasets.add(mapDataset(rs));
>        }
>        return datasets;
>    }
>    catch(Exception ex) {
>        logger.error("Error getting list of Datasets\n", ex);
>        return null;
>    }
>    finally {
>        Database_Utils.closeResultSet(rs);
>        Database_Utils.closeStatement(ps);
>        pool.freeConnection(connection);
>    }
> }
>
> And there can be quite a few of these per page.
>
> Thanks
>
> Martin O'Shea
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message