Cleaning a Decade of Drupal Database Cruft Before You Migrate

A Drupal 7 site that has been live for ten years carries a database nobody designed. It has cache tables that outgrew the content, a log that was never emptied, revisions of pages that were deleted in 2019, files that no page references, user accounts created by bots, and the leftovers of six modules that were uninstalled without their tables. Migrate Drupal does not know which of that is yours. It moves what the migrations can read, and it moves it slowly, because it has to read through all of it.

This is the cleanup we do before any Drupal 7 to Drupal 11 migration, in the order that costs the least and saves the most. It assumes you have Drush on the old site and a database you can query. None of it is clever. All of it is work someone has to decide to do, which is why it is usually skipped.

Why the database and not the code

The code of a Drupal 7 site is replaced by the migration; nothing from it survives except the decisions it encoded. The database is the opposite: almost everything in it is a candidate to survive, and Migrate Drupal is deliberately generous about what it carries across. Every node revision, every file record, every user, every taxonomy term with no content attached, every alias that pointed somewhere that no longer exists. The cost shows up three times: in the hours the migration takes to run, in the hours you spend debugging rows that should not have been there, and in a Drupal 11 site that starts life with the same sediment the old one had.

The migration cost is driven more by what is in the database than by what is in the code. A site with 4,000 nodes and 400,000 revisions is a bigger migration than a site with 40,000 nodes and no revision history. Cleaning is where that cost is cut, and it is cut before the first migration runs.

What a decade actually leaves behind

It helps to think of the database as strata. The content you want is the thin layer at the top. Under it are layers that were laid down by the system rather than by editors, and each layer gets a different treatment.

Two of those layers surprise people. Revisions migrate in full: the d7_node_revision migration faithfully recreates every saved version of every node, including the forty autosaves of a page that was edited by committee. And orphans migrate because, to the migration, an unreferenced file is a file and a term with no content is a term. Nothing in the pipeline asks whether anyone wanted them.

Step 0: snapshot and measure

Take a full dump before anything else and put it somewhere you cannot overwrite it. Then measure, so you can prove the cleanup did something and so the decisions below are made with numbers instead of guesses. One query answers most of it:

SELECT table_name, ROUND((data_length + index_length) / 1048576) AS mb, table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 40;

Write down the total and the top twenty. On the neglected Drupal 7 sites we are handed, the top of that list is rarely content. It is cache_form, watchdog, a couple of field_revision_body-sized revision tables, and, on sites that ran Webform for years, webform_submitted_data. The content the client cares about is usually a fraction of the dump.

Step 1: caches, logs, sessions and queues

These tables are system state. Drupal rebuilds them on demand, nothing in them migrates, and they are the cheapest cut you will make. Drush has known this for a decade: the common skip list in example.drushrc.php excludes the cache tables, watchdog, sessions and the search index from a dump, and the dump you hand to the migration should use it.

drush watchdog-delete all
drush cc all
drush sql-dump --skip-tables-key=common --gzip --result-file=/backups/d7-clean.sql

Two tables deserve a sentence each. cache_form is the one that grows without limit on sites where cron is unreliable, because every rendered form stores its state there until cron sweeps it; it is not unusual for it to be the largest table on the server. And watchdog on a site with the database logging module enabled and a high row limit is a decade of PHP notices nobody read. Empty both. If anyone objects to losing the log, export it to a file first; it does not belong in the migration source either way.

While you are here, flood, queue, batch, semaphore, history and the ctools_object_cache and ctools_css_cache tables are the same kind of thing: transient, rebuildable, and irrelevant to the new site.

Step 2: revisions

Revision history is the layer that most changes the migration’s run time, because it multiplies every field table. In Drupal 7 each field has a field_data_ table and a field_revision_ table, and the revision table holds one row per field per saved version. A body field on a site that saves often can have ten rows in the revision table for every row in the data table.

Decide a policy before you prune: for most sites it is “keep the current revision plus the last five”, with a longer tail for content types that have a legal or editorial reason to keep history. The Drupal 7 Node Revision Delete module applies a policy like that per content type and runs it in batches, which is safer than hand-written SQL against node_revision; deleting revision rows directly leaves the field_revision_ tables out of step. Once it has run, the migration still brings the revisions you kept, which is what you want.

If the client says history must be kept in full, keep it in full and budget the time. What you must not do is keep it by accident because nobody asked.

Step 3: fields that were deleted but never purged

When a field is deleted in Drupal 7 it is not removed. Its tables are renamed to field_deleted_data_N and field_deleted_revision_N and marked for purging, and cron removes a small batch of rows on each run, ten by default. On a site where cron ran rarely or the field was large, those tables can sit there for years. They are invisible in the UI, present in every dump, and useless to the migration.

drush php-eval 'field_purge_batch(100000);'

Run that until the field_deleted_ tables are gone. Then open the Field list report at admin/reports/fields and look at the fields that are still live. Every field you will not model on the new site is a field you can delete now, which starts the same purge and removes its migration from the plan. A field with no values in any row is the easy case; a field with values that nobody has looked at since 2017 is the common one, and that is an editorial decision, so put it in front of the content owner with the numbers from step 0.

Step 4: files

Files fail migrations in both directions. A row in file_managed whose file is no longer on disk becomes an error when d7_file tries to copy it, and a file on disk with no row is simply left behind without anyone noticing. The Audit Files module for Drupal 7 reports both lists, plus the files that have a managed record but no entry in file_usage, which are the orphans: uploaded, never placed, or placed on content that was deleted.

Resolve the missing files first, because they are the ones that will stop the migration: either find them in a backup or delete the records. Then decide about the orphans. The honest default is to delete orphans older than a year, keep the rest, and tell the client what you did. Do the same reconciliation for private files if the site has a private path; the migration copies from both.

Step 5: people and comments

Every account in users migrates, and so does every comment. On a site that allowed registration for a few years, that means thousands of accounts with a username, a throwaway e-mail address and nothing else, created by bots and never blocked. They cost nothing on the old site and they cost something on the new one: they get migrated, they appear in the people list, and some of them hold e-mail addresses you have no basis to keep.

Query first: accounts that never logged in, have no content, no comments and no role beyond authenticated are a safe list. Cancel them with the method that deletes the account and its content, in batches, with Views Bulk Operations rather than by hand. Treat comments the same way: unapproved comments older than a few months are spam that was never reviewed; delete them rather than migrating a moderation queue nobody will work through.

What we never delete without a named person agreeing: accounts that own published content, and anything with a role above authenticated. Those are editorial, not technical.

Step 6: content you are not bringing

By now the system layers are gone and what is left is content. Some of it should not come: content types that were abandoned, nodes that have been unpublished for years, landing pages for campaigns that ended, the duplicate of the About page somebody made in 2015. Each of those is a migration you do not have to configure, a template you do not have to build, and a decision you have to make.

Make it per content type, with counts. For each type: migrate as is, migrate into a different type, or drop. For what you drop, the URLs still exist on the internet, in Google’s index and in other sites’ links, and dropping the content without a redirect is how a migration loses traffic it did not need to lose. We wrote the repair guide for that in website migration SEO, and we published a module that builds the redirect map from the migration itself: Migrate Redirect Map reads the migrate map tables after the content migrations have run, resolves every legacy alias to the new URL, and lists every path that would 404 so the dropped content gets a redirect to its nearest replacement before launch.

Unpublished content is the trap in this step. Migrate Drupal brings it across unpublished, which looks harmless until you realise it also brought its revisions, files and terms. Delete what will never be published; keep what is genuinely a draft.

Step 7: data of the modules you are leaving behind

Every migration leaves modules behind, and most of them leave tables. Webform is the usual big one: webform_submitted_data can hold years of form submissions, and whether they migrate is a question for the client, not for Drush. Export them to a file the client can keep, migrate the forms, and let the submissions go unless there is a reason to keep them queryable. The multilingual modules are the other common case; the Drupal 7 translation tables are their own project, which is why they have their own article.

Then the custom tables. Every Drupal 7 site that was developed in-house has a few: a custom_import_log, a legacy_products_tmp. Nobody remembers them, no migration reads them, and they appear in the dump forever. List every table that does not belong to a module you recognise, ask once, and drop them. If a custom source plugin is needed for a table that does matter, Migrate Plus and custom source plugins is the guide.

After the clean

Deleting rows does not shrink MySQL tables; run OPTIMIZE TABLE on the large ones, or dump and reload, and then measure again with the query from step 0. Keep the before and after numbers in the project record. They are the one piece of evidence the client will understand, and they are the baseline for the counts you check after the migration: nodes per type, files, terms, users, before and after, should match what you decided to keep.

Then run the migration against a copy and read the messages table. A clean source produces a short list; a dirty one produces thousands of lines about missing files and unresolved references that hide the real problems. The 90-day migration calendar puts this dry run in week three for exactly that reason.

One thing not to clean: the migrate_map_ and migrate_message_ tables on the new site. They look like cruft once the migration is done. They are the record of which old ID became which new ID, they are what incremental re-runs use, and they are what the redirect map is built from. Keep them until the redirects are live and verified.

The uncomfortable part

Only step 1 and step 3 are technical. Everything else on this list is an editorial decision wearing a database table, and it needs a named person on the client side who can say “delete it”. Migrations run late far more often because nobody would make those calls than because the code was hard. Put the table sizes and the row counts in front of that person in week one, with a recommendation next to each line, and ask for a decision by a date. A migration that starts with that conversation is half as long as one that starts with drush migrate:import.

And do none of it on the live site without the snapshot from step 0. The cleanup is reversible until the moment you skip that step.

If you would rather have the audit done for you, send us the site and we come back with the table-size report, the list of decisions with our recommendation on each, and a fixed price for the migration that follows, from Dover, Delaware and Riyadh. Our Drupal development page describes what the migration itself includes, and the State of Drupal 7 census shows how many sites are still ahead of you in the queue.

Field Notes

Get the next one by email

Research on Drupal, WordPress and search. Measured, not guessed. New pieces straight to your inbox.

Company website

Email address Subscribe

Confirmed opt-in. One click to leave, any time.