Connection Java ConnectionPool does not close, gets stuck in a dream

Question

I have a webapp that uses JNDI queries to connect to a database.

The connection works fine and returns a request without problems. The problem is that the connection does not close properly and gets stuck in sleep mode (according to the mysql administrator). This means that they become unusable and then the connections end.

Can someone give me some pointers on what I can do so that the connection is successfully returned to the pool.

public class DatabaseBean { private static final Logger logger = Logger.getLogger(DatabaseBean.class); private Connection conn; private PreparedStatement prepStmt; /** * Zero argument constructor * Setup generic databse connection in here to avoid redundancy * The connection details are in /META-INF/context.xml */ public DatabaseBean() { try { InitialContext initContext = new InitialContext(); DataSource ds = (DataSource) initContext.lookup("java:/comp/env/jdbc/mysite"); conn = ds.getConnection(); } catch (SQLException SQLEx) { logger.fatal("There was a problem with the database connection."); logger.fatal(SQLEx); logger.fatal(SQLEx.getCause()); } catch (NamingException nameEx) { logger.fatal("There was a naming exception"); logger.fatal(nameEx); logger.fatal(nameEx.getCause()); } } /** * Execute a query. Do not use for statements (update delete insert etc). * * @return A ResultSet of the execute query. A set of size zero if no results were returned. It is never null. * @see #executeUpdate() for running update, insert delete etc. */ public ResultSet executeQuery() { ResultSet result = null; try { result = prepStmt.executeQuery(); logger.debug(prepStmt.toString()); } catch (SQLException SQLEx) { logger.fatal("There was an error running a query"); logger.fatal(SQLEx); } return result; } 

SNIP

 public void close() { try { prepStmt.close(); prepStmt = null; conn.close(); conn = null; } catch (SQLException SQLEx) { logger.warn("There was an error closing the database connection."); } } } 

This is inside a javabean that uses a database connection.

 public LinkedList<ImportantNoticeBean> getImportantNotices() { DatabaseBean noticesDBBean = new DatabaseBean(); LinkedList<ImportantNoticeBean> listOfNotices = new LinkedList<ImportantNoticeBean>(); try { PreparedStatement preStmt = noticesDBBean.getConn().prepareStatement("SELECT pseudonym, message, date_to, date_from " + "FROM importantnotices, users " + "WHERE importantnotices.username = users.username " + "AND NOW() >= date_from AND NOW() <= date_to;"); noticesDBBean.setPrepStmt(preStmt); ResultSet result = noticesDBBean.executeQuery(); while (result.next()) { ImportantNoticeBean noticeBean = new ImportantNoticeBean(); noticeBean.setAuthor(result.getString("pseudonym")); noticeBean.setMessage(result.getString("message")); noticeBean.setDateTo(result.getDate("date_to")); noticeBean.setDateFrom(result.getDate("date_from")); listOfNotices.add(noticeBean); } result.close(); } catch (SQLException SQLEx) { logger.error("There was an error in ImportantNoticesBean.getImportantNotices()"); logger.error(SQLEx); } finally { noticesDBBean.close(); } return listOfNotices; } <Context reloadable="true"> <Resource name="jdbc/mysite" auth="Container" type="javax.sql.DataSource" username="user" password="password" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/mysite" maxActive="10" maxIdle="5" maxWait="6000" removeAbandoned="true" logAbandoned="false" removeAbandonedTimeout="20" /> </Context> 

Answer 1

The problem is that the connection does not close properly and gets stuck in sleep mode

In fact, it was only half.

The problem I ran into was that each application defined a new connection to the sever database. Therefore, every time I closed all connections, application A created a bunch of new connections in accordance with the WEB.xml configuration file and would work happily. Appendix B will do the same. The problem is that they are independent pools that try to capture to the limit defined by the server. I think this is a kind of race condition. Therefore, when application A finishes with connections, it sits waiting for them to be reused until the timeout expires, while application B, which requires a connection, now refuses resources, even though application A ended with and should return to the pool. Once the timeout has passed, the connection is freed, and B (or C, etc.) can receive it again.

eg. if the limit is 10 (mySQL profile limit), and each application is configured to use no more than 10, 20 connection attempts will be made. Obviously, this is a bad situation.

The solution is RTFM and put the connection information in the right place . This makes general publishing a pain, but there are ways around it (for example, linking to other xml files from context).

Just to be explicit: I put the connection details in WEB.xml for each application, and it had a fight.

Answer 2

It seems that you are closing the connection correctly - unless prepStmt.close () throws a SQLException, I cannot find the connection leak.

What pool implementation are you using? When you close the connection, the pool should not immediately close the underlying MySQL connection - after all, this is the point of the connection pool! Therefore, on the MySQL side, the connections would look live, although your application does not use any; they can simply be held by the TC connection pool.

You might want to experiment with the connection pool settings. Ask him to compress the pool when the system is in standby mode. Or ask him to update all connections periodically. Or you have a strict upper bound on the number of concurrent connections it ever receives from MySQL, etc.

One way to check if your code has a connection leak is to force ds.getConnection () to always open a new physical connection and conn.close () to release the connection (if your connection pool has settings for them), then if you look at the connections on the MySQL side, you can find out if the code really has a connection leak or not.

Answer 3

This is a similar question - Connection Pool Settings for Tomcat

This is my answer to this question, and it fixed the problem for another guy. It can also help you.

Tomcat Documentation

DBCP uses the Jakarta-Commons database connection pool. It depends on the number of components of Jakarta-Commons:

 * Jakarta-Commons DBCP * Jakarta-Commons Collections * Jakarta-Commons Pool 

I use the same pooling material, and I set these properties to prevent the same thing that he just did not configure via tomcat. But if the first does not work, try this.

 testWhileIdle=true timeBetweenEvictionRunsMillis=300000 

Answer 4

Well, I could sort it out. I changed the database configuration resource to the following:

 *SNIP* maxActive="10" maxIdle="5" maxWait="7000" removeAbandoned="true" logAbandoned="false" removeAbandonedTimeout="3" *SNIP* 

This works quite well. What happens, afaik, is that as soon as I get to ten connections, Tomcat checks for abandoned connections (idle time> 3). He does this in a batch job every time the maximum number of connections is reached. The potential problem is that I need more than 10 requests at a time (not unique to me). The important thing is that removeAbandonedTimeout is less than maxWait.

Is this what is supposed to happen? those. Is the pool supposed to work this way? If it seems to me, at least for me, that you will wait until something (the connection) breaks down before fixing, and does not allow it to “break” in the first place. Maybe I still do not understand.

Answer 5

One thing that @binil skipped is that you don’t close the result set in the event of an exception. Depending on the driver implementation, this may cause the connection to remain open. Move the call to result.close () to the finally block.

Answer 6

I use the same configuration as you. If the connection in mysql administrator (windows) shows that it is in sleep mode, it means only the union, but not used. I checked this by running a test program with multiple threads making random queries in Mysql. if this helps here, my configuration is:

  defaultAutoCommit="false" defaultTransactionIsolation="REPEATABLE_READ" auth="Container" type="javax.sql.DataSource" logAbandoned="true" removeAbandoned="true" removeAbandonedTimeout="300" maxActive="-1" initialSize="15" maxIdle="10" maxWait="10000" username="youruser" password="youruserpassword" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://yourhost/yourdatabase"/>