Leftover migrate_map and migrate_message tables

+1

I am also interested in knowing the answer to this. After a successful migration, when one knows that they won't be doing any more migrations, is it okay to drop all migrate_* tables.

The tables absolutely should remain after running a migration process - the migrate_message tables are necessary for diagnosing any problems that may have occurred during migration, and the migrate_map tables are necessary to permit rolling back a migration or running incremental (delta) migrations after the initial bulk migration.

A related question is whether the tables should be removed when uninstalling the migrate module - there's an existing issue for that: #2713327: Provide a way to remove migration tables (ID map etc.).

@mikeryan, thanks for the feedback. However, lets say that my migration went well, I've done all of my cleanup, no more migrations/rollbacks will be performed, and I am moving forward without any further need for the migration functionalities. In that scenario, would it be okay to deactivate/uninstall the Migrate Upgrade module, then drop all of those tables? Thanks again.

Yes, the expectation is that you will disable the migration modules once you're done with them. Unless/until #2713327: Provide a way to remove migration tables (ID map etc.) is addressed, you can safely drop the tables.

Thanks mikeryan for confirming that

Just noting, after a completed migration, uninstalling the migrate modules doesn't appear to delete the migrate_% tables in the database. They need to be manually deleted after that.

Probably a few ways to do it, but here's a sample .install file update hook to remove them:

/**
 * Drop completed migrate_% db tables
 */
function MY_MODULE_update_8017(&$sandbox) {
  // Get table list
  $result = db_query("SHOW TABLES LIKE :table", [':table' => 'migrate_%']);
  // Init drop counter
  $count = 0;
  // Iterate and drop matched table
  foreach ($result as $row) {
    // Get table name from row data
    $data = array_values((array)$row);
    $table_name = $data[0];
    // Validate table name again
    if (strpos($table_name, 'migrate_') === 0) {
      // Drop the table
      db_drop_table($table_name);
      // Increment drop counter
      $count++;
      // Log dropped table
      \Drupal::logger('my_module')->info('Dropped table: @table', ['@table' => $table_name]);
    }
  }
  // Log total dropped table count
  \Drupal::logger('my_module')->info('Dropped @count tables', ['@count' => $count]);
}

For Drupal 9:

/**
 * Drop completed migrate_% db tables
 */
function MY_MODULE_update_8017(&$sandbox) {
  // Get table list
  $result = \Drupal::database()
    ->query("SHOW TABLES LIKE :table", [':table' => 'migrate_%'])
    ->fetchAll();  // Init drop counter

  $count = 0;
  // Iterate and drop matched table
  foreach ($result as $row) {
    // Get table name from row data
    $data = array_values((array)$row);
    $table_name = $data[0];
    // Validate table name again
    if (strpos($table_name, 'migrate_') === 0) {
      // Drop the table
      \Drupal::database()->schema()->dropTable($table_name);

      // Increment drop counter
      $count++;
      // Log dropped table
      \Drupal::logger('my_module')->info('Dropped table: @table', ['@table' => $table_name]);
    }
  }
  // Log total dropped table count
  \Drupal::logger('my_module')->info('Dropped @count tables', ['@count' => $count]);
}

Just wanted to bump for relevance and appreciate the Drupal 9 syntax for clearing the tables mentioned in #9