CiviCRM Community Forums (archive)

Hi guys,

I'm using Civi 4.4 in Drupal 7. I have a group of users named 'Clients' at the Drupal side. For users in this group, they have the permission to access the CRM, but don't have the permission to view all contacts. I enable the 'Contribution details' report in Civicrm dashboard. However, I get a 'DB error: syntax error' in the page. Then I checked the logging messages to find the details and it provides some debugging messages saying:
Oct 01 12:10:47  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, contacthonor.id as civicrm_contact_honor_id_honor, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, sum(contribution_soft_civireport.amount) as civicrm_contribution_total_amount_sum, address_civireport.country_id as civicrm_address_country_id 
        FROM  civireport_contribution_detail_temp1 temp1_civireport
               INNER JOIN civicrm_contribution contribution_civireport
                       ON temp1_civireport.civicrm_contribution_contribution_id = contribution_civireport.id
               INNER JOIN civicrm_contribution_soft contribution_soft_civireport
                       ON contribution_soft_civireport.contribution_id = contribution_civireport.id
               INNER JOIN civicrm_contact      contact_civireport  INNER JOIN civicrm_acl_contact_cache aclContactCache ON contact_civireport.id = aclContactCache.contact_id
                       ON contact_civireport.id = contribution_soft_civireport.contact_id
            LEFT JOIN civicrm_address address_civireport
                   ON contact_civireport.id = address_civireport.contact_id AND
                      address_civireport.is_primary = 1

            LEFT JOIN civicrm_email email_civireport
                   ON contact_civireport.id = email_civireport.contact_id AND
                      email_civireport.is_primary = 1

            LEFT JOIN civicrm_contact contacthonor
                      ON contacthonor.id = contribution_civireport.honor_contact_id  GROUP BY contact_civireport.id, contribution_civireport.id  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON contact_civireport.id = contribution_soft_civireport.contact_id
            L' at line 8]
    [type] => DB_Error
    [user_info] => CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, contacthonor.id as civicrm_contact_honor_id_honor, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, sum(contribution_soft_civireport.amount) as civicrm_contribution_total_amount_sum, address_civireport.country_id as civicrm_address_country_id 
        FROM  civireport_contribution_detail_temp1 temp1_civireport
               INNER JOIN civicrm_contribution contribution_civireport
                       ON temp1_civireport.civicrm_contribution_contribution_id = contribution_civireport.id
               INNER JOIN civicrm_contribution_soft contribution_soft_civireport
                       ON contribution_soft_civireport.contribution_id = contribution_civireport.id
               INNER JOIN civicrm_contact      contact_civireport  INNER JOIN civicrm_acl_contact_cache aclContactCache ON contact_civireport.id = aclContactCache.contact_id
                       ON contact_civireport.id = contribution_soft_civireport.contact_id
            LEFT JOIN civicrm_address address_civireport
                   ON contact_civireport.id = address_civireport.contact_id AND
                      address_civireport.is_primary = 1

            LEFT JOIN civicrm_email email_civireport
                   ON contact_civireport.id = email_civireport.contact_id AND
                      email_civireport.is_primary = 1

            LEFT JOIN civicrm_contact contacthonor
                      ON contacthonor.id = contribution_civireport.honor_contact_id  GROUP BY contact_civireport.id, contribution_civireport.id  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON contact_civireport.id = contribution_soft_civireport.contact_id
            L' at line 8]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, contacthonor.id as civicrm_contact_honor_id_honor, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, sum(contribution_soft_civireport.amount) as civicrm_contribution_total_amount_sum, address_civireport.country_id as civicrm_address_country_id 
        FROM  civireport_contribution_detail_temp1 temp1_civireport
               INNER JOIN civicrm_contribution contribution_civireport
                       ON temp1_civireport.civicrm_contribution_contribution_id = contribution_civireport.id
               INNER JOIN civicrm_contribution_soft contribution_soft_civireport
                       ON contribution_soft_civireport.contribution_id = contribution_civireport.id
               INNER JOIN civicrm_contact      contact_civireport  INNER JOIN civicrm_acl_contact_cache aclContactCache ON contact_civireport.id = aclContactCache.contact_id
                       ON contact_civireport.id = contribution_soft_civireport.contact_id
            LEFT JOIN civicrm_address address_civireport
                   ON contact_civireport.id = address_civireport.contact_id AND
                      address_civireport.is_primary = 1

            LEFT JOIN civicrm_email email_civireport
                   ON contact_civireport.id = email_civireport.contact_id AND
                      email_civireport.is_primary = 1

            LEFT JOIN civicrm_contact contacthonor
                      ON contacthonor.id = contribution_civireport.honor_contact_id  GROUP BY contact_civireport.id, contribution_civireport.id  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON contact_civireport.id = contribution_soft_civireport.contact_id
            L' at line 8]"]
)
It seems that the error is due to the wrong placement of a 'INNER JOIN' in here
INNER JOIN civicrm_contact      contact_civireport  INNER JOIN civicrm_acl_contact_cache aclContactCache ON contact_civireport.id = aclContactCache.contact_id
                       ON contact_civireport.id = contribution_soft_civireport.contact_id
I haven't change anything relevant to the report; so all the SQL query is generated from the Core files. Does anyone know how to fix this? or maybe some clues about the relevant files which generate this SQL query strings?

Thanks.

most likely this might be an issue with the report and how it integrates the ACL clause, so if it is still broken with michael's suggestion, check the php code for the report

lobo