By zakjan on

Hi,
I need to run multisite D6 instalation, every site will have own domain, own content, but all sites will share user/profile tables.

No site will be the master, so I think the simpliest solution will be having one database with many tables (prefixed with site1_, site2_, shared_). Could someone show me 100% functional tutorial?

Or the second option is having more databases (site1, site2) with site-related tables and one database with shared tables. How to do this?

Thanks
John Z.

⋅ Categories: Drupal 6.x

Comments

zakjan’s picture

zakjan commented

Oh. I just realized I have to use D5 because of some modules.

-Anti-’s picture

-Anti- commented

> No site will be the master

Whilst no 'site' will be a 'master', your account has to have one MAIN domain.
The other domains must be 'parked' on your account.
If you have them as 'add-on' domains, I think you then need to set up 'virtualhosts'.

So, before your multisite is set-up, www.domain.com in a browser displays your default drupal installation.
But the 'parked' (or 'addon') domains will also display the default drupal installation.
Once you have achieved that, you can start setting up your multisite.

There is usually no 'main' database in a multisite set-up. But since you're sharing the user tables, you WILL have to decide which database (or which prefix-set) will hold the shared user tables.

The share itself is set up within each settings.php (there will be one settings.php for every site/domain).

> Could someone show me 100% functional tutorial?

I don't think it's possible - If you look at the instructions that are around, usually the first post is someone who figured it out on their server, and then there are 20 comments of people who can't get that method to work. Every host/server appears to be slightly different, so detailed instructions that worked for one person don't seem to work for very many other people.

The best you can probably do is the 'documents section', the comments in the 'settings.php' file, and there are a couple of videos on the net (but they are useless for actual details). There might be an e-book somewhere on a P2P network with more information.

I had to spend over 30 hours experimenting with multisite configurations, before I got something useable (I actually ended up using sub-domains instead, which suited my purpose better).

Personally I'd suggest that you get the multisite set-up first, with no user tables being shared. Then when that's all working, you *should* be able just to edit the settings.php files to make each site partially utilise another database (using a $db_url array) or database prefix (using a $db_prefix array).

EDIT:

Yes, I can confirm for you that a 'un-shared' multisite set-up can be easily edited into a 'shared' one after installation.

Eg. In a single database set-up where site1 uses MS_1 as its prefix, and site2 uses MS_2 as its prefix, you can share the site1 user tables by editing the site2 settings.php:

From -

$db_url = 'mysqli://username:password@localhost/databasename';
$db_prefix = 'MS_2';

To -

$db_url = 'mysqli://username:password@localhost/databasename';
$db_prefix = array(
'default' => 'MS_2',
'users' => 'MS_1',
'sessions' => 'MS_1',
'role' => 'MS_1',
'authmap' => 'MS_1',
'sequences' => 'MS_1',
);

Eg. In a multi-database set-up where site1 uses DB1, and site2 uses DB2, and there are no table prefixes, you can share the site1 user tables by editing the site2 settings.php:

$db_url = array(
'default' => 'mysqli://username:password@localhost/DB2',
'users' => 'mysqli://username:password@localhost/DB1',
'sessions' => 'mysqli://username:password@localhost/DB1',
'role' => 'mysqli://username:password@localhost/DB1',
'authmap' => 'mysqli://username:password@localhost/DB1',
'sequences' => 'mysqli://username:password@localhost/DB1',
);

Cheers.

barrya’s picture

barrya commented

I'm doing a test install of 6.2, and have been playing with the multi-site offering. A little explanation on what I want to achieve, and why I've posted here and not a new message. I've also done a search and can't see an answer to my problem.

What I am trying to do is as follows

Run multiple sites, each under a different domain.
Each site has unique content not viewable by other domains
There is one shared user database between all sites.

I am looking to use a multi-site solution, mainly due to the number of sites I intend to launch. I believe it will make code management and updates a lot easier, although there may be some issues I am unaware of.

Now onto the problem.

I saw this post, and a couple like it and I thought the following would work in my settings.php file.

$db_url = array(
'default' => 'mysqli://username:password@localhost/DB2',
'users' => 'mysqli://username:password@localhost/DB1',
'sessions' => 'mysqli://username:password@localhost/DB1',
'role' => 'mysqli://username:password@localhost/DB1',
'authmap' => 'mysqli://username:password@localhost/DB1',
'sequences' => 'mysqli://username:password@localhost/DB1',
);

This to me says that the default database to use is DB2 - which is the database for this site. When it comes to anything user related look in DB1 - the master database.

However, I can not get this to work. When I access the second site that uses DB2 I receive errors when trying to login, basically it can't find a valid user. When I create an account it creates an account with uid 1 in the users table of DB2. It is not looking at DB1 at all.

Anyone else experienced this?

Thanks

Barry.

-Anti-’s picture

-Anti- commented

Did you use prefixes in your databases?
If so, you'll need to also use the $db_prefix array.
Conversely, ensure $db_prefix=''; if there are no prefixes.

Also, did you copy and paste from the default settings.php or did you type the database info manually?
In other words, are you sure there isn't a mistake, or illegal characters in the password, etc?
Are you sure it is mysqli and not mysql you're supposed to be using?

As a test, I would try to get your second site to just completely use the other database, first,
without an array, using the one line: $db_url = 'mysqli://username:password@localhost/databasename';
If you can get that to work, then it narrows the problem down to the array itself and not some other
connection problem.

I have tested the $db_prefix method and it worked fine.
I got the $db_url array information from another website, but I haven't personally tested it.

EDIT: I tried to find the source which stated that shared tables could be used across separate databases, but I can't find it. So I'm now wondering if I misread one of the 100's of pieces of information I had to research in order to set-up my own multi-site. It's very possible that shared tables can *only* be used with a shared, prefixed database - although I can't find any articles which specifically state that.

barrya’s picture

barrya commented

Thanks for the reply,

- There are no prefixes in either database.
- I am using mysqli.

I had already performed the test and can confirm that the second site can talk to the database fine. It also picks up the "default" key in the array fine. it just seems to ignore the rest of the keys.

I'll have a bit of a play and go through the source and see if I can see anything in there to confirm or deny whether this is possible or not.

Worst case i can use a shared code base and either have seperate databases with no shared users, or shared database with prefixed tables and shared users.

Thanks for the help

Barry.

barrya’s picture

barrya commented

OK, I've had a play this morning and have it working now. I used the following in my settings.php file.

$db_url = "mysqli://DBUSERNAME:DBPASSWORD@localhost/";

$sitedb = "SITEDB";
$logindb = "LOGINDB";
	
$db_prefix = array(
		'default' 	=> $sitedb.'.',
		'users'     => $logindb.'.',
		'sessions'  => $logindb.'.',
		'role'      => $logindb.'.',
		'authmap'   => $logindb.'.',
		'sequences' => $logindb.'.',
);

Key assumptions - all databases that are used are accessed by the same user account and s/he has the same permissions on each database.

How it works.
$db_url is now only specifying the connection information to the database server.

We create a $db_prefix array and specify the connection to the database itself here following by a period "." This tells drupal when looking at the "users" key in the array to use DATABASE.TABLE rather than just TABLE.

if we want to add prefixes to the tables as well, we can still do this by using the following example. In this example there are two prefixes, but you can specify 1 or more following the same pattern.

$db_url = "mysqli://DBUSERNAME:DBPASSWORD@localhost/";

$sitedb = "SITEDB";
$logindb = "LOGINDB";
$prefix1 = "pre1_";
$prefix2 = "pre2_";
	
$db_prefix = array(
		'default' 	=> $sitedb.'.'.$prefix1,
		'users'     => $logindb.'.'.$prefix2,
		'sessions'  => $logindb.'.'.$prefix2,
		'role'      => $logindb.'.'.$prefix2,
		'authmap'   => $logindb.'.'.$prefix2,
		'sequences' => $logindb.'.'.$prefix2,
);

I hope that helps, it certainly seems to be working for me so far, and I haven't noticed any problems yet. I'll report back if I discover any.

zakjan’s picture

zakjan commented

Great work, thats exactly I needed!
I totally forgot this sql syntax :banghead:.
Thanks

-Anti-’s picture

-Anti- commented

Awesome work.
Thanks for correcting my mis-information!
I'm sure many people will find this helpful.

rowshan’s picture

rowshan commented

Dear Barrya,
Very thanks for your solution. It's what i have looking for.
One question. Is this solution good for sharing between drupal 5.x and 6.x. databese?
Regards :)