Website suspended: mysql loop caused using too much CPU - Please fix

[PeeBee]

Hi

I have recently moved over to f2s and was impressed by the fact that you got free php and MySQL support. I'm not so impressed by the fact that my website has been suspended already!

I have sent the following to them via a Ticket:

"My website (www.barfootp.f2s.com) is currently suspended.

The Reason file in the area states "mysql loop caused using too much CPU - Please fix."

Could you please advise on which of my PHP scripts was causing this mysql loop, and how I go about getting my website un-suspended.

Thank you"

I have had no reply to the Ticket apart from the automated one and I get no-one to pick-up when I call Tech Support.

Has anyone here any ideas as to why the site was suspended as the reason given is meaningless to me? The exact same site runs fine on my PCs at home (using both Windows and Linux versions of Apache, PHP and MySQL) and when hosted via a friend's web servers.

Looking at my log files I can see the last script that ran before the site was suspended and the "Page Not Found" messages started. Would this have been the script that caused the problem? I can post the script here if anyone thinks that would help.

Thanks in advance for any help.

PB

[Xenith]

Just recheck through your scripts and find the parts that do loops ( notably in your last script to run possibly ). And dry run the code in your head to see if it is a never-ending loop. I think many things like this are also due to people not adding

<?php
mysql_close()
?>

After using the mysql $query.

Correct me if im wrong as I am still learning php myself but un-ending loops are a fundamental flaw in any programming language and a common site

[HxBro]

post the script (* out the username/password if it's there) and I'll have a look at it

[PeeBee]

Thanks

The last entries in my logs before the site was suspended were:

143.238.232.107 - - [06/Dec/2005:11:55:15 +0000] "GET /names.php?Surname=Weaving HTTP/1.1" 200 3297 "http://www.barfootp.f2s.com/surnames.php?letter=W" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
143.238.232.107 - - [06/Dec/2005:11:55:48 +0000] "GET /names.php?Surname=Whelan HTTP/1.1" 200 4112 "http://www.barfootp.f2s.com/surnames.php?letter=W" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)"
203.164.8.124 - - [06/Dec/2005:22:20:24 +0000] "GET / HTTP/1.1" 404 286 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
203.164.8.124 - - [06/Dec/2005:22:20:32 +0000] "GET / HTTP/1.1" 404 286 "-" "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"

which seems to point to the names.php script. Here it is:

<?
require("session.php");
require("config.php");
$strSQL="select * from Individuals";
if (empty($_GET['Surname'])) {
$strSQL=$strSQL." order by surname, givenname";
$strTitle="Name Index";
} else {
if ($_GET['Surname'] == 'Unknown') {
$strSQL=$strSQL." where Surname=''";
} else {
$strSQL=$strSQL." where Surname='".$_GET['Surname']."'";
$Surname=stripslashes($_GET['Surname']);
}
$strSQL=$strSQL." order by givenname";
$strTitle="Name Index - ".$Surname;
$strCrossref="Click <a href=\"crossref.php?surname=".rawurlencode($Surnam e)."\">here</a> to see the contributors listed for this surname.<img border=\"0\" src=\"/images/new.gif\" alt=\"New Feature\" />";
}
require("header1.php");
if (!empty($strCrossref)) {
print "<p>$strCrossref</p>\n";
}
$result1=mysql_query($strSQL);
$inds=mysql_num_rows($result1);
?>
<p>
<?
for ($n=0;$n<$inds;$n++) {
$id=mysql_result($result1, $n, 'indid');
$surname=mysql_result($result1, $n, 'surname');
$givenname=mysql_result($result1, $n, 'givenname');
$birthdate=mysql_result($result1, $n, 'birthdate');
$deathdate=mysql_result($result1, $n, 'deathdate');
$living=mysql_result($result1, $n, 'living');
if ($living==-1) {
print "<a href=\"m2h.php?IndID=".$id."\">".$surname.", ".$givenname." </a>&nbsp;***Private***<br />";
} else {
print "<a href=\"m2h.php?IndID=".$id."\">".$surname.", ".$givenname."&nbsp;</a> ";
if (!empty($birthdate)) {
print "&nbsp;b".$birthdate."&nbsp;";
}
if (!empty($deathdate)) {
print "&nbsp;d".$deathdate."&nbsp;";
}
print "<br />";
}
}
?>
</p>
<hr />
<?
require("home.php");
require("links.php");
require("footer.php");
require("end.php");
?>

The purpose of the script is to display a page containing all of the people in the database with the surname that was sent as a parameter. The session.php file sets up the connection and the config.php sets up certain variables such as when the data on the site was last updated.

The Individuals table contains approx. 47000 records. I can imagine that if the names.php script was called with the Surname parameter empty then it could have caused a large amount of processing for the server! This might have caused the problem in the first place.

Deciding what is wrong with the script is a pointless task if f2s don't get back to me about un-suspending my site but I appreciate any help.

PB

[HxBro]

I'm guessing one of the requires at the end has a mysql_close() in (as xenith suggested). I'm not certain if it would be, but it can probably be optimised a bit better to not do as many calls to the mysql result, rather than selecting * specify the fields you want, and use echo instead of print (although debatable if it is quicker) :

<?
require("session.php");
require("config.php");

$strSQL="select indid, surname, givenname, birthdate, deathdate, living from Individuals";

if (empty($_GET['Surname'])) {
$strSQL=$strSQL." order by surname, givenname";
$strTitle="Name Index";
} else {
if ($_GET['Surname'] == 'Unknown') {
$strSQL=$strSQL." where Surname=''";
} else {
$strSQL=$strSQL." where Surname='".$_GET['Surname']."'";
$Surname=stripslashes($_GET['Surname']);
}

$strSQL=$strSQL." order by givenname";
$strTitle="Name Index - ".$Surname;
$strCrossref="Click <a href=\"crossref.php?surname=".rawurlencode($Surnam e)."\">here</a> to see the contributors listed for this surname.<img border=\"0\" src=\"/images/new.gif\" alt=\"New Feature\" />";
}

require("header1.php");

if (!empty($strCrossref)) {
echo "<p>$strCrossref</p>\n";
}

$result1=mysql_query($strSQL);

echo "<p>";

while ($row = mysql_fetch_array($result1))
{
$id=$row['indid'];
$surname=$row['surname'];
$givenname=$row['givenname'];
$birthdate=$row['birthdate'];
$deathdate=$row['deathdate'];

if ($row['living']==-1) {
echo "<a href=\"m2h.php?IndID=".$id."\">".$surname.", ".$givenname." </a>&nbsp;***Private***<br />";
} else {
echo "<a href=\"m2h.php?IndID=".$id."\">".$surname.", ".$givenname."&nbsp;</a> ";
if (!empty($birthdate)) {
echo "&nbsp;b".$birthdate."&nbsp;";
}

if (!empty($deathdate)) {
echo "&nbsp;d".$deathdate."&nbsp;";
}

echo "<br />";
}
}

echo "</p>";
echo "<hr />";

require("home.php");
require("links.php");
require("footer.php");
require("end.php");
?>

might be worth trying it on the other servers you have access to, and see if it does run quicker or not. I could imagine the 47K lines might be too much data for the f2s server.

[wicksee]

Hi PeeBee.

I can't see anything fundamentally wrong with your script - for example your for loop doesn't appear to be capable of infinity.

However, if you have a lot of records (as you do) then I would definitely recommend using HxBro's approach with a while loop and mysql_fetch_array(). This function fetches an entire row at a time which for your purposes (and with lots of records) is a lot more efficient than mysql_result() (which only fetches one cell at a time).

http://uk.php.net/mysql_result
string mysql_result ( resource result, int row [, mixed field] )

Retrieves the contents of one cell from a MySQL result set.

When working on large result sets, you should consider using one of the functions that fetch an entire row (specified below). As these functions return the contents of multiple cells in one function call, they're MUCH quicker than mysql_result(). Also, note that specifying a numeric offset for the field argument is much quicker than specifying a fieldname or tablename.fieldname argument.

See Also
mysql_fetch_row()
mysql_fetch_array()
mysql_fetch_assoc()
mysql_fetch_object()

PS. I don't think that mysql_close() calls are a problem. Connections are terminated automatically at the end of a script in all cases, so calls to that function are not really necessary except for "code tidyness". Having too many mysql_close() calls (ie. closing a connection that is already closed or never existed) would just generate an error. It would not cause any extra CPU cycles.

[PeeBee]

Wicksee and HxBro:

Thanks for your responses - I'll have a look at re-writing and testing the script using the functions suggested.

As I said though, it's all pretty pointless if I can't get f2s to remove the suspension from the website! Any shortcuts through to Tech Support that you knwo of? Any of their people look in on this site and don't mind getting PM'd?

Thanks and Merry Christmas to all

PB

[HxBro]

is the site disabled, or the files just removed ? have you tried ftp'ing the files back ?

[PeeBee]

The folder name has been changed to www.barfootp.f2s.com.suspended so I can change the files as much as I like but I can't access the site. I don't have the correct permissions to rename the folder back again.

PB

[PeeBee]

Hi again

F2S finally got back to me and un-suspended my site on 13 Jan - over 5 weeks after the suspension!

I made some changes to the names.php file - specifically I changed the behaviour if the script was called without a Surname parameter. Previously it would have pulled the names of every inidividual from the database but now it just pulls a subset.

I didn't have any links to the script without the paramtere but someone could have edited the address line in their browser - it would have exceeded the max_execution_time php setting which is what caused the high CPU usage in the first place (I think).

Thanks for everyone's help, I will take up some of your advice when I next have time to modify my scripts.

PB