CiviCRM Community Forums (archive)

Okay, I'm stuck and need help. I am trying to integrate Views2 with CiviCRM, specifically the CiviEvent component. My system is Drupal 6.16 and CiviCRM 3.1.1 (not 3.1.3, as initially reported).

I've followed the directions on http://wiki.civicrm.org/confluence/display/CRMDOC/Views2+Integration+Module:

• Separate databases, same dbuser and pw.
• The dbuser has SELECT rights in both databases.
• No CiviCRM custom data field. I used to have one, but deleted it through the CiviCRM system to simplify integration. I hope to add it back once basic functionality is established.
• The database prefixes have been acquired from http://www.mysite.com/civicrm/admin/setting/uf?reset=1 and have been pasted into the 'settings.php' file.
• The Views2 cache was then cleared.

My site keeps throwing the following warning:

user warning: Table 'my_drupal_database.civicrm_event' doesn't exist query: SELECT civicrm_event.id AS id, civicrm_event.start_date AS civicrm_event_start_date, civicrm_event.summary AS civicrm_event_summary, civicrm_event.title AS civicrm_event_title FROM civicrm_event civicrm_event LIMIT 0, 10 in ~/sites/all/modules/views/includes/view.inc on line 771.

I then tried the advice at http://redspire.net/content/civicrm-views-2-integration-remote-civicrm-database and http://redspire.net/content/problems-and-solutions-problems-views-2-civicrm-and-drupal-6

• Both databases are defined in Drupal's 'settings.php' file, per the instructions in http://drupal.org/node/18429 . These definitions were configured for Drupal's Backup and Migrate module and this module has automatically backed up both databases successfully, so apparently they are defined correctly.
• As suggested, I've tested both 'default' => '', and 'default' => 'drupal', in the 'settings.php' file even though I believe I've had the integration working without either in a prior installation attempt.

Still no joy. Am I missing a step? How can I get this working?

--
Edit: I thought this might be helpful to share. The following are the prefixes that were copied into the 'settings.php' file per the wiki instructions (linked above):

$db_url['default'] = 'mysqli://dbuser:dbpassword@localhost/my_drupal_database';
$db_url['civicrm'] = 'mysqli://dbuser:dbpassword@localhost/my_CiviCRM_database';
$db_prefix = array(
  'default'                                  => '',
  'civicrm_acl'                              => 'my_CiviCRM_database.',
  'civicrm_acl_cache'                        => 'my_CiviCRM_database.',
  'civicrm_acl_contact_cache'                => 'my_CiviCRM_database.',
  'civicrm_acl_entity_role'                  => 'my_CiviCRM_database.',
  'civicrm_activity'                         => 'my_CiviCRM_database.',
  'civicrm_activity_assignment'              => 'my_CiviCRM_database.',
  'civicrm_activity_target'                  => 'my_CiviCRM_database.',
  'civicrm_address'                          => 'my_CiviCRM_database.',
  'civicrm_cache'                            => 'my_CiviCRM_database.',
  'civicrm_case'                             => 'my_CiviCRM_database.',
  'civicrm_case_activity'                    => 'my_CiviCRM_database.',
  'civicrm_case_contact'                     => 'my_CiviCRM_database.',
  'civicrm_component'                        => 'my_CiviCRM_database.',
  'civicrm_contact'                          => 'my_CiviCRM_database.',
  'civicrm_contact_type'                     => 'my_CiviCRM_database.',
  'civicrm_contribution'                     => 'my_CiviCRM_database.',
  'civicrm_contribution_page'                => 'my_CiviCRM_database.',
  'civicrm_contribution_product'             => 'my_CiviCRM_database.',
  'civicrm_contribution_recur'               => 'my_CiviCRM_database.',
  'civicrm_contribution_soft'                => 'my_CiviCRM_database.',
  'civicrm_contribution_type'                => 'my_CiviCRM_database.',
  'civicrm_contribution_widget'              => 'my_CiviCRM_database.',
  'civicrm_country'                          => 'my_CiviCRM_database.',
  'civicrm_county'                           => 'my_CiviCRM_database.',
  'civicrm_currency'                         => 'my_CiviCRM_database.',
  'civicrm_custom_field'                     => 'my_CiviCRM_database.',
  'civicrm_custom_group'                     => 'my_CiviCRM_database.',
  'civicrm_dashboard'                        => 'my_CiviCRM_database.',
  'civicrm_dashboard_contact'                => 'my_CiviCRM_database.',
  'civicrm_dedupe_rule'                      => 'my_CiviCRM_database.',
  'civicrm_dedupe_rule_group'                => 'my_CiviCRM_database.',
  'civicrm_discount'                         => 'my_CiviCRM_database.',
  'civicrm_domain'                           => 'my_CiviCRM_database.',
  'civicrm_email'                            => 'my_CiviCRM_database.',
  'civicrm_entity_file'                      => 'my_CiviCRM_database.',
  'civicrm_entity_tag'                       => 'my_CiviCRM_database.',
  'civicrm_event'                            => 'my_CiviCRM_database.',
  'civicrm_file'                             => 'my_CiviCRM_database.',
  'civicrm_financial_trxn'                   => 'my_CiviCRM_database.',
  'civicrm_grant'                            => 'my_CiviCRM_database.',
  'civicrm_group'                            => 'my_CiviCRM_database.',
  'civicrm_group_contact'                    => 'my_CiviCRM_database.',
  'civicrm_group_contact_cache'              => 'my_CiviCRM_database.',
  'civicrm_group_nesting'                    => 'my_CiviCRM_database.',
  'civicrm_group_organization'               => 'my_CiviCRM_database.',
  'civicrm_im'                               => 'my_CiviCRM_database.',
  'civicrm_line_item'                        => 'my_CiviCRM_database.',
  'civicrm_loc_block'                        => 'my_CiviCRM_database.',
  'civicrm_location_type'                    => 'my_CiviCRM_database.',
  'civicrm_log'                              => 'my_CiviCRM_database.',
  'civicrm_mail_settings'                    => 'my_CiviCRM_database.',
  'civicrm_mailing'                          => 'my_CiviCRM_database.',
  'civicrm_mailing_bounce_pattern'           => 'my_CiviCRM_database.',
  'civicrm_mailing_bounce_type'              => 'my_CiviCRM_database.',
  'civicrm_mailing_component'                => 'my_CiviCRM_database.',
  'civicrm_mailing_event_bounce'             => 'my_CiviCRM_database.',
  'civicrm_mailing_event_confirm'            => 'my_CiviCRM_database.',
  'civicrm_mailing_event_delivered'          => 'my_CiviCRM_database.',
  'civicrm_mailing_event_forward'            => 'my_CiviCRM_database.',
  'civicrm_mailing_event_opened'             => 'my_CiviCRM_database.',
  'civicrm_mailing_event_queue'              => 'my_CiviCRM_database.',
  'civicrm_mailing_event_reply'              => 'my_CiviCRM_database.',
  'civicrm_mailing_event_subscribe'          => 'my_CiviCRM_database.',
  'civicrm_mailing_event_trackable_url_open' => 'my_CiviCRM_database.',
  'civicrm_mailing_event_unsubscribe'        => 'my_CiviCRM_database.',
  'civicrm_mailing_group'                    => 'my_CiviCRM_database.',
  'civicrm_mailing_job'                      => 'my_CiviCRM_database.',
  'civicrm_mailing_spool'                    => 'my_CiviCRM_database.',
  'civicrm_mailing_trackable_url'            => 'my_CiviCRM_database.',
  'civicrm_mapping'                          => 'my_CiviCRM_database.',
  'civicrm_mapping_field'                    => 'my_CiviCRM_database.',
  'civicrm_membership'                       => 'my_CiviCRM_database.',
  'civicrm_membership_block'                 => 'my_CiviCRM_database.',
  'civicrm_membership_log'                   => 'my_CiviCRM_database.',
  'civicrm_membership_payment'               => 'my_CiviCRM_database.',
  'civicrm_membership_status'                => 'my_CiviCRM_database.',
  'civicrm_membership_type'                  => 'my_CiviCRM_database.',
  'civicrm_menu'                             => 'my_CiviCRM_database.',
  'civicrm_msg_template'                     => 'my_CiviCRM_database.',
  'civicrm_navigation'                       => 'my_CiviCRM_database.',
  'civicrm_note'                             => 'my_CiviCRM_database.',
  'civicrm_openid'                           => 'my_CiviCRM_database.',
  'civicrm_openid_associations'              => 'my_CiviCRM_database.',
  'civicrm_openid_nonces'                    => 'my_CiviCRM_database.',
  'civicrm_option_group'                     => 'my_CiviCRM_database.',
  'civicrm_option_value'                     => 'my_CiviCRM_database.',
  'civicrm_participant'                      => 'my_CiviCRM_database.',
  'civicrm_participant_payment'              => 'my_CiviCRM_database.',
  'civicrm_participant_status_type'          => 'my_CiviCRM_database.',
  'civicrm_payment_processor'                => 'my_CiviCRM_database.',
  'civicrm_payment_processor_type'           => 'my_CiviCRM_database.',
  'civicrm_pcp'                              => 'my_CiviCRM_database.',
  'civicrm_pcp_block'                        => 'my_CiviCRM_database.',
  'civicrm_phone'                            => 'my_CiviCRM_database.',
  'civicrm_pledge'                           => 'my_CiviCRM_database.',
  'civicrm_pledge_block'                     => 'my_CiviCRM_database.',
  'civicrm_pledge_payment'                   => 'my_CiviCRM_database.',
  'civicrm_preferences'                      => 'my_CiviCRM_database.',
  'civicrm_preferences_date'                 => 'my_CiviCRM_database.',
  'civicrm_premiums'                         => 'my_CiviCRM_database.',
  'civicrm_premiums_product'                 => 'my_CiviCRM_database.',
  'civicrm_price_field'                      => 'my_CiviCRM_database.',
  'civicrm_price_set'                        => 'my_CiviCRM_database.',
  'civicrm_price_set_entity'                 => 'my_CiviCRM_database.',
  'civicrm_product'                          => 'my_CiviCRM_database.',
  'civicrm_project'                          => 'my_CiviCRM_database.',
  'civicrm_relationship'                     => 'my_CiviCRM_database.',
  'civicrm_relationship_type'                => 'my_CiviCRM_database.',
  'civicrm_report_instance'                  => 'my_CiviCRM_database.',
  'civicrm_saved_search'                     => 'my_CiviCRM_database.',
  'civicrm_state_province'                   => 'my_CiviCRM_database.',
  'civicrm_subscription_history'             => 'my_CiviCRM_database.',
  'civicrm_tag'                              => 'my_CiviCRM_database.',
  'civicrm_task'                             => 'my_CiviCRM_database.',
  'civicrm_task_status'                      => 'my_CiviCRM_database.',
  'civicrm_tell_friend'                      => 'my_CiviCRM_database.',
  'civicrm_timezone'                         => 'my_CiviCRM_database.',
  'civicrm_uf_field'                         => 'my_CiviCRM_database.',
  'civicrm_uf_group'                         => 'my_CiviCRM_database.',
  'civicrm_uf_join'                          => 'my_CiviCRM_database.',
  'civicrm_uf_match'                         => 'my_CiviCRM_database.',
  'civicrm_value_event_info_1'               => 'my_CiviCRM_database.',
  'civicrm_worldregion'                      => 'my_CiviCRM_database.',
);

I had not, but taking your suggestion I just did. There is no warning on the resultant page; it is blank -- no event data displayed.

Still looking for ideas.

Yes, there should be data. I had previously entered two complete Event records. They're still there.

The 'Recent log entries' displayed two errors. The two errors seem to be the same; the details are below (Colons added to labels for clarity). The errors appear similar though not identical to those originally thrown in the Views module.

Type:   php
Date:   Thursday, April 8, 2010 - 12:26
User:   administrator
Location:   http://www.mysite.com/events
Referrer:   
Message:   Table 'my_drupal_database.civicrm_event' doesn't exist query: SELECT civicrm_event.id AS id, civicrm_event.start_date AS civicrm_event_start_date, civicrm_event.title AS civicrm_event_title FROM civicrm_event civicrm_event WHERE ((civicrm_event.is_active <> 0) AND (civicrm_event.is_public <> 0)) AND ((DATE_FORMAT(ADDTIME(civicrm_event.start_date, SEC_TO_TIME(-14400)), '%Y-%m') <= '2010-04' AND DATE_FORMAT(ADDTIME(civicrm_event.start_date, SEC_TO_TIME(-14400)), '%Y-%m') >= '2010-04')) ORDER BY civicrm_event_start_date ASC in ~/sites/all/modules/views/includes/view.inc on line 775.
Severity:   error
Hostname:   xx.xx.x.xxx
Operations:

Type:   php
Date:   Thursday, April 8, 2010 - 12:26
User:   administrator
Location:   http://www.mysite.com/events
Referrer:   
Message:   Table 'my_drupal_database.civicrm_event' doesn't exist query: SELECT civicrm_event.id AS id, civicrm_event.start_date AS civicrm_event_start_date, civicrm_event.title AS civicrm_event_title FROM civicrm_event civicrm_event WHERE ((civicrm_event.is_active <> 0) AND (civicrm_event.is_public <> 0)) AND ((DATE_FORMAT(ADDTIME(civicrm_event.start_date, SEC_TO_TIME(-14400)), '%Y-%m') <= '2010-04' AND DATE_FORMAT(ADDTIME(civicrm_event.start_date, SEC_TO_TIME(-14400)), '%Y-%m') >= '2010-04')) ORDER BY civicrm_event_start_date ASC in ~/sites/all/modules/views/includes/view.inc on line 775.
Severity:   error
Hostname:   xx.xx.x.xxx
Operations:

I updated one of the fields in the View without changing it and then double checked the error output by that update. It was again similar, though not identical, to the 'Recent log entries' details.

• user warning: Table 'my_drupal_database.civicrm_event' doesn't exist query: SELECT COUNT(*) FROM (SELECT civicrm_event.id AS id FROM civicrm_event civicrm_event WHERE (civicrm_event.is_active <> 0) AND (civicrm_event.is_public <> 0) ) count_alias in ~/sites/all/modules/views/includes/view.inc on line 745.
  
• user warning: Table 'my_drupal_database.civicrm_event' doesn't exist query: SELECT civicrm_event.id AS id, civicrm_event.start_date AS civicrm_event_start_date, civicrm_event.title AS civicrm_event_title FROM civicrm_event civicrm_event WHERE (civicrm_event.is_active <> 0) AND (civicrm_event.is_public <> 0) ORDER BY civicrm_event_start_date ASC LIMIT 0, 5 in ~/sites/all/modules/views/includes/view.inc on line 771.

both versions should work fine. the only advice i have is any of these:

1. Try to redo all the steps again (of views integration) just to be sure.
2. even just try to make a fresh view, a very simple one, in civicrm and see if it has errors
3. contact me offline (via my user here or the link in my sig) and perhaps i can look at it

Happy to help. Glad to hear it worked out.