PHP MySQL Fetch -- 500 Internal Server Error

[rohitbanerjee]

Hello,

 

So I have a weird error where if I fetch more than a certain number of rows from a mysql table, it triggers a 500 Internal Server Error.

 

I am using Apache Web Server (through GoDaddy) and the offending code is below:

 

set_time_limit(0);
$this->Connect();
$Output = array();
$search = "SELECT * FROM <table> WHERE user_id = ?";
if($Statement = $this->MySQLi->prepare($search)){
  $Statement->bind_param("i", $UserId);
  $Statement->execute();
  $Statement->bind_result(<result variables>);
  $count = 0;
  while($Statement->fetch() && $count++ < 70){
ChromePhp::log(<result variables>);
  }
  $Statement->close();
}
$this->Disconnect();

 

ChromePhp::log is a way of dumping things to the Javascript Console in your browser from within a PHP script just as a heads-up. So when I set the stop number as 70, everything is fine. If I try to fetch more than that it triggers a 500 internal server error on Apache Server port 443. I have looked through the error logs and can't figure out the cause but this is almost certainly a server configuration issue? I'd appreciate any feedback, especially anyone familiar with GoDaddy's hosting services

 

Thanks

[xyph]

Just to clarify, you get a 500 error, and it's not showing up in your Apache error logs?

 

Seems odd to me.

 

Also, it seems your query is only requesting a single row, unless `user_id` isn't unique. If that's the case, your while loop should only loop once anyways. mysqli_stmt->fetch() will return null when there's no results left, causing your while loop to return FALSE and end.

 

I have no idea why it's perform they way it is.

[rohitbanerjee]

$UserId is unique and while($Statement->fetch() && $count++ < 70) executes 70 times not once. I said I couldn't figure out the error, not that an error isn't posted. The 500 error isn't appearing on the server logs but the error log entry is the following:

 

[Mon Apr 09 07:32:50 2012] [warn] RSA server certificate CommonName (CN) `<domain>' does NOT match server name!?

 

I don't know if this is the cause or not because I don't think there is an error with my code per se as I can turn the internal error on and off depending on how many rows I fetch.

 

 

 

[rohitbanerjee]

Actually there is another error:

 

Premature end of script headers: <file>.php

 

[xyph]

Why are you telling me PHP performs differently than the manual?

 

Database:

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(10) NOT NULL,
  `expires` datetime NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `value`, `expires`) VALUES
(1, 'foobar', '2012-03-11 18:07:46');

 

Code:

<?php

$id = 1;

$sql = new mysqli( 'localhost','root','','db' );

$q = 'SELECT `value` FROM `items` WHERE `id`=?';
$stmt = $sql->prepare($q);
$stmt->bind_param('i',$id);

$stmt->execute();

$stmt->bind_result( $value );

$count = 0;
while( $stmt->fetch() && $count++ < 500 ) {
echo $value.' - '.$count.'<br>';
}

?>

 

Output:

foobar - 1<br>

 

I'm not sure what is happening in your code. Perhaps removing code until you've isolated the issue.

 

Again, if you only have 1 row returned, mysqli_stmt->fetch() will return null after the first loop. ( null && *anything* ) will return FALSE.

 

var_dump( null && true );

[rohitbanerjee]

Yeah, but you only inserted one thing into the table. If you inserted multiple things then fetch would return multiple rows. Fetch exits when there are no more rows to fetch.

[xyph]

It doesn't matter how many rows the table has. WHERE `id` = ? will limit your query to return 1 row.

 

I've added 50 rows to my table, none of which also have an `id` of 1. The results were the same.

[rohitbanerjee]

I don't know what to tell you ..

 

$Statement->fetch() will fetch a row. while($Statement->fetch()) will keep fetching rows until there are no more rows to fetch.

 

http://www.php.net/manual/en/mysqli-stmt.fetch.php

 

That's from the php manual, look at the last example.

[rohitbanerjee]

No, no you misunderstood my response. There are multiple rows with the same user id.

[xyph]

What happens if you use mysqli_stmt->store_result() before binding?

 

From what I've read on the error, it could be MySQL timing our, or reaching a size/execution limit of some sort.

 

[rohitbanerjee]

Nope, same issue -- by binding did you mean before bind_param or bind_result. I tried it before both and no result. I think you may be right regarding timeouts -- I checked the apache logs and it seems that everytime I tried to visit the error, there was a response of 500 408. 408 meaning that the website did not respond fast enough, presumably because of the mysql fetches?

[xyph]

It's possible. It's hard to say without more intimate knowledge of your tables, and MySQL/Apache/PHP settings.

 

Here's a good reference

http://kb.liquidweb.com/apache-error-premature-end-of-script-headers/

[rohitbanerjee]

I actually found that exact same webpage and am pretty convinced the error is the second bullet point -- I've fired this off to the godaddy support staff and hopefully resolve this thing.

 

Thanks for the help and sorry for the misunderstanding earlier