Views 3 integration

[jdaniluk]

I'm trying to follow the instructions for Drupal Views 3 Integration so that I can use my Civi data to build Drupal views.  After I paste the code from admin/setting/uf?reset=1 into my settings.php file and save the changes, my Drupal site crashes with the error
   PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ntrlst2_ntrls.semaphore' doesn't exist. SELECT expire, value FROM (semaphore) WHERE name=:name; Array([:name]=variable_init ) in lock_may_be_available() (line 164 of /home/ntrlst2/public_html/includes/lock.inc).

I'm using CiviCRM 4.0.4 and Drupal 7.7.  They are using separate databases on the same host.

What have I missed?

[Hershel]

Post the code you used. It should look something like this:

Code: [Select]

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'my_drupal',
      'username' => 'myuser',
      'password' => 'mypass',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

$databases['default']['default']['prefix']= array(
  'civicrm_acl'                              => '`my_civicrm`.',
  'civicrm_acl_cache'                        => '`my_civicrm`.',
  'civicrm_acl_contact_cache'                => '`my_civicrm`.',
etc.

[Rajan Mayekar]

Hi,
you will get this on <drupal base url>/civicrm/admin/setting/uf?reset=1

Rajan

[jdaniluk]

Here is the code.  The first part (up to the semicolon) is the original code that was in settings.php.  The rest is what I copied from .../civicrm/admin/setting/uf?reset=1.  There are two databases, but the same user and password work for both of them.

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'ntrlst2_ntrls',
      'username' => 'myuser',
      'password' => 'mypass',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => 'd7',
    ),
  ),
);

$databases['default']['default']['prefix']= array(
  'civicrm_acl'                              => '`ntrlst2_civi`.',
  'civicrm_acl_cache'                        => '`ntrlst2_civi`.',
  'civicrm_acl_contact_cache'                => '`ntrlst2_civi`.',
  'civicrm_acl_entity_role'                  => '`ntrlst2_civi`.',
  'civicrm_action_log'                       => '`ntrlst2_civi`.',
  'civicrm_action_mapping'                   => '`ntrlst2_civi`.',
  'civicrm_action_schedule'                  => '`ntrlst2_civi`.',
  'civicrm_activity'                         => '`ntrlst2_civi`.',
  'civicrm_activity_assignment'              => '`ntrlst2_civi`.',
  'civicrm_activity_target'                  => '`ntrlst2_civi`.',
  'civicrm_address'                          => '`ntrlst2_civi`.',
  'civicrm_address_format'                   => '`ntrlst2_civi`.',
  'civicrm_batch'                            => '`ntrlst2_civi`.',
  'civicrm_cache'                            => '`ntrlst2_civi`.',
  'civicrm_campaign'                         => '`ntrlst2_civi`.',
  'civicrm_campaign_group'                   => '`ntrlst2_civi`.',
  'civicrm_case'                             => '`ntrlst2_civi`.',
  'civicrm_case_activity'                    => '`ntrlst2_civi`.',
  'civicrm_case_contact'                     => '`ntrlst2_civi`.',
  'civicrm_component'                        => '`ntrlst2_civi`.',
  'civicrm_contact'                          => '`ntrlst2_civi`.',
  'civicrm_contact_type'                     => '`ntrlst2_civi`.',
  'civicrm_contribution'                     => '`ntrlst2_civi`.',
  'civicrm_contribution_page'                => '`ntrlst2_civi`.',
  'civicrm_contribution_product'             => '`ntrlst2_civi`.',
  'civicrm_contribution_recur'               => '`ntrlst2_civi`.',
  'civicrm_contribution_soft'                => '`ntrlst2_civi`.',
  'civicrm_contribution_type'                => '`ntrlst2_civi`.',
  'civicrm_contribution_widget'              => '`ntrlst2_civi`.',
  'civicrm_country'                          => '`ntrlst2_civi`.',
  'civicrm_county'                           => '`ntrlst2_civi`.',
  'civicrm_currency'                         => '`ntrlst2_civi`.',
  'civicrm_custom_field'                     => '`ntrlst2_civi`.',
  'civicrm_custom_group'                     => '`ntrlst2_civi`.',
  'civicrm_dashboard'                        => '`ntrlst2_civi`.',
  'civicrm_dashboard_contact'                => '`ntrlst2_civi`.',
  'civicrm_dedupe_exception'                 => '`ntrlst2_civi`.',
  'civicrm_dedupe_rule'                      => '`ntrlst2_civi`.',
  'civicrm_dedupe_rule_group'                => '`ntrlst2_civi`.',
  'civicrm_discount'                         => '`ntrlst2_civi`.',
  'civicrm_domain'                           => '`ntrlst2_civi`.',
  'civicrm_email'                            => '`ntrlst2_civi`.',
  'civicrm_entity_batch'                     => '`ntrlst2_civi`.',
  'civicrm_entity_file'                      => '`ntrlst2_civi`.',
  'civicrm_entity_financial_trxn'            => '`ntrlst2_civi`.',
  'civicrm_entity_tag'                       => '`ntrlst2_civi`.',
  'civicrm_event'                            => '`ntrlst2_civi`.',
  'civicrm_file'                             => '`ntrlst2_civi`.',
  'civicrm_financial_account'                => '`ntrlst2_civi`.',
  'civicrm_financial_trxn'                   => '`ntrlst2_civi`.',
  'civicrm_grant'                            => '`ntrlst2_civi`.',
  'civicrm_group'                            => '`ntrlst2_civi`.',
  'civicrm_group_contact'                    => '`ntrlst2_civi`.',
  'civicrm_group_contact_cache'              => '`ntrlst2_civi`.',
  'civicrm_group_nesting'                    => '`ntrlst2_civi`.',
  'civicrm_group_organization'               => '`ntrlst2_civi`.',
  'civicrm_im'                               => '`ntrlst2_civi`.',
  'civicrm_line_item'                        => '`ntrlst2_civi`.',
  'civicrm_loc_block'                        => '`ntrlst2_civi`.',
  'civicrm_location_type'                    => '`ntrlst2_civi`.',
  'civicrm_log'                              => '`ntrlst2_civi`.',
  'civicrm_mail_settings'                    => '`ntrlst2_civi`.',
  'civicrm_mailing'                          => '`ntrlst2_civi`.',
  'civicrm_mailing_bounce_pattern'           => '`ntrlst2_civi`.',
  'civicrm_mailing_bounce_type'              => '`ntrlst2_civi`.',
  'civicrm_mailing_component'                => '`ntrlst2_civi`.',
  'civicrm_mailing_event_bounce'             => '`ntrlst2_civi`.',
  'civicrm_mailing_event_confirm'            => '`ntrlst2_civi`.',
  'civicrm_mailing_event_delivered'          => '`ntrlst2_civi`.',
  'civicrm_mailing_event_forward'            => '`ntrlst2_civi`.',
  'civicrm_mailing_event_opened'             => '`ntrlst2_civi`.',
  'civicrm_mailing_event_queue'              => '`ntrlst2_civi`.',
  'civicrm_mailing_event_reply'              => '`ntrlst2_civi`.',
  'civicrm_mailing_event_subscribe'          => '`ntrlst2_civi`.',
  'civicrm_mailing_event_trackable_url_open' => '`ntrlst2_civi`.',
  'civicrm_mailing_event_unsubscribe'        => '`ntrlst2_civi`.',
  'civicrm_mailing_group'                    => '`ntrlst2_civi`.',
  'civicrm_mailing_job'                      => '`ntrlst2_civi`.',
  'civicrm_mailing_recipients'               => '`ntrlst2_civi`.',
  'civicrm_mailing_spool'                    => '`ntrlst2_civi`.',
  'civicrm_mailing_trackable_url'            => '`ntrlst2_civi`.',
  'civicrm_mapping'                          => '`ntrlst2_civi`.',
  'civicrm_mapping_field'                    => '`ntrlst2_civi`.',
  'civicrm_membership'                       => '`ntrlst2_civi`.',
  'civicrm_membership_block'                 => '`ntrlst2_civi`.',
  'civicrm_membership_log'                   => '`ntrlst2_civi`.',
  'civicrm_membership_payment'               => '`ntrlst2_civi`.',
  'civicrm_membership_status'                => '`ntrlst2_civi`.',
  'civicrm_membership_type'                  => '`ntrlst2_civi`.',
  'civicrm_menu'                             => '`ntrlst2_civi`.',
  'civicrm_navigation'                       => '`ntrlst2_civi`.',
  'civicrm_note'                             => '`ntrlst2_civi`.',
  'civicrm_openid'                           => '`ntrlst2_civi`.',
  'civicrm_option_group'                     => '`ntrlst2_civi`.',
  'civicrm_option_value'                     => '`ntrlst2_civi`.',
  'civicrm_participant'                      => '`ntrlst2_civi`.',
  'civicrm_participant_payment'              => '`ntrlst2_civi`.',
  'civicrm_participant_status_type'          => '`ntrlst2_civi`.',
  'civicrm_payment_processor'                => '`ntrlst2_civi`.',
  'civicrm_payment_processor_type'           => '`ntrlst2_civi`.',
  'civicrm_pcp'                              => '`ntrlst2_civi`.',
  'civicrm_pcp_block'                        => '`ntrlst2_civi`.',
  'civicrm_persistent'                       => '`ntrlst2_civi`.',
  'civicrm_phone'                            => '`ntrlst2_civi`.',
  'civicrm_pledge'                           => '`ntrlst2_civi`.',
  'civicrm_pledge_block'                     => '`ntrlst2_civi`.',
  'civicrm_pledge_payment'                   => '`ntrlst2_civi`.',
  'civicrm_preferences'                      => '`ntrlst2_civi`.',
  'civicrm_preferences_date'                 => '`ntrlst2_civi`.',
  'civicrm_premiums'                         => '`ntrlst2_civi`.',
  'civicrm_premiums_product'                 => '`ntrlst2_civi`.',
  'civicrm_prevnext_cache'                   => '`ntrlst2_civi`.',
  'civicrm_price_field'                      => '`ntrlst2_civi`.',
  'civicrm_price_field_value'                => '`ntrlst2_civi`.',
  'civicrm_price_set'                        => '`ntrlst2_civi`.',
  'civicrm_price_set_entity'                 => '`ntrlst2_civi`.',
  'civicrm_product'                          => '`ntrlst2_civi`.',
  'civicrm_project'                          => '`ntrlst2_civi`.',
  'civicrm_relationship'                     => '`ntrlst2_civi`.',
  'civicrm_relationship_type'                => '`ntrlst2_civi`.',
  'civicrm_report_instance'                  => '`ntrlst2_civi`.',
  'civicrm_saved_search'                     => '`ntrlst2_civi`.',
  'civicrm_state_province'                   => '`ntrlst2_civi`.',
  'civicrm_subscription_history'             => '`ntrlst2_civi`.',
  'civicrm_survey'                           => '`ntrlst2_civi`.',
  'civicrm_tag'                              => '`ntrlst2_civi`.',
  'civicrm_task'                             => '`ntrlst2_civi`.',
  'civicrm_task_status'                      => '`ntrlst2_civi`.',
  'civicrm_tell_friend'                      => '`ntrlst2_civi`.',
  'civicrm_timezone'                         => '`ntrlst2_civi`.',
  'civicrm_uf_field'                         => '`ntrlst2_civi`.',
  'civicrm_uf_group'                         => '`ntrlst2_civi`.',
  'civicrm_uf_join'                          => '`ntrlst2_civi`.',
  'civicrm_uf_match'                         => '`ntrlst2_civi`.',
  'civicrm_value_ce_information_4'           => '`ntrlst2_civi`.',
  'civicrm_value_event_information_3'        => '`ntrlst2_civi`.',
  'civicrm_value_library_extended_address_1' => '`ntrlst2_civi`.',
  'civicrm_value_library_information_2'      => '`ntrlst2_civi`.',
  'civicrm_website'                          => '`ntrlst2_civi`.',
  'civicrm_worldregion'                      => '`ntrlst2_civi`.',
);

[Hershel]

That looks good. Are both DBs accessible via the same server location? Meaning are both at localhost or some other address?

[jdaniluk]

Yes, both databases are on localhost.

[Hershel]

Your error still is:

Code: [Select]

Table 'ntrlst2_ntrls.semaphore' doesn't exist
This seems quite odd, seems not connected to Views, but I can't be sure. Can you check again that adding that code starting with

Code: [Select]

$databases['default']['default']['prefix']= array(
causes this error? If so, on what page?

[jdaniluk]

Here's my sequence of events, which I just replicated again.

Site was working fine.  I had the home page of the Drupal site up on two computers.  On one I was logged in as the admin user.  On the other I was an anonymous user.

My original settings.php file was in place.  I prepared a copy of the settings.php file with the additional code from <mysite>/civicrm/admin/setting/uf?reset=1 as described above.   I diff'ed the two files to verify that that was the only difference.

I renamed the original settings.php to save it, and I made a copy of the changed file named settings.php.

I went to the browser where I had the site open to the home page, refreshed the page and got the error.  The error appeared on both computers - where I was logged in and where I was anonymous.

I deleted the file that had the extra code in it and renamed the old file as settings.php again.

I went to the browsers where the site was open, navigated to the home page, and it was OK again. 

Prior to my test today, I updated all my drupal modules to the latest versions, but I have not yet updated CiviCRM to 4.0.5.  I'm still running 4.0.4.

[Hershel]

I do not know what this is.

Can you run update.php (mysite.com/update.php to confirm no Drupal updates are pending?

Beyond that, the only thing I could suggest is try disabling modules to see if that can get you past this error.

[cognoscento]

I'm having a vaguely similar issue...  I get the following error with CiviCRM-related views:

Code: [Select]

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'interpdrupalcivi2.drupal_civicrm_membership' doesn't exist
I'm sure the problem is just that I don't have the right DB prefix array, but when I visit my own <site>/civicrm/admin/setting/uf?reset=1, (as per http://wiki.civicrm.org/confluence/display/CRMDOC40/Views3+Integration) I don't get the views integration settings I need... nothing shows up, other than identifying the drupal "user" table.

Can someone help me by giving me the first couple lines of the array? (I can take it from there...) My tables for both drupal and civicrm are in the same database (interdrupalcivi2), my drupal tables have a "drupal_" prefix and my civicrm tables have a "civicrm_" prefix.

(I'm using Drupal 7.7 and civicrm 4.0.5.)

Ps. Why won't the views integration settings show up for me?

[Donald Lobo]

If drupal and civicrm are in the same database, then you dont need to add anything to the db_prefix array..

Hence the views integration settings does not show up for you

lobo

[jdaniluk]

I ran update.php and no updates are pending.  I changed my theme back to Bartik and disabled all the Drupal modules that I felt I could disable and still have Views to work with.  Still got the error.

My PHP skills aren't strong enough to interpret the database array code in settings.php and figure out what should be different. The error message seems to say that it's trying to query a database table called ntrlst2_ntrls.semaphore.  (It's trying to acquire or release a lock during Drupal initialization.)

My Drupal database uses a prefix of "d7" so this table doesn't exist; in my database it's called ntrlst2_ntrls.d7semaphore.  I'm guessing that the addition of the Civi Views 3 integration code is breaking the prefix information for my Drupal database.   

To recap, this is my settings.php after adding the code from <mysite>/civicrm/admin/setting/uf?reset=1

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'ntrlst2_ntrls',
      'username' => 'myuser',
      'password' => 'mypass',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => 'd7',
    ),
  ),
);

$databases['default']['default']['prefix']= array(
  'civicrm_acl'                              => '`ntrlst2_civi`.',
  'civicrm_acl_cache'                        => '`ntrlst2_civi`.',
...
 'civicrm_website'                          => '`ntrlst2_civi`.',
  'civicrm_worldregion'                      => '`ntrlst2_civi`.',
);

And this is the full text of the error I see immediately after the new settings.php is in place, as soon as I refresh the home page of my Drupal site:

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

Error message
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ntrlst2_ntrls.semaphore' doesn't exist: SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => variable_init ) in lock_may_be_available() (line 164 of /home/ntrlst2/public_html/includes/lock.inc).

Uncaught exception thrown in shutdown function.
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table &#039;ntrlst2_ntrls.semaphore&#039; doesn&#039;t exist: DELETE FROM {semaphore} WHERE (value = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] =&gt; 9484853514e4bef317669e2.99803222 ) in lock_release_all() (line 266 of /home/ntrlst2/public_html/includes/lock.inc).

Drupal 7.7 and CiviCRM 4.0.4 are in separate databases on localhost.  The same user and password works for both databases.  The Drupal 7 tables have a prefix of d7. 

[Hershel]

Try adding one line for now:

Code: [Select]

$databases['default']['default']['prefix']= array(
  'semaphore'                              => 'd7',
  'civicrm_acl'                              => '`ntrlst2_civi`.',
  'civicrm_acl_cache'                        => '`ntrlst2_civi`.',
...
and see what happens.

[jdaniluk]

I added the line you suggested, and got a different error:

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

Error message
 PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ntrlst2_ntrls.variable' doesn't exist: SELECT name, value FROM {variable}; Array ( ) in variable_initialize() (line 727 of /home/ntrlst2/public_html/includes/bootstrap.inc).

[Hershel]

That's what I suspected would happen. You will need to add a line like

Code: [Select]

  'semaphore'                              => 'd7',
for each table in your Drupal install. Then everything will work I think.