New to Drupal and CiviCRM. Kind of new to mysql and php. Guess I like a challenge:

Drupal: 7.41
CiviCRM: 4.6.9
Views & UI: 7.x-3.13

Drupal modules that are selected are:
CiviCRM Entities 7.x-2.0-beta1
CiviCRM Relationship to Role Sync 7.x-2.0-beta1
CiviCRM Rules Integration 4.6.9
Entity Reference 7.x-1.1

Drupal and Civi in two separate databases on the same server.

Trying to create a view. Keep getting an error that Views can't find a the table name. It seems Drupal is looking CiviCRM tables in the Drupal, not CiviCRM database.

Steps taken
In Views admin
1) Add new view
2) Give view a name
3) In Show, select CiviCRM Contact
4) Click on Continue & edit
5) Error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'drupal.civicrm_contact' doesn't exist

Note: It does not matter what CiviCRM table I choose, I'll get the error for that table.

Debug steps:
1) Check settings.php. A snippet is shown below. The point here, prefix is set to ''. All civi tables are in settings.php

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'drupal',
      'username' => 'dpal',
      'password' => '**********',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

/** CiviCRM Views Integration
$databases['default']['default']['prefix']= array(
  'default' => '',
  'civicrm_acl'                              => '`civicrm`.',
  'civicrm_acl_cache'                        => '`civicrm`.',
  'civicrm_acl_contact_cache'                => '`civicrm`.',
  'civicrm_acl_entity_role'                  => '`civicrm`.',
  'civicrm_action_log'                       => '`civicrm`.',
  'civicrm_action_mapping'                   => '`civicrm`.',
etc . . .

2) Login to mysql as my drupal user dpal and run the following query:

SELECT civicrm_contact.id AS id FROM civicrm.civicrm_contact LIMIT 10 OFFSET 0;

Works. No problem.

3) While step 2) is working, double check that dpal is GRANT'ed SELECT for civicrm

SHOW GRANTS for dpal@localhost;
Grants for dpal@localhost                                                                 |
| GRANT ALL PRIVILEGES ON . TO 'dpal'@'localhost' IDENTIFIED BY PASSWORD 
| GRANT SELECT ON civicrm.* TO 'dpal'@'localhost'

4) Debug my view by select Show SQL Query. I get:

SELECT civicrm_contact.id AS id
FROM 
{civicrm_contact} civicrm_contact
LIMIT 10 OFFSET 0

It seems {civicrm_contact} show point to civicrm to get this to work. My understanding of CiviCRM Entities is that drupal should be able to reference civicrm as it would it's own database but doesn't seem to be able to. I would think that how it should work . . . this is all new to me so I'm still learning.

What other steps can I take to debug this problem? I think I've taken every step I can find to fix the problem.

Comments

carclubadmin’s picture

The fire is out.

See /** CiviCRM

There was no */

Why do computers do what they're told to do and not what they're intended to do?

ErwinDeClerck’s picture

I'm having exactly the same problem...
Moreover I have the problem that the host refuses access of users from one database to an other... So I can't grant any rights.
Seems like the only thing I can do will be a fusion of all the tables together...
Did you find a better solution?
Kind regards,
Erwin