Viewing MySQL View all News

How to Log MySQL Errors in WordPress

MySQL query errors can be difficult to diagnose and correct.

For performance reasons, most MySQL installations fail to retain an error log for posterity; instead, if an error occurs, the reason is passed back to the application during runtime. If that error isn’t captured then and there, it is lost forever.

Development Environments

WordPress contains a special development mode that can be enabled by adding WP_DEBUG, WP_DEBUG_LOG, and/or WP_DEBUG_DISPLAY constants to your configuration. When enabled, PHP errors/warnings/notices and MySQL errors will be logged to wp-content/debug.log and/or printed to the screen.

WordPress’s $wpdb object also provides some debugging functions in case you wanted to debug MySQL issues independently of PHP ones:

  1. $wpdb->show_errors(): this causes MySQL error information to be printed to the screen as it happens. You would call this function before the queries you are looking to debug.
  2. $wpdb->hide_errors(): this reverses the behavior of show_errors() and returns WordPress to its default configuration. You can call this function anytime after executing the questionable queries.
  3. $wpdb->print_error(): this prints the error, if any, from the most recent query.

Production Environments

These tools are probably all you need when developing a new theme or plugin, but you shouldn’t use these under production environments. For one thing, printing random, technical-looking strings in the middle of a document will break the layout and confuse your users. It can also provide interesting information to any bad actors who might be poking around your site. But even if you’re just logging the information, WP_DEBUG_LOG isn’t a great idea: it degrades your site performance and, under most server configurations, exposes way too much information to anyone who knows where to look.

Of course, by the time a site is live, you should have thoroughly debugged everything, so there’s no need to log query failures, right? Well… maybe.

There are a lot of ways to mess up a MySQL query. Chances are, no matter how many times you tested your code during development, you’ll have missed some highly obscure edge case. Even if you didn’t, and everything was coded perfectly, sometimes an update to the WordPress core can subtly change the way a query is structured.

Such a change occurred recently with the release of WordPress 4.4. In prior versions, Null values passed via $wpdb->insert() or the like were typecast according to the type specified. %s would convert a Null value to '', %d to 0, etc. Now, however, Null values are passed as-is to MySQL. For columns with NOT NULL attributes, this can create problems where previously none existed.

So what to do?

Though we were unable to find any documentation, investigation into the WordPress source code revealed that MySQL errors from the current page request are collected in an obscure global variable during runtime, $EZSQL_ERROR.

We can access this variable in a custom PHP function that we then trigger through one of WordPress’ action hooks. Since we want to capture all errors for a given page request, the shutdown action is the best candidate as it triggers just before PHP terminates.

The following example code block does just that. At the end of every WordPress page execution, the function looks to see if any MySQL errors were encountered. If there were any, it combines some basic runtime information (date, page, etc.) with the error details and emails it to the site administrator.

//-------------------------------------------------
// Database logging - query errors
//
// email database query errors to the contact
// specified
//
// @param n/a
// @return n/a

function db_debug_log(){

	//WP already stores query errors in this obscure
	//global variable, so we can see what we've ended
	//up with just before shutdown
	global $EZSQL_ERROR;

	try {
		//proceed if there were MySQL errors during runtime
		if(is_array($EZSQL_ERROR) && count($EZSQL_ERROR)) {
			//build a log entry
			$xout = array();

			//let's start with some environmental information
			$xout[] = "DATE: " . current_time('r');
			$xout[] = "SITE: " . site_url();
			$xout[] = "IP: " . $_SERVER['REMOTE_ADDR'];
			$xout[] = "UA: " . $_SERVER['HTTP_USER_AGENT'];
			$xout[] = "SCRIPT: " . $_SERVER['SCRIPT_NAME'];
			$xout[] = "REQUEST: " . $_SERVER['REQUEST_URI'];
			$xout[] = "\n\n\n\n";

			//and lastly, add the error messages with some line separations for readability
			foreach($EZSQL_ERROR AS $e) {
				$xout[] = str_repeat('-', 50) . "\n" . implode("\n", $e) . "\n" . str_repeat('-', 50);
				$xout[] = "\n\n\n\n";
			}

			//email it!
			//if a plugin overrides the content-type header for outbound emails, change the message body
			//below to nl2br(esc_html(implode("\n", $xout)))
			wp_mail(get_bloginfo('admin_email'), '[' . get_bloginfo('name') . '] DB Error', implode(“\n”, $xout));
		}
	} catch(Exception $e){ }

	return;
}
add_action('shutdown', 'db_debug_log');

If email isn’t desirable, whether for reasons of security or practicality, the general idea could be altered to push data via error_log() or write the contents to any arbitrary log file (preferably in a non-web-accessible location).

These techniques can help make hunting down elusive MySQL errors easier. With a proper record in place, developers can see what went wrong and where, and find a solution more quickly.

Posted By
Josh Stoik

WordPress Object Cache

It is not unusual for WordPress to run dozens or even hundreds (yikes!) of database queries when putting together a page. What’s worse, these queries aren’t the sort from children’s bedtime stories. They’re the dirty ones, full of temporary tables, JOINs on JOINs on JOINs, generic data types, and insufficient indexing. They’re the MySQL equivalent of The Wire.

As traffic loads increase, these computationally-heavy operations can quickly lead to a bottleneck, making your site sluggish or bringing it offline altogether.

To get a better idea of your own database use (or abuse), install a plugin like Query Monitor, which puts detailed query stats in the admin toolbar.

If you’re nearing the limit of what MySQL can handle, there are a few basic things you should consider doing:

  1. Go through your plugins and deactivate and delete anything that isn’t being used.
  2. Set reasonable post limits (10 or fewer) for archive pages.
  3. If you have a custom theme, try to minimize the number of secondary queries being run inside The Loop.

Beyond that, well, that’s the purpose of this article: Object Caching!

WordPress has the ability to cache query results (and other key/value data) in memory for later retrieval so it doesn’t have to pester MySQL on repeat requests.

This can dramatically reduce the load on MySQL, while maintaining comparable speed.

The main disadvantage of Object Cache is that objects are, well, cached. This means that changes made in the backend might not be immediately reflected on the front end.

This can be a deal breaker for applications with transactional data like e-commerce sites, unless the code contains strategic calls to wp_cache_flush(). But for content-driven sites like blogs and portfolios, it can be the perfect solution.

Object Cache also takes over handling of “transient” data, which otherwise get stored in the wp_options table. This will both speed up plugins and themes that rely on the transient API, and also reduce bloat in the database (there is no automatic garbage collection process run against wp_options, so data remains forever, and ever, and ever… (unless the same cache key is requested again)).

WordPress Object Caching is designed with extensibility in mind. All it needs is an API for key/value storage and retrieval.

There are innumerable candidates for the job, so let’s just focus on some of the most common:

XCache

XCache is my favorite PHP opcode cacher (opcode cache, incidentally, will also speed up the execution of your PHP scripts in general).

It is lightweight, scalable, and supports the latest versions of PHP.

To get started, install the XCache PHP extension (available in most Linux repositories), tweak the INI settings* (variable cache is the relevant feature here), and restart PHP.

After that, you can then enable Object Caching in WordPress by installing XCache Object Cache Backend or a more comprehensive caching solution like W3 Total Cache.

If you have multiple sites running on the same server, you need to ensure you are running version 3.0.3 or later.

The XCache extension comes bundled with admin scripts you can copy to your web root to get an idea of how cache is being utilized. You should install this after it has been running for a while to see if the memory allocations need to be raised or lowered (just make sure you restrict access, and delete it when you’re done!). Remember, the memory settings are per-process, so don’t get carried away.

APC

APC is another PHP opcode cacher, but it is no longer actively developed.

Unless you are running an older version of PHP or your hosting environment comes bundled with it, XCache is a better option.

To get started, install the APC PHP extension, tweak the INI settings, and restart PHP. You can install a standalone plugin like APC Object Cache Backend or, again, go with something like W3TC.

There remains, however, one excellent use case for APC: Facebook’s HHVM engine supports it out of the box! If you have already replaced PHP with HHVM, just install the APC Object Cache Backend plugin and you’re good to go.

Redis

Redis is a powerful key/value caching solution with support for multiple servers.

For complex web applications consisting of multiple servers, it is an ideal solution as the cache can be shared across more than one machine.

For more simple setups, it is a bit much, and noticeably slower than XCache or APC.

To get started, install the Redis server and PHP extension (if you are running HHVM instead of PHP, the “extension” is built-in). Once you have that configured, you can install the Redis Object Cache plugin.

Posted By
Josh Stoik