Photo courtesy of Amazing Travel Photos
Ever since Wordpress 2.6, the editor would automatically save backups and revisions for every wordpress post. In a few cases, there are good reasons, to save every revision. For instance, when more than one person is collaborating on a single document. Or when different people handle the writing and the editing, its desirable to keep track of who added what and when.
But in many cases, saving every revisions is not necessary. The extra revisions just waste space and add overhead to the tables in the MySQL database. Unfortunately, Wordpress comes with the revision feature turned on and their is no switch in the Admin panel to turn it off.
Big Databases Can Be A Problem Backing Up
Recently I had the experience of administering a wordpress blog that was having problems backing up the MySQL database. Backups were done through phpMyAdmin using Export. A wordpress.sql was generated, but upon inspection it observed to be incomplete. The export had stopped partway through the wp-posts table and completely missed the tables that followed, like wp-links and wp-users.
The size of the wordpress.sql database backup file created was 18.4 Megabytes. We looked at older backups from a year earlier and observed that, as new posts were added, the filesize grew each week. Then, sometime in April, the backup file stopped growing. Since April, all the backups were missing many posts and were incomplete. [This shows a good reason to test backup files.]
I suspected that the problem was phpMyAdmin timing out. There are various limitations imposed by the webhosting company that will limit the maximum size of the backup file.
Alternative 1: Backup from the unix command line
You can backup and restore a MySQL database using the MySQL client tools from the unix command line. This would be the best alternative for backing up your wordpress database. You would use the mysqldump command as follows:
%mysqldump -u username -p database >wordpress.sql
To restore from a backup:
%mysql -u username -p <wordpress.sql
To securely access a server over the internet, you need to use the secure shell progam, ssh. Unfortunately, some webhosts don’t give users ssh access.
Alternative 2: Wordpress Tools>Export
A second alternative is to use the wordpress Export command, found under the tools menu in the Admin panel. This outputs an .xml file that contains all the blog content including posts, comments, etc. The .xml can then be imported into another wordpress blog.
Although this is a useful function for exporting posts to another blog, this is not really a backup, since none of the database tables are not exported.
Alternative 3: Reduce the database size
Since we couldn’t run commands from a shell, the only alternative left was to reduce the size of the database. Using phpMyAdmin, I could see that the largest table in the database was wp-posts, which weighed in about 24 MBytes. This seemed rather large.
By looking at some of the posts I could see the problem. Many posts had about ten or twenty revisions! During the course of writing and editing blog posts, authors would write part of an article and save it. Later they would return to add more to the article and save it again. After publishing, any typos that were missed were corrected and saved. The result was the long list of revision posts.
Disabling Post Revisions
There’s no switch in the control panel to turn off revisions. This is a major oversight of Wordpress. You have to add a couple of lines of code to wp-settings.php or wp-config.php. Here’s the code:
define('AUTOSAVE_INTERVAL', 300 ); // seconds
define('WP_POST_REVISIONS', false );
The first line of code makes the autosave interval every 5 minutes, rather than the default 60 seconds that Wordpress is installed with. The second line of code disable posts revisions. The Wordpress installation has WP_POST_REVISIONS set to true which means unlimited number of revisions. You can also set it to a number. I set it to false.
Deleting Revisions from the Database
The next thing to do is delete all the revisions from the wp-posts table in the database. You can using phpMyAdmin to execute this SQL Query:
DELETE FROM wp_posts WHERE post_type = "revision";
After you run the query, look at the table structure. You’ll see that wp-posts table now has a lot of overhead. Check it and then repair table.
We were able to reduce the wp-posts table from about 24MBytes down to about 2.4 MBytes! After we exported the wordpress database, the complete wordpress.sql backup file was about 4.9 MBytes. No more incomplete backup files from phpMySQL.
There may be other tables in wordpress that have data from all these revisions. This blog post Delete WordPress 2.6 Revisions recommends using this SQL query to delete all of them. Misfortunately, I already used the other query to delete the revisions from wp-posts, so I don’t think this would work now. Also, I don’t understand SQL well enough to know exactly what this query actually does, so I would be reluctant to try it anyway. I don’t know what a,b, c and LEFT JOIN are all about.
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'
Quite a few wordpress bloggers were up in arms over the problems caused by the automatic revisions, especially since they couldn’t easily be turned off. One lesson learned is the Law of Unintended Consequences when adding new features to software. The added feature may sound cool and nifty, but it may screw things up for a lot of people.
This tends to make people gunshy about adding a new plug-in or upgrading to the latest release. The old adage “if it ain’t broke, don’t fix it” comes to mind.
This problem was discussed in several blog posts and on the Wordpress forum. A Google search on “disable post revisions wordpress” turns up quite a bit of discussion including:
One of the sad things is that, despite all the people that have had problems with auto-save and excessive post revisions, some of the Wordpress developers are adamant that this feature is completely harmless and it is not necessary to have a way to disable it. This shows really bad judgement and lack of understanding of user needs. With that kind of attitude, I feel kind of like Timothy Leary’s younger brother about upgrading to a new version of Wordpress–Really Leary.