Share tables across instances (not recommended)

Last updated December 18, 2011. Created by puregin on May 7, 2005.
Edited by sertaconay, loopduplicate, damien_vancouver, christefano. Log in to edit this page.

You can share tables between Drupal installations by setting table prefixes ($db_prefix) on only some tables but not others. One interesting application for this is to share the taxonomy tables (vocabularies, term_data). Another interesting use is to share users across Drupal installations. To share tables in Drupal, sites must be shared in the same database .

Warnings!

This procedure could result in unexpected results, depending on which tables you choose to share, including broken version updates and/or security holes.

Upgrades to the next version of Drupal are not supported! Be sure you have read EVERYTHING on this page, most especially the issues surrounding a major Drupal upgrade, before considering using shared tables!.

Sharing tables may also introduce security issues. For instance, if one site is compromised, an attacker could compromise the shared database tables and compromise your other sites!

You will NOT be able to upgrade Drupal to the next major version unless you first convert your database back to a non-shared database. This conversion will require intermediate to advanced SQL administration skills and is not for beginners! If you cannot update Drupal core, your site has a limited lifespan of just a few years! See the bottom of this page for information on updating, and be sure you understand it and feel confident you will be able to successfully upgrade when the time comes!

Share as few tables as possible

Plan carefully and share only the tables that you need to. This will ensure that when it is time to upgrade Drupal to the next version, converting your database back to non-shared is as little work as possible.

Preparing the sites in a shared database

Before you set up shared tables, you must first have two or more sites in the same database. Drupal prefixes the table names of multiple sites using the $db_prefix variable from each site's settings.php file.

To install a new shared site, or create a multi-site database from two or more existing sites, follow the instructions at: Share a single database across multiple sites.

It is important that you ensure that your sites are working properly in the multi-site database before you try and share tables using a $db_prefix array!

Setting $db_prefix to share tables

The comments in settings.php explain how to use $db_prefix to share tables:

To convert from a single $db_prefix to the shared table array, you should first set your existing prefix as the 'default' element, and then add in prefixes the other site's tables for each table you wish to share.

Example: Sharing Users

This example shows how to share users. The users from a master site prefixed with 'master_' are shared to a slave site prefixed with 'slave1_'.

Things to be aware of

Which tables that you will share depends on your site's specific needs., but remember that you should share as few tables as possible!

The sequences table is usually required if you are going to share other tables. If you are combining two existing sites, which both have their own sequences tables, it is essential you merge the sequences data so that the shared sequences tables have the maximum ID value for both sites. Otherwise you will receive database errors and even worse, user submitted content will probably be lost!

The following tables contain data that is highly site specific and therefore should not be shared:

• cache and cache_*
• variable

Note:You can only explicitly specify which tables will be shared and not the other way round. For example the following may fail and is not recommended:

Upgrading Drupal to a new version with shared tables

Be aware that upgrading with shared tables is not an easy task! You should be comfortable with renaming database tables, as well as cloning and merging databases.

If you try and upgrade without first preparing your database, you will have problems, because this will happen:

1. On the first site -> Shared tables get updated with the hook_update() of particular modules which are responsible for them
2. On the second site -> Shared tables get updated again (get broken), because the second site still holds schema info and module version info from before the install and the hook_update() scritp is executed again.

To update your shared table sites successfully, you can try one of the other two options. There are still other ways to tackle the problem as well, but these two options should give you some ideas on how to go about it.

Option 1 - Convert back to non-shared tables with multiple databases

1. Create separate temporary databases for each site by cloning the shared database. So if you had three sites in database shareddb, you'd make three new databases. Name them something like shareddb_site1, shareddb_site2, and shareddb_site3. Each of these databases would have a copy of all of the shared tables.
2. Place your sites in Maintenance Mode or otherwise take them offline for the rest of the procedure. You do not want users logging in and making changes during the procedure, or you might lose the changes!
3. Modify the settings.php files for each site so that they are running out of their temporary databases, no longer out of the shared one.
4. Upgrade your Drupal codebase (PHP files) with the new Drupal core files.
5. Run update.php for each site to upgrade its database.
6. Merge all of the upgraded tables back into one database again. You will have to choose one site to take each shared table from. As long as uses have not been creating content while you have been following these steps, it will not matter which site you take them from. Otherwise, take the shared tables from the site that has been updated the most recently.
7. Modify the settings.php files back to how they were, using the new merged/shared database with the upgraded tables. Your sites should now be upgraded!

Option 2 - Upgrade in shared database (Advanced)

1. Run update.php on one installation and look through the report of changes which prints itself at the end to see which tables were updated
2. Determine which modules are responsible for updates to shared tables - this should not be difficult as usually modules do not change tables of other modules
3. Manually update schema versions in the {system} table of each of the other sites for these modules. THIS IS TRICKY - if a module does changes to both shared and not shared tables you have to notice this and fall back to Option 1, as otherwise some updates to the not shared tables will be skipped
4. Run update.php for remaining sites – this will not run hook_update() on shared tables again because drupal will think these tables are up do date (from the {systems} table)

   Option 2 is more complex but has it benefits. It will not require you to put your site into maintenance mode for longer periods of time. With Opton 1 you have to take your sites down for the entire procedure, otherwise users may change data on a site whose non-shared table you are planning on throwing away. So if you have an active community on you site, Option 2 is preferable and will be much faster, but will take more preparation and more advanced skills.