By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,966 Members | 812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,966 IT Pros & Developers. It's quick & easy.

script dies yet there are no errors in error log

P: n/a

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);
?>




Oct 20 '08 #1
Share this Question
Share on Google+
15 Replies


P: n/a
>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);
?>
Oct 20 '08 #2

P: n/a
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.
js*******@attglobal.net
==================

Oct 20 '08 #3

P: n/a
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?

Oct 21 '08 #4

P: n/a
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.
js*******@attglobal.net
==================

Oct 21 '08 #5

P: n/a
>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.
Oct 21 '08 #6

P: n/a
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);
?>
Oct 21 '08 #7

P: n/a
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);
?>
Oct 21 '08 #8

P: n/a
On Oct 20, 10:28*pm, Lawrence Krubner <lawre...@krubner.comwrote:
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?- Hidequoted 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
Oct 21 '08 #9

P: n/a
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.
js*******@attglobal.net
==================

Oct 21 '08 #10

P: n/a
On 21 Oct, 10:56, Bill H <b...@ts1000.uswrote:
On Oct 20, 10:28*pm, Lawrence Krubner <lawre...@krubner.comwrote:


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.

Oct 21 '08 #11

P: n/a
Captain Paralytic wrote:
On 21 Oct, 10:56, Bill H <b...@ts1000.uswrote:
>On Oct 20, 10:28 pm, Lawrence Krubner <lawre...@krubner.comwrote:


>>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.

Oct 21 '08 #12

P: n/a
>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);
?>

Oct 21 '08 #13

P: n/a
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.


Oct 21 '08 #14

P: n/a
On Oct 21, 1:12 pm, Lawrence Krubner <lawre...@krubner.comwrote:
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
Oct 22 '08 #15

P: n/a
On Oct 22, 2:54 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:
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.


Oct 24 '08 #16

This discussion thread is closed

Replies have been disabled for this discussion.