CiviCRM Community Forums (archive)

Hi all,

I've been trying to get Views to work with Civi on my shared hosting environment for several days without any luck (though I have it working fine on my local dev server running MAMP). Hopefully someone here has figured out how to do this....

This is the error message I am getting:

'Exception: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user \'db_user1\'@\'209.68.1.154\' for table \'civicrm_contact\''

This is the relevant piece of info from the CiviCRM Installation doc:

"Note that if you plan to use the Drupal Views module to display CiviCRM data within your Drupal pages [that's what i'm trying to do], and if you are going to use separate databases for Drupal and CiviCRM [that's what i have setup], you need to ensure that your Drupal database user has SELECT permissions for your CiviCRM database."

I have confirmed with my web host that the database users are set up with SELECT permission. My web hosting account does not let me specify usernames/passwords for my databases, so both databases use different usernames and passwords. They are also on different servers, so I have ensured that my CiviCRM database is configured to accept local/remote connections.

This is how I have setup my database settings in the "settings.php" file:

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'db1',
      'username' => 'db_user1',
      'password' => '######',
      'host' => 'db142a.webhost.com',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
     'civicrm' =>
   array (
   'civicrm' =>
   array (
      'database' => 'db2',
      'username' => 'db_user2',
      'password' => '######',
      'host' => 'db142b.webhost.com',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

$databases['default']['default']['prefix']= array(
  'civicrm_acl'                              => '`db2`.',
  'civicrm_acl_cache'                        => '`db2`.',
  'civicrm_acl_contact_cache'                => '`db2`.',
// etc....
);

By all accounts, and from every example I've seen, this should work. But it doesn't. Mind you, every example I've run across has both databases configured with the same username and password (which I have also done on my local installation which works fine). Views is calling the right tables from the right database, but it's using the wrong username and password.

I received the following advice from my web host:

"On a shared database server, access to a given database is only possible
with the usernames that are associated with that database... You should either instruct Drupal/CiviCRM to access you databases using
the usernames that were created for the database being accessed, or use a
single database for both software packages."

Isn't that what I'm telling Drupal to do with my database settings? Or am I missing some other vital piece of the puzzle? Has anyone else had any luck setting up this kind of configuration on a shared hosting plan?

Thanks!
Crispin

I forgot to add that when I insert the database pointer ( 'database' => 'civicrm', ) in the "civicrm.views.inc" file, as per the instructions for Views2 integration here: http://andrewbrookins.com/redspire/civicrm-views-2-integration-remote-civicrm-database - I get the following white-screen error message:

   Error
   The website encountered an unexpected error. Please try again later.

    Notice: Undefined index: default in Database::openConnection() (line 1621 of /usr/www/users/myaccount/mysite/includes/database/database.inc).
    DatabaseDriverNotSpecifiedException: Driver not specified for this database connection: civicrm in Database::openConnection() (line 1622 of /usr/www/users/myaccount/mysite/includes/database/database.inc).

Removing the key/value pair fixes the above error.

So something isn't quite right. But what??

@jalama,

Thanks for clarifying that requirement. It really isn't outlined that clearly anywhere in the Civi documentation. The other solution my web host suggested was to upgrade my account up to a VPS (virtual private server) which would give me complete control over my database configuration (and the username/password combos). Of course, that means a big jump in monthly hosting fees, but it does come with other benefits too. So now I just need to figure out a way to get my client to pay for the upgrade.

Cheers,
Crispin