CiviCRM Community Forums (archive)

Hi there,

I'm evaluating CiviCRM for use in our company and everything has worked out wonderful so far - until I tried out the ACL-System.
But first things first, my system:

Ubuntu 10.04LTS
Apache Webserver
(<-- PHP + Mysql)
Drupal 7.10 (Only CiviCRM + LDAP running, tried to switch LDAP auth off as well)
CiviCRM 4.0

Here are my restrictions for the "authenticated user"'`s role in the drupal administration panel: (everything listed here is checked)
-adding and importing contacts
-accessing CiviCRM
-accessing the contact dashboard
-translating
-merging duplicates
-accessing CiviMail
-(un-)subscribing to pages
-viewing public CiviMail content

My ACL Settings are the following: (Roles: Administrator + Authenticated)
-Authenticated can View the Group "TestGroup01"
-Authenticated can Edit the Group "TestGroup01"
-Administrator can View "All Groups"
-Administrator can Edit "All Groups"

The Group "TestGroup01" has 3 entrys in it:
--> TestUser01, TestUser02, TestUser03

They all have registered and working email-adresses and Testuser01 even has a login.

Okay, now let's move on to my problem: How to reproduce the bug...
When I login with TestUser01, go to CiviCRM and launch any search for contacts, no matter if it's an empty search, an explicit search for a contact name or a search for all members of TestGroup01 - The result is an error screen which says:

Sorry. A non-recoverable error has occurred. DB Error: no such field. > Error Details (unclickable). Return to home page. (Link)

Well... that's not much information actually. The .log-file tells me a little more: (#/ = the drupal install directory)

[I also attached this part as logfile.txt]

Dec 12 17:00:00  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

[code ] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache-ACL` ON contact_a.id = `civicrm_group_contact_cache-ACL`.contact_id    LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_9` ON contact_a.id = `civicrm_group_contact_cache_9`.contact_id   WHERE  ( `civicrm_group_contact_cache_9`.group_id = 9 )  AND  ( ( `civicrm_group_contact-ACL`.group_id IN (9) AND `civicrm_group_contact-ACL`.status IN ("Added") ) OR `civicrm_group_contact_cache-ACL`.group_id IN (9) )  AND (contact_a.is_deleted = 0)     [nativecode=1054 ** Unknown column 'civicrm_group_contact-ACL.group_id' in 'where clause']
    [type] => DB_Error
    [user_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache-ACL` ON contact_a.id = `civicrm_group_contact_cache-ACL`.contact_id    LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_9` ON contact_a.id = `civicrm_group_contact_cache_9`.contact_id   WHERE  ( `civicrm_group_contact_cache_9`.group_id = 9 )  AND  ( ( `civicrm_group_contact-ACL`.group_id IN (9) AND `civicrm_group_contact-ACL`.status IN ("Added") ) OR `civicrm_group_contact_cache-ACL`.group_id IN (9) )  AND (contact_a.is_deleted = 0)     [nativecode=1054 ** Unknown column 'civicrm_group_contact-ACL.group_id' in 'where clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache-ACL` ON contact_a.id = `civicrm_group_contact_cache-ACL`.contact_id    LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_9` ON contact_a.id = `civicrm_group_contact_cache_9`.contact_id   WHERE  ( `civicrm_group_contact_cache_9`.group_id = 9 )  AND  ( ( `civicrm_group_contact-ACL`.group_id IN (9) AND `civicrm_group_contact-ACL`.status IN ("Added") ) OR `civicrm_group_contact_cache-ACL`.group_id IN (9) )  AND (contact_a.is_deleted = 0)     [nativecode=1054 ** Unknown column 'civicrm_group_contact-ACL.group_id' in 'where clause']"]
)


Dec 12 17:00:00  [info] $backTrace = #/drupal/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 205
, handle,
#/drupal/sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 931
#/drupal/sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 968
#/drupal/sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 564
#/drupal/sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
#/drupal/sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
#/drupal/sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
#/drupal/sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
#/drupal/sites/all/modules/civicrm/packages/DB/DataObject.php, query, 2424
#/drupal/sites/all/modules/civicrm/packages/DB/DataObject.php, _query, 1610
#/drupal/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 152
#/drupal/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 879
#/drupal/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, executeQuery, 3619
#/drupal/sites/all/modules/civicrm/CRM/Contact/Selector.php, searchQuery, 869
#/drupal/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php, alphabetQuery, 91
#/drupal/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php, getDynamicCharacters, 117
#/drupal/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php, createLinks, 61
#/drupal/sites/all/modules/civicrm/CRM/Contact/Form/Search.php, getAToZBar, 832
#/drupal/sites/all/modules/civicrm/CRM/Contact/Form/Search/Basic.php, postProcess, 233
#/drupal/sites/all/modules/civicrm/CRM/Core/Form.php, postProcess, 250
#/drupal/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Refresh.php, mainProcess, 79
#/drupal/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
#/drupal/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
#/drupal/sites/all/modules/civicrm/CRM/Core/Controller.php, handle, 284
#/drupal/sites/all/modules/civicrm/CRM/Core/Invoke.php, run, 224
#/drupal/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 447
, civicrm_invoke,
#/drupal/includes/menu.inc, call_user_func_array, 503
#/drupal/index.php, menu_execute_active_handler, 21

So I found out that it runs this following SQL Query: (re-formatted for better readability)

SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1))
as sort_name
FROM civicrm_contact contact_a
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache-ACL` ON contact_a.id = `civicrm_group_contact_cache-ACL`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_9` ON contact_a.id = `civicrm_group_contact_cache_9`.contact_id
WHERE  ( `civicrm_group_contact_cache_9`.group_id = 9 )
AND  ( ( `civicrm_group_contact-ACL`.group_id IN (9) AND `civicrm_group_contact-ACL`.status IN ("Added") ) OR `civicrm_group_contact_cache-ACL`.group_id IN (9) )
AND (contact_a.is_deleted = 0)

In my opinion this one makes no sense, because it joins the same table with another one twice and there's even a syntax problem, so I tried to run the query manually and also tried to fix it manually - my result is this:

SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1))
AS sort_name
FROM civicrm_contact contact_a
LEFT JOIN civicrm_group_contact `civicrm_group_contact-ACL` ON contact_a.id = `civicrm_group_contact-ACL`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_9` ON contact_a.id = `civicrm_group_contact_cache_9`.contact_id
WHERE (`civicrm_group_contact_cache_9`.group_id = 9)
AND ((`civicrm_group_contact-ACL`.group_id IN (9) AND `civicrm_group_contact-ACL`.status IN ("Added")) OR `civicrm_group_contact_cache_9`.group_id IN (9))
AND (contact_a.is_deleted = 0)

This makes much more sense to me, what do you think?

Well the problem is... I found out that it is generated in the

sites/all/modules/civicrm/CRM/ACL/BOA/ACL.php

and I'm not very used to debugging PHP code... I did some output to files but didn't get any interesting information from this, so I was hoping you could eventually help me out here, as some of you probably have a much better overview over the code than I could ever obtain. Do you have any hints for me? Am I the only one experiencing this?

If you reached this sentence after reading all the text above, then I already want to THANK YOU. I hope you can help me.

Regards,

Roland

PS: Sorry, I had to put it as Quote (not as Code) because otherwise it gave me an error that I'm not allowed to post external links...

Hi Lobo,

thanks for your very fast help, as far as I could test it in the last 10 minutes you solved my problem, thanks.
If there occurs any further problem, I'll post again here, thanks so far.

Regards,

Roland