This article explains a practical implementation of a technique outlined in the article "Sharing Drupal tables between databases using MySQL5 Views".
Problem
You have multiple (multisite) Drupal sites and you would like to manage the content for all of these sites through a single interface. Depending on the nature of a given piece of content, you may want the content published on one, several or all of your subsites, but you do not want to have to create copies of the same content for each site.
Solution
Taxonomy plus MySQL5 views. (NOTE: this solution will not work with versions of MySQL prior to 5.)
Assumming you have your subsites properly set up and running, the first step is to create a special vocabulary which you will use to target content.
Go to [your site's baseurl]/admin/taxonomy/add/vocabulary and create a vocabulary. We'll call it simply "sites".
Next, go back to your taxonomy page (/admin/taxonomy) and select "edit vocabulary" for the "sites" vocabulary.
Add a name for each of the subsites you would like to manage. For our example, we'll have two subsites, "foo" and "bar", and one master site, "master".
Now add at least three pieces of test content. Target one piece of content for each of foo, bar and both.
Next, we're going to create a node view for each of our subsites that we'll use to replace the actual node table.
The SQL is as follows:
CREATE VIEW [subsite, eg. "foo"]_node AS
SELECT n.* FROM node n, term_data td, term_node tn, vocabulary v
WHERE v.name = '[vocabulary name, eg. "sites"]'
AND td.vid = v.vid
AND td.name = '[subsite vocab term, eg. "foo"]'
AND td.tid = tn.tid
AND n.nid = tn.nid
;
Because the terms that serve as our subsite labels may very well exist within other vocabularies, we also need to join on the vocabulary table to ensure our solution works reliabley.
Finally, we need to have our subsites use the views we have created instead of our master nodes table, which only the "master" site will have access to directly.
In your drupal's sites directory, you should have directories that correspond to each of your drupal sites (both master and subsites). Edit the settings.php file for each of your subsites, and use the db_prefix variable to point the site to your view. So sites/foo.example.com/settings.php would contain the following:
$db_prefix = array(
'node' => 'foo_',
);
At this point, you'll want to disable creation of content from within each of your subsites. You can do this in the from the admin/access page. If you attempt to create content from within the subsites, you'll likely get a 'duplicate key' error.
I hope that explanation is clear. These articles are written rather hastily. If you questions or suggestions regarding this solution, please leave a comment.
Is there any way of doing this without using MySQL5 Views?
You can share content between sites using table prefixing. But you can't apply the approach here (to hide some certain content from one or more of your sites) without using views.
Upgrade!
You don't need to do any JOINs in the sql above.
If you're not using JOIN, then wouldn't it make your WHERE clause longer than what is readable? JOINing queries and tables have always been the shorter (and smarter) way.
Any suggestions/alternatives?
I summarizing what i have done and pls tell me which part i did wrong :
1) created vocab "sites"
2) created term "foo", "bar" & "master"
3)created content targeting each sites. when adding story/page, just select from the pulldown menu since I enabled this "sites" vocab for this page/story content type.
4) created view. there's 2 database: master and subsites1. Run SQL query/queries on database "subsites1". I edited the original, since all SELECT are from different database, "master"
CREATE VIEW foo_node ASSELECT n.* FROM master.node n, master.term_data td, master.term_node tn, master.vocabulary v
WHERE v.name = 'sites'
AND td.vid = v.vid
AND td.name = 'foo'
AND td.tid = tn.tid
AND n.nid = tn.nid
;
5. This created a new "foo_node" table in "subsite1" database with the content which was created earlier. I also notice any new content will be added in the "foo_node" table automatically. any content created from master site that is tagged/targeted with "foo" will be captured there. this is what VIEW all about ...isn't.
6. In the subsite settings.php add this. Since I want to share users info, I also put it there. note that, for node it will get from default database, but with prefix "foo_".
$db_prefix = array('default' => 'subsite1.',
'users' => 'master.',
'sessions' => 'master.',
'profile_fields' => 'master.',
'profile_values' => 'master.',
'role' => 'master.',
'authmap' => 'master.',
'sequences' => 'master.',
'node' => 'foo_',
);
6. So now moment of truth. Check in the mastersite, all content created earlier listed. But, when I go to subsite, it show's n/a?? What's wrong here? The content is there in the "foo_node" table in "subsite1" database.
8. Now, If I were to create content from subsite, yes, I get - duplicate error message. What if I want this capability. How to avoid duplication? That mean content can be either master of subsite, but of course, if content created from subsite, it will be automatically stored this their respective database and shown in it's domain. If content created from master, it must be tagged accordingly.
I also checkout Drupal. how can OG be a help? Any comparison?
pls advice.
Hi najibx,
I try same thing for my site and I also get stopped at same point with “n/a” problem…
I try lots of debug for this issue and I come up with below listed solution to avoid this problem…
You need to follow same steps as you mention over here, just one minor change in step # 6……
In drupal when ever we add new content in our site it stores data in two tables “node” and “node_revisions” to store node type, title, status, etc. and uses “node_revisions” table for teaser, body, format, etc. so as per our situation when we try to display node data from “subsites1” it tries to get data from subsites1’s “node_revisions” table. So instead of subsites1’s “node_revisions” table we need to share “master.node_revisions” table.
Replace the code:
$db_prefix = array(
'default' => 'subsite1.',
'users' => 'master.',
'sessions' => 'master.',
'profile_fields' => 'master.',
'profile_values' => 'master.',
'role' => 'master.',
'authmap' => 'master.',
'sequences' => 'master.',
'node' => 'foo_',
);
With
$db_prefix = array(
'default' => 'subsite1.',
'users' => 'master.',
'sessions' => 'master.',
'profile_fields' => 'master.',
'profile_values' => 'master.',
'role' => 'master.',
'authmap' => 'master.',
'sequences' => 'master.',
'node' => 'foo_',
'node_revisions' => 'master.'
);
Enjoy !!!
for step # 8
I am working on it...
when ever i get solution i will get back here....
These problems were solved in the latest release of drupal, FYI - along with many computer security issues such as preventing trojan horses, etc.
the substitution issues should no longer be a problem
I've been using this technique to power three very similar websites that target different geolocations. Very helpful write up .. thanks very much.
That's where this technique can really reach it's maximum potential. Sub-Niching of existing sites.
Secured Loan Options
thanks for this,
Work great !
Harry, I Don't mean to be such a noob but as powerful as all this is and an interesting, there is just one problem. Most all of it is over my head. On a scale of 1-10, me being probably a 2 with this where do I need to be in order to be able to apply it? Where do I go to expedite the process or is there really no hope and should I stick with learning incrmentially and giveup hope of catching up? Sorry for all the questions but I am hoping against hope you have some guideance for those of us that are way behind. If there is no hope we will just admire from afar...
This is awesome! I wonder, has anybody done this with CCK tables too or made a list of all the tables that one would need in order to use this to manage keeping content "synced" for a dev and production site?
This seems to me like a potentially great way to handle the problem of needing to have users working on the live production site, adding content, comments, etc. while development happens on another site. Anybody have thoughts about that?
Wow!
This is a great write up. Thank you Harry!
I am curious to know a sammos, if this view can be created based on a CCK field as opposed to a taxonomy vocab?
What about the move to 6.x? should everything work the same?
Once again, thank you for this awesome tutorial.
I am a Drupal/CMS newbie - basically an HTML/CSS/JS/Flash guy - but I got a gig administering a setup where there are multiple sites, with content being authored by multiple users, some content to appear on more than one site, some on just one. So I picked Drupal, got a multi-site, multi-db setup working locally, and now, thanks to this tutorial, a way to author content on the "master" site to appear in one or many sub-sites. I did a bit of floundering before I got it working - for instance, I did need to implement the 'node_revisions' line in $db_prefix, even though I'm on 6.4 - but it's working fine now.
I am intrigued by the line in the tutorial, "Disable content creation in the subsites," as I don't know how that can be achieved. But I figure if I only give content authors a login on the master site, and don't share users between sites, then they can't create content on the subsites.
Now I need to figure out how to let the authors decide where on the subsites the content will appear, but I imagine that's standard Drupal functionality.
So, thanks for the tut. It is valuable enough that you should consider revising it to include some of the comments.
Dale
A great demonstration of what is possible with drupal!
Drupal really is a great CMS, albeit somewhat difficult to use for those new to sing a CMS.
Tom
Great stuff - I am moving away from WordPress to Drupal so it's great to see you are using it here. Seems like it has a solid following and you've done some great stuff with it on this site.
Cheers!
Marnie, Acai Berry Blog
Thanks cool post.
energy performance certificates.
Hello from france , thanks a lot
I've been using wordpress for the longest time as I thougbht it did an ok job of managing multiple blogs but as of late I've been somewhat frustrated by the lack of user friendliness in terms of swithing between accounts. I've been resing up on drupal 6 to see if this app can do a better job of managing multiple accounts and that's how I stumbled on your site. Thanks for the info. -Phil
Umm. it doen't work for me..
Do i have to join the table together?
or i may set sth. wrong
:~
-----------------------------
IELTS
Umm. it doen't work for me.. Do i have to join the table together? or i may set sth. wrong :~ ----
I'd like to run Drupal on multiple domains (multisite), but I want to use only one Drupal instance, it means only one database and only one db prefix. My idea is to have all my content together in one database and somewhere in configuration only set something like "this node is root of this domain and that node is root of that domain". Is it possible?