[SOLVED] 500 internal server error

[alexcroox]

I am calling an php file that reads 7000~ rows of MySQL data and saves almost all (depending on filters) data into an array and then into an Excel file ready for download, all via an ajax call from another php page.

 

It seems to work when I put a LIMIT up to around 1000~ (doesn't always work on 1000, maybe due to server load from other websites?), otherwise it returns a javascript popup (from the ajax) with a 500 internal server error, then seems to take my website offline for that reason for 10 minutes or so.

 

I have used the code;

 

ini_set('max_execution_time', '300');
ini_set("max_input_time","120");
ini_set("memory_limit","100M");

 

on both the main php page and the one I am calling via ajax with no joy.

 

Based on the overall functionality is there anything I can do to stop this Internal server error triggering? (I know the functionality works OK, because if I limit the db call to 700 rows or less it works fine).

 

Many thanks.

[soak]

A few thoughts / ideas:

 

First, have you verified that the ini_set's are taking effect (check with phpinfo()

Second, are you selecting JUST the data that you need? (i.e. "SELECT id, name" rather than "SELECT *")

Thirdly, is there a way that you can batch the selects and do say 500 at a time.

Fourtly, (and probably the most important) can you apply your filters using the SQL query so that you only select the rows that you need?

Lastly, can you streamline the process as much as possible? Do you need to go via an array or can you just grab a row from the db and put it straight where it needs to go rather than into the array then to where it needs to go?

[alexcroox]

Hi Soak thanks for your reply.

 

1) I will test phpinfo once my website comes back up from the last failed test

2) I am just selecting the rows I need yes.

3) The batch selects is a solution I'm sure will work, but I was avoiding it because of extra development time in case it was resolvable with current function. Looks like I might have to go down that route.

4) Yes but unfortunately for this particular requirement the filter means it is almost every row that is returned.

5) I thought putting all the data in an array and then writing to a file at the end might be faster then writing to a file on every step of the loop a thousand times?

[soak]

2) Not just the rows, only the fields that you need.

5) It probably would. I suspect that a straightforward string would use less memory than an array though (I don't know this for fact but I'd be very surprised if it wasn't true). You could also write the string to the file every 500 or so lines and be back down to 0kb memory usage ;-)

 

[alexcroox]

2) Sorry miss-typed there, I meant fields.

 

Oddly enough, when I try to load another php page that doesn't have much in it, say just an echo statement, it loads OK, but when I try to load other parts of the site that I know work, and my problem script it comes up with Internal Server error for a good 10 minutes before allowing me back in. Does anyone know much about this 500 error code and what might cause it lock up other php files, does it crash sessions?databases?file reading? and therefore prevent any other file that uses these functions to load until it resolves itself?

[PFMaBiSmAd]

So, have you checked your web server log file to find out what error is occurring that is triggering the 500 http response page?

[ober]

http://pcsupport.about.com/od/findbyerrormessage/a/500servererror.htm

 

Where I work, these are typically caused by a server running out of memory or some other fatal error.  My guess is you are passing too much information in an array or via non-standard methods.  That is a lot of data to send back and forth via HTTP requests.

 

I would consider another method of generating the Excel file.

[alexcroox]

I solved this by running the script on our local apache server which did not fall over Obviously a restriction on the shared hosting! Thank you for your help!