0

I have Web application in Cent Os server created in JSP. After some time the connections to mysql execeeds the limit and application could not work without restarting the mysql service.

Even I restarts the mysql and tomcat service some connections are allready created by mysql.

[root@localhost ~]# service tomcat restart

Shutting down Tomcat: Using CATALINA_BASE: /usr/local/apache-tomcat-7.0.27 Using CATALINA_HOME: /usr/local/apache-tomcat-7.0.27 Using CATALINA_TMPDIR: /usr/local/apache-tomcat-7.0.27/temp Using JRE_HOME: /usr/java/jre1.6.0_10 Using CLASSPATH: /usr/local/apache-tomcat-7.0.27/bin/bootstrap.jar:/usr/local/apache-tomcat-7.0.27/bin/tomcat-juli.jar

Starting Tomcat: Using CATALINA_BASE: /usr/local/apache-tomcat-7.0.27 Using CATALINA_HOME: /usr/local/apache-tomcat-7.0.27 Using CATALINA_TMPDIR: /usr/local/apache-tomcat-7.0.27/temp Using JRE_HOME: /usr/java/jre1.6.0_10 Using CLASSPATH: /usr/local/apache-tomcat-7.0.27/bin/bootstrap.jar:/usr/local/apache-tomcat-7.0.27/bin/tomcat-juli.jar

[root@localhost ~]#

service mysql restart

Shutting down MySQL. [ OK ]

Starting MySQL...... [ OK ]

[root@localhost ~]# netstat | grep mysql | wc -l

180

As shown above it shows 180 connections all ready running and the connections are like

[root@localhost ~]# netstat | grep mysql

tcp 1 0 localhost.localdomain:49058 localhost.localdomain:mysql CLOSE_WAIT tcp 1 0 localhost.localdomain:49061 localhost.localdomain:mysql CLOSE_WAIT tcp 1 0 localhost.localdomain:49060 localhost.localdomain:mysql CLOSE_WAIT tcp 1 0 localhost.localdomain:49063 localhost.localdomain:mysql CLOSE_WAIT tcp 1 0 localhost.localdomain:49062 localhost.localdomain:mysql CLOSE_WAIT

I have checked my sql files three times and there is no connections remained unclosed

Please give me the solution...

Dinesh Dabhi
  • 856
  • 1
  • 7
  • 18
  • How is your mySQL status when your tomcat is not running? Are there any applications using the database? – Amir Pashazadeh Oct 05 '12 at 10:44
  • If there are no other applications, be sure that you have a connection leak in your application, fix your application. – Amir Pashazadeh Oct 05 '12 at 10:44
  • Your application doesn't close connections, or prepared statements, or result sets. That the only way it can happens. – user1516873 Oct 05 '12 at 10:45
  • @ user1516873 Is prepared statements or result sets can affect my connections? – Dinesh Dabhi Oct 05 '12 at 10:47
  • Yes, it can. At least one of them, second will closing automatically. Also `ResultSet` will closed when it will be garbage collected. – user1516873 Oct 05 '12 at 11:11
  • @user1516873 Even if i close the connection and not closed the resultset then what happens – Dinesh Dabhi Oct 05 '12 at 11:18
  • @DineshDabhi theoretically, when you close connection, it will free all accociated resources, but can you be sure it was closed without error? – user1516873 Oct 05 '12 at 11:31
  • @user1516873 yes i have closed all connection in final block like here finally { if (pstmt != null) { try { pstmt.close(); } catch (SQLException sqlex) { sqlex.printStackTrace(); } pstmt = null; } if (con != null) { try { closeconnection(con); } catch (SQLException sqlex) { sqlex.printStackTrace(); } con = null; } – Dinesh Dabhi Oct 05 '12 at 11:51
  • sorry, it not readable, add it in question – user1516873 Oct 05 '12 at 11:56

2 Answers2

3

CLOSE_WAIT tcp socket state means that connection is in process if being closed, waiting for application to execute close() method. You should always execute close() method after finishing the usage of some resource (connection to database, file IO, network connections, etc).

You can check this example how to correctly handle your database connection.

Serhiy
  • 4,073
  • 3
  • 36
  • 66
1

It sounds to me as if you have a connection leak in your application somewhere.

One of the most common reasons for leaking connections in Java is to not include a finally in your try catch block.

Check out this post for more details.

Community
  • 1
  • 1
Tom Mac
  • 9,693
  • 3
  • 25
  • 35