What's the Best Way to Deal with MySQL Errors?

[Fluoresce]

I am currently handling MySQL errors like this:

$conn = mysql_connect("", "", "");
if(!$conn) {
     // Present message to user.
     echo "<p>Sorry, there has been an error.  The webmaster has been informed.</p>";
     // Send yourself an e-mail.
     error_log("MySQL connection failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
}
else {
     $selectdb = mysql_select_db("");
     if(!$selectdb) {
          // Present message to user.
          echo "<p>Sorry, there has been an error.  The webmaster has been informed.</p>";
          // Send yourself an e-mail.
          error_log("MySQL selectdb failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
     }
     else {                   
          $query = mysql_query("", $conn);
          if(!$query) {
               // Present message to user.
               echo "<p>Sorry, there has been an error.  The webmaster has been informed.</p>";
               // Send yourself an e-mail.
               error_log("MySQL query failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
          }
          else {
               // Rest of code.
          }
     }
}

The code works. If there's an error with mysql_connect(), mysql_select_db() or mysql_query(), I am sent an e-mail and the user is presented with a message.  If the error is with mysql_connect(), the error is also logged.

 

However, the code looks very unprofessional and over the top.  It makes my scripts look complicated.

 

What's the best way to deal with MySQL errors?

 

Is this better?

$conn = mysql_connect("localhost", "", "");
if(!$conn) {
     // Send yourself an e-mail.
     error_log("MySQL connection failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
     // Redirect user to error page.
     header("Location: mysql-error.php");
     die();
}
$selectdb = mysql_select_db("");
if(!$selectdb) {
     // Send yourself an e-mail.
     error_log("MySQL select database failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
     // Redirect user to error page.
     header("Location: mysql-error.php");
     die();
}
$query = mysql_query("", $conn);
if(!$query) {
     // Send yourself an e-mail.
     error_log("MySQL failed failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
     // Redirect user to error page.
     header("Location: mysql-error.php");
     die();
}

Edited October 24, 2013 by Fluoresce

[mentalist]

I never use die!

 

You can always handle non-existent data simply with default values, or you can test to see if you have a valid $conn by testing that it isn't FALSE, or use try catch

 

 

Are you aware that mysql_connect is deprecated?

http://uk3.php.net/manual/en/function.mysql-connect.php

 

Instead: http://uk3.php.net/manual/en/book.mysqli.php

[vinny42]

 

I never use die!

+1

 

Your code should not die() when the connection fails, because that means your website will show a blank or at least half-completed page. Not good.

 

Also, don't send emails when errors happen, log them to a file. If this error happens on a popular website it will send a few hundred thousand emails per hour, your mail account will break.

Mail the logfile (or better: a digest of the logfile) once every 15 minutes or so.

 

As for handling the errors: make it throw exceptions, and let your aplication decide what to do when it catches a database-related exception. Usually you will just want tp display an HTML page telling the user that there is a problem and that you have been notified about it.

[mac_gyver]

for those of you posting about using the die(); statement, did you look at the code to see how it is being used? it's after a header() redirect, where you do need to use a die/exit statement because you don't want the remainder of the logic on the page to run.

[mentalist]

 

for those of you posting about using the die(); statement, did you look at the code to see how it is being used? it's after a header() redirect, where you do need to use a die/exit statement because you don't want the remainder of the logic on the page to run.

Very good I'm too used to seeing exit() there instead.

[vinny42]

 

 

it's after a header() redirect, where you do need to use a die/exit statement because you don't want the remainder of the logic on the page to run.

 

I missed the redirect, or I would have posted a very shouty post about that:

 

Don't redirect when there is an internal error in the page. Google and such will follow that link and could possibly decide that your page has moved. Not only  does that remove the orignial liknk from the index, it could also result in a duplicate content penalty. One night of database problems can seriously hurt your ranking if you redirect.

 

A more gentle approach is not to exit and not to redirect, but to throw an exception taht is caught by a piece of code that prints a userfriendly errormessage and sets the HTTP statuscode to "temporarily unavailable". that leaves your logic in tact and stops others from misinterpreting the situation.

 

Still no die() though. :-)

[Fluoresce]

Mail the logfile (or better: a digest of the logfile) once every 15 minutes or so.

 

How do you do that? An outline would be very much appreciated.

 

A more gentle approach is not to exit and not to redirect, but to throw an exception taht is caught by a piece of code that prints a userfriendly errormessage and sets the HTTP statuscode to "temporarily unavailable". that leaves your logic in tact and stops others from misinterpreting the situation.

 

Sounds good! Trying to learn how to do this now.

 

Thanks for warning me about redirecting and e-mailing.

[vinny42]

 

How do you do that? An outline would be very much appreciated.

 

One way of doing it is to append each error to the same file, which will be the "most recent errors" log.

If you write the errors like this:

 

 

[2013-10-25 13:08:23]

FATAL Could not connect o the database.

 

<more details here>

[2013-10-25 13:08:45]

FATAL Out of memory

 

<more details here>

[2013-10-25 13:12:09]

INFO Bad password

 

User xxx tried to login with password yyy.

 

 

Then you can parse the file and recognise every error individually.  That makes it possible to create a list of just the timestamp and the single-line description, which you can then put into an email so the recipient just gets:

 

[2013-10-25 13:08:23] FATAL Could not connect o the database.

[2013-10-25 13:08:45] FATAL Out of memory

[2013-10-25 13:12:09] INFO Bad password

 

Which is easy to interpret; if there are no FATAL lines in the digest then nothing serious is going on (you may want to add the number of FATAL erros in the subject line so the recipient can see whether poop is flying or not, without reading the email itself)

 

The actual sending of the file can be triggered by either a cronjob if you are on linux/un*x/freebsd, or a windows-scheduler job if you are on windows.

 

After creating and sending the digest you can rename the file to the current date and compress it, so the admin can find it again later to look at the details of the messages.

 

Does that give you enough information to get started?

 

 

Thanks for warning me about redirecting and e-mailing

 

Happy to help.

[Fluoresce]

 

One way of doing it is to append each error to the same file, which will be the "most recent errors" log.

If you write the errors like this:

 

 

[2013-10-25 13:08:23]

FATAL Could not connect o the database.

 

<more details here>

[2013-10-25 13:08:45]

FATAL Out of memory

 

<more details here>

[2013-10-25 13:12:09]

INFO Bad password

 

User xxx tried to login with password yyy.

 

 

Then you can parse the file and recognise every error individually.  That makes it possible to create a list of just the timestamp and the single-line description, which you can then put into an email so the recipient just gets:

 

[2013-10-25 13:08:23] FATAL Could not connect o the database.

[2013-10-25 13:08:45] FATAL Out of memory

[2013-10-25 13:12:09] INFO Bad password

 

Which is easy to interpret; if there are no FATAL lines in the digest then nothing serious is going on (you may want to add the number of FATAL erros in the subject line so the recipient can see whether poop is flying or not, without reading the email itself)

 

The actual sending of the file can be triggered by either a cronjob if you are on linux/un*x/freebsd, or a windows-scheduler job if you are on windows.

 

After creating and sending the digest you can rename the file to the current date and compress it, so the admin can find it again later to look at the details of the messages.

 

Does that give you enough information to get started?

 

 

Thanks!

 

That looks like some advanced stuff! I will learn how to do it.

 

For now, shall I just use this?  (Note that my site's new, so I don't have much traffic.  The prospect of receiving lots of e-mails in the event of an error is currently non-existent.)

$conn = mysql_connect("localhost", "", "");
if(!$conn) {
     // Send yourself an e-mail.
     error_log("MySQL connection failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
     // Redirect user to error page.
     header("HTTP/1.1 503 Service Temporarily Unavailable");
     header("Location: mysql-error.php");
     die();
}
$selectdb = mysql_select_db("");
if(!$selectdb) {
     // Send yourself an e-mail.
     error_log("MySQL select database failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
     // Redirect user to error page.
     header("HTTP/1.1 503 Service Temporarily Unavailable");
     header("Location: mysql-error.php");
     die();
}
$query = mysql_query("", $conn);
if(!$query) {
     // Send yourself an e-mail.
     error_log("MySQL failed failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
     // Redirect user to error page.
     header("HTTP/1.1 503 Service Temporarily Unavailable");
     header("Location: mysql-error.php");
     die();
}

[Fluoresce]

$conn = mysql_connect("localhost", "", "");
if(!$conn) {
     // Send yourself an e-mail.
     error_log("MySQL connection failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
     // Redirect user to error page.
     header("HTTP/1.1 503 Service Temporarily Unavailable");
     header("Location: mysql-error.php");
     die();
}
$selectdb = mysql_select_db("");
if(!$selectdb) {
     // Send yourself an e-mail.
     error_log("MySQL select database failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
     // Redirect user to error page.
     header("HTTP/1.1 503 Service Temporarily Unavailable");
     header("Location: mysql-error.php");
     die();
}
$query = mysql_query("", $conn);
if(!$query) {
     // Send yourself an e-mail.
     error_log("MySQL failed failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personaladdress@example.com", "From: help@mysite.com");
     // Redirect user to error page.
     header("HTTP/1.1 503 Service Temporarily Unavailable");
     header("Location: mysql-error.php");
     die();
}

That seems over the top, too.

 

How do you guys handle MySQL errors?

[vinny42]

Well, the major points still stand: don't die, don't redirect.

 

This die() business is only even remotely possible during the initial connection. You simply cannot die() on a query failure because that will completely mess up your page and if you are not extremely carefull; it will mess up your data too.

 

As for the redirect, stop doing that. It just adds a completely useless page into the user's browsing history and when they click "back" to back to the page before they got the error, they actually end up on the page that has the error, and are redirected back to the error message.

 

So again; throw an exception, catch it and print an error. no die(), no redirect, just one script that handles everything.

[Fluoresce]

So again; throw an exception, catch it and print an error. no die(), no redirect, just one script that handles everything.

 

Thanks!

 

Here's what I've got so far:

function mysql_error_handling(){ 
     throw new Exception(); 
} 
try {
     $con = mysql_connect('localhost','user','password') or mysql_error_handling("Connection failed! ");
     mysql_select_db('database') or mysql_error_handling("Select database failed! ");
     $query = mysql_query('SELECT * FROM `table`', $con) or mysql_error_handling("Query failed! "); 
} 
catch(exception $e) {     
     // Display user-friendly message to user.
     echo "<p>Sorry, an error has occurred. The webmaster has been notified.</p>";

     // Log error in file. 
     trigger_error($e->getMessage() . mysql_error(), E_USER_WARNING);

     // Send yourself an email.  Make sure that ignore_repeated_errors
     // is set to On to prevent multiple e-mails on popular website.
     error_log($e->getMessage() . date() . $_SERVER['REQUEST_URI'], 1, "myemail@example.com");    
}
// Continue rest of script.  For example:
while($results = mysql_fetch_assoc($query)) {
     // Do something. 
}

How does that look?

 

You said that I shouldn't send myself an e-mail if there's a MySQL error because, if my website's popular, I'll receive 1,000s of e-mails. Can't I prevent this by simply setting ignore_repeated_errors in my php.ini file to On?

[vinny42]

Uhm... yeah, in a way, that's what I meant.  :-)

 

 

You said that I shouldn't send myself an e-mail if there's a MySQL error because, if my website's popular, I'll receive 1,000s of e-mails. Can't I prevent this by simply setting ignore_repeated_errors in my php.ini file to On?

 

No, firstly because that setting is for PHP's log function, which you are not using, and secondly because you don't want to completely ignore repeating errors, you just don't want to get separate emails for all of them.

[Fluoresce]

Uhm... yeah, in a way, that's what I meant.  :-)

 

 

Okay, here's what I've got now (and I'm quite proud of myself!). This goes at the top of the page, above the HTML:

<?php
    // Create a function that throws an exception.
    function handle_mysql_error($err) {
        throw new Exception($err);
    }
    // Code to try.  If it fails, it will be caught by the 'catch' block.
    try {
        $conn = mysql_connect("localhost", "user", "password") or handle_mysql_error("Connection failed! ");
        mysql_select_db("database") or handle_mysql_error("Select database failed! ");
        // Select data from database.
        $sql = "SELECT * FROM `table`";                    
        $query = mysql_query($sql, $conn) or handle_mysql_error("Query failed! ");
    }
    catch(exception $e) {
        // Set appropriate header for bots.
        header('HTTP/1.1 503 Service Temporarily Unavailable');
        // Initialise user-friendly message to be displayed in body for user.
        $userErrorMessage = "<p>Sorry, this service is temporarily unavailable. The webmaster has been notified.</p>";
        // Log the error in the error log file.
        trigger_error($e->getMessage() . mysql_error(), E_USER_WARNING);
        // Send yourself an email.
        error_log($e->getMessage() . "Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "personalemail@example.com", "From: help@mysite.com");
    }
?>

And this goes in the body of the HTML:

if(isset($userErrorMessage)) {
     echo $userErrorMessage;
}
else {
     while($row = mysql_fetch_assoc($query)) {
          // Do whatever.
     }
}

One problem remains: If there's an error and the site's very popular, I will be sent thousands of e-mails in a short period of time!  Apart from that, it's quite cool, right? 

 

I've checked it and it works perfectly.

[vinny42]

Just one more thing; executing a query can also fail and should also throw an exception. Queries can be eecuted anywhere in the code, so it's common practice to put the entire script inside one big try block, so that all exceptions (if they are not caught by some deeper nested try/catch) jump to the outer-most catch.

 

So quite literally this:

 

try

{

  // do *EVERYTHING* here.

}

catch(your_database_exception $e)

{

  // Catch your custom database exceptions here

}

catch(Exception $e)

{

  // Catch regular exceptions tht have not been caught yet

}

[Fluoresce]

Just one more thing; executing a query can also fail and should also throw an exception. Queries can be eecuted anywhere in the code, so it's common practice to put the entire script inside one big try block, so that all exceptions (if they are not caught by some deeper nested try/catch) jump to the outer-most catch.

 

So quite literally this:

 

try

{

  // do *EVERYTHING* here.

}

catch(your_database_exception $e)

{

  // Catch your custom database exceptions here

}

catch(Exception $e)

{

  // Catch regular exceptions tht have not been caught yet

}

 

Dude, you're killing me! I thought I was finished.