How Do I Log PHP and MySQL Errors?

error handling and error reporting

[Tehuti]

I have lots of PHP and MySQL on my website. At the moment, they're all working just fine.

How do I find out if, one day, one of the PHP codes or MySQL statements stops working?

In cPanel, I have error logs. I heard that I can keep informed of errors by getting PHP to log errors in there. What's the proper way of doing this?

I heard that I have to configure my php.ini file and use trigger_error in my code, but I can't find any clear instructions.

Any advice will be appreciated.

[Readie]

trigger_error in your code is if you want to manually throw an error - it doesn't really have much to do with general script maintenance.

Getting PHP to log errors into your error logs can be achieved a number of ways.

- In your php.ini file, find (or add if needed) the error_reporting flag, and set its value [php.net] appropriately
- Adding a couple of lines to your .htaccess file (if there is one)

php_value error_reporting <<INTEGER SEE LINK IN PREVIOUS OPTION>>
php_flag log_errors on

- Adding a function call to all relevent PHP scripts (not 100% - some errors prevent the execution of the function if it's in the same file and thus the error is not logged)

error_reporting(<<CONSTANT SEE LINK IN FIRST OPTION>>);

[Tehuti]

Thanks, Readie. I finally know what I have to do.

I went into my php.ini file and set these settings:

error_reporting = E_ALL
display_errors = Off
log_errors = On
error_log = [file_name]

Now, if there is an error, an error report is written in the appropriate file. The error doesn't appear on the Web page because display_errors is turned off.

The function trigger_error() isn't necessary to log the errors. In fact, if you use trigger_error() in situations like this

$con = mysql_connect("","","");
if(!conn) {
  trigger_error("Connection failed" . mysql_error())
}

two errors will be logged, one by the system and one by trigger_error(). From what I understand, you are supposed to use trigger_error() to trigger and log errors in instances when PHP doesn't automatically log errors.

My errors are logged in a file that appeared by itself. PHP must have created it. Initially, the file only contained errors. It's because error_reporting was set to "E_ALL & ~E_NOTICE", which means that all errors were to be logged (E_ALL), but notices were not to be logged (& ~E_NOTICE). When I deleted "& ~E_NOTICE" to allow notices to also be logged, I was bombarded with notices! It took me a day to clear everything up. I learnt loads.

I then made some changes to my scripts. Now, if there's a MySQL error in the future, not only is the error going to be logged but I am also going to be sent an e-mail. Here's how I done it:

$con = mysql_connect("", "", "");
if(!$con) {
  echo "<p>User-friendly message for user.</p>";
  error_log("MySQL connection failed! Date: " . date("l jS \of F, Y, h:i:s A") . ". File: " . $_SERVER['REQUEST_URI'], 1, "[e-mail]", $headers);
}
else {
 // Rest of code
}

How do you guys protect against possible future MySQL errors? Is it worth using the same code for every instance of mysql_connect(), mysql_select_db() and mysql_query()?

[Readie]

Good to hear you're learning Tehuti :)

For catching future errors...

A solution that I kinda liked was to create a custom error handler function using set_error_handler [php.net], and to create a wrapper function for the db query, check the return from the db query function, and use trigger_error if there was a problem. My error handler would then fire me an Email containing details of the error, GET/POST data, request URI etc - everything I needed to reproduce the error.

---

By the way... the mysql_ set of functions is deprecated as of PHP 5.5. Take a look at the mysqli [php.net] implementation - it offers a greatly enhanced feature set (including paramaterised queries) - and I'm told it has faster performance (not verified that for myself being a Postgres fan :))

[Tehuti]

Thanks, Readie.

I have learnt that the method that I posted above isn't wise. It would be silly of me to send myself an e-mail every time that there is a MySQL error. If my website becomes popular, I would receive a few hundred thousand e-mails in the space of a few hours!

Apparently, I have to use PHP exceptions to handle MySQL errors. I'm not sure what that exactly means at the moment, but I am excited to find out.

Do you know how to handle MySQL errors with exceptions? I have checked online, but I haven't found a clear explanation.

What I don't understand is, if I use trigger_error() to log MySQL errors on a popular website, wouldn't thousands of errors be logged in a short period of time, and wouldn't that be as problematic as sending yourself thousands of e-mails? How can logging errors with trigger_error() be managed?

Handling MySQL errors (i.e., logging them and making sure that you are notified of them) is becoming quite a puzzle (for me, at least).

[Tehuti]

Okay, here's what I've got so far. 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, "[e-mail]", "From: [e-mail]");
 }
?>

And this goes in the body of the HTML:

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

I've checked it and it works perfectly. However, 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!

How can I prevent that from happening? Any ideas will be much appreciated.

[Readie]

Hmm. I don't think there is a fool proof way of preventing that from happening. The most obvious answer is not the friendliest, but I will say it anyway: code it well. Test it well. It shouldn't need to send the Email in the first place.

But, being in the industry for just a few years has taught me that it's pretty much impossible to catch everything, so I'll try and give a friendly answer too :)

There is an alternative to set_error_handler - and that is to use register_shutdown_function [php.net] and try and pick up on errors in there (possibly with error_get_last() maybe?). This would atleast mean that you would be limited to one email per page request.

Only problem with that is the shutdown function is a bit cumbersome to use - can't rememeber the exact extent of it, but pretty much everything is gone (superglobals, debug_backtrace() etc) - unset. You need to pass everything you need into the function.

---

Or... Error logging. Assuming you're running Linux (although the general logic behind everything in this suggestion is possible on Windows) - you could have a daily cron job grepping the error log file, and send you an Email alert to check the error log.

So, say you wrote your mysql errors to the file /var/log/httpd/mysql.log, with the date format of YYYY-MM-DD HH-mm-ss, you could tie it in to a simple PHP script:

<?php

if(trim(shell_exec('cat /var/log/httpd/mysql.log | grep -m 1 "' . date('Y-m-d') . '"')) != '') {
  mail('[e-mail]', 'MySQL errors found', 'MySQL errors found on site example.com', "From: [e-mail]");
}

?>

You just then have to do something to stop the mysql error log from getting too big :) But there's plenty of solutions around for log management and a quick Google should provide you with some (Not going to offer my own suggestion as I've always been fortunate enough to either be friends with my hosts, or work for a company with sysadmins - so I've never had to do log management myself :)).

[swa66]

I've checked it and it works perfectly. However, 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!

How can I prevent that from happening? Any ideas will be much appreciated.

Rate limiting...

e.g.
- instead of sendign the email, store the errors in persistent storage (append it)
- add a cron job o regularly check the storage and send out one message
You then choose yourself how often you can get an email .... (and how much delay you tolerate).

[Tehuti]

Sounds good, Swa66. I have to check that out.

Thank you both for your input.