[SOLVED] Large number of looped database inserts fails

[chombone]

Not sure if this is PHP or MySQL really.

 

I'm reading the pixels of an image and storing them in a database table which has an auto incremented key (the code is being executed by a browser page). Every time I get to approximately 3000 rows (the number is never the same) the code stops. No error. It just stops. The echo of $sqlstring works right up to the point where it stops. It only happens for large enough values of $scale, e.g 10 is fine (presumably because it only has 100 iterations) but 100 fails because there should be 10000 iterations. $source is the name of the black and white image and it has the same dimensions as $scale.

 

Main section of code :

<?php
$scale = 100;
for ($x=0;$x<$scale;$x++) 
   {
    for ($y=0;$y<$scale;$y++) 
       {
        //get the pixel rgb values
        $pixel = imagecolorat($source,$x,$y);
        $cols  = imagecolorsforindex($source, $pixel);
        $g = $cols['red']; // b&w image so any of red green or blue will do for grey value
        // store pixel value
        $sqlstring = "INSERT INTO image_index (image_file, x, y, g) VALUES (\"" . $filename ."\"," . $x . "," . $y . "," . $g . ")";
        echo $sqlstring . "<br />";
        mysql_query($sqlstring,$connection) or die("Image index storage failed : " . mysql_error());
        }
   }
echo "finished";
?>

 

It never gets to "finished". The output of $sqlstring stops and at the bottom of the browser it says done.

 

Any ideas?

[marcus]

where is $filename defined?

[lemmin]

I have had this problem before. I got around it by making the loop echo to the browser at every stage. You might even need to set the script timeout a little higher when you do that (depending on how long your script takes, of course.) This only works if the script isn't being used for any display to users, otherwise the page they want to see with have a huge amount of unnecessary text. My script was simply to put something in the database, so it was fine.

[chombone]

I didn't show the definition of $filename as it's just the full pathname of the file and predefined elsewhere as a parameter (I just used the start and end tags to highlight the code, not to show the actual start and end).

 

Annoyingly, if the insert statement isn't present the loops complete and the last value for $sqlstring output is

INSERT INTO image_index (image_file, x, y, g) VALUES ("gallery_images/others/sussudio_bw.jpg",99,99,103)

which is correct.

[chombone]

I got around it by making the loop echo to the browser at every stage.

 

Yeah, tried it. It correctly throws out all the insert statements to the screen if the inserts aren't executed. If they are executed to the database they fail about one third through with no error.

 

i.e. the last line on the page shows some thing like

 

INSERT INTO image_index (image_file, x, y, g) VALUES ("gallery_images/others/sussudio_bw.jpg",29,14,103)

 

instead of

 

INSERT INTO image_index (image_file, x, y, g) VALUES ("gallery_images/others/sussudio_bw.jpg",99,99,116)

 

where 99,99 are the final coordinate pair.

 

 

[PC Nerd]

woudl there be a way for you to store the data in anotehr form, then do one long query?  seems to me that maybe either CPu limit is being hit, or script timeout on your server.  Either one.  Also, does mysql limit sql queries from the same page?  or is it only by IP etc?

 

gdlk

[lemmin]

I would try running the "Compact and Repair Database" tool. If your table uses an AutoNumber field, repairing might fix some errors.

[chombone]

woudl there be a way for you to store the data in anotehr form, then do one long query?  seems to me that maybe either CPu limit is being hit, or script timeout on your server.  Either one.  Also, does mysql limit sql queries from the same page?  or is it only by IP etc?

 

gdlk

 

Thanks! It's a script timeout (as Lemmin also suggested)- I realised it was giving up after exactly 30 seconds. I reset the max_execution_time from 30 to 120 in PHP.INI but this had no effect, so I used the function set_time_limit(120) in the code and this gave it enough time to complete. So, now I'm wondering why changing it in PHP.INI has no effect.