Why does this PHP code hang on calls to mysql_query()?

Question

I'm having trouble with this PHP script where I get the error

Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/vhosts/richmondcondo411.com/httpdocs/places.php on line 77

The code hangs here:

function getLocationsFromTable($table){

    $query = "SELECT * FROM `$table`";
    if( ! $queryResult = mysql_query($query)) return null;

    return mysql_fetch_array($queryResult, MYSQL_ASSOC);

}

and here (so far):

function hasCoordinates($houseNumber, $streetName){

    $query = "SELECT lat,lng FROM geocache WHERE House = '$houseNumber' AND StreetName = '$streetName'";
    $row = mysql_fetch_array(mysql_query($query), MYSQL_ASSOC);
    return ($row) ? true : false;

}

both on the line with the mysql_query() call.

I know I use different styles for each code snippet, it's because I've been playing with the first one trying to isolate the issue.

The $table in the first example is 'school' which is a table which definitely exists. I just don't know why it sits there and waits to time out instead of throwing an error back at me.

The mysql queries from the rest of the site are working properly. I tried making sure I was connected like this

//connection was opened like this:
//$GLOBALS['dbh']=mysql_connect ($db_host, $db_user, $db_pswd) or die ('I cannot connect to the database because: ' . mysql_error());

if( ! $GLOBALS['dbh']) return null;

and it made it past that fine. Any ideas?

Update

---

It's not the size of the tables. I tried getting only five records and it still timed out. Also, with this line:

$query = "SELECT lat,lng FROM geocache WHERE House = '$houseNumber' AND StreetName = '$streetName'";

it is only looking for one specific record and this is where it's hanging now.

Answer 1

It sounds like MySQL is busy transmitting valid data back to PHP, but there's so much of it that there isn't time to finish the process before Apache shuts down the PHP process for exceeding its maximum execution time.

Is it really necessary to select everything from that table? How much data is it? Are there BLOB or TEXT columns that would account for particular lag?

Analyzing what's being selected and what you really need would be a good place to start.

Answer 2

Does your code timeout trying to connect or does it connect and hang on the query?

If your code actually gets past the mysql_query call (even if it has to wait a long time to timeout) then you can use the mysql_error function to determine what happened:

mysql_query("SELECT * FROM table");
echo mysql_errno($GLOBALS['dbh']) . ": " . mysql_error($GLOBALS['dbh']) . "\n";

Then, you can use the error number to determine the detailed reason for the error: MySQL error codes

If your code is hanging on the query, you might try describing and running the query in a mysql command line client to see if it's a data size issue. You can also increase the maximum execution time to allow the query to complete and see what's happening:

ini_set('max_execution_time', 300); // Allow 5 minutes for execution

Answer 3

I don't know about the size of your table, but try using LIMIT 10 and see if still hangs.

It might be that your table is just to big to fetch it in one query.

Answer 4

Time spent waiting for mysql queries to return data does not count towards the execution time. See here.

The problem is most likely somewhere else in the code - the functions that you are blaming are possibly called in an infinite loop. Try commenting out the mysql code to see if I'm right.

Answer 5

Unless the parameters $houseNumber and $streetName for hasCoordinates() are already sanitized for the MySQL query (very unlikely) you need to treat them with mysql_real_escape_string() to prevent (intentional or unintentional) sql injections. For mysql_real_escape_string() to work properly (e.g. if you have changed the charset via mysql_set_charset) you should also pass the MySQL connection resource to the function.

Is the error reporting set to E_ALL and do you look at the error.log of the webserver (or have set display_erorrs=On)?

Try this

function hasCoordinates($houseNumber, $streetName) {
  $houseNumber = mysql_real_escape_string($houseNumber);
  $streetName = mysql_real_escape_string($streetName);
  $query = "
    EXPLAIN SELECT
      lat,lng
    FROM
      geocache
    WHERE
      House='$houseNumber'
      AND StreetName='$streetName'
  ";
  $result = mysql_query($query) or die(mysql_error());
  while ( false!==($row=mysql_fetch_array($result, MYSQL_ASSOC)) ) {
    echo htmlspecialchars(join(' | ', $row)), "<br />\n";
  }
  die;
}

and refer to http://dev.mysql.com/doc/refman/5.0/en/using-explain.html to interpret the output.

Answer 6

-If you upped the execution time to 300 and it still went through that 300 seconds, I think that by definition you've got something like an infinite loop going.

-My first suspect would be your php code since mysql is used to dealing with large sets of data, so definitely make sure that you're actually reaching the mysql query in question (die right before it with an error message or something).

-If that works, then try actually running that query with known data on your database via some database gui or via the command line access to the database if you have that, or replacing the code with known good numbers if you don't.

-If the query works on it's own, then I would check for accidental sql injection coming from with the $houseNumber or $streetName variables, as VolkerK mentioned.