Mass Updating Drupal Fields with MySQL Stored Procedure

By Kevin, June 2nd, 2017

I was doing an onsite with a client earlier this week, assisting in a demo of the Drupal 8 solution we were building for their team.

During the meeting, it was expressed that they wanted all instances of a text editor to have the same buttons and capabilities.

The way I had (and always do) set up the site was to have three text formats. One with almost no CKEditor buttons, one with a few more, and one with almost all of them (Full HTML).

The change is easy to make, and decided to demonstrate how easy that change was in the meeting.

However, I forgot that by changing the field settings to default to a different format that this would cause all existing field values to revert to a plain text box. This is because I (as usual) use the Better Formats module to default formatted text fields to a specific format and hide the text format selection button.

The database had hundreds of rows spread across a couple dozen tables that needed updating in its format column to change the value to ‘full_html’. I would also have to make this change on other environments. So, looking through the database and running updates or changing values manually would be inefficient and tedious.

For this I figured I could employ a SQL query. First, I looked for a way to find all column names that matched a particular pattern. The pattern I was looking for was field_%_format. All fields from the Drupal Field API that store text format values have a field in their table matching this pattern.

Once I had that query, I thought hmm, how can I loop the results, and run an UPDATE statement to update everything in the database? Before I knew it, I found out I had to make a cursor and eventually a stored procedure, two things I had never done or had to do before. Turns out, it wasn’t that difficult.

Using resources and some reading, I put together a stored procedure that took a single argument (text format machine name), grabbed all fields matching that pattern, and set their value to the argument value.


CREATE PROCEDURE changeFieldFormatValueTo (IN format_name VARCHAR(32))
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 COLUMN_NAME LIKE 'field_%_format';
  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, ' = "', format_name, '"');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cursor_fields;
END;

After you define the stored procedure, you can just call it anytime from the MySQL prompt or MySQL client:


mysql > CALL changeFieldFormatValueTo("full_html");

This opens up new possibilities later for other “fixes” to make in Drupal quickly. I could even create another stored procedure that takes more arguments, say, if I needed a way to change one format value to another for fields that have a certain value (so I don’t affect all fields, only a select set). That would be a matter of adding more arguments, and changing the UPDATE query in the prepared statement to have a WHERE condition.

I may have been able to achieve this with a custom module, some Batch API ops etc etc. But that probably would have taken longer to make and I felt like doing something that was more bare metal and faster.

One thing to note, this was not doable from a client like SequelPro (macOS). I had to ssh into my local vagrant box and remote servers to create the stored procedure. SequelPro currently has no way to define or edit stored procedures from its interface. I hear that you can with MySQL Workbench, but I did not want to sit and install that beast when I could just do it from the mysql prompt. Plus it’s more time in the command line, which is where I like to be anymore.

Tags