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

[Zac Spitzer]

try using featureReader close()

z

On Wed, Aug 6, 2008 at 7:30 PM, 徐震 <xuzhengeology at gmail.com> wrote:
> 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.
>
> _______________________________________________
> mapguide-users mailing list
> mapguide-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapguide-users
>
>



-- 
Zac Spitzer -
http://zacster.blogspot.com (My Blog)
+61 405 847 168