PHP or MySQL freezing (not sure which)

a2jfreak

I have a chunk of code that intermittently works/doesn't work. I'm not sure why. I don't know if the issue is PHP-related or MySQL-related. I really don't know how to go about finding out which it is. The code just freezes during the loop. Say there are 900 records. Maybe the first 500 will print or the first 800 will print but then it freezes. Sometimes it works just fine. I don't understand why.

You'll see sprintf(). I know it's inefficient and makes the code less readable. I was just looking for something--anything--I could do to change the code and see if it would work. None of the changes I've made have fixed the issue. Sometimes the code works. Sometimes it freezes. Very very frustrating.

[php]
<?php
$query = 'SELECT * FROM `missing` WHERE `isMissing`="Yes" AND `ID` !="'.$id.'" ORDER BY `lastName`';
$result = mysql_query($query, $link) or die('Query Failed! ' . mysql_error());

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
?>
<option value="<?php echo $row; ?>"><?php echo sprintf("%s %s %s", $row,$row,$row); ?></option>
<?php
}
mysql_free_result($result);
?>
[/php]

primesuspect

are you 100% sure there's no hardware issue such as a bad sector on the HD? I've encountered maddening problems like that before that were actually due to the .MYI sitting on a bad sector.

a2jfreak

Brian, I've checked the disk and not had any problems. Are there any particular programs you suggest I run that are different from the "standard" fs utils?

primesuspect

No, not really. I'd run a myisamchk and fsck or whatever you need for your filesystem. Other than that, perhaps it is a software problem.

Think of running memtest on the box, too? I know that's next to impossible on production servers, but...

a2jfreak

If I get the problem solved, I'll post here and perhaps if anyone else ever has the same problem my frustrations won't have to be shared for long.

jhenry

Do you have error reporting on?

lightnin

Sounds like an inefficient query. 'select * ' statements automatically invoke a table scan in mysql... so it's best to avoid them.

Can you give us the schema? Maybe i can help you speed that query up a bit. or do some table optimizations, such as proper indexing. Also, if isMissing is yes or no, why not make it a boolean?

a2jfreak

jhenry wrote:

Do you have error reporting on?

Yes.

lightnin wrote:

Sounds like an inefficient query. 'select * ' statements automatically invoke a table scan in mysql... so it's best to avoid them.

Can you give us the schema? Maybe i can help you speed that query up a bit. or do some table optimizations, such as proper indexing. Also, if isMissing is yes or no, why not make it a boolean?

SELECT * makes MySQL scan the table? Isn't that what I want? I do need all the fields for the returned record. You're suggesting I should "SELECT table.col1,table.col2.table.col3,...,table.colXXX FROM" instead?

isMissing is an ENUM.

Also, now the problem sometimes exhibits itself (freezing) BEFORE the SQL code is ever reached. I'm certain it's a PHP problem. Thing is, no errors are reported as the code itself is just fine. Any ideas what could cause PHP to freeze? I allow 60 seconds for scripts (which is about 60 seconds longer than the page takes) and I allow 24MB of RAM is which way more than plenty. PHP version is 4.3.9 for CentOS 4.4 Final. I tried updating to 5.x via the centosplus repository, but I ran into some issues. In fact, I still have issues now with apache, but at least I can make apache run it just doesn't like to stop via service httpd stop or service httpd restart

Linc

Start hacking chunks of the code out until it stops freezing and narrow it down to the statement that's causing it. Debugging shouldn't be guess work.

You can use the full path to the apachectl file to start/stop/restart Apache. e.g.: /srv/bin/whatev/apachectl restart

wthww

A couple things I can think of here. One, is php. PHP by default has two things that could be stalling you here, and they are the maximum ammount of time a query is allowed to process, and the maximum size of a query. I don't really know how big each record is, nor the ammount of time its taking for the database to be queried, but I have serious doubt this is the reason at all, because that doesnt sound like php. PHP would've thrown en error about the query being too big, or the maximum execution time being too small. What it sounds like to me is there is a hicum with your database server. And, I've never had an issue (except for hitting the size limit on longblob fields, but thats another story). Here are two things you can change in your php.ini to see if this corrects it.

max_execution_time 60

and I can't remember the other off the top of my head. Tho, be warned that max_execution_time shouldn't be going over for that ammount of records (Usually around a couple thousand) doing that query, and, if this fixed it, you may want to invest in better database hosting.

//wthww

lightnin

SELECT * makes MySQL scan the table? Isn't that what I want?

No. Check out indexing... if you have a large table, a table scan is bad. very bad. (unless under some *very specific* situations which i doubt you've implemented)

see this:
http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html

indexing is your friend. And yes, list out every column you are selecting. PITA now, less brittle and more readable in the future. or swap to PDO or some similar framework (RoR?) to lessen the burden of repetitive coding.

php itself usually isn't slow... could you be getting hung up in creating a link to the db? if that's not it...

http://www.phpunit.de/

unit test your code man. something's not smelling right.

I'd love a closer look at your code, you can PM me for an email addy an i'd be glad to help.