salesforce_mapping_object_revision table grows very large

AFAIK the revisions table is only used to display the sync history on the %/salesforce tab.
A quick win here might be to allow admins to disable revisioning completely for salesforce mapping objects (and delete the associated revisions table).

I'd propose a solution to cull the revisions on cron, perhaps by time/date or number of entries. I used a function like this to cleanup the tables when I first noticed the tables got out of control:

function sf_revision_cleanup_cron() {

	$query = "select salesforce_mapping_object_revision.salesforce_mapping_object_id, salesforce_mapping_object_revision.revision_id 
from salesforce_mapping_object,salesforce_mapping_object_revision where 
salesforce_mapping_object.salesforce_mapping_object_id=salesforce_mapping_object_revision.salesforce_mapping_object_id AND 
salesforce_mapping_object.revision_id!=salesforce_mapping_object_revision.revision_id LIMIT 0,100000";
	$result = db_query($query);
	foreach($result as $record) {
  		$num_deleted = db_delete('salesforce_mapping_object_revision')
			->condition('salesforce_mapping_object_id', $record->salesforce_mapping_object_id)
			->condition('revision_id', $record->revision_id)
  			->execute();
	}

}

We had the same issue and it was stopping the daily backup from running. We decided to exclude the table data from the backup and migrate backup, it was 900MB of the overall 1GB database, then backed up the whole db server side. Rather than delete all of the data it might be useful to be able to set a limit eg keep revision information for X days/months/years, as antonioG4 suggest too, so this table can be trimmed down.

in all my installations, i've never once referred to the revision history on a production install.
i've found that watchdog logs sufficient for debugging and forensics, and the revision logs not so much.
but, do other folks rely on revision logs more heavily?
should revision settings be configurable?

In the short term this in a custom module allows adjustment of the number of days worth of revisions to keep and the number to delete on each cron run:

function delete_old_sf_revisions_cron() {
	$maxRevisionAge = 90; // in days
	$maxNoToDelete = 100;

    $timestamp = time() - ($maxRevisionAge * 86400); 

    // search db for old revisions
	$results = db_select('salesforce_mapping_object_revision')
 	->fields(NULL, array('entity_updated'))
 	->condition('entity_updated', $timestamp,'<')
 	->range(0, $maxNoToDelete);
 	$timestamps = $results->execute()->fetchCol(); 

 	// if no results log this
 	if (empty($timestamps)) {
 		watchdog('Delete Old SalesForce Revisions', 'salesforce_mapping_object_revision table size ok. No items older than ' . $maxRevisionAge . ' days old.');
 	}

 	// if we got some old revisions then delete them
 	if (!empty($timestamps)) {
		db_delete('salesforce_mapping_object_revision') 
		->condition('entity_updated', $timestamps, 'IN') 
		->execute();

		// log to screen if required:
		// drupal_set_message(t((count($timestamps) . ' items removed from salesforce_mapping_object_revision table to stop it getting to big.')), 'status');

		watchdog('Delete Old SalesForce Revisions', count($timestamps) . ' items removed from salesforce_mapping_object_revision table to stop it getting to big. 
			Currently removing all a maximum of ' . $maxNoToDelete . ' items older than ' . $maxRevisionAge . ' days old on each cron run. 
			Settings can be adjusted in delete_old_sf_revisions.module');
	}
  
}

In a lot of our installations we rely heavily on this table for debugging, although I can see the challenge with it growing significantly.

I'd be interested in a solution that limits the number of revisions per object, although the date-based version seems useful as well. It's just unfortunate for objects that sync once and don't get updated that you eventually lose all the history.

I added a setting for limiting revisions in D8, as well as a drush command to clean up existing excessive revisions.

The setting is an integer limit for the number of revisions to save per mapped object.

The cleanup mechanism is a post-save (in d7: hook_update) to do the cleanup, so that we don't have to query the entire revisions table during cron.

The drush command is useful if the limit changes, or to clean up old revisions on existing sites.

In D8, i set the default limit to 10.
For D7, we probably want the default to be "unlimited", so that admins have to opt-in for this.

I have the same issue. The revision table has gotten so huge and running a cron to reduce the number of rows in the table is not happening fast enough to significantly reduce the size of the table. Is there any issue with just truncating the revision table?

Thank you.

i don't think you can just truncate the revision table, because drupal relies on revisions for even a basic load. you might lose access to all mapped objects.

you might try truncating, then replicating the data from salesforce_mapping_object

don't do this in production, obviously

Thank you aaronbauman. That is what I exactly did. Truncate the revision table and insert the rows from the mapping object table into the revision table.

TRUNCATE TABLE `salesforce_mapping_object_revision`;
INSERT INTO `salesforce_mapping_object_revision` SELECT * FROM `salesforce_mapping_object`;

But, I do see a weird issue now. Right away I see both tables have the same number of rows which is obvious. But within a minute I see that the revision table starts growing. So I did a max(revision_id) on both the tables and they both have the same max(revision_id), which means some revision_ids in between are getting inserted into the revision table, which is why there are more number of rows in the revision table all of a sudden though they have the same max(revision_id). Any idea how or why that might be the case. So, its not like there are new pulls which means newer revision_ids, there are revision_ids in between. So I tested triggering a pull myself and a newer revision_id got created and tested a push as well and a newer revision_id got created as well. So, I think we can rule out that the new pulls or pushes are not taking some revision_ids in between, which wouldn't make sense.

Any idea why the revision table has rows that are not in the main mapping object table though they have the same max(revision_id) after truncating and inserting from the mapping table to the revision table.

@st455 that sounds like odd behavior indeed, i'm not sure there's anything in this project which could explain it. my inclination is that it's something in entity api.

I came across this thread as I am having this exact same problem with my salesforce_mapping_object_revision table showing 30 million rows.

@Saratt looking at the truncate command, it resets the auto_increment (https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html) which would be why you are seeing the number of records increase but not the max revision_id.

Looking at the script in #7 seems to be the way to go to remove out old records from beyond a certain date.

Is there any movement on getting a D7 backport for the drush script / setting?

Have backported that D8 commit into D7 against 7.x-3.2.

This is working for my site, and has taken us from 11million rows down to around 1 million now!

Currently has a couple of drush functions to run against all rows, or against a specific salesforce_mapping_object_id. Also taken from the D8 version on hook_entity_update it prunes any SF mapped object against the entity.

Hi @astoker88, nice work, thanks for this :).

How long did for the drush command to clear out 10 million records and did it put much load on the server?

I have a site which now has over 40 million records in this revision table, so have been keeping an eye on and this while also looking at sensible options to clear down the table :).

Hi @mike.davis ... it definitely took longer than a few hours but i didn't track it on my prod environment.

Will be dependant on the number of mapped entities vs revisions.. thinking that less mapped entities with more revisions will be slightly faster.. i had 160k entities (from memory took around 4 hours).

I found a problem with the the patch in #16 (7.x version). I'm not sure if the same problem exists in D8, but it doesn't appear to work quite the same.

The issue is that when determining which records to prune in salesforce_mapping_prune_revisions(), the query does an 'order by' on the 'entity_updated' column. In our case, we had many instances where several revisions had the exact same timestamp for 'entity_updated', and the result is that the pruning job does not necessarily leave the latest revision intact, as it's basically picking them arbitrarily at that point.

If the prune ends up deleting the record that is the most recent/current revision, the corresponding salesforce_mapping_object cannot be loaded at all, which is obviously a major issue.

I believe the fix is simply to sort by 'revision_id' instead, which should ensure that the latest revision is always left intact at the top. I'm not sure if I'll be able to contribute a patch, but will try.

yes indeed the issue named on #19 is totally true, a simple modification to the previous patch fix that.

\

I updated the patch against to the latest 7.3-x-dev branch and I made an improvement:
- On the settings page there will be a checkbox to "Keep the first record of revisions".
If this is checked, the prune will keep the oldest record to have information about when the record originally created.
Additionally, I made a little refactoring and Drupal standardization.

7.x is no longer supported