Altering the length of a Drupal 8 text field that contains data

Friday, July 21, 2017 - 11:31

I'm still super-busy with ongoing Drupal 8 projects, but one thing I had to do recently was alter the length of a text field, that already contained data. It sounds like it should be easy, right?

The client had asked for early access to the site, to start entering content; I've provided them with the Media Entity modules (arguably D8's lightweight successor to D7's heavyweight Media suite). When a document entity is created, and a file uploaded, Media Entity works out from the extension what its MIME type is, and then saves that into another field of your choosing. I'd set this field storage to be 64 characters long: easily enough to store strings like "application/pdf", right?

All went well for a few days, until, despite permitting the uploading of all file extensions they'd requested, the client was still reporting some files failing. One of these was "new" Excel files: .xlsx; these have a MIME type of "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".

Count the number of characters in that. I can wait. OK, it's sixty-five.

Changing the length of a field ought to be—certainly would appear to be—straightforward. You run a command to change the site's stored configuration:

$ drush cset field.storage.media.field_file_type settings.max_length 12

and then you run database updates:

$ drush updb --entity-updates
The following updates are pending:
 
media entity type : 
  The media.field_file_type field needs to be updated.
Do you wish to run all pending updates? (y/n):

Just hit "y", right? Well, no:

Drupal\Core\Entity\Exception\FieldStorageDefinitionUpdateForbiddenException: The SQL storage cannot change the schema for an existing field (field_file_type in media entity) with data.
  in Drupal\Core\Entity\Sql\SqlContentEntityStorageSchema->updateDedicatedTableSchema() (line 1312 of /vagrant/www/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorageSchema.php).
 
Failed: Drupal\Core\Entity\Exception\FieldStorageDefinitionUpdateForbiddenException: !message in Drupal\Core\Entity\Sql\SqlContentEntityStorageSchema->updateDedicatedTableSchema() (line 1312 of /vagrant/www/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorageSchema.php).

This, it turns out, is simply not supported by the underlying SQL drivers: at the time of writing. A weird omission, you might say; and I'd agree, but I dare say Drupal as a project has its reasons, and there's just some edge case I haven't thought of that means this can't go into core. Annoying, though.

However, if you're happy to write the SQL yourself, then you can certainly do so: it just then turns out that your database is no longer the same as Drupal expects it to be. Which is OK in the short term—longer data could be inputted into that field—but could have undefined consequences in the long term—Drupal would arguably be justified in trimming it to 64 characters, in a later release.

To actually make the change at all relevant points in the application, you need to do the following:

  1. ALTER the database tables directly using raw SQL.
  2. Change the site confguration (we did that above).
  3. Force the current, "desired" schema records in Drupal to match.
  4. Force the "most recently installed" schema records to also match.

Only then wll both the database be modified to suit, and Drupal believe that the database ought to look like that anyway. The last two steps are, in a way, "brain surgery", altering Drupal's own historical record of schema installs.

Since doing it myself, I've stumbled across a much more comprehensive example by Marcus Bernal. A "simple" ALTER is much less more complex than this one, so below is a Drupal update hook that did the trick:

<?php
 
use Drupal\Core\Database\Database;
 
/**
 * Implements hook_update_N().
 */
function mymodule_update_8302() {
  $connection = Database::getConnection('default');
 
  // Modify tables directly.
  drupal_set_message('media.field_file_type: altering database tables.', 'warning');
  $connection->query(
    'ALTER TABLE media_revision__field_file_type MODIFY field_file_type_value varchar(255);'
  );
  $connection->query(
    'ALTER TABLE media__field_file_type MODIFY field_file_type_value varchar(255);'
  );
 
  // Force configuration to be set to match.
  drupal_set_message('media.field_file_type: checking config for any required updates.');
  $config_factory = \Drupal::configFactory();
  $config = $config_factory->getEditable('field.storage.media.field_file_type');
  if ($config->get('settings.max_length') != 255) {
    drupal_set_message('media.field_file_type: config needs updating; updating.', 'warning');
    $config->set('settings.max_length', 255);
    $config->save();
  }
  else {
    drupal_set_message('media.field_file_type: config has already been updated.');
  }
 
  // Live schemas.
  drupal_set_message('media.field_file_type: checking live schema matches database.');
  $store = \Drupal::service("keyvalue")->get("entity.storage_schema.sql");
  $data = $store->get("media.field_schema_data.field_file_type");
  $live_schema_change = FALSE;
  foreach (["media_revision__field_file_type", "media__field_file_type"] as $table) {
    if ($data[$table]["fields"]["field_file_type_value"]["length"] != 255) {
      $data[$table]["fields"]["field_file_type_value"]["length"] = 255;
      $live_schema_change = TRUE;
    }
  }
  if ($live_schema_change) {
    drupal_set_message('media.field_file_type: live schema needs updating; updating.', 'warning');
    $store->set("media.field_schema_data.field_file_type", $data);
  }
  else {
    drupal_set_message('media.field_file_type: live schema has already been updated.');
  }
 
  // Last-installed schemas.
  $schema_repository = \Drupal::service("entity.last_installed_schema.repository");
  $last_max_length = $schema_repository->getLastInstalledFieldStorageDefinitions("media")["field_file_type"]->getSetting("max_length");
  if ($last_max_length != 255) {
    drupal_set_message('media.field_file_type: last installed schema record needs updating; updating.', 'warning');
    $field_storage_definitions = \Drupal::entityManager()->getFieldStorageDefinitions("media");
    $field_storage_definitions["field_file_type"]->setSetting("max_length", 255);
    // Now set as the most recent schema definition.
    $schema_repository->setLastInstalledFieldStorageDefinitions(
      "media", $field_storage_definitions
    );
  }
  else {
    drupal_set_message('media.field_file_type: last installed schema record has already been updated.');
  }
}

Phew, eh? But not only did that all work, but it seemed repeatable: if I explicitly ran the update function a second time, it knew it had already run and backed off.

Not that I offer any warranty, for anyone else trying it themselves. Besides, isn't working out just what you have to do next most of the fun? No? Oh, just me, then.