Fixing list text field data integrity issues in Drupal 10.2

By Kevin, December 28th, 2023

Drupal 10.2 has arrived and ships with several new features, bugfixes, enhancements and quality of life improvements.

One change that might catch you off guard though involves List (text) fields. These fields store key/value pairs of information, designed to present users with a select list of options in a field. 

Prior to 10.2, you could enter virtually anything for the 'key' portion that you wanted. Starting with 10.2 however, only keys that are formed like machine names (only underscores, no dashes, spaces, symbols or otherwise) are allowed. If you had a key like this-is-the-value|My Value, you will now find that when editing that field the configuration form will not validate.

list field not validating
Old key values with dashes now present an error, and require change.

This presents an interesting challenge for existing sites. For one, you will be blocked from changing this list until the key values are fixed. If the key values are fixed, you may have a residual data problem depending on what the list was for. In our case, this was a list that contained CSS classes that were applied to a block. If you don't update the key and the configuration, its configuration may no longer import correctly. For some, you may have several thousand database records worth of values using the now invalid key(s).

There are a few things you could do. The first thing you could try to do are a series of update hooks that query and batch update field values across your various entities. Depending on the amount of data (and if you use revisions) this update could take a very long time to do and in the interim may make it so your content forms don't validate.

In my case, I have 2 projects with the 10.2 on deck to go to production when we recognized this change. We did not have the time to write update hook(s) or do the trial and error of validating all the data was updated. For us, we needed a faster solution with as little downtime as possible. We have several block types that have list fields being used in production for these projects.

It turns out that I had solved this issue years ago for a different use case.

Stored Procedure Update

Note that what follows may not be a bulletproof solution for you. Using stored procedures and MySQL to rewrite data on the fly could create new issues for you, and you need to be totally aware of exactly what you are doing. For a primer on stored procedures, you can refer to the MySQL documentation on them.

What I am going to do is the following:

  • Create a stored procedure that takes two inputs
  • The first input is the value to search for, the second value is what to replace that with
  • Query for any block field that is a VARCHAR (assumed list text fields)
  • Replace the values

If I can do that, then I should be able to deploy the update as:

  • Fix the allowed values on the field to validate
  • Export the configuration with the 10.2 update
  • Deploy the update to production
  • Backup the database
  • Run the stored procedure function
  • Clear caches

Here is the stored procedure for my case:

CREATE PROCEDURE replaceFieldValueTo  (IN v_string_from VARCHAR(255), IN v_string_to VARCHAR(255))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE table_name_value VARCHAR(64);
  DECLARE column_name_value VARCHAR(64);
  DECLARE cursor_fields CURSOR FOR SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'block_content_%' AND COLUMN_NAME LIKE 'field_%_value' AND DATA_TYPE = 'varchar';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
  OPEN cursor_fields;
  ColumnList: LOOP
    FETCH cursor_fields INTO table_name_value, column_name_value;
    
    IF done THEN 
      Leave ColumnList;
    END IF;
    
    SET @sql = CONCAT('UPDATE ', table_name_value, ' SET ', column_name_value, ' = replace(', column_name_value, ', "', v_string_from, '", "', v_string_to ,'")');
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cursor_fields;
END;

Once executed, this creates a new function that can be called from the MySQL prompt or any connected MySQL client. If I wanted to change 'some-value' to 'some_value', then I could call it:

CALL replaceFieldValueTo('some-value', 'some_value');

This would update any instance of that string in the tables identified in the query of the stored procedure.

Here, I am calling it against two values I need to update:

CALL replaceFieldValueTo('full-width', 'full_width');
CALL replaceFieldValueTo('two-up', 'two_up');

We can see that the values in the database have been updated:

mysql values

Since our stored procedure query searches only for block_content_% tables, this updates all of the revision table records for those fields as well. That will ensure revision data is corrected in the event you have content you want to restore revisions of.

After that, the field forms are satisfied. The stored procedure executes rather quickly. That is because its raw SQL, no Drupal API's or events are triggered. As far as Drupal is concerned, it is not aware anything has happened. Once we clear the cache at the end of our updates, everything is fine.

On the frontend, we only had to update a few instances in Twig files:

twig

With just a handful of those changes, we had a fast resolution for our projects and deploying 10.2 to production with minimal downtime or issues.

Acquia does not allow stored procedures

It's true. You are not permitted to create stored procedures on Acquia directly. We worked around this by coordinating and planning the upgrade with clients, taking a backup of the database, fixing the data locally, then pushing the result back up to the Acquia MySQL instance.

I will reiterate that this isn't a bulletproof solution for everyone and it can vary based on your needs and project setup. For some, you may find that this is the path forward you need to complete the upgrade. For others, it may not be a viable solution.

Tags