2

I'm building a Drupal 7 multisite and I want to have a separate database for each site but with some shared tables. So, I have two sites called siteone and sitetwo. I also have 3 databases - oxone (for first site), oxtwo (for second one) and oxshared (where I want to keep shared tables for both sites.

I used the following config for settings.php (for siteone), but it shows me a PDO exception... What I'm doing wrong? Will be very appreciate for any help or advice.

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'oxone',
      'username' => 'oxone',
      'password' => '*********',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => array(
         'default'   => 'oxone.',
         'users'     => 'oxshared.',
         'users_roles'     => 'oxshared.',
         'sessions'  => 'oxshared.',
         'role'      => 'oxshared.',
         'role_permission'      => 'oxshared.',
         'authmap'   => 'oxshared.',
     ),
    ),
  ),
  'oxshared' => 
  array (
    'default' => 
    array (
      'database' => 'oxshared',
      'username' => 'oxshared',
      'password' => '**********',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ), 
);

The exception is:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'oxone'@'localhost' for table 'users': SELECT u., s. FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.sid = :sid; Array ( [:sid] => _5UERN3eEWligNxBusEYCq3Lnf48pfvWDu9FDQDZUI8 ) in _drupal_session_read() (line 102 of /home/qsoul/public_html/siteone.dev.lo/includes/session.inc)

Why doesn't Drupal use the oxshared user when accessing the tables in the oxshared database, as defined in the prefix array? It uses the oxone user instead.

  • What's the exception? – Clive Mar 9 '15 at 17:43
  • This one: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'oxon'@'localhost' for table 'users': SELECT u.*, s.* FROM {users} u INNER JOIN {sessions} s ON u.uid = s.uid WHERE s.sid = :sid; Array ( [:sid] => _5UERN3eEWligNxBusEYCq3Lnf48pfvWDu9FDQDZUI8 ) in _drupal_session_read() (line 102 of /home/qsoul/public_html/siteone.dev.lo/includes/session.inc). – john Mar 9 '15 at 17:58
  • Pretty self-explanatory error I'd say...see stackoverflow.com/questions/4767055/…. You may also have a typo (oxon v oxone) – Clive Mar 9 '15 at 18:08
  • But why drupal doesn't use oxshared user to get those shared data, but tryes to do it via oxeone? p.s. 'oxon' typo is only here (just made a type while replacing real data), in setings.php its 'oxone'. – john Mar 9 '15 at 18:27
  • No worries, glad you got it sorted :) I've edited the info from the comments into the question, it'll help people with the same problem in the future to find your answer – Clive Mar 9 '15 at 19:45
1

The question is closed. It seams to be that to have some tables in a separate database:
1) that second db should be placed at the same host as default one.
2) the user who has access to the default db should also has access to the second db.

Adding one more db to settings.php is not needed. It my case the correct settings.php db config has to look like:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'oxone',
      'username' => 'oxone',
      'password' => '*********',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => array(
         'default'   => 'oxone.',
         'users'     => 'oxshared.',
         'users_roles'     => 'oxshared.',
         'sessions'  => 'oxshared.',
         'role'      => 'oxshared.',
         'role_permission'      => 'oxshared.',
         'authmap'   => 'oxshared.',
     ),
    ),
  ),
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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