php - How does a mysql server restart affect the currently running jobs

991

On my virtual dedicated linux server, there are always some php jobs running. These jobs keep writing data in different tables. I was wondering, what happens if i need to restart my mysql server as i have made some config changes? Will all the running jobs be killed if i restart my mysql server ? or will they be on hold for a while , throwing error while the server is being restarted?

Thanks for your help

851

Answer

Solution:

Depends on how well those php jobs were written. They may hold data until a connection is re-established, they mail fail and 'call for help' or they may fail horribly leaving data in a state of disarray because transaction logic wasn't used.

You could have the best possible outcome, or the worst it entirely depends on how well those php jobs were developed.

656

Answer

Solution:

If you put a bit of code around the actual mysql php calls, you can catch the errors and deal with them.

function mquery( $qry, $lnk ) {
global $dbl;  # your pre-established database link

$result = mysql_query( $qry, $lnk );

if ( $result === false ) {
    $error_msg = mysql_error( $lnk );

    while ( $result === false && @$i < 25 ) {
        $i++;

        $error_no  = mysql_errno( $lnk );

        if      ( $error_no == 1054 ||  # "MySQL Error #1054: Unknown column..."
                     $error_no == 1064 ||  # "You have an error in your SQL syntax" - MAC - May 30, 2012
                     $error_no == 1065 ) { # "Query was empty" 
            break;  # don't loop on errors - it will not succeed
        }
        else if ( $error_no == 1205 ||  # SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
                     $error_no == 1213 ) { # "Deadlock found when trying to get lock; try restarting transaction" 
            sleep( $i );                 # try sleeping a bit longer to get past the deadlock
        }
        else if ( $error_no == 2003 ||  # SQLSTATE[HY000] [2003] Can't connect to MySQL server
                     $error_no == 2006 ||  # "MySQL server has gone away" -- reconnect 
                     $error_no == 2013 ) { # SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading authorization packet', system error: 0
            sleep( $i );
            if      ( $lnk == $dbl )    { # you may have multiple database links...
                get_dbl();   # re-establish your database link
                $lnk = $dbl;
            }
        }
        else {
            sleep( 1 ); # keeps us from sleeping too long on other errors
        }

        $result = mysql_query( $qry, $lnk );
    }
    echo "\n<!-- MySQL Error #$error_no: '$error_msg' (tried $i times) -->\n";
}

return $result;

} // end mquery

If you find that you have other errors that may succeed if you wait a little while, then add them.

For errors that won't succeed no matter how long you wait, add them to the 'break' block of code.

472

Answer

Solution:

Depends how they were written.

In all likelihood, as they're written in PHP, they will get a massive load of errors in the database, which will trigger PHP E_WARNINGs, which the application will completely ignore and generate junk data in its output file, enter an infinite loop, or some other completely useless behaviour.

They certainly won't get killed. However, they might die (which may be reasonable behaviour, provided they can restart and do the right thing).

People are also looking for solutions to the problem: How do I return an variable and an array from a method in php?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.