[mapguide-users] too Many Connections error(JSP+MySQL)

[=?GB2312?B?0OzV8A==?=]

Hi,
    I use JSP and MySQL to develop .FDO connects these two parts together.
    In the software,i use resource service and feature service to search
data stored in the MySQL database.Once i run the page for searching.
I will got two sleep process in MySQL.I guess the reason is i forgot to
close connections.So i go back to the code to close connections and
services.No use.

Codes here:

public Vector select(String mapName,String layerName,String
layerClassName,String filterString)  throws MgException
{
  MgResourceService resService =
(MgResourceService)siteConnection.CreateService(MgServiceType.ResourceService);
  MgFeatureService featService =
(MgFeatureService)siteConnection.CreateService(MgServiceType.FeatureService);

  MgMap map = new MgMap();
  map.Open(resService, mapName);

  MgLayer layer = getLayerByName(map, layerName);

  MgFeatureQueryOptions query = new MgFeatureQueryOptions();

  query.SetFilter(filterString);
   MgResourceIdentifier resId = new
MgResourceIdentifier(layer.GetFeatureSourceId());
  MgFeatureReader featureReader =featService.SelectFeatures(resId,
layerClassName, query);
  Vector vec=new Vector();
   while (featureReader.ReadNext())
  {

   MgAgfReaderWriter geometryReaderWriter = new MgAgfReaderWriter();
   String mc = featureReader.GetString("MC");
   long id=featureReader.GetInt64("FeatId");
   long id1=featureReader.GetInt64("id");
   MgByteReader byteReader = featureReader.GetGeometry("GEOMETRY");

   MgGeometry geometry = geometryReaderWriter.Read(byteReader);
   MgPoint point = geometry.GetCentroid();
   double x = point.GetCoordinate().GetX();
   double y = point.GetCoordinate().GetY();
   xy n=new  xy(x,y,id,id1,mc);
   vec.add(n);
   geometryReaderWriter.delete();        //close
  }

  resService.delete();                          //close
  featService.delete();                         //close
  map.delete();                                  //close
  return vec;

}

out of this function,i closed the connection to the site with
"siteConnection.delete();"

Once i run this function ,checking the process of MySQL with "show
processlist" in MySQL command shell,i will got two more sleep process,which
can not be closed.

original:

mysql> show processlist;

| Id    | User | Host                | db          | Command | Time | State
| Info
|   1   | root  | localhost:4955  | psd        | Sleep        |    2
 |           | NULL
| 193 | root   | localhost:3941 | NULL      | Query       |    0   | NULL  |
show processlist |
| 194 | root   | localhost:3963 | jsof_data | Sleep        |
4   |           | NULL
| 195 | root   | localhost:3964 | jsof_data | Sleep        |    8
|            | NULL

2 rows in set (0.00 sec)

ofter used the function:

mysql> show processlist;

| Id    | User | Host                | db          | Command | Time | State
| Info
|   1   | root  | localhost:4955  | psd        | Sleep        |    2
 |           | NULL
| 193 | root   | localhost:3941 | NULL      | Query       |    0   | NULL  |
show processlist |
| 194 | root   | localhost:3963 | jsof_data | Sleep        |
4   |           | NULL
| 195 | root   | localhost:3964 | jsof_data | Sleep        |    8
|            | NULL
| 196 | root   | localhost:3965 | jsof_data | Sleep
 |    2   |           | NULL
| 197 | root   | localhost:3966 | jsof_data | Sleep        |    3
|            | NULL




6 rows in set (0.00 sec)

i tried to kill the sleep process with the ways as follow:

<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.io.*"%>

<%
     String path = request.getContextPath();
     String basePath = request.getScheme() + "://"
                                + request.getServerName() + ":" +
request.getServerPort()
                                + path + "/";
     Class.forName("com.mysql.jdbc.Driver");
     String url =
"jdbc:mysql://localhost/psd?characterEncoding=utf8&user=root&password=root";
     Connection conn = DriverManager.getConnection(url);
     Statement stmt = conn.createStatement();
     Statement stmt2 = conn.createStatement();
     ResultSet rs = stmt.executeQuery("show processlist");
     while (rs.next()) {
         String Id = rs.getString("Id");
            String cmd = rs.getString("Command");
            String Time = rs.getString("Time");
            int i = Integer.parseInt(Time);
            if(i>10&&cmd.equals("Sleep"))                        //Kill the
process which has been slept for more than 10 seconds
               stmt2.executeQuery("kill "+Id);
            else
             }
     rs.close();
     stmt.close();
     stmt2.close();
     conn.close();
%>

But i will get "The MySQL has gone away"error message.I have to restart
MapGuideServer service to fix this problem.

Can anyhelp me on this problem?

Thanks for your attention:)

Jacky.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapguide-users/attachments/20080806/d2402d2f/attachment.html