PHP / MySQL script hangs after ~2500 queries

Question

I have a script that loads between ~5000-8000 objects, performs some logic on them and then attempts to insert/update the MySQL table.

After about 2500 queries, the database stops executing the queries and the script just hangs with no error thrown. I need advice on what you would do in this situation. The server is a Windows box if that makes a difference. I am using mysql_connect in PHP5.

I have placed mysql_error statements with exit commands after all of the queries and they never fire. I have also set

set_time_limit(0);
ini_set("memory_limit", "256M");
error_reporting(E_ALL | E_STRICT);

in an attempt to throw more errors. Nothing at all happens, no errors and it does not finish executing. The row that it stops at is also random, nothing unique about it as it is always a different row that is updated before it stops.

What type of techniques would you use to profile this bug?

Answer 1

Are you able to load the data using the LOAD DATA INFILE? This will require access to the mysql console on the server. If you can, that would be the way to go.

Aside from that, The only other way would be to stagger the inserts so run, say 2200 queries then do a page reload with a header redirect to load the next set etc (store the last id or something unique in the session to resume at).

Also, if you can make the queries EXTENDED INSERTS and just load them at around 500 at a time, that would be helpful too. I say 500 at a time because depending on the size of the data MySQL will hiccup if you try to input too much data through the extended inserts at one time.

Answer 2

1. You should not execute long running tasks via apache. Run it from the command line.

2. If you really want to do it that way, set "max_execution_time" aswell and run the phpinfo() function to see if your config settings really happened. Depending on your server, you script is not even allowed to change the execution time of the scripts. If that is the case, call your hosting company and ask them if they can change that for you.