3

UPDATE: I upgraded to 4.6.3 and the problem persists. I can see that it's related to our petition, but beyond that I'm stumped. I'm having problems with other reports timing out too now, and we've only got around 600 contacts.

The error below occurs when I go to Contacts > Contact Reports > Activities

"Sorry but we are not able to provide this at the moment. DB Error: no such field"

Before the upgade this worked fine. Everything else seems to be functioning okay. Tired it in demo and it works there. Does anyone have ideas?

Thanks!

Here is the full text of the error message I see when I'm in debugging mode:

Sorry but we are not able to provide this at the moment.
DB Error: no such field
Error Details
Database Error Code: Unknown column '1INNER' in 'on clause', 1054
Additional Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
SELECT civicrm_contact_target.sort_name as civicrm_contact_contact_target, civicrm_contact_target.id as civicrm_contact_contact_target_id, civicrm_email_target.email as civicrm_email_contact_target_email, activity_civireport.id as civicrm_activity_id, activity_civireport.source_record_id as civicrm_activity_source_record_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.subject as civicrm_activity_activity_subject, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, activity_civireport.status_id as civicrm_activity_status_id, activity_civireport.duration as civicrm_activity_duration, activity_civireport.details as civicrm_activity_details, activity_civireport.campaign_id as civicrm_activity_campaign_id, activity_civireport.engagement_level as civicrm_activity_engagement_level, address_civireport.street_address as civicrm_address_street_address, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_name as civicrm_address_street_name, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code, address_civireport.state_province_id as civicrm_address_state_province_id, value_petition_questions_1_civireport.member_of_1 as civicrm_value_petition_questions_1_custom_1, value_petition_questions_1_civireport.retired_2 as civicrm_value_petition_questions_1_custom_2, value_petition_questions_1_civireport.affiliated_with_3 as civicrm_value_petition_questions_1_custom_3, value_petition_questions_1_civireport.interested_in_4 as civicrm_value_petition_questions_1_custom_4, option_value_civireport.label as option_value_civireport  
        FROM civicrm_activity activity_civireport
             INNER JOIN civicrm_activity_contact  activity_contact_civireport
                    ON activity_civireport.id = activity_contact_civireport.activity_id AND
                       activity_contact_civireport.record_type_id = 3
             INNER JOIN civicrm_contact civicrm_contact_target
                    ON activity_contact_civireport.contact_id = civicrm_contact_target.id

            LEFT JOIN civicrm_email civicrm_email_target
                   ON activity_contact_civireport.contact_id = civicrm_email_target.contact_id AND
                      civicrm_email_target.is_primary = 1INNER JOIN civicrm_option_value option_value_civireport
            ON option_value_civireport.option_group_id = 2
            AND option_value_civireport.value = activity_civireport.activity_type_id
                 LEFT JOIN civicrm_address address_civireport
                           ON (civicrm_contact_target.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1

LEFT JOIN civicrm_value_petition_questions_1 value_petition_questions_1_civireport ON value_petition_questions_1_civireport.entity_id = activity_civireport.id  WHERE activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20120510000000 ) AND ( activity_date_time <= 20150509235959 ) AND ( activity_civireport.activity_type_id IN (1,2,3,4,5,6,7,8,9,10,11,12,17,19,22,28,29,30,31,32,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51) ) AND ( address_civireport.postal_code = 93101 ) AND value_petition_questions_1_civireport.member_of_1 REGEXP '[[:<:]]CalPERS|CalSTRS[[:>:]]'  [nativecode=1054 ** Unknown column '1INNER' in 'on clause']
    [type] => DB_Error
    [user_info] => CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
SELECT civicrm_contact_target.sort_name as civicrm_contact_contact_target, civicrm_contact_target.id as civicrm_contact_contact_target_id, civicrm_email_target.email as civicrm_email_contact_target_email, activity_civireport.id as civicrm_activity_id, activity_civireport.source_record_id as civicrm_activity_source_record_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.subject as civicrm_activity_activity_subject, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, activity_civireport.status_id as civicrm_activity_status_id, activity_civireport.duration as civicrm_activity_duration, activity_civireport.details as civicrm_activity_details, activity_civireport.campaign_id as civicrm_activity_campaign_id, activity_civireport.engagement_level as civicrm_activity_engagement_level, address_civireport.street_address as civicrm_address_street_address, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_name as civicrm_address_street_name, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code, address_civireport.state_province_id as civicrm_address_state_province_id, value_petition_questions_1_civireport.member_of_1 as civicrm_value_petition_questions_1_custom_1, value_petition_questions_1_civireport.retired_2 as civicrm_value_petition_questions_1_custom_2, value_petition_questions_1_civireport.affiliated_with_3 as civicrm_value_petition_questions_1_custom_3, value_petition_questions_1_civireport.interested_in_4 as civicrm_value_petition_questions_1_custom_4, option_value_civireport.label as option_value_civireport  
        FROM civicrm_activity activity_civireport
             INNER JOIN civicrm_activity_contact  activity_contact_civireport
                    ON activity_civireport.id = activity_contact_civireport.activity_id AND
                       activity_contact_civireport.record_type_id = 3
             INNER JOIN civicrm_contact civicrm_contact_target
                    ON activity_contact_civireport.contact_id = civicrm_contact_target.id

            LEFT JOIN civicrm_email civicrm_email_target
                   ON activity_contact_civireport.contact_id = civicrm_email_target.contact_id AND
                      civicrm_email_target.is_primary = 1INNER JOIN civicrm_option_value option_value_civireport
            ON option_value_civireport.option_group_id = 2
            AND option_value_civireport.value = activity_civireport.activity_type_id
                 LEFT JOIN civicrm_address address_civireport
                           ON (civicrm_contact_target.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1

LEFT JOIN civicrm_value_petition_questions_1 value_petition_questions_1_civireport ON value_petition_questions_1_civireport.entity_id = activity_civireport.id  WHERE activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20120510000000 ) AND ( activity_date_time <= 20150509235959 ) AND ( activity_civireport.activity_type_id IN (1,2,3,4,5,6,7,8,9,10,11,12,17,19,22,28,29,30,31,32,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51) ) AND ( address_civireport.postal_code = 93101 ) AND value_petition_questions_1_civireport.member_of_1 REGEXP '[[:<:]]CalPERS|CalSTRS[[:>:]]'  [nativecode=1054 ** Unknown column '1INNER' in 'on clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
SELECT civicrm_contact_target.sort_name as civicrm_contact_contact_target, civicrm_contact_target.id as civicrm_contact_contact_target_id, civicrm_email_target.email as civicrm_email_contact_target_email, activity_civireport.id as civicrm_activity_id, activity_civireport.source_record_id as civicrm_activity_source_record_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.subject as civicrm_activity_activity_subject, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, activity_civireport.status_id as civicrm_activity_status_id, activity_civireport.duration as civicrm_activity_duration, activity_civireport.details as civicrm_activity_details, activity_civireport.campaign_id as civicrm_activity_campaign_id, activity_civireport.engagement_level as civicrm_activity_engagement_level, address_civireport.street_address as civicrm_address_street_address, address_civireport.street_number as civicrm_address_street_number, address_civireport.street_name as civicrm_address_street_name, address_civireport.city as civicrm_address_city, address_civireport.postal_code as civicrm_address_postal_code, address_civireport.state_province_id as civicrm_address_state_province_id, value_petition_questions_1_civireport.member_of_1 as civicrm_value_petition_questions_1_custom_1, value_petition_questions_1_civireport.retired_2 as civicrm_value_petition_questions_1_custom_2, value_petition_questions_1_civireport.affiliated_with_3 as civicrm_value_petition_questions_1_custom_3, value_petition_questions_1_civireport.interested_in_4 as civicrm_value_petition_questions_1_custom_4, option_value_civireport.label as option_value_civireport  
        FROM civicrm_activity activity_civireport
             INNER JOIN civicrm_activity_contact  activity_contact_civireport
                    ON activity_civireport.id = activity_contact_civireport.activity_id AND
                       activity_contact_civireport.record_type_id = 3
             INNER JOIN civicrm_contact civicrm_contact_target
                    ON activity_contact_civireport.contact_id = civicrm_contact_target.id

            LEFT JOIN civicrm_email civicrm_email_target
                   ON activity_contact_civireport.contact_id = civicrm_email_target.contact_id AND
                      civicrm_email_target.is_primary = 1INNER JOIN civicrm_option_value option_value_civireport
            ON option_value_civireport.option_group_id = 2
            AND option_value_civireport.value = activity_civireport.activity_type_id
                 LEFT JOIN civicrm_address address_civireport
                           ON (civicrm_contact_target.id =
                               address_civireport.contact_id) AND
                               address_civireport.is_primary = 1

LEFT JOIN civicrm_value_petition_questions_1 value_petition_questions_1_civireport ON value_petition_questions_1_civireport.entity_id = activity_civireport.id  WHERE activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20120510000000 ) AND ( activity_date_time <= 20150509235959 ) AND ( activity_civireport.activity_type_id IN (1,2,3,4,5,6,7,8,9,10,11,12,17,19,22,28,29,30,31,32,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51) ) AND ( address_civireport.postal_code = 93101 ) AND value_petition_questions_1_civireport.member_of_1 REGEXP '[[:<:]]CalPERS|CalSTRS[[:>:]]'  [nativecode=1054 ** Unknown column '1INNER' in 'on clause']"]
)
  • Can you please specify what version you upgraded from and to? – Coleman May 2 '15 at 20:40
  • CiviCRM 4.5 to 4.6.2 w/ WordPress 4.2.1 – Laurie May 2 '15 at 23:59
  • Try enabling debugging (Administer menu > System Settings > Debugging and Error Handling), then run the report again. You'll receive a much more verbose error; edit your question with the updated error message and we'll see what we can do! – Jon G - Megaphone Tech May 3 '15 at 0:16
  • Thanks Jon. I've edited my original post to include the updated error message. Please let me know if you have more ideas. – Laurie May 9 '15 at 18:14
  • P.S. This website has an online petition. It's working fine, but I'm unable to import data for people who have signed the paper version. I need to generate a petition signature activity report in order to see exactly which fields to include and how to format the data. I was able to run this report successfully before I upgraded to 4.6.2. – Laurie May 9 '15 at 18:29
2

UPDATE: This is now submitted as CRM-16640, pull request to fix is here: https://github.com/civicrm/civicrm-core/pull/5971. This fix should be built in to 4.6.4+.

You've found a bug. I found a fix, but have to run out so don't have time at the moment to submit it upstream - I'll try to do so later this week.

For now - if you're able to edit your CiviCRM files, go to your civicrm plugin folder and find the file CRM/Report/Form/Activity.php. On line 521 you'll see this line:

    $this->_from .= "INNER JOIN civicrm_option_value {$this->_aliases['civicrm_option_value']}

Add a space before the word "INNER" like so:

    $this->_from .= " INNER JOIN civicrm_option_value {$this->_aliases['civicrm_option_value']}
  • Thank you!!! I will give this a try and report back. I really appreciate the response. :) – Laurie May 30 '15 at 2:44
0

If it's working on the demo site, then I would try generating a new activity report from the template. Go to (Administer menu > CiviReport > Create New Report from Template) and select "Activity Report". If it works from there, save the new report and delete the old one - and of course, come back here to report on whether it worked!

  • Thank you! I tried that and got the exact same error. :( – Laurie May 2 '15 at 23:59

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.