Dev Notes

Software Development Resources by David Egan.

Manage and Safely Delete Revisions in WordPress


MariaDB, MySQL, WordPress
David Egan

In my opinion, Revisions in WordPress are a waste of time. I don’t know anyone that actually uses them - and worse, they can have a serious impact on site performance, especially in the admin area.

Revision Management Deleting: There is an API function to delete revisions, but there is no UI. That can certainly change. WordPress Codex, Revisions

Personally, I find this ridiculous - it means that for the average user every time a post is updated the database gets a bit more bloated - and there is no UI to remove this bloat.

When to be Concerned

You may hit a 500 server error when trying to access an edit page for content that has a lot of associated revisions.

Editing a reasonably sized page with hundreds of revisions can cause problems, even with 256MB server memory available. This trac ticket outlines the problem nicely. I had to deal with this exact scenario today, which prompted this post.

Enabling error logging may give you a message like this:

[14-Mar-2018 10:43:37 UTC] PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /var/www/html/example.com/web/wp/wp-includes/meta.php on line 840

…you’ll find a similar message in your Apache logs.

Increase Available Memory

This is a band-aid solution, but increasing the available memory can provide an instant fix, and buy some time. Under Ubuntu, edit /etc/php/7.0/apache2/php.ini:

; Maximum amount of memory a script may consume (was 128MB, now 256M)
; http://php.net/memory-limit
; Bump up the memory, within reasonable bounds
memory_limit = 256M

IMPORTANT: Restart Apache for the changes to take effect. You could also add a directive to a .htaccess file if you’re using one, or add a directive in an Apache virtual host configuration.

Now that the client has stopped panicking, you can fix the real problem: Revisions.

TLDR; Delete Revisions

You need MySQL access to delete revisions.

Get a MySQL command prompt, using your target database:

mysql -u root -p my_database

Unfortunately, it’s not quite as simple as just deleting posts from wp_posts table having a post_type of ‘revision’ - you should also remove associated postmeta and wp_term_relationships records.

You can achieve this with the following MySQL/MariaDB commands:

DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';

This command deletes records from tables a, b and c - defined as wp_posts, wp_term_relationships and wp_postmeta. The rows deleted are determined by these criteria:

  1. Rows in wp_posts table which have a post_type of ‘revision’
  2. Rows in wp_term_relationships which have an object_id equal to the ID of a wp_posts ‘revision’ row.
  3. Rows in wp_postmeta which have a post_id equal to the ID of a wp_posts ‘revision’ row.

For a custom agency build with a lot of custom fields, number 3 is likely to be a big one. For each revision saved, WordPress will add a new postmeta record for each custom field.

Prevent Revisions

Limit revisions to a rational number, or prevent them altogether - add this to wp-config.php:

define('WP_POST_REVISIONS', 10); // Older revisions will be automatically deleted
define('WP_POST_REVISIONS', FALSE); // No revisions at all

Resources


comments powered by Disqus