| Bug #13022 | slowing down without traffic | ||
|---|---|---|---|
| Submitted: | 6 Sep 2005 20:27 | Modified: | 6 Oct 2005 11:53 |
| Reporter: | Jens Doll | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1 | OS: | Windows (Windows XP Pro) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[6 Sep 2005 20:27]
Jens Doll
[6 Sep 2005 20:43]
MySQL Verification Team
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL.
[10 Sep 2005 20:09]
Jens Doll
The cause lies in the following JAVA bean, which calls MYSQL. Aan Open connection, an insert/update, a commit and a close connection.:
----------- jsp code calling bean ---------------
<% String Referrer = ""; String[] paths = request.getParameterValues("referrer");
if (paths != null) if (paths.length > 0) {Referrer = request.getScheme() + "://" + request.getServerName() + paths[0];}
if (Referrer.equals("")) Referrer = request.getRequestURL().toString(); %>
<jsp:useBean id='monitor' scope='session' class='datenbankzugriff.Monitoring' type="datenbankzugriff.Monitoring" />
<jsp:setProperty name="monitor" property="addr" value="<%= request.getRemoteAddr() %>" />
<jsp:setProperty name="monitor" property="host" value="<%= request.getRemoteHost() %>" />
<jsp:setProperty name="monitor" property="url" value="<%= request.getRequestURL().toString() %>" />
<jsp:setProperty name="monitor" property="referrer" value="<%= Referrer %>" />
<jsp:setProperty name="monitor" property="stats" value="" />
<jsp:setProperty name="monitor" property="close" value="<%= Referrer %>" />
----------- bean monitoring ---------------------
package datenbankzugriff;
/**
* <p>Title: </p>
*
* <p>Description: </p>
*
* <p>Copyright: Copyright (c) 2005</p>
*
* <p>Company: </p>
*
* @author not attributable
* @version 1.0
*/
import java.sql.*;
import java.net.*;
import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class Monitoring {
String urlprod = "jdbc:mysql://myhost:3306"; // production
String urltest = "jdbc:mysql://mytesthost:3306"; // test
String database = "/monitoring";
Connection con = null;
Statement sqlstmt = null;
ResultSet result = null;
String lastmessage = null;
String urlfrom = null;
long Recordcount = 0;
String Addr = null;
String Host = null;
String Url = null;
String Referrer = null;
String Hostname = null;
public Monitoring() {
super();
}
private void DbTry(String ipfrom) {
Recordcount = 0;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {
ex.printStackTrace();
lastmessage = "Failed to load mSQL driver.";
return;
}
try {
// first try production database
con = DriverManager.getConnection(urlprod + database, "monitor",
"pw");
sqlstmt = con.createStatement();
} catch (SQLException ex) {
try {
// then try test database
con = DriverManager.getConnection(urltest + database, "monitor",
"pw9999999");
sqlstmt = con.createStatement();
} catch (SQLException ex1) {
ex1.printStackTrace();
lastmessage = "M=" + ex1.getMessage()
+ "S=" + ex1.getSQLState()
+ "C=" + ex1.getErrorCode();
}
}
try {
sqlstmt = con.createStatement();
result = sqlstmt.executeQuery(
"select count(*) from monitoring.urltable where ip='" +
ipfrom + "'");
result.next();
Recordcount = (int) result.getLong(1);
} catch (SQLException ex) {
ex.printStackTrace();
lastmessage = "M=" + ex.getMessage()
+ "S=" + ex.getSQLState()
+ "C=" + ex.getErrorCode();
}
}
public String getIpaddress(String hostname) {
String ipaddr = "";
try {
InetAddress inet = InetAddress.getByName(hostname);
ipaddr = inet.getHostAddress();
}
catch (Exception ex) {
System.out.println (ex.toString());
}
return ipaddr;
}
public String getHostname(String ipaddr) {
String hostname = "";
try {
InetAddress inet = InetAddress.getByName(ipaddr);
hostname = inet.getHostName();
}
catch (Exception ex) {
System.out.println (ex.toString());
}
return hostname;
}
public long getIpcount(String ipfrom) {
if (result == null) {
DbTry(ipfrom);
}
return Recordcount;
} // process results one row at a time
public void setStats(String non) { // initializes record;
setIpaddr(Host, Addr, Url);
}
public void setAddr(String a) { // initializes record;
Addr = a;
Hostname = getHostname(a);
}
public void setHost(String a) { // initializes record;
Host = a;
}
public void setUrl(String a) { // initializes record;
Url = a;
}
public void setReferrer(String referrer) { // initializes record;
Referrer = referrer;
}
public void setIpaddr(String urlfrom, String ipfrom, String urlvisited) { // initializes record;
getIpcount(ipfrom);
if (Recordcount == 0) { // perform insert
try {
String Stt = "insert into monitoring.urltable (urlfrom, ip, urlvisited, count, firstdate, lastdate, referrer, hostname) values(\"" +
urlfrom + "\", \"" + ipfrom + "\", \""+ urlvisited + "\", 1, current_timestamp, current_timestamp, \""+Referrer+"\", \""+Hostname+"\")";
sqlstmt.executeUpdate ( Stt );
} catch (SQLException ex) {
ex.printStackTrace();
lastmessage = "M=" + ex.getMessage()
+ "S=" + ex.getSQLState()
+ "C=" + ex.getErrorCode();
}
}
else { // perform update
try {
String Stt = "update monitoring.urltable set count = count + 1, lastdate = current_timestamp where urlfrom = \"" +
urlfrom + "\" and urlvisited = \""+urlvisited + "\" and ip = \"" + ipfrom + "\"";
sqlstmt.executeUpdate( Stt );
} catch (SQLException ex) {
ex.printStackTrace();
lastmessage = "M=" + ex.getMessage()
+ "S=" + ex.getSQLState()
+ "C=" + ex.getErrorCode();
}
}
// now commit insert or update
if (result != null) {
try {
sqlstmt.executeUpdate("commit");
} catch (SQLException ex) {
ex.printStackTrace();
lastmessage = "M=" + ex.getMessage()
+ "S=" + ex.getSQLState()
+ "C=" + ex.getErrorCode();
}
}
}
public void setClose(String a) { // closes the database
try {
con.close();}
catch (Exception ex) {
ex.printStackTrace();
lastmessage = "M=" + ex.getMessage();}}
}
[10 Sep 2005 20:13]
Jens Doll
Hello Miguel, the code, which I atteched could perhaps help you in the investigation. Regards Jens.
[11 Sep 2005 16:44]
Jens Doll
and here is the missing table definition ------------ table monitoring.urltable ------------------ CREATE TABLE `urltable` ( `urlfrom` varchar(48) NOT NULL default '', `count` int(10) unsigned NOT NULL default '0', `username` varchar(48) NOT NULL default '', `ip` varchar(16) NOT NULL default '', `cookie` varchar(45) NOT NULL default '', `firstdate` datetime NOT NULL default '0000-00-00 00:00:00', `lastdate` datetime NOT NULL default '0000-00-00 00:00:00', `urlvisited` varchar(48) NOT NULL default '', `referrer` varchar(45) NOT NULL default '', `hostname` varchar(45) NOT NULL default '', PRIMARY KEY (`urlfrom`,`ip`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[25 Sep 2005 14:47]
MySQL Verification Team
If you are using host names please do the following test on your side: start the server including the start option: skip-name-resolve and connect client using IP numbers. Thanks in advance.
[6 Oct 2005 10:16]
Jens Doll
The IP resolution could have been the cause. I substituted the hostnames by localhost (not a remote hostname!) and the site is seems to be OK. Is this a Windows XP problem? I plan to migrate to Linux ... Thanks.
[6 Oct 2005 11:53]
MySQL Verification Team
Thank you for the feedback. I guess that issue is an OS setting issue so you need to read the Manual and Technical articles about, DHCP if you have enabled and so on. I am changing for not a bug since isn't a server issue. Thanks.
