Multisite installation fails with shared tables

Did you follow this part of the instructions?

 * To have all database names prefixed, set $db_prefix as a string:
 *
 *   $db_prefix = 'main_';
 *
 * To provide prefixes for specific tables, set $db_prefix as an array.
 * The array's keys are the table names and the values are the prefixes.
 * The 'default' element holds the prefix for any tables not specified
 * elsewhere in the array. Example:
 *
 *   $db_prefix = array(
 *     'default'   => 'main_',
 *     'users'     => 'shared_',
 *     'sessions'  => 'shared_',
 *     'role'      => 'shared_',
 *     'authmap'   => 'shared_',
 *     'sequences' => 'shared_',
 *   );

kwurzel: just to confirm, you should only have:

$db_prefix = 'sub_';

and no changes to the array if you're prefixing every table. You shouldn't need the array at all for either your example.com or sub.example.com install.

OK so you're sharing tables then. That helps to track this down. Sharing tables hasn't been officially supported in previous Drupal versions, I'm not sure if that's changed for 6.x, but I'm downgrading to 'normal'.

There's also a good chance this is duplicate of: http://drupal.org/node/210238 (which is already critical)

@catch: I don't think this is a duplicate of /node/210238. That issue appears to be a problem just getting the proper DB authentication working. kwurzel has obviously already gotten that portion working or the DB would not be getting updated to generate these errors.

Wow that is heavy. I am using shared tables on Drupal 5.

TR

Ok, this is probably a documentation issue: to install a new instance with shared tables, you have first to let drupal create all tables in the new prefix, and only then to point it to the shared tables.

What is not supported is updating or installing while sharing tables.

Hi Damien,

what do you mean with

What is not supported is updating or installing while sharing tables.

?

I have two drupal-6.2 web sites sharing (code and) tables in this way:

First site:

$db_prefix = '';

Second site:

$db_prefix = array(
      'default' =>'',
      'accesslog'=> 'slave_',
      'blocks'=> 'slave_',
      'blocks_roles'=> 'slave_',
      'boxes' => 'slave_',
      'cache' => 'slave_',
      'cache_block'=> 'slave_',
      'cache_content'=> 'slave_',
      'cache_filter'=> 'slave_',
      'cache_form'=> 'slave_',
      'cache_menu'=> 'slave_',
      'cache_page'=> 'slave_',
      'cache_update'=> 'slave_',
      'cache_views'=> 'slave_',
      'menu_custom' => 'slave_',
      'menu_links' => 'slave_',
      'menu_router' => 'slave_',
      'variable' => 'slave_',
);

Do you mean I won't be able to upgrade modules in the future ?

thank you

Do you mean I won't be able to upgrade modules in the future ?

Yes exactly. You will have to fall-back to non-shared setup to perform upgrades. Moreover, you should not share cache_* tables, who knows how those could conflict?

wait what? how is this a documentation issue?

@sepeck: installation or upgrading with shared tables has never been supported, and just CAN'T work. There is nearly no documentation (or really outdated stuff for Drupal 4.7.x) on that matter in the official handbook, even to clarifies what's possible and want can't be done.

I am not sharing cache table, but if it is not possibile to upgrade or install (automatically) it means that Drupal doesn't really support table sharing.
So by now multi-site just can be:

1) same code, different tables, different content for different domains
2) same code, same tables, same content for different domains

For table sharing I will have to monitor table structure during module upgrades... good luck to me.

I am checking "domain" project, but unfortunately it's still a beta, perhaps that will be a good solution.

@Devis: you can also use variable overrides (the $conf array in your settings.php) to selectively alter variables (for example the theme) of a multisite. So you can easily have:

3) same code, same tables, same content but a different configuration

I just ran into this issue this morning with my Drupal 6.2 site configuration.
I tried to install different subdomains on my XAMPP configuration with shared tables in order to have different content and theme for each subdomain.

I believed that it was possible since I found how to set different table management using $db_prefix and $base_url ().

Anyway, I need a multi-site configuration in which I have have 6 sub domains and the www.domain.com is the front page to guide the user to sub1.domain.com, sub2.domain.com, and so on. Each subdomain has its own content, taxonomy, blocks and theme.
But I need to share the users for all the subdomains and some content and block too. E.g.: there are navigation links and footer content information to be always present in each page.

The question is: may I share at least some of this content along all the subdomains with tables?
Secondly: I now have a local www.domain.com and a sub1.domain.com working perfectly and sharing tables without problems. Instead, when I tried to install other two subdomains Drupal showed me errors like those reported by kwurzel. Why a subdomain works and the rest not?
Thanks in advance

Changing component

I was just trolling through the old issues and thought this one seemed fairly important to the installation documentation.

Should we do something with this content: http://drupal.org/node/2622 like add a serious warning re. upgrading or even remove this section completely? I recently moved it to the main installation guide (as part of the process of archiving the 4.x guide) but after reading this thread I'm thinking it shouldn't be there at all and maybe I should move it back to the (archived) 4.x guide

I'm changing this to critical for the moment.

Hm, well that page is covering two things and the info in it is outdated. I think we need to make those two things clear and distinct. We should have one page re: sharing a database for multiple sites (db prefixing), which is supported, and then another "this is unsupported, warning, warning" page about how to share tables for the brave of heart.

So that page needs to be updated (e.g. there is no database/database.x anymore and all in all I don't think it is this complicated anymore - I don't know since I've not done it myself) and remove the section about "If you want to share users across multiple sites..." since we already have http://drupal.org/node/22267, which needs the raging warning on it.

I added the big warning to http://drupal.org/node/22267 and also changed the title to reflect the questionable nature.

I don't know enough about sharing a database on multiple installations via prefixing to systematically update the other page, though.

The warning from #21 looks good. I can help revising http://drupal.org/node/2622.

These Multiple site installs sharing one database could be an important use case IMO (for people who only get one database included with their hosting package).

The issues as I see them with http://drupal.org/node/2622

1) the page does not properly document the multisite installation process, as described in the original issue of this thread, and the solution was described in #10.

2) The page is horrifically out of date, referencing Drupal 4 instructions, versions, and scripts. It needs a modern rewrite.

3) it's not clear if upgrades are possible? ... or how one would go about a major core version upgrade if sharing a database (but not sharing tables). If this procedure is difficult or impossible, then that should be clearly identified on 2622. That way the risks are presented at the start, and do not come as an unpleasant surprise when one's site cannot be upgraded later.

I will try out multisite installing and upgrading to answer these issues and then come up with appropriately updated content for 2622.

that's fantastic, thanks damien!

Getting down to this, I have to update http://drupal.org/node/22267 quite a bit first, so that the original issue of this thread is solved (the poor instructions about how to do a shared table install). I've removed any duplicate documentation on how to do a multi-site database install and will be updating http://drupal.org/node/2622 with proper documentation next.

OK, http://drupal.org/node/2622 is also updated.

Please review them in order, http://drupal.org/node/2622 is first, followed by the more advanced and not recommended http://drupal.org/node/22267.

I still need to do the research/testing on updating Drupal with table prefixes, and if necessary add more warnings to 2622 about that. I will then verify that the instructions for both articles can still work on D7, and we can ammend as necessary.

As the guy who just added the warning to the top of 22267 way back when, it looks great :)

And as someone who hasn't done many setups with table-prefixes, the revised documentation at 2622 looks strong to me.

Thanks, Damien!

adding tags

Since info on how to share tables is in settings.php, there should be an information there about the issue with updating. Not everyone will be lucky enough to google these issues, but everyone will read settings.php

I if I understand correctly, this is what happens when you run update.php:

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 agaim

So what you really should do to avoid issues during update is to:

SOLUTION 1
1. remove the array info on shared tables from settings.php
2. run update.php on each site as if it was not sharing tables - you have to have “dummy” tables with default prefixes created and up to date for the script to success without errors.
3. bring back the array with shared tables into settings.php

or
SOLUTION 2
1. run update.php on one instalation 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 {system} table of remaining 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 SOLUTION 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 knows these tables are up do date (fron the {systems} table)

SOLUTION 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 SOLUTION 1 you have to do it because other wise your users will run into problems. For example if you share a users table (which seems to be the most common), users will not be able to log in while your're pointing settings.php to "dummy tables" instead of the shared ones with and those who set up accounts at that time, will loose them, once you move back to shared tables. So if you have an active community on you site SOLUTION 2 is preferable.

Anyway, I think the documentation should explain in more detail as to what happens when you try to update an installation with shared tables and what are the options. A statement "Upgrades to the next version of Drupal are not supported!" somewhere in a page on drupal.org seems insufficient to me.

I can volunteer to write something for settings.php but I am not adept enough to be sure that I am 100% correct with what I wrote above.

@MagicMcJ: Thanks for that detailed information! I agree that more information needs to be in the documentation than "it isn't supported". I would be happy to try and refactor your description from #28 into the documentation page for shared tables at http://drupal.org/node/22267 (unless you'd prefer to do so yourself, please let me know in that case). I will try and present what you've given as "the instructions", with some extra warnings about backing everything up properly first, and so on.

I must also still test updating a shared database installation and include update instructions in http://drupal.org/node/2622. If anyone reading has some detailed information on the core upgrade process with a shared database between multiple sites (along the lines of #28 but for the shared database scenario), it would be helpful to know beforehand! But I expect the shared database upgrade will likely just work out of the box, unlike the extremely tricky shared tables scenario.

Shared database will work out of the box as each installation acts only on it's tables, which are determined by the prefix from settings.php. The only trick is with shared tables, when more than one installation operates on the same set of tables.

Please use whatever you want from #28.

Sounds good! I am most interested in testing to make sure it's not easy to clobber (or double-update) the wrong set of tables when doing a shared database installation. this will probably come down to just a strongly worded warning when doing the install.php script (ie. whatever you do, don't get the prefix wrong). Also suggesting people who are sharing databases between sites not have one site using default (of no prefix) might help avoid this issue when upgrading. Thanks again!

OK, I have finished edits to the two pages in question. Upgrade information has been added to both:

• Share a single database across multiple sites: /node/2262
• Share tables across instances (not recommended): /node/22267

Thank to this thread's contributors, these handbook pages which everyone reads have been updated from Drupal 4.x days and should now be accurate, including the appropriate warnings for the shared-table stuff. What we really need now is one of you to agree that these pages are reviewed and tested by the community, and mark the issue as so in its status.

@MagicMcJ, I agree that there should be a warning under the shared table example in settings.php, since it includes how to do it. But, this issue here is about the poor quality of the old documentation, so let's mark it reviewed & tested and then I suggest we open that as a separate issue for a core settings.php patch. We can reference this thread in the new issue, which would be a patch to core's settings.php. Meanwhile we can get this bug marked reviewed & tested and closed, as the documentation issue is now fixed.

So... if anyone could review them both for me and make sure they read OK, I'd love to see this issue reviewed and able to get closed! It's marked critical and really is fixed IMO (especially when you consider how the pages were in the past).

Both of the above look good for me. I think we can close this and just in the final link give a link to the new issue.

Thanks MagicMcJ! I am going to close this issue then and call it done!

As for the settings.php changes, let's start a new issue then link to it like you suggest. Core patches are of course read by a *lot* more people so it will be easier for us with a clean issue. I'm not sure if it's good or bad practice to include links to a Drupal node (ie. /node/22267) from within settings.php, or whether we need to spell out all the warnings again there. I also don't like the example they give you in settings.php, I think it should be more like the shared users table example on 22267.

I'll do some inquiring on IRC today and try and find out what the correct way would be, then I'll make an issue and link to it from here, and perhaps you and I can try and push that one through? I agree with you 100% there needs to be additional warnings! Thanks again for reviewing the changes!