Database Workflow Tool

Learn about the database that runs in your Pantheon site.

Discuss in our Forum Discuss in Slack

This section provides information on the database workflow tool for Pantheon sites.

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

You can use the tools in the Database / Files tab to overwrite the databases on your site's Dev or Test environment with the database on your Live environment. This allows 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. Refer to Using the Pantheon Workflow for more information.

MySQL Clients

You can use any number of MySQL clients to administer your site's database and manage configurations, such as:

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 database. However, only the InnoDB database engine supports transactions. On MyISAM, the table must be locked. On small databases this is not an issue, but could be for larger databases. We also use the --quick option, which reduces the time it would take for large tables. For more information, refer to 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 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 several methods:

There are several plugins with search and replace functionality. WP Migrate DB, for example, works well on our platform.

Make sure you select the Find & Replace functionality:

Enable Find & Replace

Another popular search-replace plugin is Better Search Replace. However, there is an additional filter that must be added for it to work on Live, as outlined in Plugins and Themes with Known Issues.

You can use Terminus to 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 --dry-run

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 "http://live-example.pantheonsite.io" "https://test.example.com" --all-tables --verbose --dry-run

Note: The example code above includes --dry-run, which executes the command but prevents permanent changes. Remove this flag when you are confident that the values are correct.

Consider the following example if you are using Quicksilver scripts.

Replace example#.pantheonsite.io and example.com with the domains you want to find and replace on each passthru line:

<?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;
  }
}
?&gt;

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.

 Note

In addition to the example above, URLs may be stored in an encoded format. If the example above fails to resolve all issues, search for patterns like %3A%2F%2Fexample.com and :\/\/example.com.

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] => 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] => 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

More Resources