[mapguide-users] too Many Connections error(JSP+MySQL)
=?GB2312?B?0OzV8A==?=
xuzhengeology at gmail.com
Mon Aug 11 02:54:05 EDT 2008
Thank u veymuch for your useful help.
It did work well now.Thank u again.: )
I rechecked my codes to see whether i have other mistakes.
I find that featureReader.close() is used for the close of select operation.
For featService.UpdateFeatures operation,we should set the 3rd parameter to
false like this:
featService.UpdateFeatures(resId, commands, false);
so the connections created by Update operation can be released.
Jacky.
2008/8/6, Zac Spitzer <zac.spitzer at gmail.com>:
>
> 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
> _______________________________________________
> mapguide-users mailing list
> mapguide-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapguide-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapguide-users/attachments/20080811/e7ade266/attachment.html
More information about the mapguide-users
mailing list