0

UPDATE 29th July :

Yes it's taken me this long to work it out so now I need some advice please. I have tracked the place where "rogue" SQL down is being created and it's source is in this hook : hook_civicrm_aclWhereClause. The PHP jumps around a little but calls "getSelectWhereClause" in civicrm-core/DAO.php which in turn calls "addSelectWhereClause" also in DAO.php.

I have traced what is returned and it is from here that the "rogue" SQL :

"AND contact_civireport.is_deleted != 1 AND relationship_civireport.contact_id_a IN .... etc."

is being generated. Looking around I see that a caveat for hook_civicrm_aclWhereClause is that it not be called at all if the logged in user has access to the "edit all contacts" permission which a "super-user" does. I went into Joomla and added that permission to my member category but the SQL is still incorrect.

My "fudge" (and before there are cries of outrage I know it's a fudge) is to comment out the lines in form/detail.php that append the field $this>aclWhere to the "WHERE" clause.

My PHP skills are limited (but getting better) so what I need advice on is:

a) Did I edit the permission in the correct place? b) If I did then it didn't seem to work so where does the PHP look to generate this (what is to me incorrect) code:

Entering getSelectWhereClause tablename: contact_civireport vals: Array ( ) vals: Array ( [0] => != 1 ) Evaluating vals now field: is_deleted

Leaving getSelectWhereClause clauses :Array ( [0] => contact_civireport.is_deleted != 1 ) clause: string(38) "contact_civireport.is_deleted != 1" ret: array(1) { ["civicrm_contact.is_deleted"]=> string(38) "contact_civireport.is_deleted != 1" }

UPDATE : 24th May

In my original post I said the issue appear to affect only those users that were designated as "Member". Doing some more playing around today it appears as if ALL users other than those designated as "Super Users" get the same issue with the rogue SQL being appended to the query.

We have a search defined in CiviCRM to look for various vehicles. If I log onto the site using my id which has "Super User" privileges I can run the query and results are returned. However, if I use an account which doesn't have the same privileges (i.e. a "normal" member account) I get a screen back saying "Sorry, but we are not able to provide this at the moment. DB Error: no such field"

I put debug on and it further reported:

Error Details

Database Error Code: Unknown column 'relationship_civireport.contact_id_a' in 'IN/ALL/ANY subquery', 1054.

When I look at the SQL it does indeed show that it can't resolve the field in question however my query is why would it work for an Admin user and not a normal one? It does seem to point a privilege issue somewhere but how can I identify what this is so I can fix it?

UPDATE:

Doing some more digging around it appears that the generated SQL for the non-Admin user includes these lines which is what causes the issue:

AND `contact_civireport`.`is_deleted` != 1 
AND `relationship_civireport`.`contact_id_a` IN 
  (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1) 
AND `relationship_civireport`.`contact_id_b` IN 
  (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1)

So logged in as Admin, run the report , SQL doesn't have those extra conditions in and the report runs.

Log in as "normal" member , run the report , those lines appear and, as it says, it can't find the field because it's trying to get it from something that isn't a part of the query!

UPDATE : 22nd May

The version is CiviCRM 4.7.19. In terms of the report I believe the underlying one to be from "Contacts" under the "Constituent Report (Detail)" template. It is trying to display all those people who own a car made in a certain year range.

UPDATE : 23rd May

I will try and precis the differences I can see in generating the reports

In the Member log I have this line which doesn't appear in the Admin log:

[info] $Query = SET @civicrm_user_id = 267

In the Admin log I have these lines which don't appear in the Member log :

[info] $Query = SELECT * FROM civicrm_uf_match WHERE (  civicrm_uf_match.domain_id = 1 )  AND (  civicrm_uf_match.uf_id = 187 )
[info] $Query =
SELECT    civicrm_contact.*,
          civicrm_email.email          as email
FROM      civicrm_contact
LEFT JOIN civicrm_email ON civicrm_email.contact_id = civicrm_contact.id
     AND  civicrm_email.is_primary = 1
WHERE     civicrm_contact.id = 10909

[info] $Query = SELECT *  FROM civicrm_contact_type  WHERE (  civicrm_contact_type.name  = 'Individual' )

[info] $Query = SELECT  id  FROM civicrm_uf_group WHERE (  civicrm_uf_group.name  = 'summary_overlay' )
[info] $Query =
SELECT type.*, parent.name as parent, parent.label as parent_label
FROM      civicrm_contact_type type
LEFT JOIN civicrm_contact_type parent ON type.parent_id = parent.id
WHERE  type.name IS NOT NULL

Then the two logs seem to follow the same route but the incorrect SQL generated in the Member log looks like this:

[info] $Query = SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, value_vehicle_1_civireport.car_year_1 as civicrm_value_vehicle_1_custom_1, value_vehicle_1_civireport.vehicle_make_2 as civicrm_value_vehicle_1_custom_2, value_vehicle_1_civireport.vehicle_model_3 as civicrm_value_vehicle_1_custom_3, value_vehicle_1_civireport.vehicle_model_code_31 as civicrm_value_vehicle_1_custom_31, value_vehicle_1_civireport.vehicle_body_style_4 as civicrm_value_vehicle_1_custom_4, value_vehicle_1_civireport.vehicle_body_description_29 as civicrm_value_vehicle_1_custom_29, value_vehicle_1_civireport.doors_5 as civicrm_value_vehicle_1_custom_5, value_vehicle_1_civireport.lhd_or_rhd_8 as civicrm_value_vehicle_1_custom_8, value_vehicle_1_civireport.body_serial_number_9 as civicrm_value_vehicle_1_custom_9, value_vehicle_1_civireport._of_cylinders_7 as civicrm_value_vehicle_1_custom_7, value_vehicle_1_civireport.engine_serial_number_10 as civicrm_value_vehicle_1_custom_10, value_vehicle_1_civireport.hudson_engine__11 as civicrm_value_vehicle_1_custom_11, value_vehicle_1_civireport.vehicle_condition_13 as civicrm_value_vehicle_1_custom_13, value_vehicle_1_civireport.vehicle_modified_32 as civicrm_value_vehicle_1_custom_32
FROM civicrm_contact contact_civireport
LEFT JOIN civicrm_value_vehicle_1 value_vehicle_1_civireport ON value_vehicle_1_civireport.entity_id = contact_civireport.id WHERE ( contact_civireport.is_deceased = 0 ) AND ( value_vehicle_1_civireport.car_year_1 IN (1909, 1910, 1911, 1912, 1913) ) AND ( ( value_vehicle_1_civireport.vehicle_make_2 IN ( 'Hudson') ) ) **AND `contact_civireport`.`is_deleted` != 1 AND `relationship_civireport`.`contact_id_a` IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1) AND `relationship_civireport`.`contact_id_b` IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1)**    ORDER BY value_vehicle_1_civireport.car_year_1 ASC, value_vehicle_1_civireport.vehicle_make_2 ASC, value_vehicle_1_civireport.vehicle_model_3 ASC  LIMIT 0, 10

Then these steps happen in the Member log which I assume is a "tidy-up":

[info] $Query = SELECT GET_LOCK( '181fec6b8c8018b4408d33805c7d9c73e37c2684', 3 )
[info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593_container"
 [info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
(NULL,"2017-05-23 07:06:09","a:4:{s:8:\"defaults\";a:0:{}s:9:\"constants\";a:0:{}s:6:\"values\";a:1:{s:6:\"Detail\";a:0:{}}s:5:\"valid\";a:1:{s:6:\"Detail\";N;}}","CiviCRM Session","_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593_container")
[info] $Query = SELECT RELEASE_LOCK( '181fec6b8c8018b4408d33805c7d9c73e37c2684' )
[info] $Query = SELECT GET_LOCK( '70b6381734fcfe80b5c1603e4bcea118a9040f61', 3 )
[info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "CiviCRM_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593"
[info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
(NULL,"2017-05-23 07:06:09","a:3:{s:8:\"entryURL\";s:127:\"http://xxx.xxx.org/index.php?option=com_civicrm&task=civicrm/report/instance/181&option=com_civicrm&reset=1\";s:5:\"qfKey\";s:37:\"d3ccfb3ef5be498a753ab5c22d2f11d4_7593\";s:6:\"crmPID\";i:1;}","CiviCRM Session","CiviCRM_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593")
[info] $Query = SELECT RELEASE_LOCK( '70b6381734fcfe80b5c1603e4bcea118a9040f61' )

In the Admin log the SQL looks like this and then you can there is more activity afterwards which I assume is part of the report running correctly:

[info] $Query = SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, value_vehicle_1_civireport.car_year_1 as civicrm_value_vehicle_1_custom_1, value_vehicle_1_civireport.vehicle_make_2 as civicrm_value_vehicle_1_custom_2, value_vehicle_1_civireport.vehicle_model_3 as civicrm_value_vehicle_1_custom_3, value_vehicle_1_civireport.vehicle_model_code_31 as civicrm_value_vehicle_1_custom_31, value_vehicle_1_civireport.vehicle_body_style_4 as civicrm_value_vehicle_1_custom_4, value_vehicle_1_civireport.vehicle_body_description_29 as civicrm_value_vehicle_1_custom_29, value_vehicle_1_civireport.doors_5 as civicrm_value_vehicle_1_custom_5, value_vehicle_1_civireport.lhd_or_rhd_8 as civicrm_value_vehicle_1_custom_8, value_vehicle_1_civireport.body_serial_number_9 as civicrm_value_vehicle_1_custom_9, value_vehicle_1_civireport._of_cylinders_7 as civicrm_value_vehicle_1_custom_7, value_vehicle_1_civireport.engine_serial_number_10 as civicrm_value_vehicle_1_custom_10, value_vehicle_1_civireport.hudson_engine__11 as civicrm_value_vehicle_1_custom_11, value_vehicle_1_civireport.vehicle_condition_13 as civicrm_value_vehicle_1_custom_13, value_vehicle_1_civireport.vehicle_modified_32 as civicrm_value_vehicle_1_custom_32
        FROM civicrm_contact contact_civireport
LEFT JOIN civicrm_value_vehicle_1 value_vehicle_1_civireport ON value_vehicle_1_civireport.entity_id = contact_civireport.id WHERE ( contact_civireport.is_deceased = 0 ) AND ( value_vehicle_1_civireport.car_year_1 IN (1909, 1910, 1911, 1912, 1913) ) AND ( ( value_vehicle_1_civireport.vehicle_make_2 IN ( 'Hudson') ) )   ORDER BY value_vehicle_1_civireport.car_year_1 ASC, value_vehicle_1_civireport.vehicle_make_2 ASC, value_vehicle_1_civireport.vehicle_model_3 ASC  LIMIT 0, 10

[info] $Query = SELECT FOUND_ROWS();

[info] $Query =
SELECT cg.table_name, cf.id
FROM  civicrm_custom_field cf
INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
WHERE cg.extends IN ('Contact','Individual','Household','Organization') AND
      cg.is_active = 1 AND
      cf.is_active = 1 AND
      cf.is_searchable = 1 AND
      cf.data_type   NOT IN ('ContactReference', 'Date') AND
      cf.id IN (1,2,3,31,4,29,5,8,9,7,10,11,13,32)

[info] $Query = SELECT *  FROM civicrm_custom_field WHERE (  civicrm_custom_field.id = 1 )

< snip ; same sort of SQL >

[info] $Query = SELECT contribution_civireport.contact_id as civicrm_contribution_contact_id   FROM
                            civicrm_contact  contact_civireport
                            INNER JOIN civicrm_contribution       contribution_civireport
                                    ON contact_civireport.id = contribution_civireport.contact_id

[info] $Query = SELECT membership_civireport.contact_id as civicrm_membership_contact_id, membership_civireport.end_date as civicrm_membership_membership_end_date   FROM
                            civicrm_contact  contact_civireport
                            INNER JOIN civicrm_membership       membership_civireport
                                    ON contact_civireport.id = membership_civireport.contact_id
                         WHERE    contact_civireport.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 )

[info] $Query = SELECT participant_civireport.contact_id as civicrm_participant_contact_id   FROM
                            civicrm_contact  contact_civireport
                            INNER JOIN civicrm_participant       participant_civireport
                                    ON contact_civireport.id = participant_civireport.contact_id
                         WHERE    contact_civireport.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 )

[info] $Query = SELECT  id, label_a_b, label_b_a, contact_type_a, contact_type_b, contact_sub_type_a, contact_sub_type_b
 FROM civicrm_relationship_type  WHERE (  civicrm_relationship_type.is_active = 1 )

[info] $Query = SELECT relationship_civireport.relationship_type_id as civicrm_relationship_relationship_type_id, relationship_civireport.contact_id_b as civicrm_relationship_contact_id_b ,contact_civireport.display_name as contact_b_name,  contact_a.id as contact_a_id , contact_a.display_name  as contact_a_name  FROM
                            civicrm_relationship relationship_civireport

                            LEFT JOIN civicrm_contact  contact_civireport ON
                                contact_civireport.id = relationship_civireport.contact_id_b
                            LEFT JOIN civicrm_contact  contact_a ON
                               contact_a.id = relationship_civireport.contact_id_a
                         WHERE    (contact_civireport.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 )
                                  OR
                                  contact_a.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 ) ) AND
                                  relationship_civireport.is_active = 1 AND
                                  contact_a.is_deleted = 0 AND

[info] $Query = SELECT *  FROM civicrm_component

[info] $Query =
SELECT  v.label as label ,v.value as value, v.grouping as grouping
FROM   civicrm_option_value v,
       civicrm_option_group g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'activity_type'
  AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  AND  ( v.component_id IS NULL OR v.component_id IN (1,2,3,4,6,8)) ORDER BY v.weight

[info] $Query =
SELECT  v.label as label ,v.value as value, v.grouping as grouping
FROM   civicrm_option_value v,
       civicrm_option_group g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'activity_status'
  AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  ORDER BY v.weight

[info] $Query =
SELECT  v.label as label ,v.value as value, v.grouping as grouping
FROM   civicrm_option_value v,
       civicrm_option_group g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'relative_date_filters'
  AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  ORDER BY v.weight


[info] $Query = SELECT * FROM civicrm_preferences_date WHERE (  civicrm_preferences_date.name  = 'searchDate' )

[info] $Query = SELECT * FROM civicrm_report_instance WHERE (  civicrm_report_instance.id = 181 )

[info] $Query = SELECT data FROM civicrm_cache WHERE group_name = "navigation" AND path = "navigationList"

[info] $Query =
SELECT id, title, description, visibility, parents
FROM   civicrm_group
WHERE  id IN (133,139,145,137,144,33,31,30,36,29,32,38,35,39,37,34,2,24,28,20,26,23,21,27,22,25,116,4,117,111,114,12,18,16,13,10,11,7,6,9,19,17,8,15,3,112,128,119,147,138,120,124,125,126,127,113,131,130,143,53,52,54,55,115,56,57,58,59,60,61,62,63,64,65,67,66,68,69,70,72,71,73,74,75,76,77,78,79,80,81,82,83,84,86,85,108,87,88,89,105,104,136,91,99,100,107,92,94,95,98,97,96,101,106,102,103,93,109,141,48,50,40,46,118,44,43,45,51,47,49,5,140,142,146,122,135,134)
 ORDER BY title

[info] $Query = SELECT *  FROM civicrm_navigation  WHERE (  civicrm_navigation.id = 483 )  AND (  civicrm_navigation.domain_id = 1 )

[info] $Query = SELECT GET_LOCK( '51f408a0235586dbe891208004a7e5fc67e72dad', 3 )

[info]  QUERY DONE IN 0.000398  seconds. Result is 1 rows by 1 columns.

[info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578_container"

[info]  QUERY DONE IN 0.000546  seconds. Result is 1 rows by 1 columns.

[info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
(NULL,"2017-05-23 07:07:57","a:4:{s:8:\"defaults\";a:0:{}s:9:\"constants\";a:0:{}s:6:\"values\";a:1:{s:6:\"Detail\";a:0:{}}s:5:\"valid\";a:1:{s:6:\"Detail\";N;}}","CiviCRM Session","_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578_container")

[info] $Query = SELECT RELEASE_LOCK( '51f408a0235586dbe891208004a7e5fc67e72dad' )

[info] $Query = SELECT GET_LOCK( '431ea828bed0270e81256e366f6cf4b682d6cfac', 3 )

[info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "CiviCRM_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578"

[info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
(NULL,"2017-05-23 07:07:57","a:3:{s:8:\"entryURL\";s:127:\"http://xxxx.xxxx.org/index.php?option=com_civicrm&amp;task=civicrm/report/instance/181&amp;option=com_civicrm&amp;reset=1\";s:5:\"qfKey\";s:37:\"6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578\";s:6:\"crmPID\";i:1;}","CiviCRM Session","CiviCRM_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578")

[info] $Query = SELECT RELEASE_LOCK( '431ea828bed0270e81256e366f6cf4b682d6cfac' )
  • What version of CiviCRM is this? What is the underlying report template, and is it custom? Please update your question to reflect that. If this is a current version of CiviCRM and a non-custom report template, we should document the steps to replicate this on demo.civicrm.org and file a bug. – Jon G - Megaphone Tech May 21 '17 at 20:35
  • So I have done a little more digging and have attached two files from the SQL log. One is using a member account and the other the Admin account and you can see the differences in there. Is there a way to upload these files? – Hudson Essex Terraplane Club May 23 '17 at 16:56
4

I just ran into this issue (on current Drupal, current CiviCRM setup) and confirmed that the way it's set now, a non-admin user needs the permission "access deleted contacts" to run this report.

Hope that's helpful - not much of an answer but I lost access to my old account and don't have enough rep to post a comment :)

  • Solution of Eli Lisseck worked for me. Although I don't want them all to have access to the deleted contacts, it fixed my issue. And since my PHP knowledge is null, this is great! – Betty Dolfing Apr 6 '18 at 6:43

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.