Viewing PHP 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 Multi-Parameter Search

For our work on the Lichter Realty website, we were tasked with building a multi-parameter property search.

Lichter wanted their clients to be able to filter properties based on multiple criteria: contract type, property type, square footage, and availability.

Screen Shot 2015-09-28 at 6.58.55 AM

 

Since the Lichter site is built in WordPress, to achieve this we had to build out a way to dynamically modify WP Query based on the user’s selections. To make this easier, all of the properties have multiple taxonomies to handle these various parameters, and a set of custom fields to manage availability.

First, let’s build out a form so users can make their selections. We’re using GET as our method so a filtered search ends up with shareable URLs. GET is the default, but I like to set it anyway to make it easier to figure out what’s going on at a glance.

Most of the parameters are using checkbox inputs so we can select multiple values. The exceptions are contract type and availability, which have all options, so we can use radio buttons.

The checkbox options get input names with [] at the end to make sure our values get saved in an array. The values for each input is the ID of the associated taxonomy term, except in the case of the all options.

Note: In the live site, there is more markup for presentational purposes, but for simplicity I’ve stripped that out here so we can focus on the inputs.

<form method="get" class="property-filters">
	
	<!-- CONTRACT TYPE -->
	<fieldset class="contract-type">
		<div class="accent-sans">View properties that are:</div>

		<input type="radio" id="contract-type--sale" name="contract-type" value="30" />
		<label for="contract-type--sale">For Sale</label>
		
		<input type="radio" id="contract-type--lease" name="contract-type" value="31" />
		<label for="contract-type--lease">For Lease</label>

		<input type="radio" id="contract-type--all" name="contract-type" value="all" checked />
		<label for="contract-type--all">All</label>
	</fieldset>

	<!-- PROPERTY TYPE -->
	<fieldset>
		<div class="accent-sans">Property Type:</div>
		
		<input type="checkbox" id="property-type--industrial" name="property-type[]" value="4" />
		<label for="property-type--industrial">Industrial</label>

		<input type="checkbox" id="property-type--flex-space" name="property-type[]" value="6" />
		<label for="property-type--flex-space">Flex Space</label>

		<input type="checkbox" id="property-type--loft-office" name="property-type[]" value="5" />
		<label for="property-type--loft-office">Loft/Office</label>

		<input type="checkbox" id="property-type--commercial" name="property-type[]" value="3" />
		<label for="property-type--commercial">Commercial</label>

		<input type="checkbox" id="property-type--land" name="property-type[]" value="27" />
		<label for="property-type--land">Land</label>
	</fieldset>

	<!-- SQUARE FOOTAGE -->
	<fieldset>
		<div class="accent-sans">Square Footage:</div>

		<input type="checkbox" id="sqft--5kless" name="sqft[]" value="7" />
		<label for="sqft--5kless">5k &amp; Less</label>

		<input type="checkbox" id="sqft--5k10k" name="sqft[]" value="8" />
		<label for="sqft--5k10k">5K - 10K</label>

		<input type="checkbox" id="sqft--10k15k" name="sqft[]" value="9" />
		<label for="sqft--10k15k">10K - 15K</label>

		<input type="checkbox" id="sqft--15kgreater" name="sqft[]" value="10" />
		<label for="sqft--15kgreater">15k &amp; Greater</label>
	</fieldset>

	<!-- AVAILABILITY -->
	<fieldset>
		<div class="accent-sans">Availability:</div>

		<input type="radio" id="availability--now" name="availability" value="now" />
		<label for="availability--now">Now Available</label>

		<input type="radio" id="availability--soon" name="availability" value="soon" />
		<label for="availability--soon">Coming Soon</label>

		<input type="radio" id="availability--all" name="availability" value="all" />
		<label for="availability--all">All Units</label>
	</fieldset>

	<button type="submit" class="btn red">Filter</button>
</form>

You might have noticed above that availability works a bit differently than the other options. Availability is a date based filter. In the backend, we have custom fields (lovingly created through the help of Advanced Custom Fields Pro).

The first field is a checkbox to indicate that there is availability at this property, or soon will be. This is so completely unavailable properties can be left out of the listings entirely. Once that’s checked, a date field is presented, and the administrator can pick the availability date.

If the date is today or before, that property is considered available now. If the availability date is after today, it’s considered available soon. We’ll see how these work in the query later on.

Now that we have the form set up, we can work on our query.

First, let’s grab the paged variable and then reset the query so we can use it however we want. We want to make sure to capture the paged variable before the reset, otherwise it just won’t be there!

$paged = ( get_query_var('paged')  ? get_query_var('paged') : 1 );
$wp_query = null;
$wp_query = new WP_Query();

Now we can set up base arguments for our query. These arguments are used for the initial state of the properties page, as well as the base for the filtered searches.

// BASE ARGS

$args = array(
	'post_type' => 'property',
	'orderby' => 'meta_value_num',
	'order' => 'ASC',
	'meta_key' => 'availability_date',
	'meta_compare' => 'EXISTS',
	'meta_query' => array(
		array(
			'key' => 'available_now__soon',
			'value' => true,
			'compare' => 'EXISTS',
		),
	),
	'paged' => $paged
);

We’re using the first meta_key and meta_compare arguments to make sure that the availability date field actually is present. We can then set orderby to meta_value_num to order the properties by their availability date. Then we use a meta query to further ascertain that the available now or soon flag is set to true.

A property could have once had an availability date set, but then had their availability turned off. In this case, the availability date field would still evaluate as existing, so we need this double check.

Technically, we don’t need a checkbox at all to toggle availability. Instead, we could have instructed the site administrators to leave the date field blank for unavailable.

But this is a less intuitive interface for them, so we’ve included the checkbox for added usability, even if it makes our query slightly more complicated.

Now, let’s check if anything has been searched, or if we can just go along our merry way. We’re going to need to check whether each parameter has been toggled more than once in the following code, so let’s save the answers in variables before we start our checks. We want to check:

  1. if the array key exists in GET for this parameter
  2. if we’re expecting the values to be an array (all the radio inputs in our form), verify that it is an array and
  3. if it is an array, that there is at least one value present in the array

If all the appropriate criteria are met, the variable is set to true.

// ARE WE FILTERED?

$property = (array_key_exists('property-type', $_GET) && is_array($_GET['property-type']) && count($_GET['property-type']));
$sqft = (array_key_exists('sqft', $_GET) && is_array($_GET['sqft']) && count($_GET['sqft']));
$availability = (array_key_exists('availability', $_GET));
$contract = (array_key_exists('contract-type', $_GET));

While we’re setting up variables, let’s set one up to help us keep track of whether or not any of the taxonomy-based filters have been toggled. This will help us check whether or not we need to add a tax query to WP Query later on.

// set up an array to hold our active taxonomy based filters
$taxables = array();

Now that we have our variables set up, let’s open up a big if statement. We’re going to check if any of the filters have been toggled. All of our query modifications will happen inside this if statement.

You’ll notice that for availability and contract type, we not only check their existence, but if they’re set to all. all is the default, so it technically counts as untoggled.

if($property || $sqft || ($availability && $_GET['availability'] !== 'all') || ($contract && $_GET['contract-type'] !== 'all')) {

}

If any one of the above criteria are met, we move forward.

Before we get going with the heavy lifting, let’s refresh our memories about taxonomy queries. They generally look like this:

$args = array(
	'tax_query' => array(
		'relation' => 'AND',
		array(
			'taxonomy' => 'movie_genre',
			'field' => 'slug',
			'terms' => array( 'action', 'comedy' ),
		),
		array(
			'taxonomy' => 'actor',
			'field' => 'term_id',
			'terms' => array( 103, 115, 206 ),
		),
	),
);
$query = new WP_Query( $args );

We now know that a tax query is an array, with each taxonomy filter its own array within that, and that we should set the relation to AND in case we have more than one. Let’s set up a variable to hold our tax query parameters:

$tax_query = array('relation' => 'AND');

Now let’s get property and square footage going. They work exactly the same way except we’ll pass different values to them. We want to iterate through all the values passed in GET for that parameter and add them to a $terms array.

We can then compose an array inside $tax_query with the taxonomy and the values needed. We’re also adding our check value ($property or $sqft) to the $taxables array.


// IF PROPERTY TYPE FILTER IS SELECTED

if($property) {
	$taxables[] = $property;
	$terms = null;

	foreach($_GET['property-type'] as $property_type) {
		$terms[] = $property_type;
	}

	$tax_query[] = array(
		'taxonomy' => 'property_type',
		'field' => 'term_id',
		'terms' => $terms
	);
}

// IF SQUARE FOOTAGE FILTER IS SELECTED

if($sqft) {
	$taxables[] = $sqft;
	$terms = null;

	foreach($_GET['sqft'] as $sqft) {
		$terms[] = $sqft;
	}

	$tax_query[] = array(
		'taxonomy' => 'square_footage',
		'field' => 'term_id',
		'terms' => $terms
	);
}

Contract type works similarly, except we also need to check that the value isn’t all before proceeding, and we don’t need to iterate through terms since availability isn’t an array.

// IF CONTRACT TYPE FILTER IS SELECTED

if($contract && $_GET['contract-type'] !== 'all') {
	$taxables[] = $contract;

	$tax_query[] = array(
		'taxonomy' => 'contract_type',
		'field' => 'term_id',
		'terms' => $_GET['contract-type']
	);
}

Now that we’ve run through all the possible taxonomy-based filters, we can use that $taxables array. We had been adding items to it if a given taxonomy parameter had values toggled within it. Now we can use that to check if we need to add our $tax_query to the our query arguments.

// IF ANY TAXONOMY FILTERS ARE SELECTED, WE ADD THE TAX ARGS TO OUR QUERY
if(count($taxables)) {
	$args['tax_query'] = $tax_query;
}

All that’s left is availability. For availability, we’re going to be adding to the existing meta query. We want to pass today’s value and the availablity_date key. We have to make sure to set the type to date or our comparisons won’t work properly. The comparison will vary between less than or equal to for now or greater than for soon.

// IF AVAILABILITY FILTER IS SELECTED

if($availability && $_GET['availability'] !== 'all') {
	$meta_query = array(
	'key' => 'availability_date',
	'value' => date('Y-m-d', current_time('timestamp')),
	'type' => 'date'
	);

	if($_GET['availability'] === 'now') {
		$meta_query['compare'] = '<=';
	}

	else {
		$meta_query['compare'] = '>';
	}

	$args['meta_query'][] = $meta_query;
}

And that’s it for our if statement. Let’s take a look at it all together:

if($property || $sqft || ($availability && $_GET['availability'] !== 'all') || ($contract && $_GET['contract-type'] !== 'all')) {

	$tax_query = array('relation' => 'AND');


	// IF PROPERTY TYPE FILTER IS SELECTED

	if($property) {
		$taxables[] = $property;
		$terms = null;

		foreach($_GET['property-type'] as $property_type) {
			$terms[] = $property_type;
		}

		$tax_query[] = array(
			'taxonomy' => 'property_type',
			'field' => 'term_id',
			'terms' => $terms
		);
	}


	// IF SQUARE FOOTAGE FILTER IS SELECTED

	if($sqft) {
		$taxables[] = $sqft;
		$terms = null;

		foreach($_GET['sqft'] as $sqft) {
			$terms[] = $sqft;
		}

		$tax_query[] = array(
			'taxonomy' => 'square_footage',
			'field' => 'term_id',
			'terms' => $terms
		);
	}


	// IF CONTRACT TYPE FILTER IS SELECTED

	if($contract && $_GET['contract-type'] !== 'all') {
		$taxables[] = $contract;

		$tax_query[] = array(
			'taxonomy' => 'contract_type',
			'field' => 'term_id',
			'terms' => $_GET['contract-type']
		);
	}


	// IF ANY TAXONOMY FILTERS ARE SELECTED, WE ADD THE TAX ARGS TO OUR QUERY

	if(count($taxables)) {
		$args['tax_query'] = $tax_query;
	}
	

	// IF AVAILABILITY FILTER IS SELECTED
	
	if($availability && $_GET['availability'] !== 'all') {
		$meta_query = array(
			'key' => 'availability_date',
			'value' => date('Y-m-d', current_time('timestamp')),
			'type' => 'DATE'
		);

		if($_GET['availability'] === 'now') {
			$meta_query['compare'] = '<=';
		}

		else {
			$meta_query['compare'] = '>';
		}

		$args['meta_query'][] = $meta_query;
	}
}

After that, all we need to do is add call a new query based on our arguments:

$wp_query->query($args);

And that’s it! It even works with that mysterious devil, pagination.

Be sure to check out the live search on Lichter Realty.

Posted By
Tiffany Stoik

HHVM: The Gift That Keeps On Giving

Over the past 20 years, PHP has become the de facto server-side programming language of the web.

From leading open-source software like WordPress to some of the Internet’s most heavily-trafficked sites (Facebook, Twitter, and Wikipedia to name a few), you’ll find it everywhere.

But its developer-friendly syntax comes at a cost.

Before any Web Magic™ can happen, the human-readable PHP source code must be run through an interpreter. This process is expensive (particularly in terms of memory usage) and time-consuming. Ultimately, this sets an upper-bound on the number of simultaneous page requests a given server can handle. As a site nears this ceiling, it becomes necessary to either make the site more efficient, or upgrade the hardware.

For some sites, the answer is to sidestep PHP altogether and implement static page caching. But for others, where dynamic processing is unavoidable, the solution might lie in the interpreter itself.

As system administrators know, there is no shortage of PHP interpreters on the market.

Each has its own quirks, configurations, and compatibility issues, so there is no one-size-fits-all solution. But with a little experimentation and a lot of testing, performance gains can be substantial.

One of the most promising interpreters available today is the Hip-Hop Virtual Machine (HHVM). Developed in-house by Facebook, the name of the game for HHVM is speed. For one thing, HHVM meets the machine halfway, converting PHP source code into an intermediate bytecode (this bytecode, then, is what’s left to dynamically interpret at runtime).

This is similar to what PHP extensions like Xcache bring to the table. Another trick up its sleeves: HHVM uses a single multi-threaded process to handle requests (unlike, for example, PHP-FPM, which spawns multiple processes). This leads to much more stable CPU and memory usage for servers operating under heavy loads.

A couple months ago, one of the more resource-heavy sites managed by Bright Bright Great was suffering from a substantial increase in traffic. While publicity is one of the better problems to have, it did pose difficulties for the server, which simply wasn’t configured for that level of interest.

After some (read: lots of) compatibility testing, we successfully transitioned from PHP-FPM to HHVM. The results were astounding.

The average DOM-Ready time dropped by 46%. Simultaneous page serves increased by 112%. And CPU usage under load, once maxed out, remained stable at a healthy 55%.

In fact, the PHP gains were so successful the bottleneck was shifted to MySQL. After some code rewrites to help the site take advantage of object caching (via HHVM’s implementation of APC), the simultaneous page serving capacity was nearly doubled again.

But wait, there’s more!

As part of HHVM’s regular development cycle, its engineers are locked in a room with a surfeit of junk food and coffee (I imagine) and tasked to further optimize the code already in place. By focusing on improvements that directly benefit widely-used software, the real-world impact of any gains made is maximized. With the recent release of version 3.8, HHVM was able to serve 9.7% more pages per second for WordPress, 4.8% for MediaWiki, and 13.9% for Drupal8 (uncached).

We ran quick before and after tests ourselves (against the site previously mentioned) using the command line program siege. Sure enough, the upgrade was able to serve 4.3% more pages per second.

HHVM is indeed the gift that keeps on giving!

Posted By
Josh Stoik