My Drupal 7 installation has a handful of tables that have been corrupted. Attempts to repair them have not been successful so my best chance of being able to move forward is to recreate them.

I have not been able to find the DDLs for the tables that are corrupt so my question is, where might I find the DDLs for the core Drupal tables so I may recreate them?

The 2 tables that seem to be giving me the most grief because they are not available are field_data_field_files and field_revision_field_files.

  • What are DDLs ? – leymannx Jul 23 '19 at 17:17
  • Sorry, a database term for Data Definition Language, basically the statements required to create a table and all of the columns within that table. – Dave Jul 23 '19 at 17:33
  • Those tables are created on the fly, not from DDLs. The best way to recreate the table is to recreate the field via the API rather than scripting the tables manually – Clive Jul 23 '19 at 17:36
  • 1
    Much appreciate it sir. I was rather surprised to discover that one wasn't available to grab and use. Seems rather unusual to say the least :) – Dave Jul 23 '19 at 18:55
  • 2
    Prior to Drupal 8, Unusual may as well have been Drupal's codename... – Clive Jul 23 '19 at 18:56

Those tables are generated dynamically by the field API, and as such don't have a pre-built DDL. This is essentially because fields can be added through the UI/programmatically after installation. Rather than keeping a monolithic registry of those fields and values, the architectural decision was made to split them out into dynamically-created tables with a conventional structure.

If you have corrupt field tables it's a bit...awkward. Drupal will already think the field exists because it'll still be listed in field_config and field_config_instance, so trying to add it again with the API/UI isn't going to work unless you also prune those records.

Those records may also be referenced elsewhere (possibly in serialised PHP strings, no less), and there's no referential integrity of any sort here anyway, so you'd have to be able to work out what types of referenced record you could have, be able to identify them all, and nuke them. Not exactly easy.

The easiest approach will probably be a bit of a hack job; get the DDL of another field table, and twist it to match the table you've lost:

mysqldump -d -uroot -p <dbname> field_data_body

Give us

DROP TABLE IF EXISTS `field_data_body`;
CREATE TABLE `field_data_body` (
  `entity_type` varchar(128) NOT NULL DEFAULT '' COMMENT 'The entity type this data is attached to',
  `bundle` varchar(128) NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  `deleted` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'A boolean indicating whether this data item has been deleted',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'The entity id this data is attached to',
  `revision_id` int(10) unsigned DEFAULT NULL COMMENT 'The entity revision id this data is attached to, or NULL if the entity type is not versioned',
  `language` varchar(32) NOT NULL DEFAULT '' COMMENT 'The language for this data item.',
  `delta` int(10) unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
  `body_value` longtext DEFAULT NULL,
  `body_summary` longtext DEFAULT NULL,
  `body_format` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
  KEY `entity_type` (`entity_type`),
  KEY `bundle` (`bundle`),
  KEY `deleted` (`deleted`),
  KEY `entity_id` (`entity_id`),
  KEY `revision_id` (`revision_id`),
  KEY `language` (`language`),
  KEY `body_format` (`body_format`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 7 (body)';

Replace the table name and comment. Remove body_value, body_summary, and body_format columns, and the body_format index. These are specific to the body field type, the rest is boilerplate and common to all field types.

It seems likely your field type was a file reference, so you need to find the columns for that field type's schema. These are in file_field_schema(); mix these into your edited DDL:

`field_files_fid` INT(10) unsigned NOT NULL,
`field_files_display` TINYINT(3) unsigned NOT NULL DEFAULT 1,
`field_files_description` TEXT DEFAULT NULL

(add comments if you wish)

And an index for the file ID:

KEY `field_files_fid` (`field_files_fid`)

Repeat for the revision table (using field_revision_body as a starting point), and you should have your table structure back in place. At this point it would be a good idea to restore the data to these tables from a backup, as they reference entities in the file_managed table (and can have references elsewhere throughout the system).

  • Thanks Clive! Will try to give this a go later today. Working on trying to put out another fire related to the reason the tables got corrupted in the first place. – Dave Jul 24 '19 at 10:59
  • You're a genius Clive :) Actually that's probably a bit too strong but it's close enough. Though I don't have anything to restore to those 2 tables just having them defined and accessible again allowed me to move forward on getting things fixed. – Dave Jul 24 '19 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.