Duplicate autosave/revisions clutter the database

[Denis-de-Bernardy]

We shouldn't be storing revisions and autosaves that are identical to the original post.

It generates a bug, to start with. If you open a post, and you leave it alone until the autosave fires, and then close the window, you end up with a notice -- a newer version of the post exists.

Then, it clutters the list of actual revisions with junk that cannot be removed without a plugin or by using SQL.

[Denis-de-Bernardy]

Mm, actually, the autosave issue described above seems fixed in trunk. But we're still cluttering the database with useless revisions. It's minor, too, so punting pending patch.

[Denis-de-Bernardy]

I've this function on the save_post hook, in case there is any interest for a patch writer:

	function save_post_revision($rev_id) {
		if ( wp_is_post_autosave($rev_id) ) {
			return;
		} elseif ( $post_id = wp_is_post_revision($rev_id) ) {
			# do nothing
		} else {
			$post_id = $rev_id;
		}
		
		global $wpdb;
		$post = get_post($rev_id);
		
		# drop dup revs
		$kill_ids = $wpdb->get_col("
			SELECT	ID
			FROM	$wpdb->posts
			WHERE	post_type = 'revision'
			AND		ID <> " . intval($rev_id) . "
			AND		post_parent = " . intval($post_id) . "
			AND		post_content = '" . $wpdb->escape($post->post_content) . "'
			");
		
		if ( $kill_ids ) {
			foreach ( $kill_ids as $kill_id ) {
				wp_delete_post_revision($kill_id);
			}
		}
		
		# stop here for real posts
		if ( $post_id == $rev_id )
			return;
		
		# drop other potential dup revs
		$kill_ids = $wpdb->get_col("
			SELECT	p2.ID
			FROM	$wpdb->posts as p2
			JOIN	$wpdb->posts as p1
			ON		p1.post_parent = p2.post_parent
			AND		p1.post_type = p2.post_type
			WHERE	p1.post_type = 'revision'
			AND		p1.post_parent = " . intval($post_id) . "
			AND		p1.post_content = p2.post_content
			AND		p1.ID > p2.ID
			");

		if ( $kill_ids ) {
			foreach ( $kill_ids as $kill_id ) {
				wp_delete_post_revision($kill_id);
			}
		}
		
		# drop near-empty revs
		$kill_ids = $wpdb->get_col("
			SELECT	ID
			FROM	$wpdb->posts
			WHERE	post_type = 'revision'
			AND		post_parent = " . intval($post_id) . "
			AND		LENGTH(post_content) <= 50
			");
		
		if ( $kill_ids ) {
			foreach ( $kill_ids as $kill_id ) {
				wp_delete_post_revision($kill_id);
			}
		}
		
		# drop adjascent revs
		$kill_ids = $wpdb->get_col("
			SELECT	p2.ID
			FROM	$wpdb->posts as p2
			JOIN	$wpdb->posts as p1
			ON		p1.post_parent = p2.post_parent
			AND		p1.post_type = p2.post_type
			WHERE	p1.post_type = 'revision'
			AND		p1.post_parent = " . intval($post_id) . "
			AND		DATEDIFF(p1.post_date, p2.post_date) < 1
			AND		p1.post_date >= p2.post_date
			AND		p1.ID <> p2.ID
			");

		if ( $kill_ids ) {
			foreach ( $kill_ids as $kill_id ) {
				wp_delete_post_revision($kill_id);
			}
		}
		
		# drop near-identical revs
		$kill_ids = $wpdb->get_col("
			SELECT	p2.ID
			FROM	$wpdb->posts as p2
			JOIN	$wpdb->posts as p1
			ON		p1.post_parent = p2.post_parent
			AND		p1.post_type = p2.post_type
			WHERE	p1.post_type = 'revision'
			AND		p1.post_parent = " . intval($post_id) . "
			AND		DATEDIFF(p1.post_date, p2.post_date) <= 7
			AND		ABS( LENGTH(p1.post_content) - LENGTH(p2.post_content) ) <= 50
			AND		p1.post_date >= p2.post_date
			AND		p1.ID <> p2.ID
			");

		if ( $kill_ids ) {
			foreach ( $kill_ids as $kill_id ) {
				wp_delete_post_revision($kill_id);
			}
		}
	} # save_post_revision()

[caesarsgrunt]

Duplicate of #7392.

[Denis-de-Bernardy]

similar, but not 100% dup. #7392 only covers autosaves. here, it's more like: don't create a revision if only a post's tags have been edited.

[Denis-de-Bernardy]

see #11324

[solarissmoke]

I've posted a patch on #7392 which I think should resolve both of these.

[westi]

Adding to the list of things to consider for the 3.6 work on Revisions.

[westi]

This need careful consideration and we will take a look for 3.6.

[adamsilverstein]

i think solving #7392 will solve this issue; the initial autosave firing is what creates the extra revision record mentioned by the author in this post. by the way, if you create a post, but go immediately to publish button from the title field without entering any content the extra revision is not created.

[adamsilverstein]

verified, #7392 patch fixes this.

[westi]

In 1211/tests:

Revisions: When we are trying to save a new revision we shouldn't save one if the previous version was the same.

See #7392 and #9843 props adamsilverstein

[westi]

In 23414:

Revisions: Before saving a new post revision make sure that something has changed in the fields that we are revisioning.

Fixes: #7392 and #9843 props adamsilverstein.

[westi]

In 1214/tests:

Revisions: Add a new test for a force save filter in wp_save_post_revision

Also Update the tests to have correct ordering, be less complex and use things like assertCount.
See #7392 and #9843

[westi]

In 23415:

Revisions: Allow a plugin to force us to skip the don't save this revision because it hasn't changed code if it knows better.

See #7392 and #9843. Also cleans up the whitespace.

[soulseekah]

Breaks post/revisions/test_revision_restore_caps_before_publish, since content hasn't changed a revision was not created, expected 2 revisions after second "edit", getting one.

Test was written for #16847 ​http://core.trac.wordpress.org/changeset/1213/tests and should probably change content the second time to create a second revision, or expect 1 revision.

[soulseekah]

alternative