0

I have made a Drupal 7 multi-site with 3 sites. Each site is running of the same Drupal install and has a separate database. The database names are myhost_site1, myhost_site2 and myhost_site 3. (My hosting provider puts in the myhost_ automatically).

I need the 3 sites to share the users and user roles. I have read various tutorials and these are the steps I have followed:

  1. Created a new database called myhost_users.

  2. Copied the following tables from myhost_site1 TO myhost_users: users, user_roles, authmap and sessions

  3. Given the database users for each of 3 main databases access to myhost_users.

  4. Open the settings.php for each site and insert the following code (replacing the existing $databse definition):

    $databases['default']['default'] = array(
      'driver' => 'mysql',      
      'database' => 'NAME OF DATABASE',
      'username' => 'DATABASE USER',
      'password' => 'DATABASE PASSWORD',
      'host' => 'localhost',
      'prefix' => array(
        'default'   => 'NAME OF DATABASE',
        'users'     => 'myhost_users',
        'sessions'  => 'myhost_users',
        'role'      => 'myhost_users',
        'authmap'   => 'myhost_users',
      ),
      'collation' => 'utf8_general_ci',
    

    );

  5. When I go to my site, I get the following error message:

    Uncaught exception thrown in session handler. PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table

  6. For the code posted in (4) I have tried using an underscore suffix for the database names (e.g, myhost_users_), but I still get an error message.

What am I doing wrong? Do I need any settings in sites/sites.php?

I would be open to using a module to do this.

Bakery is no good as it doesn't share user roles.

Account Sync shares roles, but it doesn't seem to work.

CAS requires phpCAS which I do not have access to.

1 Answer 1

2

You don't need additional modules to achieve what you want.

First: Fix you configuration:

'prefix' => array(
'default'   => 'default_db.',
'users'     => 'myhost_users.', //
'sessions'  => 'myhost_users.',
'role'      => 'myhost_users.',
'authmap'   => 'myhost_users.',
),

The dots on the end of 'users' => 'myhost_users.' tells Drupal to query the users table in myhost_users data base (SQL syntax); without dots you'll get an error like "Base table or view not found myhost_usersusers/myhost_users_users or similar.

Now,

'database' => 'db',
'username' => 'db_user',
'password' => 'db_user_pass',

are credentials for the default db where Drupal is installed. To query users db you need to add this user with his password (db_user, db_user_pass) to the myhost_users database. in other words, if you have a site with 2 subsites:

  • myhost_site1 - user of this db
  • myhost_site2 - user of this db
  • myhost_users - (assuming this db stores Drupal users, that you want to share across ites) user of this sb + user of myhost_site1 and user of myhost_site2

To stay logged in on each site it is necessary to share cookies between sites: edit settings.php file of each site, uncomment the line with # $cookie_domain = '.example.com'; and replace '.example.com' with your domain. Don't forget the dot at the beginning ('.mysite.com') -- without it both mysite.com and notmysite.com could be treated as the same domain.

4
  • Thanks, this works. Except the user doesn't stay logged on between the different sites. I thought sharing authmap would allow for this. How can I make the login stay between the sites.
    – big_smile
    Jun 1, 2015 at 14:46
  • 1
    Now you need to share cookies between sites: edit settings.php for each site, uncomment the line # $cookie_domain = '.example.com'; and replace '.example.com' with your domain. Do not forget the dot at the beggining ($cookie_domain = '.mydoamin.com'; not # $cookie_domain = 'mydomain.com';)!
    – 0x6d6c
    Jun 1, 2015 at 15:18
  • Thanks, just to double check: For the shared user db, are users, user_roles, authmap and sessions the correct tables to copy into it? Are they too many tables or do I need to copy in other ones? (My main purpose is to share users, roles and loggedin status).
    – big_smile
    Jun 1, 2015 at 17:10
  • 1
    The tables user, authmap and sessions are necessary to share users between sites. The users_roles table should be shared if you want to assign users to their roles across all sites (your case), so if one user has to have different roles in each of your sites the don't share the users_roles table.
    – 0x6d6c
    Jun 2, 2015 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.