How can you completely remove an old deleted field?

Question

After deploying a Drupal 8 site to a new server, we have the problem of an old deleted field being remembered by some subsystem or other, leading to this error being thrown on whenever cron runs:

Drupal\Core\Database\DatabaseExceptionWrapper:
SQLSTATE[42S02]: Base table or view not found:
1146 Table 'drupal8_neu_default.field_deleted_data_1c89727af8' doesn't exist:
SELECT DISTINCT t.entity_id AS entity_id
FROM {field_deleted_data_1c89727af8} t
WHERE  (bundle = :db_condition_placeholder_0)
LIMIT 50 OFFSET 0; Array
(
    [:db_condition_placeholder_0] => video
)
in
Drupal\Core\Entity\Sql\SqlContentEntityStorage->readFieldItemsToPurge()
(line 1546 of /srv/www/neu.default.ch/builds/v1.0.0-alpha.7/docroot/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

I've spelunked all over the database, and I can't figure out where to expunge the reference to this long-dead table. It's not mentioned in config and the field_config table we knew from Drupal 7 is long gone. It's also not in the exported configuration.

Does anyone know how to fix this?

Answer 1

This is in the state/key_value table, with the keys field.field.deleted field.storage.deleted.

To find them, run this sql:

select * from key_value where name = "field.field.deleted" or name = "field.storage.deleted"

See \Drupal\field\FieldConfigStorage::loadByProperties() and \Drupal\field\FieldStorageConfigStorage::loadByProperties()

Answer 2

Execute this to remove deleted field(s), pending deletion:

drush php-eval 'field_purge_batch(1000);'

Answer 3

The only way I could resolve this was to create the missing table, for example:

CREATE TABLE `field_deleted_data_XXX` (   
`bundle` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '',
`deleted` tinyint(4) NOT NULL DEFAULT '0',
`entity_id` int(10) unsigned NOT NULL,
`revision_id` int(10) unsigned NOT NULL,
`langcode` varchar(32) CHARACTER SET ascii NOT NULL DEFAULT '',
`delta` int(10) unsigned NOT NULL,
`content_translation_source_value` varchar(12) CHARACTER SET ascii NOT NULL,
PRIMARY KEY (`entity_id`,`deleted`,`delta`,`langcode`),
KEY `bundle` (`bundle`),
KEY `revision_id` (`revision_id`) );

Replacing XXX with the code that follows field_deleted_data_ in the error message. Then run:

drush php-eval 'field_purge_batch(1000);'

This may generate the error with a new code. I had to go through the process 3 times, but eventually it resolved the error.

Answer 4

Thank you every one for your answers that allowed me to build the following (imperfect) solution.

List deleted fields waiting to be purged:

drush php:eval "var_dump(\Drupal::state()->get('field.storage.deleted'))"

If the result is not an empty array, create the missing field_deleted_data_* tables:

drush php:eval '$deleted_field_storage_configs = \Drupal::state()->get("field.storage.deleted");
$database = \Drupal::database();

foreach($deleted_field_storage_configs as $field_storage_config) {
  $table_name = "field_deleted_data_" . substr(hash("sha256", $field_storage_config->getUniqueStorageIdentifier()), 0, 10);

  $sql_query = "CREATE TABLE IF NOT EXISTS `" . $table_name . "` (
    `bundle` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '\'\'',
    `deleted` tinyint(4) NOT NULL DEFAULT '\''0'\'',
    `entity_id` int(10) unsigned NOT NULL,
    `revision_id` int(10) unsigned NOT NULL,
    `langcode` varchar(32) CHARACTER SET ascii NOT NULL DEFAULT '\'\'',
    `delta` int(10) unsigned NOT NULL,
    `content_translation_source_value` varchar(12) CHARACTER SET ascii NOT NULL,
    PRIMARY KEY (`entity_id`,`deleted`,`delta`,`langcode`),
    KEY `bundle` (`bundle`),
    KEY `revision_id` (`revision_id`) );";

  $database->query($sql_query);
}
'

Remove the fields:

vendor/bin/drush php-eval 'field_purge_batch(1000);'

Check that no more fields are waiting to be purged:

drush php:eval "var_dump(\Drupal::state()->get('field.storage.deleted'))"

You can now safely remove field_deleted_data_* tables that are still present in the Drupal database.

To perfect this solution:

I based the table name computation on the code of the SqlContentEntityStorage::readFieldItemsToPurge() method.

The better way to do this would be to find a way to get the SqlContentEntityStorage and field storage definition to have the good version of table name computation:

$storage_definition = $field_definition->getFieldStorageDefinition();
$table_mapping = $this->getTableMapping();
$table_name = $table_mapping->getDedicatedDataTableName($storage_definition, $storage_definition->isDeleted());