Posted by FunkMonkey on February 24, 2009 at 11:36pm
I have a multisite install setup using Drupal 6 using two separate databases. I kind of screwed up and didn't create one database using prefixes and a shared user table. And of course, we need that capability now. I think it should be possible to do exports and imports to get that working. However, I'm not terribly familiar with MySQL and I'm quite nervous I will butcher the whole process. So...
Is it possible to combine two databases and setup shared user tables?
Is there anything I need to watch out for in attempting to do so?
Is there any documentation anywhere on how to do this? (I did some searching but didn't find much)
Thanks for your time!
Comments
yes, with some work if you really need them in one database
You can share the user's table (and others) w/o moving the databases from their current locations. If you're merging into 1 database w/ prefixes just to share the user table then I can save you a lot of work as prefixing / sharing is setup in such a way that you can specify specific tables in specific databases. Here's what I typically share between databases
$db_prefix = array(
'default' => '',
'users' => 'courses.',
'authmap' => 'courses.',
'profile_values' => 'courses.',
'profile_fields' => 'courses.',
'role' => 'courses.',
'tinymce_settings' => 'courses.',
'tinymce_role' => 'courses.',
);
courses is sorta our central database / site so this houses the user account info where as every other site just looks to it w/ this setup. All you really need though is users and authmap
"Plaguing the world with Drupal; One Plone, Moodle, Wordpress, Joomla user at a time since 2005." ~ btopro
http://elearning.psu.edu/
http://elearning.psu.edu/projects/
http://elearning.psu.edu/drupalineducation/
Ex Uno Plures
http://elmsln.org/
http://btopro.com/
http://drupal.psu.edu/
Great solution..
Thanks! That's a great solution. Using the 'courses.' notation saved the day. That's a really great way to do it since you can still keep the databases separate and share just what ya need. Much cleaner than having tons and tons of tables in one database.
You're example is very similar to our usage. I don't map the role since users can do more at their own school than they can on the main district site. I'm doing this for a school district. We have one 'district' site and each school will have it's own site.. thus the need for a multisite setup.
Good stuff.
Can't access existing content
I am doing something very similar in that I am taking two existing sites, merging the users tables in a separate database and then having both sites access their original databases as the default. This works just fine on one of the sites I am testing this with, however, on the second site I am unable to access existing content. I get a "The requested page could not be found error" on all nodes. I am able to access the admin pages just fine. I am also able to access new content that I create, but I can't get to the original content. As soon as I remove the shared users database from the settings.php file everything works perfectly again. Here is what I have in settings.php:
$db_prefix = array(
'default' => '',
'users' => 'shared.',
'sessions' => 'shared.',
'role' => 'shared.',
'authmap' => 'shared.',
'profile_fields' => 'shared.',
'profile_values' => 'shared.',
'users_roles' => 'shared.',
);
I have organic groups enabled on the site that is not working with shared users....could that have something to do with it? Has anyone else had this problem? Suggestions on how to fix it?
Have you blown away your
Have you blown away your cache? It's possible things started to be built out based on the uid's in the local table and that those don't mesh correctly w/ the shared table. I know we had something similar to what you're describing happen when we switched to multi-site from non-multisite setup. Try that, otherwise I dono since it sounds like it's a permissions issue associated to user roles / what not.
"Plaguing the world with Drupal; One Plone, Moodle, Wordpress, Joomla user at a time since 2005." ~ btopro
http://elearning.psu.edu/
http://elearning.psu.edu/projects/
http://elearning.psu.edu/drupalineducation/
Ex Uno Plures
http://elmsln.org/
http://btopro.com/
http://drupal.psu.edu/
Cleared Cache
Yup, tried clearing the cache but that didn't help. I have also completely disabled the organic groups module so that shouldn't interfere now. I tried going through the shared tables one by one and disabling the sharing for role, users_roles, etc. but I'm still getting the error when I'm just sharing the bare minimum of users & authmap....
What about killing the
What about killing the sessions table or sharing them? Had that issue too in the past.
"Plaguing the world with Drupal; One Plone, Moodle, Wordpress, Joomla user at a time since 2005." ~ btopro
http://elearning.psu.edu/
http://elearning.psu.edu/projects/
http://elearning.psu.edu/drupalineducation/
Ex Uno Plures
http://elmsln.org/
http://btopro.com/
http://drupal.psu.edu/
Tried both sharing/not
Tried both sharing/not sharing sessions and I couldn't get it working either way. I'm thinking it may have something to do with the uids changing for the already existing content. Do you think that's possible? I think I'm going to try starting out with the users table exactly as it is on the site, then import some dummy users into it and see what happens on both sites. I guess this wouldn't explain why one site is working and the other isn't because they both started out with existing content and may have seen changed uids.
Sharing users...
I would think that the nodes would still show.. at least some of them. If the UID had changed it should just bring in the wrong user information... not fail entirely. Unless there was no matching UID at all I guess.. if you had deleted a bunch of people for example. I think I remember reading in the Using Drupal book that, if you delete a user, all of their content will disappear. So maybe your shared users table doesn't have the UIDs that the nodes are looking for. That could be a major complication for this method (or any shared user table). In my case I was able to wipe the old user table entirely since there wasn't any real content yet.
AGGGHHH!!!!! I just went back and looked and the few nodes that are in there are pointing to the wrong user. So yep.. I bet that is the problem you are having.. your nodes probably don't have a valid UID. Not sure how to fix that. You might be able to use Views Bulk Operations to change the Author on a chunk of your nodes and see if that helps.
Good luck!
update
I was able to use Views Bulk Operations module to update the author on my incorrect nodes. I did have an existing UID in there though.. not sure what would happen if the UID doesn't exist. Might be able to go into the database and add a user manually and use the old UID for that user.
update 2
I misquoted the Using Drupal book. They actually say "Users may also be deleted entirely, although it's generally preferable to block users instead of deleting them, so that their name stays attached to any content that they have posted."
I may have read elsewhere that the content disappears.. or I'm just plain wrong. Sorry bout that.
Thank you so much! I'll give
Thank you so much! I'll give that a shot. Since we have content on both sites this may end up being a little bit more complicated that I initially thought. I really appreciate everyone's help!!
Update:
I got this working now. It happened that on the first site, the developer used the anonymous user to create all of the content (no clue why you would do that) but that is the reason I was able to view content after the users table merge. I made sure that I had UIDs for the authors on the second site and then everything worked like a charm. I guess the ticket is to make sure that either you keep these UIDs around after combining databases, or that you assign a different author using views bulk operations as suggested by FunkMonkey.
Shared tables and sub-site specific tables
Yeah I have experienced the same thing.
Recently my company released at 60+ multi-site and our design was to have all tables but the sessions, user, authmap, and role tables prefixed per site. This allows for centralized users, sessions, and role management and because we had to integrate with an LDAP and OpenID server the authmap shared connection information for all sites.
A few things we had to include where:
1) Make sure the cookie variable is identical for all settings.php to share the logged in session across multiple sub-sites.
2) Write a cron job to clear the old sessions from the shared_sessions table as no Drupal sub-site will clear the table.. it gets pretty big after a few weeks of 100+ users creating sessions.
So each sub-site, including the default (it is a sub-site after all) had prefixes for all tables but the "shared" tables. To accomplish this either you install your first site using the drupal installer (recommended) or manually create the shared tables based on a throw away Drupal installation. Then use the newly created Drupal schema and data as a base for quickly create new sub-site database tables. We decided to keep all of the tables for all sub-sites in a single database. I found that trying to get drupal to share tables across multiple databases for a single sub-site was a nightmare.
Robert Foley Jr
Solutions Architect
http://www.robertfoleyjr.com
Thanks, this method works
Thanks, this method works great for me. However, I have a small question. Is there any performance issue when using a separate database for user table? As user table is one of the most frequently queried table in drupal, I am just curious to know about this.
clear tutorial
I have one active site I want to build another site and share the user table of the current site. I use many modules including organic groups etc. Is there any tutorial on this that is tep by step also are there any modules that are not compatible with this setup?
I believe the tutorial where
I believe the tutorial where we use a shared table between sites may be perfect to you.
You just follow it, backing up user tables and restoring them on the shared table, configure settings.php for both sites and delete these tables on them.
If your sites share the same main domain you can even share logons, if not unfortunately ppl will have to login and logout separately...
I myself dropped the shared table mode, I was testing Domain Access module and it is much more flexible. For each content we can set in which site they will appear, much easier.
Of course you must backup everything before trying it :P
I didn't do that on my test site and all of a suddenm after a LOT of configs, I face the old Access Denied error and end up deleting everything and restarting from scratch...
https://elearning.psu.edu/dru
https://elearning.psu.edu/drupalineducation/?q=software-multi-site
This could help... detailed a bunch of it here.
"Plaguing the world with Drupal; One Plone, Moodle, Wordpress, Joomla user at a time since 2005." ~ btopro
http://elearning.psu.edu/
http://elearning.psu.edu/projects/
http://elearning.psu.edu/drupalineducation/
Ex Uno Plures
http://elmsln.org/
http://btopro.com/
http://drupal.psu.edu/
have personal users, aside from shared ones: possible?
hy, i configured to have users on new site B from another subsite A i had already populated. (weird thing: for some reason it would accept sharing table only if in cpanel i would allow same mysql user to both databases).
now, i want to be able to make registrations on the new site B without sending this users to site A as well. I only want to allow users from site A to login on site B.
the problem is, whenever i click on user/register into site B, it redirects me to user/login in site A and anyway, users created by admin on new site B don't appear on the member list (of site B).
What could i do?
Thank you for advice
to be more clear
just to mention: it's a specific role of users that can login to site B only after registering to site A. the other roles of users from site B shouldn't be able to appear or login into site A.
domain access i use on other websites, but it's pretty complicated. i suppose a line of code would be enough here?
something i miss in
$db_prefix = array(
'default' => '',
'users' => ''sharedwithA.',
'authmap' => 'sharedwithA.',
'sessions' => 'sharedwithA.',
'profile_fields' => 'sharedwithA.',
'profile_values' => 'sharedwithA.',
'role' => 'sharedwithA.',
'users_roles' => 'sharedwithA.',
);
Salesforce involved in the. Equation
Hi, i have two sites (a and b) with two db, 2 users table (obvious) and want to construct one new site (c) and retrieve the data from the other two. For what i read here, this is doable, right? My only doubt is that salesforce is providing the users to a and b, is this an obstacle in the process?
Thanks
Nico
www.theverylittleagency.com
Combining two sites user's
Combining two sites user's will introduce a problem with (most probably) overlapping user id's (users.uid, for example Drupal superuser id=1 twice). You could merge table data without uid's (but store 'original uid' and 'original site' to allow later updates), but as Drupal uses uid everywhere as a key you could not share sessions accross sites anyway.
Maintaining user data (users, authmap, sessions and some other tables) will be difficult unless you clearly have exactly one source but two separate sources for site 3.
Best bet is to create some sort of single sign on -system for site 3 and allow users to login through site 1 and/or site 2.
--
Perttu Ehn
Shared Tables
This is an article that might help you: Shared Tables
Thanks
Thanks to both for the fast answer.
@rpsu, the easing point here is that a and b don't need to be accessed by users (only admin, of course) but the users merged in the last site (c) do have to login and every data update in c, will be sent to a and b and at the end, exported to salesforce.
Anyway, after seeing the complication and after reading Lakeside's post, I will take a different road.
Anyway, thanks for the help, a new thing learnt.
nico
www.theverylittleagency.com