Posted by drm on January 22, 2010 at 5:30pm
I have a functioning multisite with completely separate databases, but I want to switch to share the user and sessions tables. After I change the settings.php file, do I need to rename that table on the lead database with the prefixes? I asked this on the drupal multisite group and nobody responded about changing an existing multisite. I've seen some tutorials about how to set it up from scratch, but am assuming this allows install.php to do some database configuration, which it can't for existing and well-populated sites. Or maybe I need to install from scratch and then upload database dumps? Thanks for any help.
Comments
Could be painful.
I'm assuming that you want to go to a shared user tables since the users of one site are also users of the other site. - correct?
This could be fugly, and a fairly tough nut to crack. Problem is this: you have users in each of the separate databases which likely have the same uid info in each database (uid 100 in one site is Joe, uid 100 in the other site is Bill). Anything that those users have created (comments/nodes, other content) will be associated with that uid in the separate databases, so you cant simply glue the two of them together.
In order to do that all the users in one of the databases needs new uid's so as to not conflict with the old ones.
However, they may already exist in the other database (they have a login at both sites), or they may not. So there could be user name conflicts as well as uid conflicts.
So you'd have to take a users data from one db, and assign them a new uid from the shared table, either by adding them to the shared user table and getting a new key, or use their existing key if they already exist in the shared user table by username. Then you'd have to go back to that database and update all existing references to their old uid to reflect their new uid. Not a lighthearted endeavor by any stretch, but its doable. And Im sure theres something I'm forgetting.
This isn't a problem . . .
We have sites up and running but are still in an advanced development phase. The only users are either developers or testers, and most have not created content. There are only two distinct uids in the node tables. Duplicate uid issues are nonexistent so far.
I still need to know by what process the user and sessions tables get renamed - do I have to do it? Can update.php do it? Etc, etc.
Much much easier then
Oh.. thats much much easier.. phew..
Are each of the sites in separate databases (settings.php $db_url is pointing at different locations),
or are they running in the same database with different table prefixes?
The user and session tables can be created a number of ways.. I would do it with phpMyadmin, or cli mysql, but you could also do it with update.php in an custom module.install file.
completely separate databases
Each site has a completely independent datatabase specified in the settings.php file.
I expect I would use phpmyadmin to do any db manipulations that I need to do, though I could use the mysql command line if I ran into problems. It's a dedicated host system so I have complete access and control.
Of course the site already has user and sessions tables. So since all the other tables already exist without prefixes, would I put this in each settings.php:
$db_prefix = array(
'default' => '',
'users' => 'shared_',
'sessions' => 'shared_',
);
then delete user and sessions from the other site's databases, and rename those two tables in the master site's database?
Since they are actually in
Since they are actually in separate databases, we've actually got them set up with the following:
$db_prefix = array(
'default' => '',
'users' => 'shared.',
'sessions' => 'shared.',
);
With dots instead of underscores which causes it to select a separate database other than the one specified in the db_url connection settings. ('shared' in this case is name of the master site database).I do believe that this requires that both databases have the same username/password credentials.
We're still in the midst of fully testing this, and there may be some edge cases where specifying dbname.tablename using the $db_prefix that could be problemating (notably contrib modules that forget { } around the table names). But this is working for us so far. YMMV.
It's working
This method is working for me, and I would point out that the documentation for the multisite_login module specifies the same method for sharing tables across a multisite.
Also, if any contrib modules managed to get through without database accesses not using the curly braces, they would get tagged for security holes. It's a pretty basic aspect of Drupal security for module developers, so I can't imagine that popular contrib modules might have this problem. If they did, I would want to know!
Wouldn't it be . . .
Wouldn't it be a security problem to have a multisite with a large number of live sites all using the same mysql account to access the database?
The alternative of using one database with prefixes not only shares that problem, but how many thousands of tables can a single database handle efficiently?
Depends
Well.. I dunno about it being that big of a security issue. As far as Im concerned by the time somebody has obtained one of your mysql passwords they have already compromised your system in some other fashion. The fact that the mysql passwords are stored in plain text on disk leads me to believe that having multiple database users with multiple passwords wouldnt really be all that much more secure, as they would all still have to be written down in the same place.
Number of tables in a database isnt really an issue. The query volume, nature, and complexity are more an issue when determining what the database can handle. You could probably install 200 sites on a database and have it run fine, provided that the cumulative resources required arent more than the box can handle, But one really busy site has the same issues.
Thanks
Thanks for all the comments. Seems like having potentially thousands of tables in one database would, at the least, make phpmyadmin unusable. The reason we're looking at doing this is to be able to have a multisite login function, to log in on one site, and be logged in on the rest at the same time, which generally seems to require sharing user and sessions. I'm a bit leery of trying something nonstandard (using the dots to specify different databases) since you never know down the line where you may run into a brick wall with a module you really want that makes an assumption that no longer works. I'll let you know how it turns out.
You may want to checkout the
You may want to checkout the bakery module at http://drupal.org/project/bakery. This lets you do shared sign ins with cookies across any site with a common second level domain. For example example.com and foo.example.com, or foo.example.com and bar.example.com. It will not work with foo.com and bar.com however.
Can't you just accomplish
Can't you just accomplish this by adding the following to settings.php?
$baseDomain = explode(".", $_SERVER['SERVER_NAME']);
$cookie_domain = $baseDomain[count($baseDomain) - 2] . '.' . $baseDomain[count($baseDomain) - 1];
In short, no. Having a cookie
In short, no. Having a cookie that can be read by another site doesn't not equal a login on the other site (session id's won't match among other things) unless you they are sharing a database, in which case they need to have the same cookie_domain anyway.
What the bakery module does, is use a concept of slave and master sites, where the master site holds the actual login info. When a user is logged in on that site a special cookie is set that is consumed on each slave site, and verified using a pre shared key to encrypt the cookie itself as well as hash the main cookie values into a signature (which is encrypted into the original cookie itself) to prevent tampering. When you access the slave site the cookie is tasted, validated, and then if it is fresh enough, the user is automatically logged in.
If a user attempts to log in on a slave site first, a special encrypted cookie is set, to the same cookie domain, and the login is redirected to the master site, where that cookie is tasted, validated, and if fresh enough, a second cookie is set, which will validate the user when they are redirected back to the slave site and tasted, validated, and if fresh enough, they are logged in...etc.
Take a look at the bakery code though, it's pretty interesting.
And I know, I know... you will need a paddle to wade through it's syrupy puns, but seriously it is quite nifty, and if you are posting to this thread, that means it works ;)
Not subdomains
In our case, all of the multisites are separate domains.
MySQL and security
MySQL does NOT store passwords in plain text!
Passwords
It wasn't MySQL they were talking about storing the passwords in plain text. It's the MySQL passwords stored elsewhere in plain text that the worry was about (I'm assuming in the settings file).
Jenni Simonis
http://www.forwardsupport.com
passwords
Ok, I can see that as a concern. Though of course there are ways to protect settings files using the O.S.