Database Workflow Tool

Learn about the database that runs in your Pantheon Drupal or WordPress site.


The Pantheon platform provides each site environment with a dedicated MySQL container running MariaDB that can be maintained remotely or locally. For a comprehensive list of MySQL settings, access your database and use the SHOW VARIABLES statement.

Using the tools in the Database / Files tab, you can overwrite the databases on your site's Dev or Test environment with the database on your Live environment, allowing you to pull content from Live in to other environments. Workflow Tool

Warning

Pushing content up to Live should almost never be done to a launched site, as it can overwrite the environment configurations. See Using the Pantheon Workflow.

MySQL Clients

You can use any number of MySQL clients such as MySQL Workbench, Sequel Pro, Navicat, PHPMyAdmin, and others to administer your site's database and manage configurations as needed.

Cloning the Database

Cloning relies on mysqldump, which needs a point-in-time snapshot. We recommend using the --single-transaction flag, which will use transactions to get a point-in-time snapshot without locking the DB. However, only the InnoDB database engine supports transactions. On MyISAM, the table needs to be locked. On small DBs this is not an issue, but could be for larger DBs. We also use the --quick option, which reduces the time it would take for large tables. For more information, see Converting MySQL Tables From MyISAM to InnoDB and Run MySQL Dump Without Locking Tables.

Wipe Database and Files

Use this tool if you need to completely wipe your database and files for a single environment. Wiping completely resets the database and files, and you will lose all content for that specific environment. For example, if you wipe the Dev environment, Test and Live are not affected. You will then need to import the database and files from a backup, clone them from another environment, or re-install Drupal or WordPress for that environment.

Learn more about the Pantheon Workflow.

Troubleshooting

WordPress Content References the Wrong Domain After Cloning

WordPress sites with custom domains configured on multiple environments may see references to the wrong platform domain after cloning the database from one environment to another.

The Site Dashboard runs wp search-replace during the cloning workflow to update environment URLs automatically. This operation, however, only runs once on a single set of URLs. If the target environment has a custom domain (e.g test.example.com), it's used to replace the source environment's custom domain (e.g. www.example.com). This can cause the target environment to have incorrect references to platform domains (e.g. live-example.pantheonsite.io).

You can resolve this using one of two methods:

Using Terminus, you can run an additional wp search-replace command on the target environment after cloning. Set or replace the variables $site and $env with your site name and the correct environment:

terminus remote:wp $site.$env -- search-replace "://live-example.pantheonsite.io" "://test.example.com" --all-tables --verbose

The following example also converts the URL from HTTP to HTTPS, for situations where you might have HTTPS in one environment and not another:

terminus remote:wp $site.$env -- search-replace "https://live-example.pantheonsite.io" "http://test.example.com" --all-tables --verbose

For those using Quicksilver scripts, consider the following example. On each passthru line, replace example#.pantheonsite.io and example.com with the domains you want to find and replace, respectively:

<?php
echo "Replacing previous environment urls with new environment urls... \n";

if ( ! empty( $_ENV['PANTHEON_ENVIRONMENT'] ) ) {
  switch( $_ENV['PANTHEON_ENVIRONMENT'] ) {
    case 'live':
      passthru('wp search-replace "://test-example.pantheonsite.io" "://example.com" --all-tables ');
      break;
    case 'test':
      passthru('wp search-replace "://example1.pantheonsite.io" "://test-examplesite.pantheonsite.io" --all-tables ');
      passthru('wp search-replace "://example2.pantheonsite.io" "://test-examplesite.pantheonsite.io" --all-tables ');
      passthru('wp search-replace "://example3.pantheonsite.io" "://test-examplesite.pantheonsite.io" --all-tables ');
      break;
  }
}
?>

The example above replaces three URLs when cloning to the test environment with test-examplesite.pantheonsite.io, and replaces that domain with the example custom domain example.com when cloning to the live environment.

You can find this example and many others in the Quicksilver Examples repo.

Base table or view not found

Database errors may occur during a database clone, import or while wiping the environment. In most cases, the error contains semaphore' doesn't exist and is generated because the site is accessed before a certain database operation is complete. Simply waiting for database operations to complete resolves the error.

However, Drupal 7 sites using the configuration override system to enable CSS aggregation and compression ($conf['preprocess_css'] = 1;) will see the following error after wiping an environment:

Additional uncaught exception thrown while handling exception.

Original

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table &#039;pantheon.variable&#039; doesn&#039;t exist: SELECT 1 AS expression FROM {variable} variable WHERE ( (name = :db_condition_placeholder_0) ); Array ( [:db_condition_placeholder_0] =&gt; drupal_css_cache_files ) in variable_set() (line 1265 of /srv/bindings/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/code/includes/bootstrap.inc).

Additional

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table &#039;pantheon.variable&#039; doesn&#039;t exist: SELECT 1 AS expression FROM {variable} variable WHERE ( (name = :db_condition_placeholder_0) ); Array ( [:db_condition_placeholder_0] =&gt; drupal_css_cache_files ) in variable_set() (line 1265 of /srv/bindings/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/code/includes/bootstrap.inc).

You can fix this issue by wrapping the configuration logic within a conditional statement in settings.php:

if (!function_exists('install_drupal')) {
  $conf['preprocess_css'] = 1;
}

"Connection to server closed by remote host" notice when running search-replace

Sites with large databases may encounter a timeout when trying to run terminus wp search-replace on all tables. This is due to the idle timeout limit on SSH connections.

You can avoid this by configuring your local machine to send an SSH keepalive packet every 60 seconds. Add this to your $HOME/.ssh/ssh_config file:

Host *.drush.in
  ServerAliveInterval 60

See Also