Clean up a bloated wp_commentmeta table

Spam comments are the worst. Event with Akismet active to prevent them from making it to your site, they can still cause problems. That’s because Akismet creates comment meta entries every time it does something. Over the course of time those comment meta entries can amount to hundreds of megabytes even on a reasonably small site. Multiply that by a couple hundred sites on a multisite network, and it can quickly start costing you real money in terms of hosting and causing serious problems when you back up your database.

Fortunately, MySQL can help you eliminate all this comment meta bloat with a simple query:

DELETE FROM wp_commentmeta WHERE meta_key LIKE "akismet_%";

As always, make sure to back up your database before running any destructive queries on it.

Once you’ve deleted all of those Akismet meta entries, make sure to optimize the database table.

OPTIMIZE TABLE wp_commentmeta;

If you’re running a multisite network, you’ll have to run both the query and the optimization for each site, or at least each site which is causing problems. To determine which tables are the most bloated, you can run a query like this:

SELECT table_name AS "Tables", 
    round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
    FROM information_schema.TABLES 
    WHERE table_schema = "usmblogs" AND table_name LIKE "%commentmeta"
    ORDER BY (data_length + index_length) DESC;

This will list all commentmeta tables sorted by size, from largest to smallest. Once you have a list of all tables you want to clean up, you can run the DELETE and OPTIMIZE queries for each table. Or, you could write a script to regular clear this generally useless data from your database at regular intervals.

Debug WP-Cron with Trigger Scheduled Events

I’ve added a new plugin to the directory called Trigger Scheduled Events, which does pretty much what it says on the tin. With it, you can view a list of all events scheduled by WP-Cron and run any of them instantly instead of waiting until the next time the event is scheduled to be fired.

WP-Cron is useful because it allows you to schedule events to happen later using the wp_schedule_event() function, but during development this can lead to a lot of waiting around to see if your code works. Trigger Scheduled Events gets around this problem by allowing you to run your events on demand.

Get it!

You can download the plugin through the Add New menu from your WordPress admin or you can download it from the plugin directory. If you’d like to make suggestions for improvements, you can do that over at the plugin’s GitHub page.

SSL error when upgrading a WordPress Multisite Network

When updating to WordPress 4.0, I recently saw a scary-looking error when running the database upgrade on a network with some SSL sites. It triggered when trying to update tables for a site with an SSL certificate.

Warning! Problem updating Your server may not be able to connect to sites running on it. Error message: SSL certificate problem, verify that the CA cert is OK. Details: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

To be totally honest, I’m not exactly sure why this happens, but I was able to find a way to fix it by telling WordPress to ignore SSL checks when running the database upgrade. Create a file in wpcontent/mu-plugins/ and name it something like network-upgrade-ignore-ssl.php. Put the following in the file:

add_filter('https_ssl_verify', '__return_false');
add_filter('https_local_ssl_verify', '__return_false');

That’s it! You should now be able to get through your upgrade smoothly.

Shopify Image Variant Swap on iOS

A while back I was building a Shopify store for a client and I had used the Variant Images app to assign photos to individual product options. This app is pretty cool, but it has a couple of drawbacks. The dealbreaker for me was that its javascript doesn’t work at all on iOS currently.

In order to get image swapping working on iOS, I ended up writing my own simple script to detect when an option is changed, look for an image to match, and swap it into place. Implementation is pretty simple.

  1. Create your product options.
  2. Upload your images and set the ALT text to match the value of the option. If you’re selling t-shirts and you have an option called Color with variants Red, Green, and Blue, you would upload an image for each and set the ALT text on the images to “Red”, “Green”, and “Blue” in order to match them up.
  3. Insert the following code into the bottom of templates/product.liquid:
{% comment %}
Custom Image Swap - Image name must match option value…nt-swap-on-ios/

{% endcomment %}
        // create an associative array of images, using alt text as key
        var product_images = new Array();
        {% for image in product.images %}
            product_images['{{ image.alt | escape }}'] = '{{ image | product_img_url: 'original' }}';
        {% endfor %}

        // change the featured image source
        jQuery('.product-options select').change(function(){
            if ( jQuery.inArray( jQuery(this).val(), product_images ) ) {
                jQuery('.image.featured img').attr('src', product_images[jQuery(this).val()] );

Now your images should be swapping when you select a new option. As an added bonus, this script works with Shopify’s image zoom function. And since it’s pretty straightforward jQuery, if you need it to function slightly differently it’s pretty easy to modify. If you’d like to see it in action, check out the demo store.

Remove a specific item from an array in PHP

Following from yesterday’s more-specific post about removing items from the WordPress editor, here’s an easy way to remove a specific element from a PHP array by key instead of index:

function my_remove_array_item( $array, $item ) {
	$index = array_search($item, $array);
	if ( $index !== false ) {
		unset( $array[$index] );

	return $array;


$items = array( 'first', 'second', 'third');
$items = my_remove_array_item( $items, 'second' ); // remove item called 'second'

This works by searching the array for the specified item, returning its key, and then unsetting that key.

Remove a specific TinyMCE item from the WordPress editor

When filtering a row of TinyMCE buttons, here’s a way to remove a specific button by name. This is useful for cases where you’re editing the default editor instead of declaring your own editor. In this example, we’ll remove the underline button from the second row:

function my_mce_buttons_2( $buttons ) {
	$index = array_search( 'underline', $buttons );
	if ( $index !== false ) { unset( $buttons[$index] ); }

	return $buttons;
add_filter( 'mce_buttons_2', 'my_mce_buttons_2' );

Drop that code in your functions.php file to remove a specific item from your WordPress editor buttons. For more information about the individual TinyMCE toolbars available see this codex page. To list all buttons by name for a given toolbar, temporarily add print_r( $buttons ); to your filter callback function.

Determine if a WordPress post or page has children

Here’s a simple function to determine if a post, page, or custom post has children in WordPress. It works by getting the children of the current post and returning a count. It will return 0 (false) if there are no children and some positive integer (true) if there are children.

function has_children() {
	global $post;
	return count( get_posts( array('post_parent' => $post->ID, 'post_type' => $post->post_type) ) );

Use it in your page or post templates:

if ( has_children() ) {
	// do something if this item has children

Clean up a bloated wp_term_relationships table

Sometimes the wp_term_relationships table becomes bloated with many orphaned relationships. This happens particularly often if you’re using your site not as a blog but as some other type of content site where posts are deleted periodically. I recently worked on a site that had 18,000 term relationships for posts that no longer exist, and it was slowing the site down. In my case it was the way a real estate plugin from Placester IDX manages real estate listings, but you may find similar problems caused by other custom functionality.

Fortunately, MySQL can help you find these orphaned relationships with a pretty simple query:

SELECT * FROM wp_term_relationships
    LEFT JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID
    WHERE wp_posts.ID is NULL;

It can even automatically delete the orphaned entries for you! Make sure you back up your database before running the following query:

DELETE wp_term_relationships FROM wp_term_relationships
    LEFT JOIN wp_posts ON wp_term_relationships.object_id = wp_posts.ID
    WHERE wp_posts.ID is NULL;

The number of rows deleted should match the number of rows returned in the SELECT query above.

Once you’ve deleted the unnecessary items, make sure to optimize the database table.

OPTIMIZE wp_term_relationships;

Prevent WordPress from guessing if users hit a 404 error

WordPress has a feature called Canonical Redirects which attempts to make sure that users always end up on the one true URL for a given request. That’s great for SEO. Built in to this feature, however, is something that mystifies a lot of developers: when a user reaches a 404, WordPress will use some fuzzy matching try to guess what they meant and redirect them.

To me, this is pretty much the polar opposite of a canonical redirect. I’d much rather have 404s result in a helpful 404 page which I can track in analytics. It can also be very confusing when you’re trying to add your own rewrite rules to WordPress.

Fortunately, you can filter the Canonical Redirect to prevent this strange 404 behavior. Add this to functions.php in your theme:

function stop_404_guessing($url) {
	if (is_404()) { return false; }
	return $url;
add_filter('redirect_canonical', 'stop_404_guessing');

Now WordPress will continue redirecting to the canonical URL, unless you hit a 404 in which case it will display your 404 page as expected.