Getting Connection Establishing in Linux Server

[Manikandan Rajmohan]

I am working in webportal using Java,Mysql. I am facing below issue. I cant able to resolve the issue. Daily i am restart the server then this issue is not coming... how to resolve this issue. without restart the server....

Error
====
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object

[Kees Jan Koster]

Dear Manikandan,

This exception tells you that you are not closing the database connection properly. You code should close all connections in a finally block.

Kees Jan

[Manikandan Rajmohan]

Kees Jan Koster wrote:Dear Manikandan,

This exception tells you that you are not closing the database connection properly. You code should close all connections in a finally block.

Kees Jan

But i am closing all the connection proerly....

This is my configration in server.xml

<Context path="" reloadable="true" docBase="/test/test/public_html" debug="1">
<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver" maxActive="1000" maxIdle="1" maxWait="10"
name="jdbc/agent" password="password" type="javax.sql.DataSource"
url="jdbc:mysql://locahost:3306/testing?autoReconnect=true" username="username"/>
</Context>

[Ben Souther]

Can you post a short example of how you're getting an using the connection from within your Java code?

[Manikandan Rajmohan]

Ben Souther wrote:Can you post a short example of how you're getting an using the connection from within your Java code?

The following connection method for getting the connection. I am establishing a connection in my action class and i am having dao. After finishing the data base operation then i am closing the statment etc., at last i am closing the connection in my action class. This s a process for getting and closing connection.


public Connection getConnection() {
Connection con = null;
try {
initContext = new InitialContext();
if (initContext != null) {
Context envContext = (Context) initContext
.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup("jdbc/agent");
con = ds.getConnection();
if (ds != null) {
con = ds.getConnection();
in++;
}
log.info("Request Con No :: " + in);
log.info("Con :: " + con);
}
} catch (NamingException e) {
e.printStackTrace(pw);
log.error(sw);
} catch (SQLException e) {
e.printStackTrace(pw);
log.error(sw);
}
return con;
}

------------------------------------
Close
====

public void closeInitContext(Connection con) {
try {
if (con != null)
con.close();
if (initContext != null)
initContext.close();
in--;
} catch (NamingException e) {
e.printStackTrace(pw);
log.error(sw);
} catch (SQLException e) {
e.printStackTrace(pw);
log.error(sw);
} finally {
log.info("After Close Con No :: " + in);
}
}

[Ben Souther]

As Kees mentioned, you need to close your connection in a finally block.
Otherwise, any exceptions thrown before the con.close() statement will keep the program from reaching it.

Furthermore, if you're going to have a method for closing db connections, you need to make sure that that method always gets called in a finally block. Otherwise it will never be called.

I'm guessing that you've got conditions in your code where you handle errors but never call your closeInitContext method. Each time that happens you create another abandoned database connection. Once you hit the limit and can't create any more, your app dies.

Restarting the app starts the process all over again.

[Tim Holloway]

This kind of coding error is very common, unfortunately. A lot of people don't even realize that they have to do the close for both error and non-error conditions. Well, actually they probably do, but between bad examples, the push to "Git 'R Dun!" and the fact that failure modes tend to not get well tested, there's a lot of code that has slow leaks in the database connections.

Speaking of "Git 'R Dun!", it pains me to admit I once worked in a shop where they never had time to failure-proof the code, but they always had time to reboot the production server every few days.

Incidentally, one of the primary reasons I started using the Spring Framework in most of my webapps is that Spring's database facilities handle such nasty little nuances automatically. You don't have to explicitly code them over and over again. That is a way to Git 'R Dun!