Does anything about this script look expensive, in terms of resources or
execution time? This script dies after processing about 20 or 25
numbers, yet it leaves no errors in the error logs. This is on a server
that handles a fairly demanding site. The defaults, in php.ini, have all
been cranked fairly high: scripts get 180 seconds to run, and they can
have as much as 256 megs of RAM.
The input for this script is coming from a textarea in a form, and the
input could not be more simple, just a bunch of UPC codes, one per line:
881034146533x
881034146533xx
881034146533xxx
881034146533xxxx
881034146533xxxxx
881034146533xxxxxx
881034146533xxxxxxx
881034146533xxxxxxxx
881034146533xxxxxxxxx
881034146533xxxxxxxxxx
881034146533xxxxxxxxxxx
881034146533xxxxxxxxxxxx
881034146533xxxxxxxxxxxxx
881034146533xxxxxxxxxxxxxx
881034146533xxxxxxxxxxxxxxx
881034146533xxxxxxxxxxxxxxxx
881034146533xxxxxxxxxxxxxxxxx
881034146533xxxxxxxxxxxxxxxxxx
Given 15 such numbers, the script does fine. But given 200 such numbers,
it dies after processing about 10 numbers.
Supposedly, the code was written many years ago, back in 2002, and yet,
supposedly, the problem with this script (that it dies) only started
recently. Supposedly, no changes were made to the script, it simply
started to die. (Sadly, the code for the site was only recently put into
Subversion, and I only recently joined the project, so I've no way to
evaluate these claims.)
<?
set_time_limit(0);
require ("include_header.php");
$dbh = mysql_pconnect("xxxxx", "xxxxx", "xxxxx");
mysql_select_db("alb");
$upc_input = $_POST["upc_input"];
if ($upc_input) {
$pieces = explode("\n", $upc_input);
$count = 0;
$log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
$fp = fopen($log_file, 'a+');
while ($upc = $pieces[$count]) {
$upc = trim($upc);
fwrite($fp, $upc."\n");
echo "Removing UPC/ISRC Code: $upc <br />\n";
$query = "select id from `albums` where UPPER(upc_id) =
'".strtoupper($upc)."'";
$result = mysql_query($query);
if (mysql_num_rows($result)) {
// 10-15-08 - this next line makes the assumption that there can
only be
// one record for each UPC. Since I don't know enough to argue, I'll
// assume this must be true for now. I wonder if the import scripts
// enforce uniqueness on the UPC? --LK
$row = mysql_fetch_array($result);
$id = $row["id"];
/* We changed this to status='4' to indicate full removal.
Only values of status='3' will be checked when we run
our go_live.php cronjob to set albums live on certain dates
*/
$query2 = "update albums SET status='4' where id=".$id;
$result2 = mysql_query($query2);
$numUpdated = mysql_affected_rows($dbh);
if ($numUpdated) {
echo "Changed album status for UPC $id <br />\n";
} else {
// echo "Failed to change album status for UPC $id <br />\n ";
//
// 10-15-08 - what happens when some staffer puts the same UPC in
twice? I don't
// want to give a "fail" message. Let's check to see the status.
If it equals 4
// then the record has already been successfully updated. If not,
then we can
// give a fail message.
$queryStatusCheck = "SELECT status FROM albums WHERE id='$id' ";
$resultStatusCheck = mysql_query($queryStatusCheck);
$rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
$status = $rowStatusCheck["status"];
if ($status == 4) {
echo "Album status already updated <br />\n ";
} else {
echo "Failed to change album status for UPC $upc (album $id)
<br />\n ";
}
}
$query3 = "update tracks SET status='4' where album_id=".$id;
$result3 = mysql_query($query3);
$numUpdated = mysql_affected_rows($dbh);
if ($numUpdated) {
echo "Changed tracks album status for UPC $id <br /><br />\n\n ";
} else {
// echo "Failed to change tracks album status for UPC $id <br
/><br />\n\n ";
//
// 10-15-08 - what happens when some staffer puts the same UPC in
twice? I don't
// want to give a "fail" message. Let's check to see the status.
If it equals 4
// then the record has already been successfully updated. If not,
then we can
// give a fail message.
$queryStatusCheck = "SELECT status FROM tracks WHERE
album_id='$id' ";
$resultStatusCheck = mysql_query($queryStatusCheck);
$rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
$status = $rowStatusCheck["status"];
if ($status == 4) {
echo "Track album status already updated <br />\n ";
} else {
echo "Failed to change track album status for UPC $upc (album
$id) <br />\n ";
}
}
} else {
$query = "select id from `tracks` where UPPER(isrc_id) =
'".strtoupper($upc)."'";
$result = mysql_query($query);
if (mysql_num_rows($result)) {
$row = mysql_fetch_array($result);
$id = $row["id"];
$query2 = "update tracks SET status='4' where id=".$id;
$result2 = mysql_query($query2);
echo "Successfully removed and rebuilt Track Id ".$id;
} else {
echo "Could not find Code as an Album UPC or a Track ISRC";
}
}
echo "<br />";
$count++;
}
fclose($fp);
?>
|
|
0
|
|
|
|
Reply
|
 lawrence1093 (52)
|
10/20/2008 3:01:44 AM |
|
>Does anything about this script look expensive, in terms of resources or
>execution time? This script dies after processing about 20 or 25
>numbers, yet it leaves no errors in the error logs. This is on a server
>that handles a fairly demanding site. The defaults, in php.ini, have all
>been cranked fairly high: scripts get 180 seconds to run, and they can
>have as much as 256 megs of RAM.
You leak result sets, maybe 7 per UPC code. I am not sure how
intelligent PHP is in reclaiming result sets when the result is
assigned to the same variable as the last one, and all references
to the old result set are gone. Use of mysql_free_result() is
appropriate when you're through with a result set.
Assuming your result sets only return 1 row, 7*25 result sets
shouldn't come close to running you out of memory. How long does
it take the script to die? 7*25 queries in 180 seconds seems way
slow unless you've got a large table with no useful indexes. You
might, however, be in trouble memory-wise if your queries in fact
return many rows rather than one per query.
What does SHOW CREATE TABLE indicate for the tables involved?
Do they have indexes? On what?
What does the query
select upc_id, count(upc_id) from `albums` group by upc_id;
return? Does it indicate any duplicates?
What does the query
select upc_id, count(upc_id) from `albums` group by upper(upc_id);
return? Does it indicate any duplicates? How long does it take to run?
It might be better to ensure the case of the entries in the database,
(say, with update `albums` set upc_id = UPPER(upc_id); , then make
sure anything later inserted is converted to upper case first,
and change WHERE UPPER(upc_id) = ... to WHERE upc_id = ... .
as it may permit using indexes better.
>The input for this script is coming from a textarea in a form, and the
>input could not be more simple, just a bunch of UPC codes, one per line:
>
>
> 881034146533x
> 881034146533xx
> 881034146533xxx
> 881034146533xxxx
> 881034146533xxxxx
> 881034146533xxxxxx
> 881034146533xxxxxxx
> 881034146533xxxxxxxx
> 881034146533xxxxxxxxx
> 881034146533xxxxxxxxxx
> 881034146533xxxxxxxxxxx
> 881034146533xxxxxxxxxxxx
> 881034146533xxxxxxxxxxxxx
> 881034146533xxxxxxxxxxxxxx
> 881034146533xxxxxxxxxxxxxxx
> 881034146533xxxxxxxxxxxxxxxx
> 881034146533xxxxxxxxxxxxxxxxx
> 881034146533xxxxxxxxxxxxxxxxxx
>
>
>Given 15 such numbers, the script does fine. But given 200 such numbers,
>it dies after processing about 10 numbers.
>
>Supposedly, the code was written many years ago, back in 2002, and yet,
>supposedly, the problem with this script (that it dies) only started
>recently. Supposedly, no changes were made to the script, it simply
>started to die. (Sadly, the code for the site was only recently put into
>Subversion, and I only recently joined the project, so I've no way to
>evaluate these claims.)
Did you recently set up the database on a different system, and perhaps
forget to put in the indexes? Could duplicate records be piling up?
>
>
><?
>set_time_limit(0);
>
>require ("include_header.php");
>
>$dbh = mysql_pconnect("xxxxx", "xxxxx", "xxxxx");
>
>mysql_select_db("alb");
>
>$upc_input = $_POST["upc_input"];
>
>if ($upc_input) {
>
>$pieces = explode("\n", $upc_input);
>
>$count = 0;
>
>
>$log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
>$fp = fopen($log_file, 'a+');
>
>
>while ($upc = $pieces[$count]) {
> $upc = trim($upc);
> fwrite($fp, $upc."\n");
> echo "Removing UPC/ISRC Code: $upc <br />\n";
> $query = "select id from `albums` where UPPER(upc_id) =
>'".strtoupper($upc)."'";
> $result = mysql_query($query);
>
> if (mysql_num_rows($result)) {
> // 10-15-08 - this next line makes the assumption that there can
>only be
> // one record for each UPC. Since I don't know enough to argue, I'll
> // assume this must be true for now. I wonder if the import scripts
> // enforce uniqueness on the UPC? --LK
> $row = mysql_fetch_array($result);
> $id = $row["id"];
>
> /* We changed this to status='4' to indicate full removal.
> Only values of status='3' will be checked when we run
> our go_live.php cronjob to set albums live on certain dates
> */
> $query2 = "update albums SET status='4' where id=".$id;
> $result2 = mysql_query($query2);
> $numUpdated = mysql_affected_rows($dbh);
> if ($numUpdated) {
> echo "Changed album status for UPC $id <br />\n";
> } else {
> // echo "Failed to change album status for UPC $id <br />\n ";
> //
> // 10-15-08 - what happens when some staffer puts the same UPC in
>twice? I don't
> // want to give a "fail" message. Let's check to see the status.
>If it equals 4
> // then the record has already been successfully updated. If not,
>then we can
> // give a fail message.
> $queryStatusCheck = "SELECT status FROM albums WHERE id='$id' ";
> $resultStatusCheck = mysql_query($queryStatusCheck);
> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
> $status = $rowStatusCheck["status"];
> if ($status == 4) {
> echo "Album status already updated <br />\n ";
> } else {
> echo "Failed to change album status for UPC $upc (album $id)
><br />\n ";
> }
> }
>
> $query3 = "update tracks SET status='4' where album_id=".$id;
> $result3 = mysql_query($query3);
> $numUpdated = mysql_affected_rows($dbh);
> if ($numUpdated) {
> echo "Changed tracks album status for UPC $id <br /><br />\n\n ";
> } else {
> // echo "Failed to change tracks album status for UPC $id <br
>/><br />\n\n ";
> //
> // 10-15-08 - what happens when some staffer puts the same UPC in
>twice? I don't
> // want to give a "fail" message. Let's check to see the status.
>If it equals 4
> // then the record has already been successfully updated. If not,
>then we can
> // give a fail message.
> $queryStatusCheck = "SELECT status FROM tracks WHERE
>album_id='$id' ";
> $resultStatusCheck = mysql_query($queryStatusCheck);
> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
> $status = $rowStatusCheck["status"];
> if ($status == 4) {
> echo "Track album status already updated <br />\n ";
> } else {
> echo "Failed to change track album status for UPC $upc (album
>$id) <br />\n ";
> }
> }
>
>
> } else {
> $query = "select id from `tracks` where UPPER(isrc_id) =
>'".strtoupper($upc)."'";
> $result = mysql_query($query);
>
> if (mysql_num_rows($result)) {
> $row = mysql_fetch_array($result);
> $id = $row["id"];
> $query2 = "update tracks SET status='4' where id=".$id;
> $result2 = mysql_query($query2);
> echo "Successfully removed and rebuilt Track Id ".$id;
>
> } else {
> echo "Could not find Code as an Album UPC or a Track ISRC";
> }
>
> }
> echo "<br />";
> $count++;
>}
>
>fclose($fp);
>
>
>?>
|
|
|
0
|
|
|
|
Reply
|
 gordonb.9unhl (1)
|
10/20/2008 3:40:52 AM
|
|
Gordon Burditt wrote:
>> Does anything about this script look expensive, in terms of resources or
>> execution time? This script dies after processing about 20 or 25
>> numbers, yet it leaves no errors in the error logs. This is on a server
>> that handles a fairly demanding site. The defaults, in php.ini, have all
>> been cranked fairly high: scripts get 180 seconds to run, and they can
>> have as much as 256 megs of RAM.
>
> You leak result sets, maybe 7 per UPC code. I am not sure how
> intelligent PHP is in reclaiming result sets when the result is
> assigned to the same variable as the last one, and all references
> to the old result set are gone. Use of mysql_free_result() is
> appropriate when you're through with a result set.
>
Actually, it's quite a bit more than that. mysql_free_result() releases
a little memory in PHP - but also results in a call to MySQL to free
MySQL resources (which can be large).
PHP will reclaim the result set itself when the references are gone, but
it won't free the space in MySQL - that will only happen when the
connection is closed.
> Assuming your result sets only return 1 row, 7*25 result sets
> shouldn't come close to running you out of memory. How long does
> it take the script to die? 7*25 queries in 180 seconds seems way
> slow unless you've got a large table with no useful indexes. You
> might, however, be in trouble memory-wise if your queries in fact
> return many rows rather than one per query.
>
Agreed. There is another problem here. But the problem may be that
errors are being neither displayed nor logged. The php.ini file should
have:
error_reporting=E_ALL
log_errors=on
If the user wants the PHP errors in a separate file, he should have:
error_log="/path/and/file/name"
Otherwise errors will show up in his Apache error log.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
|
|
|
0
|
|
|
|
Reply
|
 jstucklex (14362)
|
10/20/2008 11:35:56 AM
|
|
Gordon Burditt wrote:
>> Does anything about this script look expensive, in terms of resources or
>> execution time? This script dies after processing about 20 or 25
>> numbers, yet it leaves no errors in the error logs. This is on a server
>> that handles a fairly demanding site. The defaults, in php.ini, have all
>> been cranked fairly high: scripts get 180 seconds to run, and they can
>> have as much as 256 megs of RAM.
>
> You leak result sets, maybe 7 per UPC code. I am not sure how
> intelligent PHP is in reclaiming result sets when the result is
> assigned to the same variable as the last one, and all references
> to the old result set are gone. Use of mysql_free_result() is
> appropriate when you're through with a result set.
>
> Assuming your result sets only return 1 row, 7*25 result sets
> shouldn't come close to running you out of memory. How long does
> it take the script to die? 7*25 queries in 180 seconds seems way
> slow unless you've got a large table with no useful indexes. You
> might, however, be in trouble memory-wise if your queries in fact
> return many rows rather than one per query.
>
> What does SHOW CREATE TABLE indicate for the tables involved?
> Do they have indexes? On what?
Thanks for the feedback. The albums table has an index on id and upc_id,
which should be enough for that table, as they are the only fields for
that table mentioned in WHERE clauses in the script. The other table
mentioned (the 'tracks' table) has an index on album_id, however, it
does not have an index on isrc_id. I'll add that in.
> What does the query
> select upc_id, count(upc_id) from `albums` group by upc_id;
> return? Does it indicate any duplicates?
Wow. I should have checked that myself. Lots of duplicates. 180215 total
returns. When I add in ORDER BY fullCount:
select upc_id, count(upc_id) as fullCount from `albums` group by upc_id
order by fullCount
10147
730099141024 86
828021600821 45
730099112017 40
711788020325 34
026297602628 34
8716514000215 30
711788030225 29
780702431929 26
675818003521 24
696215495122 23
692548801029 23
076637047220 23
5033045223344 22
180968000608 22
711788030126 22
Whenever there is an empty string, 10147 rows are selected.
> What does the query
> select upc_id, count(upc_id) from `albums` group by upper(upc_id);
> return? Does it indicate any duplicates? How long does it take to run?
> It might be better to ensure the case of the entries in the database,
> (say, with update `albums` set upc_id = UPPER(upc_id); , then make
> sure anything later inserted is converted to upper case first,
> and change WHERE UPPER(upc_id) = ... to WHERE upc_id = ... .
> as it may permit using indexes better.
>
>
>> The input for this script is coming from a textarea in a form, and the
>> input could not be more simple, just a bunch of UPC codes, one per line:
>>
>>
>> 881034146533x
>> 881034146533xx
>> 881034146533xxx
>> 881034146533xxxx
>> 881034146533xxxxx
>> 881034146533xxxxxx
>> 881034146533xxxxxxx
>> 881034146533xxxxxxxx
>> 881034146533xxxxxxxxx
>> 881034146533xxxxxxxxxx
>> 881034146533xxxxxxxxxxx
>> 881034146533xxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxxxxx
>>
>>
>> Given 15 such numbers, the script does fine. But given 200 such numbers,
>> it dies after processing about 10 numbers.
>>
>> Supposedly, the code was written many years ago, back in 2002, and yet,
>> supposedly, the problem with this script (that it dies) only started
>> recently. Supposedly, no changes were made to the script, it simply
>> started to die. (Sadly, the code for the site was only recently put into
>> Subversion, and I only recently joined the project, so I've no way to
>> evaluate these claims.)
>
> Did you recently set up the database on a different system, and perhaps
> forget to put in the indexes? Could duplicate records be piling up?
>
>>
>> <?
>> set_time_limit(0);
>>
>> require ("include_header.php");
>>
>> $dbh = mysql_pconnect("xxxxx", "xxxxx", "xxxxx");
>>
>> mysql_select_db("alb");
>>
>> $upc_input = $_POST["upc_input"];
>>
>> if ($upc_input) {
>>
>> $pieces = explode("\n", $upc_input);
>>
>> $count = 0;
>>
>>
>> $log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
>> $fp = fopen($log_file, 'a+');
>>
>>
>> while ($upc = $pieces[$count]) {
>> $upc = trim($upc);
>> fwrite($fp, $upc."\n");
>> echo "Removing UPC/ISRC Code: $upc <br />\n";
>> $query = "select id from `albums` where UPPER(upc_id) =
>> '".strtoupper($upc)."'";
>> $result = mysql_query($query);
>>
>> if (mysql_num_rows($result)) {
>> // 10-15-08 - this next line makes the assumption that there can
>> only be
>> // one record for each UPC. Since I don't know enough to argue, I'll
>> // assume this must be true for now. I wonder if the import scripts
>> // enforce uniqueness on the UPC? --LK
>> $row = mysql_fetch_array($result);
>> $id = $row["id"];
>>
>> /* We changed this to status='4' to indicate full removal.
>> Only values of status='3' will be checked when we run
>> our go_live.php cronjob to set albums live on certain dates
>> */
>> $query2 = "update albums SET status='4' where id=".$id;
>> $result2 = mysql_query($query2);
>> $numUpdated = mysql_affected_rows($dbh);
>> if ($numUpdated) {
>> echo "Changed album status for UPC $id <br />\n";
>> } else {
>> // echo "Failed to change album status for UPC $id <br />\n ";
>> //
>> // 10-15-08 - what happens when some staffer puts the same UPC in
>> twice? I don't
>> // want to give a "fail" message. Let's check to see the status.
>> If it equals 4
>> // then the record has already been successfully updated. If not,
>> then we can
>> // give a fail message.
>> $queryStatusCheck = "SELECT status FROM albums WHERE id='$id' ";
>> $resultStatusCheck = mysql_query($queryStatusCheck);
>> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
>> $status = $rowStatusCheck["status"];
>> if ($status == 4) {
>> echo "Album status already updated <br />\n ";
>> } else {
>> echo "Failed to change album status for UPC $upc (album $id)
>> <br />\n ";
>> }
>> }
>>
>> $query3 = "update tracks SET status='4' where album_id=".$id;
>> $result3 = mysql_query($query3);
>> $numUpdated = mysql_affected_rows($dbh);
>> if ($numUpdated) {
>> echo "Changed tracks album status for UPC $id <br /><br />\n\n ";
>> } else {
>> // echo "Failed to change tracks album status for UPC $id <br
>> /><br />\n\n ";
>> //
>> // 10-15-08 - what happens when some staffer puts the same UPC in
>> twice? I don't
>> // want to give a "fail" message. Let's check to see the status.
>> If it equals 4
>> // then the record has already been successfully updated. If not,
>> then we can
>> // give a fail message.
>> $queryStatusCheck = "SELECT status FROM tracks WHERE
>> album_id='$id' ";
>> $resultStatusCheck = mysql_query($queryStatusCheck);
>> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
>> $status = $rowStatusCheck["status"];
>> if ($status == 4) {
>> echo "Track album status already updated <br />\n ";
>> } else {
>> echo "Failed to change track album status for UPC $upc (album
>> $id) <br />\n ";
>> }
>> }
>>
>>
>> } else {
>> $query = "select id from `tracks` where UPPER(isrc_id) =
>> '".strtoupper($upc)."'";
>> $result = mysql_query($query);
>>
>> if (mysql_num_rows($result)) {
>> $row = mysql_fetch_array($result);
>> $id = $row["id"];
>> $query2 = "update tracks SET status='4' where id=".$id;
>> $result2 = mysql_query($query2);
>> echo "Successfully removed and rebuilt Track Id ".$id;
>>
>> } else {
>> echo "Could not find Code as an Album UPC or a Track ISRC";
>> }
>>
>> }
>> echo "<br />";
>> $count++;
>> }
>>
>> fclose($fp);
>>
>>
>> ?>
|
|
|
0
|
|
|
|
Reply
|
lawrence1093 (52)
|
10/21/2008 12:16:27 AM
|
|
Jerry Stuckle wrote:
> Gordon Burditt wrote:
>>> Does anything about this script look expensive, in terms of resources
>>> or execution time? This script dies after processing about 20 or 25
>>> numbers, yet it leaves no errors in the error logs. This is on a
>>> server that handles a fairly demanding site. The defaults, in
>>> php.ini, have all been cranked fairly high: scripts get 180 seconds
>>> to run, and they can have as much as 256 megs of RAM.
>>
>> You leak result sets, maybe 7 per UPC code. I am not sure how
>> intelligent PHP is in reclaiming result sets when the result is
>> assigned to the same variable as the last one, and all references
>> to the old result set are gone. Use of mysql_free_result() is
>> appropriate when you're through with a result set.
>>
>
> Actually, it's quite a bit more than that. mysql_free_result() releases
> a little memory in PHP - but also results in a call to MySQL to free
> MySQL resources (which can be large).
>
> PHP will reclaim the result set itself when the references are gone, but
> it won't free the space in MySQL - that will only happen when the
> connection is closed.
>
>> Assuming your result sets only return 1 row, 7*25 result sets
>> shouldn't come close to running you out of memory. How long does
>> it take the script to die? 7*25 queries in 180 seconds seems way
>> slow unless you've got a large table with no useful indexes. You
>> might, however, be in trouble memory-wise if your queries in fact
>> return many rows rather than one per query.
>>
>
> Agreed. There is another problem here. But the problem may be that
> errors are being neither displayed nor logged. The php.ini file should
> have:
>
> error_reporting=E_ALL
> log_errors=on
>
> If the user wants the PHP errors in a separate file, he should have:
>
> error_log="/path/and/file/name"
>
> Otherwise errors will show up in his Apache error log.
It took me awhile to appreciate how stupid this code is, but I finally
realized the whole, long, complicated while() loop could be rewritten
simply:
while ($upc = $pieces[$count]) {
if ($upc != "") {
$upc = trim($upc);
fwrite($fp, $upc."\n");
echo "Removing UPC/ISRC Code: $upc <br />\n";
$query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
'".strtoupper($upc)."'";
mysql_query($query);
$query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
'".strtoupper($upc)."'";
mysql_query($query);
echo "<br />";
$count++;
}
Would you recommend calling mysql_free_result() inside the loop?
|
|
|
0
|
|
|
|
Reply
|
lawrence1093 (52)
|
10/21/2008 2:28:21 AM
|
|
Lawrence Krubner wrote:
> Jerry Stuckle wrote:
>> Gordon Burditt wrote:
>>>> Does anything about this script look expensive, in terms of
>>>> resources or execution time? This script dies after processing about
>>>> 20 or 25 numbers, yet it leaves no errors in the error logs. This is
>>>> on a server that handles a fairly demanding site. The defaults, in
>>>> php.ini, have all been cranked fairly high: scripts get 180 seconds
>>>> to run, and they can have as much as 256 megs of RAM.
>>>
>>> You leak result sets, maybe 7 per UPC code. I am not sure how
>>> intelligent PHP is in reclaiming result sets when the result is
>>> assigned to the same variable as the last one, and all references
>>> to the old result set are gone. Use of mysql_free_result() is
>>> appropriate when you're through with a result set.
>>>
>>
>> Actually, it's quite a bit more than that. mysql_free_result()
>> releases a little memory in PHP - but also results in a call to MySQL
>> to free MySQL resources (which can be large).
>>
>> PHP will reclaim the result set itself when the references are gone,
>> but it won't free the space in MySQL - that will only happen when the
>> connection is closed.
>>
>>> Assuming your result sets only return 1 row, 7*25 result sets
>>> shouldn't come close to running you out of memory. How long does
>>> it take the script to die? 7*25 queries in 180 seconds seems way
>>> slow unless you've got a large table with no useful indexes. You
>>> might, however, be in trouble memory-wise if your queries in fact
>>> return many rows rather than one per query.
>>>
>>
>> Agreed. There is another problem here. But the problem may be that
>> errors are being neither displayed nor logged. The php.ini file
>> should have:
>>
>> error_reporting=E_ALL
>> log_errors=on
>>
>> If the user wants the PHP errors in a separate file, he should have:
>>
>> error_log="/path/and/file/name"
>>
>> Otherwise errors will show up in his Apache error log.
>
>
> It took me awhile to appreciate how stupid this code is, but I finally
> realized the whole, long, complicated while() loop could be rewritten
> simply:
>
> while ($upc = $pieces[$count]) {
> if ($upc != "") {
> $upc = trim($upc);
> fwrite($fp, $upc."\n");
> echo "Removing UPC/ISRC Code: $upc <br />\n";
>
> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
> '".strtoupper($upc)."'";
> mysql_query($query);
>
> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
> '".strtoupper($upc)."'";
> mysql_query($query);
>
> echo "<br />";
> $count++;
> }
>
>
> Would you recommend calling mysql_free_result() inside the loop?
>
>
>
>
mysql_free_result() is only used to release the result object from a
SELECT statement. You do not use it with UPDATE statements.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
|
|
|
0
|
|
|
|
Reply
|
jstucklex (14362)
|
10/21/2008 2:39:08 AM
|
|
>It took me awhile to appreciate how stupid this code is, but I finally
>realized the whole, long, complicated while() loop could be rewritten
>simply:
>
>while ($upc = $pieces[$count]) {
> if ($upc != "") {
> $upc = trim($upc);
> fwrite($fp, $upc."\n");
> echo "Removing UPC/ISRC Code: $upc <br />\n";
>
> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
>'".strtoupper($upc)."'";
> mysql_query($query);
>
> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
>'".strtoupper($upc)."'";
> mysql_query($query);
>
> echo "<br />";
> $count++;
>}
I still think it would be better to nail down what is in upc_id and
isrc_id (particularly, pick a case and stick to it, using UPPER()
or whatever whenever you insert something into the table), so you
can change:
WHERE UPPER(upc_id) = ...
to
WHERE upc_id = ...
A mass change of the database can be done with:
UPDATE albums set upc_id = UPPER(upc_id);
>Would you recommend calling mysql_free_result() inside the loop?
You don't do any SELECTs which return a result set, so it's not necessary.
|
|
|
0
|
|
|
|
Reply
|
 gordonb.1jzph (1)
|
10/21/2008 2:56:11 AM
|
|
Gordon Burditt wrote:
>> Does anything about this script look expensive, in terms of resources or
>> execution time? This script dies after processing about 20 or 25
>> numbers, yet it leaves no errors in the error logs. This is on a server
>> that handles a fairly demanding site. The defaults, in php.ini, have all
>> been cranked fairly high: scripts get 180 seconds to run, and they can
>> have as much as 256 megs of RAM.
>
> You leak result sets, maybe 7 per UPC code. I am not sure how
> intelligent PHP is in reclaiming result sets when the result is
> assigned to the same variable as the last one, and all references
> to the old result set are gone. Use of mysql_free_result() is
> appropriate when you're through with a result set.
>
> Assuming your result sets only return 1 row, 7*25 result sets
> shouldn't come close to running you out of memory. How long does
> it take the script to die? 7*25 queries in 180 seconds seems way
> slow unless you've got a large table with no useful indexes. You
> might, however, be in trouble memory-wise if your queries in fact
> return many rows rather than one per query.
>
> What does SHOW CREATE TABLE indicate for the tables involved?
> Do they have indexes? On what?
>
> What does the query
> select upc_id, count(upc_id) from `albums` group by upc_id;
> return? Does it indicate any duplicates?
> What does the query
> select upc_id, count(upc_id) from `albums` group by upper(upc_id);
> return? Does it indicate any duplicates? How long does it take to run?
> It might be better to ensure the case of the entries in the database,
> (say, with update `albums` set upc_id = UPPER(upc_id); , then make
> sure anything later inserted is converted to upper case first,
> and change WHERE UPPER(upc_id) = ... to WHERE upc_id = ... .
> as it may permit using indexes better.
Do the indexes slow down UPDATEs?
When I attempt to input 200 UPCs, I don't even get a PHP error, instead
I get this error:
"OK
The server encountered an internal error or misconfiguration and was
unable to complete your request.
Please contact the server administrator and inform them of the time the
error occurred, and anything you might have done that may have caused
the error.
More information about this error may be available in the server error log.
Apache/2.2.3 (CentOS) Port 443"
As I mentioned before, no errors show up in PHP error log.
If I run the "top" command while this script is executing, I see stuff
like this:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
24689 apache 18 0 14572 6312 2152 S 1 0.3 1:30.32 php
25296 apache 15 0 14976 7704 3244 S 1 0.4 4:51.05 php
29271 apache 15 0 34068 12m 4052 S 0 0.6 0:00.59 httpd
29368 root 15 0 2304 1064 792 R 0 0.1 0:00.38 top
1 root 15 0 2040 544 520 S 0 0.0 0:27.96 init
Though, the database is actually on a separate server. I suppose I
should run top on that server too.
Script works if I run this against just 5 or 10 UPCs, though the script
is insanely slow. This is true even if I run it against UPCs that I
myself have come up with, and which I'm sure are unique in the database.
I've simplified the code down to this :
if ($upc_input) {
$startTime = microtime();
echo "<p>Start time: $startTime </p>";
$upc_input = trim($upc_input);
$pieces = explode("\n", $upc_input);
$log_file =
dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
$fp = fopen($log_file, 'a+');
$numberOfUpcs = count($pieces);
echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
for ($i=0; $i < $numberOfUpcs; $i++) {
$upc = $pieces[$i];
// 10-20-08 see comment above to see why empty string is dangerous
- returns 10,147 rows!
if ($upc != "") {
$upc = trim($upc);
fwrite($fp, $upc."\n");
echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
$query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
'".strtoupper($upc)."'";
$result = mysql_query($query);
echo "<br /> \n albums updated: " . mysql_affected_rows();
$query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
'".strtoupper($upc)."'";
$result = mysql_query($query);
echo "<br /> \n tracks updated: " . mysql_affected_rows();
}
echo "<br />";
} // ends for() loop
fclose($fp);
$endTime = microtime();
echo "<p>End time: $endTime </p>";
$totalTime = $endTime - $startTime;
$totalTime = round($totalTime / 1000, 2);
echo "The total time for this script was $totalTime seconds";
}
>
>
>> The input for this script is coming from a textarea in a form, and the
>> input could not be more simple, just a bunch of UPC codes, one per line:
>>
>>
>> 881034146533x
>> 881034146533xx
>> 881034146533xxx
>> 881034146533xxxx
>> 881034146533xxxxx
>> 881034146533xxxxxx
>> 881034146533xxxxxxx
>> 881034146533xxxxxxxx
>> 881034146533xxxxxxxxx
>> 881034146533xxxxxxxxxx
>> 881034146533xxxxxxxxxxx
>> 881034146533xxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxxxxx
>>
>>
>> Given 15 such numbers, the script does fine. But given 200 such numbers,
>> it dies after processing about 10 numbers.
>>
>> Supposedly, the code was written many years ago, back in 2002, and yet,
>> supposedly, the problem with this script (that it dies) only started
>> recently. Supposedly, no changes were made to the script, it simply
>> started to die. (Sadly, the code for the site was only recently put into
>> Subversion, and I only recently joined the project, so I've no way to
>> evaluate these claims.)
>
> Did you recently set up the database on a different system, and perhaps
> forget to put in the indexes? Could duplicate records be piling up?
>
>>
>> <?
>> set_time_limit(0);
>>
>> require ("include_header.php");
>>
>> $dbh = mysql_pconnect("xxxxx", "xxxxx", "xxxxx");
>>
>> mysql_select_db("alb");
>>
>> $upc_input = $_POST["upc_input"];
>>
>> if ($upc_input) {
>>
>> $pieces = explode("\n", $upc_input);
>>
>> $count = 0;
>>
>>
>> $log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
>> $fp = fopen($log_file, 'a+');
>>
>>
>> while ($upc = $pieces[$count]) {
>> $upc = trim($upc);
>> fwrite($fp, $upc."\n");
>> echo "Removing UPC/ISRC Code: $upc <br />\n";
>> $query = "select id from `albums` where UPPER(upc_id) =
>> '".strtoupper($upc)."'";
>> $result = mysql_query($query);
>>
>> if (mysql_num_rows($result)) {
>> // 10-15-08 - this next line makes the assumption that there can
>> only be
>> // one record for each UPC. Since I don't know enough to argue, I'll
>> // assume this must be true for now. I wonder if the import scripts
>> // enforce uniqueness on the UPC? --LK
>> $row = mysql_fetch_array($result);
>> $id = $row["id"];
>>
>> /* We changed this to status='4' to indicate full removal.
>> Only values of status='3' will be checked when we run
>> our go_live.php cronjob to set albums live on certain dates
>> */
>> $query2 = "update albums SET status='4' where id=".$id;
>> $result2 = mysql_query($query2);
>> $numUpdated = mysql_affected_rows($dbh);
>> if ($numUpdated) {
>> echo "Changed album status for UPC $id <br />\n";
>> } else {
>> // echo "Failed to change album status for UPC $id <br />\n ";
>> //
>> // 10-15-08 - what happens when some staffer puts the same UPC in
>> twice? I don't
>> // want to give a "fail" message. Let's check to see the status.
>> If it equals 4
>> // then the record has already been successfully updated. If not,
>> then we can
>> // give a fail message.
>> $queryStatusCheck = "SELECT status FROM albums WHERE id='$id' ";
>> $resultStatusCheck = mysql_query($queryStatusCheck);
>> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
>> $status = $rowStatusCheck["status"];
>> if ($status == 4) {
>> echo "Album status already updated <br />\n ";
>> } else {
>> echo "Failed to change album status for UPC $upc (album $id)
>> <br />\n ";
>> }
>> }
>>
>> $query3 = "update tracks SET status='4' where album_id=".$id;
>> $result3 = mysql_query($query3);
>> $numUpdated = mysql_affected_rows($dbh);
>> if ($numUpdated) {
>> echo "Changed tracks album status for UPC $id <br /><br />\n\n ";
>> } else {
>> // echo "Failed to change tracks album status for UPC $id <br
>> /><br />\n\n ";
>> //
>> // 10-15-08 - what happens when some staffer puts the same UPC in
>> twice? I don't
>> // want to give a "fail" message. Let's check to see the status.
>> If it equals 4
>> // then the record has already been successfully updated. If not,
>> then we can
>> // give a fail message.
>> $queryStatusCheck = "SELECT status FROM tracks WHERE
>> album_id='$id' ";
>> $resultStatusCheck = mysql_query($queryStatusCheck);
>> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
>> $status = $rowStatusCheck["status"];
>> if ($status == 4) {
>> echo "Track album status already updated <br />\n ";
>> } else {
>> echo "Failed to change track album status for UPC $upc (album
>> $id) <br />\n ";
>> }
>> }
>>
>>
>> } else {
>> $query = "select id from `tracks` where UPPER(isrc_id) =
>> '".strtoupper($upc)."'";
>> $result = mysql_query($query);
>>
>> if (mysql_num_rows($result)) {
>> $row = mysql_fetch_array($result);
>> $id = $row["id"];
>> $query2 = "update tracks SET status='4' where id=".$id;
>> $result2 = mysql_query($query2);
>> echo "Successfully removed and rebuilt Track Id ".$id;
>>
>> } else {
>> echo "Could not find Code as an Album UPC or a Track ISRC";
>> }
>>
>> }
>> echo "<br />";
>> $count++;
>> }
>>
>> fclose($fp);
>>
>>
>> ?>
|
|
|
0
|
|
|
|
Reply
|
lawrence1093 (52)
|
10/21/2008 5:42:27 AM
|
|
Gordon Burditt wrote:
>> Does anything about this script look expensive, in terms of resources or
>> execution time? This script dies after processing about 20 or 25
>> numbers, yet it leaves no errors in the error logs. This is on a server
>> that handles a fairly demanding site. The defaults, in php.ini, have all
>> been cranked fairly high: scripts get 180 seconds to run, and they can
>> have as much as 256 megs of RAM.
>
> You leak result sets, maybe 7 per UPC code. I am not sure how
> intelligent PHP is in reclaiming result sets when the result is
> assigned to the same variable as the last one, and all references
> to the old result set are gone. Use of mysql_free_result() is
> appropriate when you're through with a result set.
>
> Assuming your result sets only return 1 row, 7*25 result sets
> shouldn't come close to running you out of memory. How long does
> it take the script to die? 7*25 queries in 180 seconds seems way
> slow unless you've got a large table with no useful indexes. You
> might, however, be in trouble memory-wise if your queries in fact
> return many rows rather than one per query.
>
> What does SHOW CREATE TABLE indicate for the tables involved?
> Do they have indexes? On what?
>
> What does the query
> select upc_id, count(upc_id) from `albums` group by upc_id;
> return? Does it indicate any duplicates?
> What does the query
> select upc_id, count(upc_id) from `albums` group by upper(upc_id);
> return? Does it indicate any duplicates? How long does it take to run?
> It might be better to ensure the case of the entries in the database,
> (say, with update `albums` set upc_id = UPPER(upc_id); , then make
> sure anything later inserted is converted to upper case first,
> and change WHERE UPPER(upc_id) = ... to WHERE upc_id = ... .
> as it may permit using indexes better.
Thanks for you tips.
The code is going slowly. I added microtime() to the beginning and end
of the script. It would appear that when I input just 3 UPCs, the script
takes 3 tenths of a second to complete.
Start time: 0.34472400 1224561376
You entered 3 UPCs
Removing UPC/ISRC Code: xxxxxxxxxxxx8298815948658844894
albums updated: 1
tracks updated: 0
Removing UPC/ISRC Code: xxxxxxxxxxxx3313429234532731546
albums updated: 1
tracks updated: 0
Removing UPC/ISRC Code: xxxxxxxxxxxx5751339677611914333
albums updated: 1
tracks updated: 0
End time: 0.64086300 1224561455
>> The input for this script is coming from a textarea in a form, and the
>> input could not be more simple, just a bunch of UPC codes, one per line:
>>
>>
>> 881034146533x
>> 881034146533xx
>> 881034146533xxx
>> 881034146533xxxx
>> 881034146533xxxxx
>> 881034146533xxxxxx
>> 881034146533xxxxxxx
>> 881034146533xxxxxxxx
>> 881034146533xxxxxxxxx
>> 881034146533xxxxxxxxxx
>> 881034146533xxxxxxxxxxx
>> 881034146533xxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxxxx
>> 881034146533xxxxxxxxxxxxxxxxxx
>>
>>
>> Given 15 such numbers, the script does fine. But given 200 such numbers,
>> it dies after processing about 10 numbers.
>>
>> Supposedly, the code was written many years ago, back in 2002, and yet,
>> supposedly, the problem with this script (that it dies) only started
>> recently. Supposedly, no changes were made to the script, it simply
>> started to die. (Sadly, the code for the site was only recently put into
>> Subversion, and I only recently joined the project, so I've no way to
>> evaluate these claims.)
>
> Did you recently set up the database on a different system, and perhaps
> forget to put in the indexes? Could duplicate records be piling up?
>
>>
>> <?
>> set_time_limit(0);
>>
>> require ("include_header.php");
>>
>> $dbh = mysql_pconnect("xxxxx", "xxxxx", "xxxxx");
>>
>> mysql_select_db("alb");
>>
>> $upc_input = $_POST["upc_input"];
>>
>> if ($upc_input) {
>>
>> $pieces = explode("\n", $upc_input);
>>
>> $count = 0;
>>
>>
>> $log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
>> $fp = fopen($log_file, 'a+');
>>
>>
>> while ($upc = $pieces[$count]) {
>> $upc = trim($upc);
>> fwrite($fp, $upc."\n");
>> echo "Removing UPC/ISRC Code: $upc <br />\n";
>> $query = "select id from `albums` where UPPER(upc_id) =
>> '".strtoupper($upc)."'";
>> $result = mysql_query($query);
>>
>> if (mysql_num_rows($result)) {
>> // 10-15-08 - this next line makes the assumption that there can
>> only be
>> // one record for each UPC. Since I don't know enough to argue, I'll
>> // assume this must be true for now. I wonder if the import scripts
>> // enforce uniqueness on the UPC? --LK
>> $row = mysql_fetch_array($result);
>> $id = $row["id"];
>>
>> /* We changed this to status='4' to indicate full removal.
>> Only values of status='3' will be checked when we run
>> our go_live.php cronjob to set albums live on certain dates
>> */
>> $query2 = "update albums SET status='4' where id=".$id;
>> $result2 = mysql_query($query2);
>> $numUpdated = mysql_affected_rows($dbh);
>> if ($numUpdated) {
>> echo "Changed album status for UPC $id <br />\n";
>> } else {
>> // echo "Failed to change album status for UPC $id <br />\n ";
>> //
>> // 10-15-08 - what happens when some staffer puts the same UPC in
>> twice? I don't
>> // want to give a "fail" message. Let's check to see the status.
>> If it equals 4
>> // then the record has already been successfully updated. If not,
>> then we can
>> // give a fail message.
>> $queryStatusCheck = "SELECT status FROM albums WHERE id='$id' ";
>> $resultStatusCheck = mysql_query($queryStatusCheck);
>> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
>> $status = $rowStatusCheck["status"];
>> if ($status == 4) {
>> echo "Album status already updated <br />\n ";
>> } else {
>> echo "Failed to change album status for UPC $upc (album $id)
>> <br />\n ";
>> }
>> }
>>
>> $query3 = "update tracks SET status='4' where album_id=".$id;
>> $result3 = mysql_query($query3);
>> $numUpdated = mysql_affected_rows($dbh);
>> if ($numUpdated) {
>> echo "Changed tracks album status for UPC $id <br /><br />\n\n ";
>> } else {
>> // echo "Failed to change tracks album status for UPC $id <br
>> /><br />\n\n ";
>> //
>> // 10-15-08 - what happens when some staffer puts the same UPC in
>> twice? I don't
>> // want to give a "fail" message. Let's check to see the status.
>> If it equals 4
>> // then the record has already been successfully updated. If not,
>> then we can
>> // give a fail message.
>> $queryStatusCheck = "SELECT status FROM tracks WHERE
>> album_id='$id' ";
>> $resultStatusCheck = mysql_query($queryStatusCheck);
>> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
>> $status = $rowStatusCheck["status"];
>> if ($status == 4) {
>> echo "Track album status already updated <br />\n ";
>> } else {
>> echo "Failed to change track album status for UPC $upc (album
>> $id) <br />\n ";
>> }
>> }
>>
>>
>> } else {
>> $query = "select id from `tracks` where UPPER(isrc_id) =
>> '".strtoupper($upc)."'";
>> $result = mysql_query($query);
>>
>> if (mysql_num_rows($result)) {
>> $row = mysql_fetch_array($result);
>> $id = $row["id"];
>> $query2 = "update tracks SET status='4' where id=".$id;
>> $result2 = mysql_query($query2);
>> echo "Successfully removed and rebuilt Track Id ".$id;
>>
>> } else {
>> echo "Could not find Code as an Album UPC or a Track ISRC";
>> }
>>
>> }
>> echo "<br />";
>> $count++;
>> }
>>
>> fclose($fp);
>>
>>
>> ?>
|
|
|
0
|
|
|
|
Reply
|
lawrence1093 (52)
|
10/21/2008 5:49:16 AM
|
|
On Oct 20, 10:28=A0pm, Lawrence Krubner <lawre...@krubner.com> wrote:
> Jerry Stuckle wrote:
> > Gordon Burditt wrote:
> >>> Does anything about this script look expensive, in terms of resources
> >>> or execution time? This script dies after processing about 20 or 25
> >>> numbers, yet it leaves no errors in the error logs. This is on a
> >>> server that handles a fairly demanding site. The defaults, in
> >>> php.ini, have all been cranked fairly high: scripts get 180 seconds
> >>> to run, and they can have as much as 256 megs of RAM.
>
> >> You leak result sets, maybe 7 per UPC code. =A0I am not sure how
> >> intelligent PHP is in reclaiming result sets when the result is
> >> assigned to the same variable as the last one, and all references
> >> to the old result set are gone. =A0Use of mysql_free_result() is
> >> appropriate when you're through with a result set.
>
> > Actually, it's quite a bit more than that. =A0mysql_free_result() relea=
ses
> > a little memory in PHP - but also results in a call to MySQL to free
> > MySQL resources (which can be large).
>
> > PHP will reclaim the result set itself when the references are gone, bu=
t
> > it won't free the space in MySQL - that will only happen when the
> > connection is closed.
>
> >> Assuming your result sets only return 1 row, 7*25 result sets
> >> shouldn't come close to running you out of memory. =A0How long does
> >> it take the script to die? =A07*25 queries in 180 seconds seems way
> >> slow unless you've got a large table with no useful indexes. =A0You
> >> might, however, be in trouble memory-wise if your queries in fact
> >> return many rows rather than one per query.
>
> > Agreed. =A0There is another problem here. =A0But the problem may be tha=
t
> > errors are being neither displayed nor logged. =A0The php.ini file shou=
ld
> > have:
>
> > error_reporting=3DE_ALL
> > log_errors=3Don
>
> > If the user wants the PHP errors in a separate file, he should have:
>
> > error_log=3D"/path/and/file/name"
>
> > Otherwise errors will show up in his Apache error log.
>
> It took me awhile to appreciate how stupid this code is, but I finally
> realized the whole, long, complicated while() loop could be rewritten
> simply:
>
> while ($upc =3D $pieces[$count]) {
> =A0 =A0if ($upc !=3D "") {
> =A0 =A0 =A0 =A0$upc =3D trim($upc);
> =A0 =A0 =A0 =A0fwrite($fp, $upc."\n");
> =A0 =A0 =A0 =A0echo "Removing UPC/ISRC Code: $upc =A0<br />\n";
>
> =A0 =A0 =A0 =A0$query =3D "UPDATE albums SET status=3D4 WHERE UPPER(upc_i=
d) =3D
> '".strtoupper($upc)."'";
> =A0 =A0 =A0 =A0mysql_query($query);
>
> =A0 =A0 =A0 =A0$query =3D "UPDATE tracks SET status=3D4 WHERE UPPER(isrc_=
id) =3D
> '".strtoupper($upc)."'";
> =A0 =A0 =A0 =A0mysql_query($query);
>
> =A0 =A0 =A0echo "<br />";
> =A0 =A0 =A0$count++;
>
> }
>
> Would you recommend calling mysql_free_result() =A0inside the loop?- Hide=
quoted text -
>
> - Show quoted text -
Shouldn't this:
while ($upc =3D $pieces[$count])
be:
while ($upc =3D=3D $pieces[$count])
?
It seems in the former you are assiging the value of $pieces[$count]
to $upc, not comparing as you are in the later, or does PHP do things
differently in the while loop than Perl, C, Javascript, Actionscript
etc?
Bill H
|
|
|
0
|
|
|
|
Reply
|
 bill812 (674)
|
10/21/2008 9:56:23 AM
|
|
Lawrence Krubner wrote:
> Gordon Burditt wrote:
>>> Does anything about this script look expensive, in terms of resources
>>> or execution time? This script dies after processing about 20 or 25
>>> numbers, yet it leaves no errors in the error logs. This is on a
>>> server that handles a fairly demanding site. The defaults, in
>>> php.ini, have all been cranked fairly high: scripts get 180 seconds
>>> to run, and they can have as much as 256 megs of RAM.
>>
>> You leak result sets, maybe 7 per UPC code. I am not sure how
>> intelligent PHP is in reclaiming result sets when the result is
>> assigned to the same variable as the last one, and all references
>> to the old result set are gone. Use of mysql_free_result() is
>> appropriate when you're through with a result set.
>>
>> Assuming your result sets only return 1 row, 7*25 result sets
>> shouldn't come close to running you out of memory. How long does
>> it take the script to die? 7*25 queries in 180 seconds seems way
>> slow unless you've got a large table with no useful indexes. You
>> might, however, be in trouble memory-wise if your queries in fact
>> return many rows rather than one per query.
>>
>> What does SHOW CREATE TABLE indicate for the tables involved?
>> Do they have indexes? On what?
>>
>> What does the query select upc_id, count(upc_id) from `albums`
>> group by upc_id;
>> return? Does it indicate any duplicates?
>> What does the query
>> select upc_id, count(upc_id) from `albums` group by upper(upc_id);
>> return? Does it indicate any duplicates? How long does it take to run?
>> It might be better to ensure the case of the entries in the database,
>> (say, with update `albums` set upc_id = UPPER(upc_id); , then make
>> sure anything later inserted is converted to upper case first,
>> and change WHERE UPPER(upc_id) = ... to WHERE upc_id = ... .
>> as it may permit using indexes better.
>
>
> Do the indexes slow down UPDATEs?
>
> When I attempt to input 200 UPCs, I don't even get a PHP error, instead
> I get this error:
>
> "OK
>
> The server encountered an internal error or misconfiguration and was
> unable to complete your request.
>
> Please contact the server administrator and inform them of the time the
> error occurred, and anything you might have done that may have caused
> the error.
>
> More information about this error may be available in the server error log.
> Apache/2.2.3 (CentOS) Port 443"
>
>
> As I mentioned before, no errors show up in PHP error log.
>
>
> If I run the "top" command while this script is executing, I see stuff
> like this:
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 24689 apache 18 0 14572 6312 2152 S 1 0.3 1:30.32 php
> 25296 apache 15 0 14976 7704 3244 S 1 0.4 4:51.05 php
> 29271 apache 15 0 34068 12m 4052 S 0 0.6 0:00.59 httpd
> 29368 root 15 0 2304 1064 792 R 0 0.1 0:00.38 top
> 1 root 15 0 2040 544 520 S 0 0.0 0:27.96 init
>
>
> Though, the database is actually on a separate server. I suppose I
> should run top on that server too.
>
> Script works if I run this against just 5 or 10 UPCs, though the script
> is insanely slow. This is true even if I run it against UPCs that I
> myself have come up with, and which I'm sure are unique in the database.
>
>
> I've simplified the code down to this :
>
>
>
> if ($upc_input) {
> $startTime = microtime();
> echo "<p>Start time: $startTime </p>";
>
> $upc_input = trim($upc_input);
> $pieces = explode("\n", $upc_input);
>
> $log_file =
> dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
> $fp = fopen($log_file, 'a+');
>
> $numberOfUpcs = count($pieces);
> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>
> for ($i=0; $i < $numberOfUpcs; $i++) {
> $upc = $pieces[$i];
>
> // 10-20-08 see comment above to see why empty string is dangerous -
> returns 10,147 rows!
> if ($upc != "") {
> $upc = trim($upc);
> fwrite($fp, $upc."\n");
> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>
> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
> '".strtoupper($upc)."'";
> $result = mysql_query($query);
> echo "<br /> \n albums updated: " . mysql_affected_rows();
>
> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
> '".strtoupper($upc)."'";
> $result = mysql_query($query);
> echo "<br /> \n tracks updated: " . mysql_affected_rows();
> }
> echo "<br />";
> } // ends for() loop
>
> fclose($fp);
> $endTime = microtime();
> echo "<p>End time: $endTime </p>";
>
> $totalTime = $endTime - $startTime;
> $totalTime = round($totalTime / 1000, 2);
> echo "The total time for this script was $totalTime seconds";
> }
>
>
>
>
>
>
>
>
>
>
>
>
>>
>>
>>> The input for this script is coming from a textarea in a form, and
>>> the input could not be more simple, just a bunch of UPC codes, one
>>> per line:
>>>
>>>
>>> 881034146533x
>>> 881034146533xx
>>> 881034146533xxx
>>> 881034146533xxxx
>>> 881034146533xxxxx
>>> 881034146533xxxxxx
>>> 881034146533xxxxxxx
>>> 881034146533xxxxxxxx
>>> 881034146533xxxxxxxxx
>>> 881034146533xxxxxxxxxx
>>> 881034146533xxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxxxxxxx
>>>
>>>
>>> Given 15 such numbers, the script does fine. But given 200 such
>>> numbers, it dies after processing about 10 numbers.
>>>
>>> Supposedly, the code was written many years ago, back in 2002, and
>>> yet, supposedly, the problem with this script (that it dies) only
>>> started recently. Supposedly, no changes were made to the script, it
>>> simply started to die. (Sadly, the code for the site was only
>>> recently put into Subversion, and I only recently joined the project,
>>> so I've no way to evaluate these claims.)
>>
>> Did you recently set up the database on a different system, and perhaps
>> forget to put in the indexes? Could duplicate records be piling up?
>>
>>>
>>> <?
>>> set_time_limit(0);
>>>
>>> require ("include_header.php");
>>>
>>> $dbh = mysql_pconnect("xxxxx", "xxxxx", "xxxxx");
>>>
>>> mysql_select_db("alb");
>>>
>>> $upc_input = $_POST["upc_input"];
>>>
>>> if ($upc_input) {
>>>
>>> $pieces = explode("\n", $upc_input);
>>>
>>> $count = 0;
>>>
>>>
>>> $log_file =
>>> dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
>>> $fp = fopen($log_file, 'a+');
>>>
>>>
>>> while ($upc = $pieces[$count]) {
>>> $upc = trim($upc);
>>> fwrite($fp, $upc."\n");
>>> echo "Removing UPC/ISRC Code: $upc <br />\n";
>>> $query = "select id from `albums` where UPPER(upc_id) =
>>> '".strtoupper($upc)."'";
>>> $result = mysql_query($query);
>>>
>>> if (mysql_num_rows($result)) {
>>> // 10-15-08 - this next line makes the assumption that there can
>>> only be
>>> // one record for each UPC. Since I don't know enough to argue, I'll
>>> // assume this must be true for now. I wonder if the import scripts
>>> // enforce uniqueness on the UPC? --LK
>>> $row = mysql_fetch_array($result);
>>> $id = $row["id"];
>>>
>>> /* We changed this to status='4' to indicate full removal.
>>> Only values of status='3' will be checked when we run
>>> our go_live.php cronjob to set albums live on certain dates
>>> */
>>> $query2 = "update albums SET status='4' where id=".$id;
>>> $result2 = mysql_query($query2);
>>> $numUpdated = mysql_affected_rows($dbh);
>>> if ($numUpdated) {
>>> echo "Changed album status for UPC $id <br />\n";
>>> } else {
>>> // echo "Failed to change album status for UPC $id <br />\n ";
>>> //
>>> // 10-15-08 - what happens when some staffer puts the same UPC
>>> in twice? I don't
>>> // want to give a "fail" message. Let's check to see the
>>> status. If it equals 4
>>> // then the record has already been successfully updated. If
>>> not, then we can
>>> // give a fail message.
>>> $queryStatusCheck = "SELECT status FROM albums WHERE id='$id' ";
>>> $resultStatusCheck = mysql_query($queryStatusCheck);
>>> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
>>> $status = $rowStatusCheck["status"];
>>> if ($status == 4) {
>>> echo "Album status already updated <br />\n ";
>>> } else {
>>> echo "Failed to change album status for UPC $upc (album $id)
>>> <br />\n ";
>>> }
>>> }
>>>
>>> $query3 = "update tracks SET status='4' where album_id=".$id;
>>> $result3 = mysql_query($query3);
>>> $numUpdated = mysql_affected_rows($dbh);
>>> if ($numUpdated) {
>>> echo "Changed tracks album status for UPC $id <br /><br
>>> />\n\n ";
>>> } else {
>>> // echo "Failed to change tracks album status for UPC $id <br
>>> /><br />\n\n ";
>>> //
>>> // 10-15-08 - what happens when some staffer puts the same UPC
>>> in twice? I don't
>>> // want to give a "fail" message. Let's check to see the
>>> status. If it equals 4
>>> // then the record has already been successfully updated. If
>>> not, then we can
>>> // give a fail message.
>>> $queryStatusCheck = "SELECT status FROM tracks WHERE
>>> album_id='$id' ";
>>> $resultStatusCheck = mysql_query($queryStatusCheck);
>>> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
>>> $status = $rowStatusCheck["status"];
>>> if ($status == 4) {
>>> echo "Track album status already updated <br />\n ";
>>> } else {
>>> echo "Failed to change track album status for UPC $upc (album
>>> $id) <br />\n ";
>>> }
>>> }
>>>
>>>
>>> } else {
>>> $query = "select id from `tracks` where UPPER(isrc_id) =
>>> '".strtoupper($upc)."'";
>>> $result = mysql_query($query);
>>>
>>> if (mysql_num_rows($result)) {
>>> $row = mysql_fetch_array($result);
>>> $id = $row["id"];
>>> $query2 = "update tracks SET status='4' where id=".$id;
>>> $result2 = mysql_query($query2);
>>> echo "Successfully removed and rebuilt Track Id ".$id;
>>> } else {
>>> echo "Could not find Code as an Album UPC or a Track ISRC";
>>> }
>>>
>>> }
>>> echo "<br />";
>>> $count++;
>>> }
>>>
>>> fclose($fp);
>>>
>>>
>>> ?>
>
Yes, indexes slow down updates, but not that much.
Look at the message again. It says the server error log, not the php
error log (which you may or may not even have configured).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
|
|
|
0
|
|
|
|
Reply
|
jstucklex (14362)
|
10/21/2008 11:08:18 AM
|
|
On 21 Oct, 10:56, Bill H <b...@ts1000.us> wrote:
> On Oct 20, 10:28=A0pm, Lawrence Krubner <lawre...@krubner.com> wrote:
>
>
>
>
>
> > Jerry Stuckle wrote:
> > > Gordon Burditt wrote:
> > >>> Does anything about this script look expensive, in terms of resourc=
es
> > >>> or execution time? This script dies after processing about 20 or 25
> > >>> numbers, yet it leaves no errors in the error logs. This is on a
> > >>> server that handles a fairly demanding site. The defaults, in
> > >>> php.ini, have all been cranked fairly high: scripts get 180 seconds
> > >>> to run, and they can have as much as 256 megs of RAM.
>
> > >> You leak result sets, maybe 7 per UPC code. =A0I am not sure how
> > >> intelligent PHP is in reclaiming result sets when the result is
> > >> assigned to the same variable as the last one, and all references
> > >> to the old result set are gone. =A0Use of mysql_free_result() is
> > >> appropriate when you're through with a result set.
>
> > > Actually, it's quite a bit more than that. =A0mysql_free_result() rel=
eases
> > > a little memory in PHP - but also results in a call to MySQL to free
> > > MySQL resources (which can be large).
>
> > > PHP will reclaim the result set itself when the references are gone, =
but
> > > it won't free the space in MySQL - that will only happen when the
> > > connection is closed.
>
> > >> Assuming your result sets only return 1 row, 7*25 result sets
> > >> shouldn't come close to running you out of memory. =A0How long does
> > >> it take the script to die? =A07*25 queries in 180 seconds seems way
> > >> slow unless you've got a large table with no useful indexes. =A0You
> > >> might, however, be in trouble memory-wise if your queries in fact
> > >> return many rows rather than one per query.
>
> > > Agreed. =A0There is another problem here. =A0But the problem may be t=
hat
> > > errors are being neither displayed nor logged. =A0The php.ini file sh=
ould
> > > have:
>
> > > error_reporting=3DE_ALL
> > > log_errors=3Don
>
> > > If the user wants the PHP errors in a separate file, he should have:
>
> > > error_log=3D"/path/and/file/name"
>
> > > Otherwise errors will show up in his Apache error log.
>
> > It took me awhile to appreciate how stupid this code is, but I finally
> > realized the whole, long, complicated while() loop could be rewritten
> > simply:
>
> > while ($upc =3D $pieces[$count]) {
> > =A0 =A0if ($upc !=3D "") {
> > =A0 =A0 =A0 =A0$upc =3D trim($upc);
> > =A0 =A0 =A0 =A0fwrite($fp, $upc."\n");
> > =A0 =A0 =A0 =A0echo "Removing UPC/ISRC Code: $upc =A0<br />\n";
>
> > =A0 =A0 =A0 =A0$query =3D "UPDATE albums SET status=3D4 WHERE UPPER(upc=
_id) =3D
> > '".strtoupper($upc)."'";
> > =A0 =A0 =A0 =A0mysql_query($query);
>
> > =A0 =A0 =A0 =A0$query =3D "UPDATE tracks SET status=3D4 WHERE UPPER(isr=
c_id) =3D
> > '".strtoupper($upc)."'";
> > =A0 =A0 =A0 =A0mysql_query($query);
>
> > =A0 =A0 =A0echo "<br />";
> > =A0 =A0 =A0$count++;
>
> > }
>
> > Would you recommend calling mysql_free_result() =A0inside the loop?- Hi=
de quoted text -
>
> > - Show quoted text -
>
> Shouldn't this:
>
> while ($upc =3D $pieces[$count])
>
> be:
>
> while ($upc =3D=3D $pieces[$count])
>
> ?
>
> It seems in the former you are assiging the value of $pieces[$count]
> to $upc, not comparing as you are in the later, or does PHP do things
> differently in the while loop than Perl, C, Javascript, Actionscript
> etc?
>
> Bill H- Hide quoted text -
This is quite similar to the C useage whereby the result of the
assignment is used as the test.
Having said that:
while ($upc =3D $pieces[$count]) {
if ($upc !=3D "") {
results in the if test always being true since if $upc does equal "",
the while test will also have been false.
|
|
|
0
|
|
|
|
Reply
|
 paul_lautman (2110)
|
10/21/2008 1:46:35 PM
|
|
Captain Paralytic wrote:
> On 21 Oct, 10:56, Bill H <b...@ts1000.us> wrote:
>> On Oct 20, 10:28 pm, Lawrence Krubner <lawre...@krubner.com> wrote:
>>
>>
>>
>>
>>
>>> Jerry Stuckle wrote:
>>>> Gordon Burditt wrote:
>>>>>> Does anything about this script look expensive, in terms of resources
>>>>>> or execution time? This script dies after processing about 20 or 25
>>>>>> numbers, yet it leaves no errors in the error logs. This is on a
>>>>>> server that handles a fairly demanding site. The defaults, in
>>>>>> php.ini, have all been cranked fairly high: scripts get 180 seconds
>>>>>> to run, and they can have as much as 256 megs of RAM.
>>>>> You leak result sets, maybe 7 per UPC code. I am not sure how
>>>>> intelligent PHP is in reclaiming result sets when the result is
>>>>> assigned to the same variable as the last one, and all references
>>>>> to the old result set are gone. Use of mysql_free_result() is
>>>>> appropriate when you're through with a result set.
>>>> Actually, it's quite a bit more than that. mysql_free_result() releases
>>>> a little memory in PHP - but also results in a call to MySQL to free
>>>> MySQL resources (which can be large).
>>>> PHP will reclaim the result set itself when the references are gone, but
>>>> it won't free the space in MySQL - that will only happen when the
>>>> connection is closed.
>>>>> Assuming your result sets only return 1 row, 7*25 result sets
>>>>> shouldn't come close to running you out of memory. How long does
>>>>> it take the script to die? 7*25 queries in 180 seconds seems way
>>>>> slow unless you've got a large table with no useful indexes. You
>>>>> might, however, be in trouble memory-wise if your queries in fact
>>>>> return many rows rather than one per query.
>>>> Agreed. There is another problem here. But the problem may be that
>>>> errors are being neither displayed nor logged. The php.ini file should
>>>> have:
>>>> error_reporting=E_ALL
>>>> log_errors=on
>>>> If the user wants the PHP errors in a separate file, he should have:
>>>> error_log="/path/and/file/name"
>>>> Otherwise errors will show up in his Apache error log.
>>> It took me awhile to appreciate how stupid this code is, but I finally
>>> realized the whole, long, complicated while() loop could be rewritten
>>> simply:
>>> while ($upc = $pieces[$count]) {
>>> if ($upc != "") {
>>> $upc = trim($upc);
>>> fwrite($fp, $upc."\n");
>>> echo "Removing UPC/ISRC Code: $upc <br />\n";
>>> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
>>> '".strtoupper($upc)."'";
>>> mysql_query($query);
>>> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
>>> '".strtoupper($upc)."'";
>>> mysql_query($query);
>>> echo "<br />";
>>> $count++;
>>> }
>>> Would you recommend calling mysql_free_result() inside the loop?- Hide quoted text -
>>> - Show quoted text -
>> Shouldn't this:
>>
>> while ($upc = $pieces[$count])
>>
>> be:
>>
>> while ($upc == $pieces[$count])
>>
>> ?
>>
>> It seems in the former you are assiging the value of $pieces[$count]
>> to $upc, not comparing as you are in the later, or does PHP do things
>> differently in the while loop than Perl, C, Javascript, Actionscript
>> etc?
>>
>> Bill H- Hide quoted text -
> This is quite similar to the C useage whereby the result of the
> assignment is used as the test.
>
> Having said that:
> while ($upc = $pieces[$count]) {
> if ($upc != "") {
>
> results in the if test always being true since if $upc does equal "",
> the while test will also have been false.
Thanks for pointing that out. That also means that an empty string could
stop the while loop, which by itself would be a bug. Therefore, I'm
going to change this to a for() loop.
|
|
|
0
|
|
|
|
Reply
|
lawrence1093 (52)
|
10/21/2008 3:49:32 PM
|
|
>> What does the query
>> select upc_id, count(upc_id) from `albums` group by upper(upc_id);
>> return? Does it indicate any duplicates? How long does it take to run?
>> It might be better to ensure the case of the entries in the database,
>> (say, with update `albums` set upc_id = UPPER(upc_id); , then make
>> sure anything later inserted is converted to upper case first,
>> and change WHERE UPPER(upc_id) = ... to WHERE upc_id = ... .
>> as it may permit using indexes better.
>
>
>Do the indexes slow down UPDATEs?
Slightly.
>When I attempt to input 200 UPCs, I don't even get a PHP error, instead
>I get this error:
>
>"OK
>
>The server encountered an internal error or misconfiguration and was
>unable to complete your request.
This kind of error usually is caused by a CGI or PHP failing to
output headers at all (e.g. core dump), or putting out malformed
headers (PHP is pretty good about putting out real headers before
other stuff, but in a CGI a warning/error message before headers
can do this). It may also be caused by permission problems on a
CGI (either too restrictive to execute, or too generous with write
permission to be safe to execute) but this is not usually applicable
to PHP.
>Please contact the server administrator and inform them of the time the
>error occurred, and anything you might have done that may have caused
>the error.
>
>More information about this error may be available in the server error log.
>Apache/2.2.3 (CentOS) Port 443"
>
>
>As I mentioned before, no errors show up in PHP error log.
What error log did the error message mention? Not PHP.
> // 10-20-08 see comment above to see why empty string is dangerous
>- returns 10,147 rows!
*WHY* are those 10,147 rows even in the database?
> if ($upc != "") {
> $upc = trim($upc);
> fwrite($fp, $upc."\n");
> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>
> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
>'".strtoupper($upc)."'";
> $result = mysql_query($query);
> echo "<br /> \n albums updated: " . mysql_affected_rows();
>
> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
>'".strtoupper($upc)."'";
> $result = mysql_query($query);
> echo "<br /> \n tracks updated: " . mysql_affected_rows();
> }
> echo "<br />";
> } // ends for() loop
>
> fclose($fp);
> $endTime = microtime();
> echo "<p>End time: $endTime </p>";
>
> $totalTime = $endTime - $startTime;
> $totalTime = round($totalTime / 1000, 2);
> echo "The total time for this script was $totalTime seconds";
>}
>
>
>
>
>
>
>
>
>
>
>
>
>>
>>
>>> The input for this script is coming from a textarea in a form, and the
>>> input could not be more simple, just a bunch of UPC codes, one per line:
>>>
>>>
>>> 881034146533x
>>> 881034146533xx
>>> 881034146533xxx
>>> 881034146533xxxx
>>> 881034146533xxxxx
>>> 881034146533xxxxxx
>>> 881034146533xxxxxxx
>>> 881034146533xxxxxxxx
>>> 881034146533xxxxxxxxx
>>> 881034146533xxxxxxxxxx
>>> 881034146533xxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxxxxxx
>>> 881034146533xxxxxxxxxxxxxxxxxx
>>>
>>>
>>> Given 15 such numbers, the script does fine. But given 200 such numbers,
>>> it dies after processing about 10 numbers.
>>>
>>> Supposedly, the code was written many years ago, back in 2002, and yet,
>>> supposedly, the problem with this script (that it dies) only started
>>> recently. Supposedly, no changes were made to the script, it simply
>>> started to die. (Sadly, the code for the site was only recently put into
>>> Subversion, and I only recently joined the project, so I've no way to
>>> evaluate these claims.)
>>
>> Did you recently set up the database on a different system, and perhaps
>> forget to put in the indexes? Could duplicate records be piling up?
>>
>>>
>>> <?
>>> set_time_limit(0);
>>>
>>> require ("include_header.php");
>>>
>>> $dbh = mysql_pconnect("xxxxx", "xxxxx", "xxxxx");
>>>
>>> mysql_select_db("alb");
>>>
>>> $upc_input = $_POST["upc_input"];
>>>
>>> if ($upc_input) {
>>>
>>> $pieces = explode("\n", $upc_input);
>>>
>>> $count = 0;
>>>
>>>
>>> $log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
>>> $fp = fopen($log_file, 'a+');
>>>
>>>
>>> while ($upc = $pieces[$count]) {
>>> $upc = trim($upc);
>>> fwrite($fp, $upc."\n");
>>> echo "Removing UPC/ISRC Code: $upc <br />\n";
>>> $query = "select id from `albums` where UPPER(upc_id) =
>>> '".strtoupper($upc)."'";
>>> $result = mysql_query($query);
>>>
>>> if (mysql_num_rows($result)) {
>>> // 10-15-08 - this next line makes the assumption that there can
>>> only be
>>> // one record for each UPC. Since I don't know enough to argue, I'll
>>> // assume this must be true for now. I wonder if the import scripts
>>> // enforce uniqueness on the UPC? --LK
>>> $row = mysql_fetch_array($result);
>>> $id = $row["id"];
>>>
>>> /* We changed this to status='4' to indicate full removal.
>>> Only values of status='3' will be checked when we run
>>> our go_live.php cronjob to set albums live on certain dates
>>> */
>>> $query2 = "update albums SET status='4' where id=".$id;
>>> $result2 = mysql_query($query2);
>>> $numUpdated = mysql_affected_rows($dbh);
>>> if ($numUpdated) {
>>> echo "Changed album status for UPC $id <br />\n";
>>> } else {
>>> // echo "Failed to change album status for UPC $id <br />\n ";
>>> //
>>> // 10-15-08 - what happens when some staffer puts the same UPC in
>>> twice? I don't
>>> // want to give a "fail" message. Let's check to see the status.
>>> If it equals 4
>>> // then the record has already been successfully updated. If not,
>>> then we can
>>> // give a fail message.
>>> $queryStatusCheck = "SELECT status FROM albums WHERE id='$id' ";
>>> $resultStatusCheck = mysql_query($queryStatusCheck);
>>> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
>>> $status = $rowStatusCheck["status"];
>>> if ($status == 4) {
>>> echo "Album status already updated <br />\n ";
>>> } else {
>>> echo "Failed to change album status for UPC $upc (album $id)
>>> <br />\n ";
>>> }
>>> }
>>>
>>> $query3 = "update tracks SET status='4' where album_id=".$id;
>>> $result3 = mysql_query($query3);
>>> $numUpdated = mysql_affected_rows($dbh);
>>> if ($numUpdated) {
>>> echo "Changed tracks album status for UPC $id <br /><br />\n\n ";
>>> } else {
>>> // echo "Failed to change tracks album status for UPC $id <br
>>> /><br />\n\n ";
>>> //
>>> // 10-15-08 - what happens when some staffer puts the same UPC in
>>> twice? I don't
>>> // want to give a "fail" message. Let's check to see the status.
>>> If it equals 4
>>> // then the record has already been successfully updated. If not,
>>> then we can
>>> // give a fail message.
>>> $queryStatusCheck = "SELECT status FROM tracks WHERE
>>> album_id='$id' ";
>>> $resultStatusCheck = mysql_query($queryStatusCheck);
>>> $rowStatusCheck = mysql_fetch_assoc($resultStatusCheck);
>>> $status = $rowStatusCheck["status"];
>>> if ($status == 4) {
>>> echo "Track album status already updated <br />\n ";
>>> } else {
>>> echo "Failed to change track album status for UPC $upc (album
>>> $id) <br />\n ";
>>> }
>>> }
>>>
>>>
>>> } else {
>>> $query = "select id from `tracks` where UPPER(isrc_id) =
>>> '".strtoupper($upc)."'";
>>> $result = mysql_query($query);
>>>
>>> if (mysql_num_rows($result)) {
>>> $row = mysql_fetch_array($result);
>>> $id = $row["id"];
>>> $query2 = "update tracks SET status='4' where id=".$id;
>>> $result2 = mysql_query($query2);
>>> echo "Successfully removed and rebuilt Track Id ".$id;
>>>
>>> } else {
>>> echo "Could not find Code as an Album UPC or a Track ISRC";
>>> }
>>>
>>> }
>>> echo "<br />";
>>> $count++;
>>> }
>>>
>>> fclose($fp);
>>>
>>>
>>> ?>
|
|
|
0
|
|
|
|
Reply
|
 gordonb.k1qz1 (1)
|
10/21/2008 3:58:41 PM
|
|
Jerry Stuckle wrote:
> Lawrence Krubner wrote:
>> When I attempt to input 200 UPCs, I don't even get a PHP error,
>> instead I get this error:
>>
>> "OK
>>
>> The server encountered an internal error or misconfiguration and was
>> unable to complete your request.
>>
>> Please contact the server administrator and inform them of the time
>> the error occurred, and anything you might have done that may have
>> caused the error.
>>
>> More information about this error may be available in the server error
>> log.
>> Apache/2.2.3 (CentOS) Port 443"
>>
>>
>> As I mentioned before, no errors show up in PHP error log.
>>
>>
>> If I run the "top" command while this script is executing, I see stuff
>> like this:
>>
>> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>> 24689 apache 18 0 14572 6312 2152 S 1 0.3 1:30.32 php
>> 25296 apache 15 0 14976 7704 3244 S 1 0.4 4:51.05 php
>> 29271 apache 15 0 34068 12m 4052 S 0 0.6 0:00.59 httpd
>> 29368 root 15 0 2304 1064 792 R 0 0.1 0:00.38 top
>> 1 root 15 0 2040 544 520 S 0 0.0 0:27.96 init
>>
>>
>> Though, the database is actually on a separate server. I suppose I
>> should run top on that server too.
>>
>> Script works if I run this against just 5 or 10 UPCs, though the
>> script is insanely slow. This is true even if I run it against UPCs
>> that I myself have come up with, and which I'm sure are unique in the
>> database.
>>
>>
>> I've simplified the code down to this :
>>
>>
>>
>> if ($upc_input) {
>> $startTime = microtime();
>> echo "<p>Start time: $startTime </p>";
>>
>> $upc_input = trim($upc_input);
>> $pieces = explode("\n", $upc_input);
>>
>> $log_file =
>> dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
>> $fp = fopen($log_file, 'a+');
>>
>> $numberOfUpcs = count($pieces);
>> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>>
>> for ($i=0; $i < $numberOfUpcs; $i++) {
>> $upc = $pieces[$i];
>>
>> // 10-20-08 see comment above to see why empty string is dangerous
>> - returns 10,147 rows!
>> if ($upc != "") {
>> $upc = trim($upc);
>> fwrite($fp, $upc."\n");
>> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>>
>> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
>> '".strtoupper($upc)."'";
>> $result = mysql_query($query);
>> echo "<br /> \n albums updated: " . mysql_affected_rows();
>>
>> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
>> '".strtoupper($upc)."'";
>> $result = mysql_query($query);
>> echo "<br /> \n tracks updated: " . mysql_affected_rows();
>> }
>> echo "<br />";
>> } // ends for() loop
>>
>> fclose($fp);
>> $endTime = microtime();
>> echo "<p>End time: $endTime </p>";
>>
>> $totalTime = $endTime - $startTime;
>> $totalTime = round($totalTime / 1000, 2);
>> echo "The total time for this script was $totalTime seconds";
>> }
>>
>>
>>
>>
>
> Look at the message again. It says the server error log, not the php
> error log (which you may or may not even have configured).
Good point. When I try to run 200 UPCs through this script, this appears
in the server error log:
[Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
"/var/www/cgi-bin/php4.fcgi" (pid 3807) termination signaled
[Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
"/var/www/cgi-bin/php4.fcgi" (pid 3807) terminated by calling exit with
status '0'
[Tue Oct 21 08:16:21 2008] [warn] FastCGI: scheduled the restart of the
last (dynamic) server "/var/www/cgi-bin/php4.fcgi" process: reached
dynamicMaxClassProcs (10)
[Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
"/var/www/cgi-bin/php4.fcgi" restarted (pid 3935)
PID 3935 is simply a PHP cgi process:
apache 3935 0.0 0.1 11260 2768 ? Ss 08:16 0:00
/var/www/cgi-bin/php
Anyone know what this about? I know folks use FastCGI with Ruby On
Rails, but I have not run into it before for PHP.
|
|
|
0
|
|
|
|
Reply
|
lawrence1093 (52)
|
10/21/2008 5:12:08 PM
|
|
On Oct 21, 1:12 pm, Lawrence Krubner <lawre...@krubner.com> wrote:
> Jerry Stuckle wrote:
> > Lawrence Krubner wrote:
> >> When I attempt to input 200 UPCs, I don't even get a PHP error,
> >> instead I get this error:
>
> >> "OK
>
> >> The server encountered an internal error or misconfiguration and was
> >> unable to complete your request.
>
> >> Please contact the server administrator and inform them of the time
> >> the error occurred, and anything you might have done that may have
> >> caused the error.
>
> >> More information about this error may be available in the server error
> >> log.
> >> Apache/2.2.3 (CentOS) Port 443"
>
> >> As I mentioned before, no errors show up in PHP error log.
>
> >> If I run the "top" command while this script is executing, I see stuff
> >> like this:
>
> >> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> >> 24689 apache 18 0 14572 6312 2152 S 1 0.3 1:30.32 php
> >> 25296 apache 15 0 14976 7704 3244 S 1 0.4 4:51.05 php
> >> 29271 apache 15 0 34068 12m 4052 S 0 0.6 0:00.59 httpd
> >> 29368 root 15 0 2304 1064 792 R 0 0.1 0:00.38 top
> >> 1 root 15 0 2040 544 520 S 0 0.0 0:27.96 init
>
> >> Though, the database is actually on a separate server. I suppose I
> >> should run top on that server too.
>
> >> Script works if I run this against just 5 or 10 UPCs, though the
> >> script is insanely slow. This is true even if I run it against UPCs
> >> that I myself have come up with, and which I'm sure are unique in the
> >> database.
>
> >> I've simplified the code down to this :
>
> >> if ($upc_input) {
> >> $startTime = microtime();
> >> echo "<p>Start time: $startTime </p>";
>
> >> $upc_input = trim($upc_input);
> >> $pieces = explode("\n", $upc_input);
>
> >> $log_file =
> >> dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
> >> $fp = fopen($log_file, 'a+');
>
> >> $numberOfUpcs = count($pieces);
> >> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>
> >> for ($i=0; $i < $numberOfUpcs; $i++) {
> >> $upc = $pieces[$i];
>
> >> // 10-20-08 see comment above to see why empty string is dangerous
> >> - returns 10,147 rows!
> >> if ($upc != "") {
> >> $upc = trim($upc);
> >> fwrite($fp, $upc."\n");
> >> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>
> >> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
> >> '".strtoupper($upc)."'";
> >> $result = mysql_query($query);
> >> echo "<br /> \n albums updated: " . mysql_affected_rows();
>
> >> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
> >> '".strtoupper($upc)."'";
> >> $result = mysql_query($query);
> >> echo "<br /> \n tracks updated: " . mysql_affected_rows();
> >> }
> >> echo "<br />";
> >> } // ends for() loop
>
> >> fclose($fp);
> >> $endTime = microtime();
> >> echo "<p>End time: $endTime </p>";
>
> >> $totalTime = $endTime - $startTime;
> >> $totalTime = round($totalTime / 1000, 2);
> >> echo "The total time for this script was $totalTime seconds";
> >> }
>
> > Look at the message again. It says the server error log, not the php
> > error log (which you may or may not even have configured).
>
> Good point. When I try to run 200 UPCs through this script, this appears
> in the server error log:
>
> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> "/var/www/cgi-bin/php4.fcgi" (pid 3807) termination signaled
> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> "/var/www/cgi-bin/php4.fcgi" (pid 3807) terminated by calling exit with
> status '0'
> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: scheduled the restart of the
> last (dynamic) server "/var/www/cgi-bin/php4.fcgi" process: reached
> dynamicMaxClassProcs (10)
> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> "/var/www/cgi-bin/php4.fcgi" restarted (pid 3935)
>
> PID 3935 is simply a PHP cgi process:
>
> apache 3935 0.0 0.1 11260 2768 ? Ss 08:16 0:00
> /var/www/cgi-bin/php
>
> Anyone know what this about? I know folks use FastCGI with Ruby On
> Rails, but I have not run into it before for PHP.
Also, the database is on another, dedicated, server. When I ssh to the
server that has the database, and if I run the "top" command, I notice
that mysqld is taking 100% of the CPU. Does anyone know if this is
normal or healthy? I realize it is a dedicated server, but 100% seems
like a lot.
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12996 mysql 25 0 588m 262m 3360 R 100 6.5 0:43.48 mysqld
2639 root 16 0 8556 6108 1620 S 0 0.1 2:41.70 hald
13047 root 16 0 2156 984 764 R 0 0.0 0:00.02 top
1 root 16 0 3344 548 472 S 0 0.0 0:06.44 init
2 root RT 0 0 0 0 S 0 0.0 0:00.52 migration/
0
|
|
|
0
|
|
|
|
Reply
|
 lkrubner (905)
|
10/22/2008 3:21:36 AM
|
|
On Oct 21, 1:12 pm, Lawrence Krubner <lawre...@krubner.com> wrote:
> Jerry Stuckle wrote:
> > Lawrence Krubner wrote:
> >> When I attempt to input 200 UPCs, I don't even get a PHP error,
> >> instead I get this error:
>
> >> "OK
>
> >> The server encountered an internal error or misconfiguration and was
> >> unable to complete your request.
>
> >> Please contact the server administrator and inform them of the time
> >> the error occurred, and anything you might have done that may have
> >> caused the error.
>
> >> More information about this error may be available in the server error
> >> log.
> >> Apache/2.2.3 (CentOS) Port 443"
>
> >> As I mentioned before, no errors show up in PHP error log.
>
> >> If I run the "top" command while this script is executing, I see stuff
> >> like this:
>
> >> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> >> 24689 apache 18 0 14572 6312 2152 S 1 0.3 1:30.32 php
> >> 25296 apache 15 0 14976 7704 3244 S 1 0.4 4:51.05 php
> >> 29271 apache 15 0 34068 12m 4052 S 0 0.6 0:00.59 httpd
> >> 29368 root 15 0 2304 1064 792 R 0 0.1 0:00.38 top
> >> 1 root 15 0 2040 544 520 S 0 0.0 0:27.96 init
>
> >> Though, the database is actually on a separate server. I suppose I
> >> should run top on that server too.
>
> >> Script works if I run this against just 5 or 10 UPCs, though the
> >> script is insanely slow. This is true even if I run it against UPCs
> >> that I myself have come up with, and which I'm sure are unique in the
> >> database.
>
> >> I've simplified the code down to this :
>
> >> if ($upc_input) {
> >> $startTime = microtime();
> >> echo "<p>Start time: $startTime </p>";
>
> >> $upc_input = trim($upc_input);
> >> $pieces = explode("\n", $upc_input);
>
> >> $log_file =
> >> dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
> >> $fp = fopen($log_file, 'a+');
>
> >> $numberOfUpcs = count($pieces);
> >> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>
> >> for ($i=0; $i < $numberOfUpcs; $i++) {
> >> $upc = $pieces[$i];
>
> >> // 10-20-08 see comment above to see why empty string is dangerous
> >> - returns 10,147 rows!
> >> if ($upc != "") {
> >> $upc = trim($upc);
> >> fwrite($fp, $upc."\n");
> >> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>
> >> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
> >> '".strtoupper($upc)."'";
> >> $result = mysql_query($query);
> >> echo "<br /> \n albums updated: " . mysql_affected_rows();
>
> >> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
> >> '".strtoupper($upc)."'";
> >> $result = mysql_query($query);
> >> echo "<br /> \n tracks updated: " . mysql_affected_rows();
> >> }
> >> echo "<br />";
> >> } // ends for() loop
>
> >> fclose($fp);
> >> $endTime = microtime();
> >> echo "<p>End time: $endTime </p>";
>
> >> $totalTime = $endTime - $startTime;
> >> $totalTime = round($totalTime / 1000, 2);
> >> echo "The total time for this script was $totalTime seconds";
> >> }
>
> > Look at the message again. It says the server error log, not the php
> > error log (which you may or may not even have configured).
>
> Good point. When I try to run 200 UPCs through this script, this appears
> in the server error log:
>
> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> "/var/www/cgi-bin/php4.fcgi" (pid 3807) termination signaled
> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> "/var/www/cgi-bin/php4.fcgi" (pid 3807) terminated by calling exit with
> status '0'
> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: scheduled the restart of the
> last (dynamic) server "/var/www/cgi-bin/php4.fcgi" process: reached
> dynamicMaxClassProcs (10)
> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> "/var/www/cgi-bin/php4.fcgi" restarted (pid 3935)
>
> PID 3935 is simply a PHP cgi process:
>
> apache 3935 0.0 0.1 11260 2768 ? Ss 08:16 0:00
> /var/www/cgi-bin/php
>
> Anyone know what this about? I know folks use FastCGI with Ruby On
> Rails, but I have not run into it before for PHP.
I keep tweaking the code, trying to find the line that kills the
script. You can see what I've got below. Oddly enough, if I comment
out the two calls to mysql_query, then the script works just fine. Yet
if I uncomment those lines, nothing gets written to the log file. Its
as if the script dies on the first query.
One "improvement" I've made to the script (I'm being sarcastic) is
that it no longer handles even one UPC. When I started trying to fix
the script, it could handle 20 to 25 UPCs, but died when given 100.
Now it dies when it is given even one.
if ($upc_input) {
$startTime = microtime();
echo "<p>Start time: $startTime </p>";
// 10-20-08 - this next line might be killing the script. When I run
// this query:
//
// SELECT upc_id, count( upc_id ) AS fullCount
// FROM `albums`
// GROUP BY upc_id
// ORDER BY fullCount DESC
//
// I realize there are many UPCs with lots of repeats. The worst is
the empty
// string, which returns 10,147 rows! I'm going to add in trim here,
and below
// I'm going to screen out the empty strings.
$upc_input = trim($upc_input);
$pieces = explode("\n", $upc_input);
/*
* 10-15-08 - I've been asked to figure out why this script isn't
working.
* I was curious to see what this next line wrote out to, so I added
this line
* after it:
*
* echo $log_file;
*
* This gave me this path to look at:
*
* /var/www/cgi-bin/logs/album_removals.log
*
* Turns out that file didn't exist, nor did the directory "logs".
* I created the the logs directory and chmoded it 0777. Then I
* ran the script again using some dummy data that I'd already
* added to the database. Then I looked at the log. It turned out
* that it is a very simple list of the UPCs that I typed into the
* form:
*
* 881034146533x
* 881034146533xx
* 881034146533xxx
* 881034146533xxxx
* 881034146533xxxxx
* 881034146533xxxxxx
* 881034146533xxxxxxx
* 881034146533xxxxxxxx
* 881034146533xxxxxxxxx
* 881034146533xxxxxxxxxx
* 881034146533xxxxxxxxxxx
* 881034146533xxxxxxxxxxxx
* 881034146533xxxxxxxxxxxxx
* 881034146533xxxxxxxxxxxxxx
* 881034146533xxxxxxxxxxxxxxx
* 881034146533xxxxxxxxxxxxxxxx
* 881034146533xxxxxxxxxxxxxxxxx
* 881034146533xxxxxxxxxxxxxxxxxx
*
*/
$log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/
album_removals.log';
$fp = fopen($log_file, 'a+');
$numberOfUpcs = count($pieces);
echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
for ($i=0; $i < $numberOfUpcs; $i++) {
$upc = $pieces[$i];
// 10-20-08 see comment above to see why empty string is dangerous
- returns 10,147 rows!
if ($upc != "") {
$upc = trim($upc);
fwrite($fp, $upc."\n");
echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
$query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
'".strtoupper($upc)."'";
fwrite($fp, $query."\n");
$result = mysql_query($query);
echo "<br /> \n albums updated: " . mysql_affected_rows();
$query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
'".strtoupper($upc)."'";
fwrite($fp, $query."\n\n");
$result = mysql_query($query);
echo "<br /> \n tracks updated: " . mysql_affected_rows();
}
echo "<br />";
} // ends for() loop
fclose($fp);
$endTime = microtime();
echo "<p>End time: $endTime </p>";
$totalTime = $endTime - $startTime;
echo "The total time for this script was $totalTime seconds";
}
|
|
|
0
|
|
|
|
Reply
|
lkrubner (905)
|
10/22/2008 3:37:13 AM
|
|
On Oct 21, 11:37 pm, lawrence k <lkrub...@geocities.com> wrote:
> On Oct 21, 1:12 pm, Lawrence Krubner <lawre...@krubner.com> wrote:
>
>
>
> > Jerry Stuckle wrote:
> > > Lawrence Krubner wrote:
> > >> When I attempt to input 200 UPCs, I don't even get a PHP error,
> > >> instead I get this error:
>
> > >> "OK
>
> > >> The server encountered an internal error or misconfiguration and was
> > >> unable to complete your request.
>
> > >> Please contact the server administrator and inform them of the time
> > >> the error occurred, and anything you might have done that may have
> > >> caused the error.
>
> > >> More information about this error may be available in the server error
> > >> log.
> > >> Apache/2.2.3 (CentOS) Port 443"
>
> > >> As I mentioned before, no errors show up in PHP error log.
>
> > >> If I run the "top" command while this script is executing, I see stuff
> > >> like this:
>
> > >> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> > >> 24689 apache 18 0 14572 6312 2152 S 1 0.3 1:30.32 php
> > >> 25296 apache 15 0 14976 7704 3244 S 1 0.4 4:51.05 php
> > >> 29271 apache 15 0 34068 12m 4052 S 0 0.6 0:00.59 httpd
> > >> 29368 root 15 0 2304 1064 792 R 0 0.1 0:00.38 top
> > >> 1 root 15 0 2040 544 520 S 0 0.0 0:27.96 init
>
> > >> Though, the database is actually on a separate server. I suppose I
> > >> should run top on that server too.
>
> > >> Script works if I run this against just 5 or 10 UPCs, though the
> > >> script is insanely slow. This is true even if I run it against UPCs
> > >> that I myself have come up with, and which I'm sure are unique in the
> > >> database.
>
> > >> I've simplified the code down to this :
>
> > >> if ($upc_input) {
> > >> $startTime = microtime();
> > >> echo "<p>Start time: $startTime </p>";
>
> > >> $upc_input = trim($upc_input);
> > >> $pieces = explode("\n", $upc_input);
>
> > >> $log_file =
> > >> dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
> > >> $fp = fopen($log_file, 'a+');
>
> > >> $numberOfUpcs = count($pieces);
> > >> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>
> > >> for ($i=0; $i < $numberOfUpcs; $i++) {
> > >> $upc = $pieces[$i];
>
> > >> // 10-20-08 see comment above to see why empty string is dangerous
> > >> - returns 10,147 rows!
> > >> if ($upc != "") {
> > >> $upc = trim($upc);
> > >> fwrite($fp, $upc."\n");
> > >> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>
> > >> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
> > >> '".strtoupper($upc)."'";
> > >> $result = mysql_query($query);
> > >> echo "<br /> \n albums updated: " . mysql_affected_rows();
>
> > >> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
> > >> '".strtoupper($upc)."'";
> > >> $result = mysql_query($query);
> > >> echo "<br /> \n tracks updated: " . mysql_affected_rows();
> > >> }
> > >> echo "<br />";
> > >> } // ends for() loop
>
> > >> fclose($fp);
> > >> $endTime = microtime();
> > >> echo "<p>End time: $endTime </p>";
>
> > >> $totalTime = $endTime - $startTime;
> > >> $totalTime = round($totalTime / 1000, 2);
> > >> echo "The total time for this script was $totalTime seconds";
> > >> }
>
> > > Look at the message again. It says the server error log, not the php
> > > error log (which you may or may not even have configured).
>
> > Good point. When I try to run 200 UPCs through this script, this appears
> > in the server error log:
>
> > [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> > "/var/www/cgi-bin/php4.fcgi" (pid 3807) termination signaled
> > [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> > "/var/www/cgi-bin/php4.fcgi" (pid 3807) terminated by calling exit with
> > status '0'
> > [Tue Oct 21 08:16:21 2008] [warn] FastCGI: scheduled the restart of the
> > last (dynamic) server "/var/www/cgi-bin/php4.fcgi" process: reached
> > dynamicMaxClassProcs (10)
> > [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> > "/var/www/cgi-bin/php4.fcgi" restarted (pid 3935)
>
> > PID 3935 is simply a PHP cgi process:
>
> > apache 3935 0.0 0.1 11260 2768 ? Ss 08:16 0:00
> > /var/www/cgi-bin/php
>
> > Anyone know what this about? I know folks use FastCGI with Ruby On
> > Rails, but I have not run into it before for PHP.
>
> I keep tweaking the code, trying to find the line that kills the
> script. You can see what I've got below. Oddly enough, if I comment
> out the two calls to mysql_query, then the script works just fine. Yet
> if I uncomment those lines, nothing gets written to the log file. Its
> as if the script dies on the first query.
>
> One "improvement" I've made to the script (I'm being sarcastic) is
> that it no longer handles even one UPC. When I started trying to fix
> the script, it could handle 20 to 25 UPCs, but died when given 100.
> Now it dies when it is given even one.
>
> if ($upc_input) {
> $startTime = microtime();
> echo "<p>Start time: $startTime </p>";
> // 10-20-08 - this next line might be killing the script. When I run
> // this query:
> //
> // SELECT upc_id, count( upc_id ) AS fullCount
> // FROM `albums`
> // GROUP BY upc_id
> // ORDER BY fullCount DESC
> //
> // I realize there are many UPCs with lots of repeats. The worst is
> the empty
> // string, which returns 10,147 rows! I'm going to add in trim here,
> and below
> // I'm going to screen out the empty strings.
> $upc_input = trim($upc_input);
> $pieces = explode("\n", $upc_input);
>
> /*
> * 10-15-08 - I've been asked to figure out why this script isn't
> working.
> * I was curious to see what this next line wrote out to, so I added
> this line
> * after it:
> *
> * echo $log_file;
> *
> * This gave me this path to look at:
> *
> * /var/www/cgi-bin/logs/album_removals.log
> *
> * Turns out that file didn't exist, nor did the directory "logs".
> * I created the the logs directory and chmoded it 0777. Then I
> * ran the script again using some dummy data that I'd already
> * added to the database. Then I looked at the log. It turned out
> * that it is a very simple list of the UPCs that I typed into the
> * form:
> *
> * 881034146533x
> * 881034146533xx
> * 881034146533xxx
> * 881034146533xxxx
> * 881034146533xxxxx
> * 881034146533xxxxxx
> * 881034146533xxxxxxx
> * 881034146533xxxxxxxx
> * 881034146533xxxxxxxxx
> * 881034146533xxxxxxxxxx
> * 881034146533xxxxxxxxxxx
> * 881034146533xxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxxxxxxx
> *
> */
> $log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/
> album_removals.log';
> $fp = fopen($log_file, 'a+');
>
> $numberOfUpcs = count($pieces);
> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>
> for ($i=0; $i < $numberOfUpcs; $i++) {
> $upc = $pieces[$i];
>
> // 10-20-08 see comment above to see why empty string is dangerous
> - returns 10,147 rows!
> if ($upc != "") {
> $upc = trim($upc);
> fwrite($fp, $upc."\n");
> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>
> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
> '".strtoupper($upc)."'";
> fwrite($fp, $query."\n");
> $result = mysql_query($query);
> echo "<br /> \n albums updated: " . mysql_affected_rows();
>
> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
> '".strtoupper($upc)."'";
> fwrite($fp, $query."\n\n");
> $result = mysql_query($query);
> echo "<br /> \n tracks updated: " . mysql_affected_rows();
> }
> echo "<br />";
> } // ends for() loop
>
> fclose($fp);
> $endTime = microtime();
> echo "<p>End time: $endTime </p>";
>
> $totalTime = $endTime - $startTime;
> echo "The total time for this script was $totalTime seconds";
>
> }
I took out all the UPPER and strtoupper stuff, and I was able to get
one UPC through this script. But it took .13 seconds, which seems a
little high for running just a single UPDATE query.
Start time: 0.15604500 1224640672
You entered 1 UPCs
Removing UPC/ISRC Code: xxxxxxxxxxxx7737127416741419436
albums updated: 1
tracks updated: 1
End time: 0.02507700 1224640725
The total time for this script was -0.130968 seconds
|
|
|
0
|
|
|
|
Reply
|
lkrubner (905)
|
10/22/2008 3:49:15 AM
|
|
On Oct 21, 11:37 pm, lawrence k <lkrub...@geocities.com> wrote:
> On Oct 21, 1:12 pm, Lawrence Krubner <lawre...@krubner.com> wrote:
>
>
>
> > Jerry Stuckle wrote:
> > > Lawrence Krubner wrote:
> > >> When I attempt to input 200 UPCs, I don't even get a PHP error,
> > >> instead I get this error:
>
> > >> "OK
>
> > >> The server encountered an internal error or misconfiguration and was
> > >> unable to complete your request.
>
> > >> Please contact the server administrator and inform them of the time
> > >> the error occurred, and anything you might have done that may have
> > >> caused the error.
>
> > >> More information about this error may be available in the server error
> > >> log.
> > >> Apache/2.2.3 (CentOS) Port 443"
>
> > >> As I mentioned before, no errors show up in PHP error log.
>
> > >> If I run the "top" command while this script is executing, I see stuff
> > >> like this:
>
> > >> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> > >> 24689 apache 18 0 14572 6312 2152 S 1 0.3 1:30.32 php
> > >> 25296 apache 15 0 14976 7704 3244 S 1 0.4 4:51.05 php
> > >> 29271 apache 15 0 34068 12m 4052 S 0 0.6 0:00.59 httpd
> > >> 29368 root 15 0 2304 1064 792 R 0 0.1 0:00.38 top
> > >> 1 root 15 0 2040 544 520 S 0 0.0 0:27.96 init
>
> > >> Though, the database is actually on a separate server. I suppose I
> > >> should run top on that server too.
>
> > >> Script works if I run this against just 5 or 10 UPCs, though the
> > >> script is insanely slow. This is true even if I run it against UPCs
> > >> that I myself have come up with, and which I'm sure are unique in the
> > >> database.
>
> > >> I've simplified the code down to this :
>
> > >> if ($upc_input) {
> > >> $startTime = microtime();
> > >> echo "<p>Start time: $startTime </p>";
>
> > >> $upc_input = trim($upc_input);
> > >> $pieces = explode("\n", $upc_input);
>
> > >> $log_file =
> > >> dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
> > >> $fp = fopen($log_file, 'a+');
>
> > >> $numberOfUpcs = count($pieces);
> > >> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>
> > >> for ($i=0; $i < $numberOfUpcs; $i++) {
> > >> $upc = $pieces[$i];
>
> > >> // 10-20-08 see comment above to see why empty string is dangerous
> > >> - returns 10,147 rows!
> > >> if ($upc != "") {
> > >> $upc = trim($upc);
> > >> fwrite($fp, $upc."\n");
> > >> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>
> > >> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
> > >> '".strtoupper($upc)."'";
> > >> $result = mysql_query($query);
> > >> echo "<br /> \n albums updated: " . mysql_affected_rows();
>
> > >> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
> > >> '".strtoupper($upc)."'";
> > >> $result = mysql_query($query);
> > >> echo "<br /> \n tracks updated: " . mysql_affected_rows();
> > >> }
> > >> echo "<br />";
> > >> } // ends for() loop
>
> > >> fclose($fp);
> > >> $endTime = microtime();
> > >> echo "<p>End time: $endTime </p>";
>
> > >> $totalTime = $endTime - $startTime;
> > >> $totalTime = round($totalTime / 1000, 2);
> > >> echo "The total time for this script was $totalTime seconds";
> > >> }
>
> > > Look at the message again. It says the server error log, not the php
> > > error log (which you may or may not even have configured).
>
> > Good point. When I try to run 200 UPCs through this script, this appears
> > in the server error log:
>
> > [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> > "/var/www/cgi-bin/php4.fcgi" (pid 3807) termination signaled
> > [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> > "/var/www/cgi-bin/php4.fcgi" (pid 3807) terminated by calling exit with
> > status '0'
> > [Tue Oct 21 08:16:21 2008] [warn] FastCGI: scheduled the restart of the
> > last (dynamic) server "/var/www/cgi-bin/php4.fcgi" process: reached
> > dynamicMaxClassProcs (10)
> > [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
> > "/var/www/cgi-bin/php4.fcgi" restarted (pid 3935)
>
> > PID 3935 is simply a PHP cgi process:
>
> > apache 3935 0.0 0.1 11260 2768 ? Ss 08:16 0:00
> > /var/www/cgi-bin/php
>
> > Anyone know what this about? I know folks use FastCGI with Ruby On
> > Rails, but I have not run into it before for PHP.
>
> I keep tweaking the code, trying to find the line that kills the
> script. You can see what I've got below. Oddly enough, if I comment
> out the two calls to mysql_query, then the script works just fine. Yet
> if I uncomment those lines, nothing gets written to the log file. Its
> as if the script dies on the first query.
>
> One "improvement" I've made to the script (I'm being sarcastic) is
> that it no longer handles even one UPC. When I started trying to fix
> the script, it could handle 20 to 25 UPCs, but died when given 100.
> Now it dies when it is given even one.
>
> if ($upc_input) {
> $startTime = microtime();
> echo "<p>Start time: $startTime </p>";
> // 10-20-08 - this next line might be killing the script. When I run
> // this query:
> //
> // SELECT upc_id, count( upc_id ) AS fullCount
> // FROM `albums`
> // GROUP BY upc_id
> // ORDER BY fullCount DESC
> //
> // I realize there are many UPCs with lots of repeats. The worst is
> the empty
> // string, which returns 10,147 rows! I'm going to add in trim here,
> and below
> // I'm going to screen out the empty strings.
> $upc_input = trim($upc_input);
> $pieces = explode("\n", $upc_input);
>
> /*
> * 10-15-08 - I've been asked to figure out why this script isn't
> working.
> * I was curious to see what this next line wrote out to, so I added
> this line
> * after it:
> *
> * echo $log_file;
> *
> * This gave me this path to look at:
> *
> * /var/www/cgi-bin/logs/album_removals.log
> *
> * Turns out that file didn't exist, nor did the directory "logs".
> * I created the the logs directory and chmoded it 0777. Then I
> * ran the script again using some dummy data that I'd already
> * added to the database. Then I looked at the log. It turned out
> * that it is a very simple list of the UPCs that I typed into the
> * form:
> *
> * 881034146533x
> * 881034146533xx
> * 881034146533xxx
> * 881034146533xxxx
> * 881034146533xxxxx
> * 881034146533xxxxxx
> * 881034146533xxxxxxx
> * 881034146533xxxxxxxx
> * 881034146533xxxxxxxxx
> * 881034146533xxxxxxxxxx
> * 881034146533xxxxxxxxxxx
> * 881034146533xxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxxxxxx
> * 881034146533xxxxxxxxxxxxxxxxxx
> *
> */
> $log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/
> album_removals.log';
> $fp = fopen($log_file, 'a+');
>
> $numberOfUpcs = count($pieces);
> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>
> for ($i=0; $i < $numberOfUpcs; $i++) {
> $upc = $pieces[$i];
>
> // 10-20-08 see comment above to see why empty string is dangerous
> - returns 10,147 rows!
> if ($upc != "") {
> $upc = trim($upc);
> fwrite($fp, $upc."\n");
> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>
> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
> '".strtoupper($upc)."'";
> fwrite($fp, $query."\n");
> $result = mysql_query($query);
> echo "<br /> \n albums updated: " . mysql_affected_rows();
>
> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
> '".strtoupper($upc)."'";
> fwrite($fp, $query."\n\n");
> $result = mysql_query($query);
> echo "<br /> \n tracks updated: " . mysql_affected_rows();
> }
> echo "<br />";
> } // ends for() loop
>
> fclose($fp);
> $endTime = microtime();
> echo "<p>End time: $endTime </p>";
>
> $totalTime = $endTime - $startTime;
> echo "The total time for this script was $totalTime seconds";
>
> }
Bizzare. I took out the UPPER and strtoupper() stuff from the queries,
so the SQL became simple:
$query = "UPDATE tracks SET status=4 WHERE isrc_id = '$upc' ";
The script now seems to work. I was able to run 200 UPCs through the
script and the script ran in less than a second.
|
|
|
0
|
|
|
|
Reply
|
lkrubner (905)
|
10/22/2008 6:07:58 PM
|
|
lawrence k wrote:
> On Oct 21, 11:37 pm, lawrence k <lkrub...@geocities.com> wrote:
>> On Oct 21, 1:12 pm, Lawrence Krubner <lawre...@krubner.com> wrote:
>>
>>
>>
>>> Jerry Stuckle wrote:
>>>> Lawrence Krubner wrote:
>>>>> When I attempt to input 200 UPCs, I don't even get a PHP error,
>>>>> instead I get this error:
>>>>> "OK
>>>>> The server encountered an internal error or misconfiguration and was
>>>>> unable to complete your request.
>>>>> Please contact the server administrator and inform them of the time
>>>>> the error occurred, and anything you might have done that may have
>>>>> caused the error.
>>>>> More information about this error may be available in the server error
>>>>> log.
>>>>> Apache/2.2.3 (CentOS) Port 443"
>>>>> As I mentioned before, no errors show up in PHP error log.
>>>>> If I run the "top" command while this script is executing, I see stuff
>>>>> like this:
>>>>> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>>>>> 24689 apache 18 0 14572 6312 2152 S 1 0.3 1:30.32 php
>>>>> 25296 apache 15 0 14976 7704 3244 S 1 0.4 4:51.05 php
>>>>> 29271 apache 15 0 34068 12m 4052 S 0 0.6 0:00.59 httpd
>>>>> 29368 root 15 0 2304 1064 792 R 0 0.1 0:00.38 top
>>>>> 1 root 15 0 2040 544 520 S 0 0.0 0:27.96 init
>>>>> Though, the database is actually on a separate server. I suppose I
>>>>> should run top on that server too.
>>>>> Script works if I run this against just 5 or 10 UPCs, though the
>>>>> script is insanely slow. This is true even if I run it against UPCs
>>>>> that I myself have come up with, and which I'm sure are unique in the
>>>>> database.
>>>>> I've simplified the code down to this :
>>>>> if ($upc_input) {
>>>>> $startTime = microtime();
>>>>> echo "<p>Start time: $startTime </p>";
>>>>> $upc_input = trim($upc_input);
>>>>> $pieces = explode("\n", $upc_input);
>>>>> $log_file =
>>>>> dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
>>>>> $fp = fopen($log_file, 'a+');
>>>>> $numberOfUpcs = count($pieces);
>>>>> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>>>>> for ($i=0; $i < $numberOfUpcs; $i++) {
>>>>> $upc = $pieces[$i];
>>>>> // 10-20-08 see comment above to see why empty string is dangerous
>>>>> - returns 10,147 rows!
>>>>> if ($upc != "") {
>>>>> $upc = trim($upc);
>>>>> fwrite($fp, $upc."\n");
>>>>> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>>>>> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
>>>>> '".strtoupper($upc)."'";
>>>>> $result = mysql_query($query);
>>>>> echo "<br /> \n albums updated: " . mysql_affected_rows();
>>>>> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
>>>>> '".strtoupper($upc)."'";
>>>>> $result = mysql_query($query);
>>>>> echo "<br /> \n tracks updated: " . mysql_affected_rows();
>>>>> }
>>>>> echo "<br />";
>>>>> } // ends for() loop
>>>>> fclose($fp);
>>>>> $endTime = microtime();
>>>>> echo "<p>End time: $endTime </p>";
>>>>> $totalTime = $endTime - $startTime;
>>>>> $totalTime = round($totalTime / 1000, 2);
>>>>> echo "The total time for this script was $totalTime seconds";
>>>>> }
>>>> Look at the message again. It says the server error log, not the php
>>>> error log (which you may or may not even have configured).
>>> Good point. When I try to run 200 UPCs through this script, this appears
>>> in the server error log:
>>> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
>>> "/var/www/cgi-bin/php4.fcgi" (pid 3807) termination signaled
>>> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
>>> "/var/www/cgi-bin/php4.fcgi" (pid 3807) terminated by calling exit with
>>> status '0'
>>> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: scheduled the restart of the
>>> last (dynamic) server "/var/www/cgi-bin/php4.fcgi" process: reached
>>> dynamicMaxClassProcs (10)
>>> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
>>> "/var/www/cgi-bin/php4.fcgi" restarted (pid 3935)
>>> PID 3935 is simply a PHP cgi process:
>>> apache 3935 0.0 0.1 11260 2768 ? Ss 08:16 0:00
>>> /var/www/cgi-bin/php
>>> Anyone know what this about? I know folks use FastCGI with Ruby On
>>> Rails, but I have not run into it before for PHP.
>> I keep tweaking the code, trying to find the line that kills the
>> script. You can see what I've got below. Oddly enough, if I comment
>> out the two calls to mysql_query, then the script works just fine. Yet
>> if I uncomment those lines, nothing gets written to the log file. Its
>> as if the script dies on the first query.
>>
>> One "improvement" I've made to the script (I'm being sarcastic) is
>> that it no longer handles even one UPC. When I started trying to fix
>> the script, it could handle 20 to 25 UPCs, but died when given 100.
>> Now it dies when it is given even one.
>>
>> if ($upc_input) {
>> $startTime = microtime();
>> echo "<p>Start time: $startTime </p>";
>> // 10-20-08 - this next line might be killing the script. When I run
>> // this query:
>> //
>> // SELECT upc_id, count( upc_id ) AS fullCount
>> // FROM `albums`
>> // GROUP BY upc_id
>> // ORDER BY fullCount DESC
>> //
>> // I realize there are many UPCs with lots of repeats. The worst is
>> the empty
>> // string, which returns 10,147 rows! I'm going to add in trim here,
>> and below
>> // I'm going to screen out the empty strings.
>> $upc_input = trim($upc_input);
>> $pieces = explode("\n", $upc_input);
>>
>> /*
>> * 10-15-08 - I've been asked to figure out why this script isn't
>> working.
>> * I was curious to see what this next line wrote out to, so I added
>> this line
>> * after it:
>> *
>> * echo $log_file;
>> *
>> * This gave me this path to look at:
>> *
>> * /var/www/cgi-bin/logs/album_removals.log
>> *
>> * Turns out that file didn't exist, nor did the directory "logs".
>> * I created the the logs directory and chmoded it 0777. Then I
>> * ran the script again using some dummy data that I'd already
>> * added to the database. Then I looked at the log. It turned out
>> * that it is a very simple list of the UPCs that I typed into the
>> * form:
>> *
>> * 881034146533x
>> * 881034146533xx
>> * 881034146533xxx
>> * 881034146533xxxx
>> * 881034146533xxxxx
>> * 881034146533xxxxxx
>> * 881034146533xxxxxxx
>> * 881034146533xxxxxxxx
>> * 881034146533xxxxxxxxx
>> * 881034146533xxxxxxxxxx
>> * 881034146533xxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxxxxxxx
>> *
>> */
>> $log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/
>> album_removals.log';
>> $fp = fopen($log_file, 'a+');
>>
>> $numberOfUpcs = count($pieces);
>> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>>
>> for ($i=0; $i < $numberOfUpcs; $i++) {
>> $upc = $pieces[$i];
>>
>> // 10-20-08 see comment above to see why empty string is dangerous
>> - returns 10,147 rows!
>> if ($upc != "") {
>> $upc = trim($upc);
>> fwrite($fp, $upc."\n");
>> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>>
>> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
>> '".strtoupper($upc)."'";
>> fwrite($fp, $query."\n");
>> $result = mysql_query($query);
>> echo "<br /> \n albums updated: " . mysql_affected_rows();
>>
>> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
>> '".strtoupper($upc)."'";
>> fwrite($fp, $query."\n\n");
>> $result = mysql_query($query);
>> echo "<br /> \n tracks updated: " . mysql_affected_rows();
>> }
>> echo "<br />";
>> } // ends for() loop
>>
>> fclose($fp);
>> $endTime = microtime();
>> echo "<p>End time: $endTime </p>";
>>
>> $totalTime = $endTime - $startTime;
>> echo "The total time for this script was $totalTime seconds";
>>
>> }
>
>
>
> I took out all the UPPER and strtoupper stuff, and I was able to get
> one UPC through this script. But it took .13 seconds, which seems a
> little high for running just a single UPDATE query.
>
>
>
> Start time: 0.15604500 1224640672
>
> You entered 1 UPCs
>
>
> Removing UPC/ISRC Code: xxxxxxxxxxxx7737127416741419436
> albums updated: 1
> tracks updated: 1
>
> End time: 0.02507700 1224640725
> The total time for this script was -0.130968 seconds
>
Sorry, this slipped through the cracks.
With a remote database, .13 sec. for two updates isn't unheard of. It
depends on the load on both servers (remember, your script may be
swapped out for another task, also) and network response, as well as the
actual time spend updating the database. Plus, you're doing other
things like the log file work, which will slow things down a bit (not
much, but some).
As for your problem with the CGI termination, I'm not sure - I don't use
PHP as a CGI, at least not much. Did you ever ensure that you had the
statements in your php.ini that I mentioned above? If PHP error logging
is disabled, for instance, you won't get any errors logged from it.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
|
|
|
0
|
|
|
|
Reply
|
jstucklex (14362)
|
10/22/2008 6:24:23 PM
|
|
lawrence k wrote:
> On Oct 21, 11:37 pm, lawrence k <lkrub...@geocities.com> wrote:
>> On Oct 21, 1:12 pm, Lawrence Krubner <lawre...@krubner.com> wrote:
>>
>>
>>
>>> Jerry Stuckle wrote:
>>>> Lawrence Krubner wrote:
>>>>> When I attempt to input 200 UPCs, I don't even get a PHP error,
>>>>> instead I get this error:
>>>>> "OK
>>>>> The server encountered an internal error or misconfiguration and was
>>>>> unable to complete your request.
>>>>> Please contact the server administrator and inform them of the time
>>>>> the error occurred, and anything you might have done that may have
>>>>> caused the error.
>>>>> More information about this error may be available in the server error
>>>>> log.
>>>>> Apache/2.2.3 (CentOS) Port 443"
>>>>> As I mentioned before, no errors show up in PHP error log.
>>>>> If I run the "top" command while this script is executing, I see stuff
>>>>> like this:
>>>>> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>>>>> 24689 apache 18 0 14572 6312 2152 S 1 0.3 1:30.32 php
>>>>> 25296 apache 15 0 14976 7704 3244 S 1 0.4 4:51.05 php
>>>>> 29271 apache 15 0 34068 12m 4052 S 0 0.6 0:00.59 httpd
>>>>> 29368 root 15 0 2304 1064 792 R 0 0.1 0:00.38 top
>>>>> 1 root 15 0 2040 544 520 S 0 0.0 0:27.96 init
>>>>> Though, the database is actually on a separate server. I suppose I
>>>>> should run top on that server too.
>>>>> Script works if I run this against just 5 or 10 UPCs, though the
>>>>> script is insanely slow. This is true even if I run it against UPCs
>>>>> that I myself have come up with, and which I'm sure are unique in the
>>>>> database.
>>>>> I've simplified the code down to this :
>>>>> if ($upc_input) {
>>>>> $startTime = microtime();
>>>>> echo "<p>Start time: $startTime </p>";
>>>>> $upc_input = trim($upc_input);
>>>>> $pieces = explode("\n", $upc_input);
>>>>> $log_file =
>>>>> dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/album_removals.log';
>>>>> $fp = fopen($log_file, 'a+');
>>>>> $numberOfUpcs = count($pieces);
>>>>> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>>>>> for ($i=0; $i < $numberOfUpcs; $i++) {
>>>>> $upc = $pieces[$i];
>>>>> // 10-20-08 see comment above to see why empty string is dangerous
>>>>> - returns 10,147 rows!
>>>>> if ($upc != "") {
>>>>> $upc = trim($upc);
>>>>> fwrite($fp, $upc."\n");
>>>>> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>>>>> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
>>>>> '".strtoupper($upc)."'";
>>>>> $result = mysql_query($query);
>>>>> echo "<br /> \n albums updated: " . mysql_affected_rows();
>>>>> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
>>>>> '".strtoupper($upc)."'";
>>>>> $result = mysql_query($query);
>>>>> echo "<br /> \n tracks updated: " . mysql_affected_rows();
>>>>> }
>>>>> echo "<br />";
>>>>> } // ends for() loop
>>>>> fclose($fp);
>>>>> $endTime = microtime();
>>>>> echo "<p>End time: $endTime </p>";
>>>>> $totalTime = $endTime - $startTime;
>>>>> $totalTime = round($totalTime / 1000, 2);
>>>>> echo "The total time for this script was $totalTime seconds";
>>>>> }
>>>> Look at the message again. It says the server error log, not the php
>>>> error log (which you may or may not even have configured).
>>> Good point. When I try to run 200 UPCs through this script, this appears
>>> in the server error log:
>>> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
>>> "/var/www/cgi-bin/php4.fcgi" (pid 3807) termination signaled
>>> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
>>> "/var/www/cgi-bin/php4.fcgi" (pid 3807) terminated by calling exit with
>>> status '0'
>>> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: scheduled the restart of the
>>> last (dynamic) server "/var/www/cgi-bin/php4.fcgi" process: reached
>>> dynamicMaxClassProcs (10)
>>> [Tue Oct 21 08:16:21 2008] [warn] FastCGI: (dynamic) server
>>> "/var/www/cgi-bin/php4.fcgi" restarted (pid 3935)
>>> PID 3935 is simply a PHP cgi process:
>>> apache 3935 0.0 0.1 11260 2768 ? Ss 08:16 0:00
>>> /var/www/cgi-bin/php
>>> Anyone know what this about? I know folks use FastCGI with Ruby On
>>> Rails, but I have not run into it before for PHP.
>> I keep tweaking the code, trying to find the line that kills the
>> script. You can see what I've got below. Oddly enough, if I comment
>> out the two calls to mysql_query, then the script works just fine. Yet
>> if I uncomment those lines, nothing gets written to the log file. Its
>> as if the script dies on the first query.
>>
>> One "improvement" I've made to the script (I'm being sarcastic) is
>> that it no longer handles even one UPC. When I started trying to fix
>> the script, it could handle 20 to 25 UPCs, but died when given 100.
>> Now it dies when it is given even one.
>>
>> if ($upc_input) {
>> $startTime = microtime();
>> echo "<p>Start time: $startTime </p>";
>> // 10-20-08 - this next line might be killing the script. When I run
>> // this query:
>> //
>> // SELECT upc_id, count( upc_id ) AS fullCount
>> // FROM `albums`
>> // GROUP BY upc_id
>> // ORDER BY fullCount DESC
>> //
>> // I realize there are many UPCs with lots of repeats. The worst is
>> the empty
>> // string, which returns 10,147 rows! I'm going to add in trim here,
>> and below
>> // I'm going to screen out the empty strings.
>> $upc_input = trim($upc_input);
>> $pieces = explode("\n", $upc_input);
>>
>> /*
>> * 10-15-08 - I've been asked to figure out why this script isn't
>> working.
>> * I was curious to see what this next line wrote out to, so I added
>> this line
>> * after it:
>> *
>> * echo $log_file;
>> *
>> * This gave me this path to look at:
>> *
>> * /var/www/cgi-bin/logs/album_removals.log
>> *
>> * Turns out that file didn't exist, nor did the directory "logs".
>> * I created the the logs directory and chmoded it 0777. Then I
>> * ran the script again using some dummy data that I'd already
>> * added to the database. Then I looked at the log. It turned out
>> * that it is a very simple list of the UPCs that I typed into the
>> * form:
>> *
>> * 881034146533x
>> * 881034146533xx
>> * 881034146533xxx
>> * 881034146533xxxx
>> * 881034146533xxxxx
>> * 881034146533xxxxxx
>> * 881034146533xxxxxxx
>> * 881034146533xxxxxxxx
>> * 881034146533xxxxxxxxx
>> * 881034146533xxxxxxxxxx
>> * 881034146533xxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxxxxxx
>> * 881034146533xxxxxxxxxxxxxxxxxx
>> *
>> */
>> $log_file = dirname($_SERVER["SCRIPT_FILENAME"]).'/logs/
>> album_removals.log';
>> $fp = fopen($log_file, 'a+');
>>
>> $numberOfUpcs = count($pieces);
>> echo " <p>You entered $numberOfUpcs UPCs</p> \n ";
>>
>> for ($i=0; $i < $numberOfUpcs; $i++) {
>> $upc = $pieces[$i];
>>
>> // 10-20-08 see comment above to see why empty string is dangerous
>> - returns 10,147 rows!
>> if ($upc != "") {
>> $upc = trim($upc);
>> fwrite($fp, $upc."\n");
>> echo "<br /> \n <br /> \n Removing UPC/ISRC Code: $upc ";
>>
>> $query = "UPDATE albums SET status=4 WHERE UPPER(upc_id) =
>> '".strtoupper($upc)."'";
>> fwrite($fp, $query."\n");
>> $result = mysql_query($query);
>> echo "<br /> \n albums updated: " . mysql_affected_rows();
>>
>> $query = "UPDATE tracks SET status=4 WHERE UPPER(isrc_id) =
>> '".strtoupper($upc)."'";
>> fwrite($fp, $query."\n\n");
>> $result = mysql_query($query);
>> echo "<br /> \n tracks updated: " . mysql_affected_rows();
>> }
>> echo "<br />";
>> } // ends for() loop
>>
>> fclose($fp);
>> $endTime = microtime();
>> echo "<p>End time: $endTime </p>";
>>
>> $totalTime = $endTime - $startTime;
>> echo "The total time for this script was $totalTime seconds";
>>
>> }
>
>
>
> Bizzare. I took out the UPPER and strtoupper() stuff from the queries,
> so the SQL became simple:
>
> $query = "UPDATE tracks SET status=4 WHERE isrc_id = '$upc' ";
>
> The script now seems to work. I was able to run 200 UPCs through the
> script and the script ran in less than a second.
>
That would make sense. The UPPER requires a table scan, where without
it MySQL probably is using an index.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
|
|
|
0
|
|
|
|
Reply
|
jstucklex (14362)
|
10/22/2008 6:54:34 PM
|
|
On Oct 22, 2:54 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> lawrencek wrote:
>
> > Bizzare. I took out the UPPER and strtoupper() stuff from the queries,
> > so the SQL became simple:
>
> > $query = "UPDATE tracks SET status=4 WHERE isrc_id = '$upc' ";
>
> > The script now seems to work. I was able to run 200 UPCs through the
> > script and the script ran in less than a second.
>
> That would make sense. The UPPER requires a table scan, where without
> it MySQL probably is using an index.
That's a hot tip. I suppose if I'd thought clearly about it I might
have realized that. But that is good to know.
|
|
|
0
|
|
|
|
Reply
|
lkrubner (905)
|
10/24/2008 6:17:46 PM
|
|
|
21 Replies
47 Views
(page loaded in 0.427 seconds)
|
Search |
Post Question |
Groups |
Stream |
About |
Register
32576 articles. 
5 followers. 
koptyaev
ralph58
molloy_joe
Benjamin