MySQL server has gone away error

[schilly]

I have a cron running some media compression (FFmpeg, GD stuff). The problem is that some media takes a long time to compress and by the time my compression is done, my mysql connection has timed out so I can't update the media record.

 

What's the best way to get around this?

 

My current process:

 

-query db for media needed for compression

-loop through results

-compress media

-update db record to compressed and each compression

 

interactive_timeout and wait_timeout are set to 60. I tried to do

ini_set('mysql.connect_timeout','300');

 

but it didn't help.

 

Should I get my initial results then close the connection then reopen each time a compression completes to update the record?

 

 

Thanks.

 

[cgeisler515]

You could try this.

 

Your problem is timing out before all your media is compressed data. not sure if you mean the script is timing out or the db connection is timing out.

 

if it is the db connection timing out you could try reestablishing the connection at the end of x number of loop itterations. quick example.

 

$reconnect_after = 10;

$j = 0; // counter var resets after each reconect_after is reached.

 

for($i =0; $i < sizeof($array); $i++)

{

    /media compression code goes Here

 

    if($j = $reconnect after)

    {

        //reestablish connection

        $j = 0; //resets counter

    }

    else

    {

        $j++; //increases counter

    }

}

 

if the problem is your script timing out

try deviding your work load with redirects

 

same method as above but instead of reestablishing a connection reload the page with a few passed parameters and have the page pick up where it left off.

 

 

I hope this helps.

[schilly]

thanks. it's the db connection timing out because the media compression can be anywhere from 3s - 20mins.

 

i ended up reconnecting to the db after every compression. i'm just not sure if that's the best way to do it or not.

 

i guess another option is using some kind of timer class and checking the total time the compression took to decide whether or not to reconnect.