Database Upgrade Large Termmeta

When upgrading the database from ACF4 to ACF5, it is possible to experience a timeout error preventing the upgrade from completing. The most likely cause of this timeout is a large amount of termmeta.

There are two stages to the upgrade:

1. Upgrading field settings (v5.0.0)
2. Upgrading termmeta values (v5.5.0)

To check which stage failed, review the "acf_version" value within the wp_options table.
If this value reads "5.0.0", the upgrade completed stage 1 but failed during stage 2.

In this case, the database upgrade prompt will continue to appear. Running the DB upgrade again will skip stage 1 and attempt to run stage 2. This will most likely result in a timeout issue again due to the large amount of termmeta.

The solution is to break the "stage 2" upgrade into "chunks". This is possible by making the following modifications to the "includes/upgrades.php" file acf_upgrade_550_taxonomy() function.

function acf_upgrade_550_taxonomy( $taxonomy ) {
	
	// new
	$progress = get_option( 'acf_upgrade_550_taxonomy_progress' );
	$limit = 20000;
	$i = 0;
	
	// log
	acf_dev_log('ACF Upgrade 5.5.0 Taxonomy.', $taxonomy);
	
	// global
	global $wpdb;
	
	// vars
	$search = $taxonomy . '_%';
	$_search = '_' . $search;
	
	// escape '_'
	// http://stackoverflow.com/questions/2300285/how-do-i-escape-in-sql-server
	$search = str_replace('_', '\_', $search);
	$_search = str_replace('_', '\_', $_search);
	
	// search
	// results show faster query times using 2 LIKE vs 2 wildcards
	$rows = $wpdb->get_results($wpdb->prepare(
		"SELECT * 
		FROM $wpdb->options 
		WHERE option_name LIKE %s 
		OR option_name LIKE %s",
		$search,
		$_search 
	), ARRAY_A);
	
	// loop
	if( $rows ) {
	foreach( $rows as $row ) {
		
		/*
		Use regex to find "(_)taxonomy_(term_id)_(field_name)" and populate $matches:
		Array
		(
		    [0] => _category_3_color
		    [1] => _
		    [2] => 3
		    [3] => color
		)
		*/
		if( !preg_match("/^(_?){$taxonomy}_(\d+)_(.+)/", $row['option_name'], $matches) ) {
			continue;
		}
		
		// vars
		$term_id = $matches[2];
		$meta_key = $matches[1] . $matches[3];
		$meta_value = $row['option_value'];
		
		// new: check if a progress reference has been saved
		if( $progress ){
			
			// if the current termmeta matches against the saved progress, set progress to false
			// and allow upgrade function to continue inserting data
			if( $progress === "{$term_id}/{$meta_key}" ) {
				$progress = false;
			
			// otherwise, ignore this value as it has already been saved
			} else {
				continue;
			}
		}
		
		// new: limit
		$i++;
		if( $i > $limit ) {
			die('Process terminated. Upgrade termmeta limit reached.' . count($rows));
		}
		
		// update
		// memory usage reduced by 50% by using a manual insert vs update_metadata() function. 
		//update_metadata( 'term', $term_id, $meta_name, $meta_value );
		$wpdb->insert( $wpdb->termmeta, array(
	        'term_id'		=> $term_id,
	        'meta_key'		=> $meta_key,
	        'meta_value'	=> $meta_value
	    ));
	    
	    // new: update progress reference
	    update_option( 'acf_upgrade_550_taxonomy_progress', "{$term_id}/{$meta_key}" );
	    
	    // log
		acf_dev_log('ACF Upgrade 5.5.0 Term.', $term_id, $meta_key);
		
		// action
		do_action('acf/upgrade_550_taxonomy_term', $term_id);
	}}
	
	// action for 3rd party
	do_action('acf/upgrade_550_taxonomy', $taxonomy);
}

This updated code contains a 'progress' reference that allows multiple attempts at "stage 2" without causing any duplicate any data.

Start in includes/upgrades.php at acf_upgrade_550_taxonomy(), and review the acf_version value in wp_options to identify the failed upgrade stage. Exercise the stage 2 path with a large termmeta set; it is done when the upgrade can resume in chunks without duplicate data or another timeout.