CiviCRM Community Forums (archive)

Hi,
we are now trying to get CiviCRM in to use in our organization, however we receive a lot of DB Errors and it is kind of a issue for the use.

We have ACL rules for registered users, shared contacts and a private group.

There are 6 different normal groups.

We receive this error, specially when user goes to user dashboard (civicrm/user):

<div class="icon crm-accordion-pointer"></div>
        Error Details         </div><!-- /.crm-accordion-header -->
         <div class="crm-accordion-body">
                                        <div class="crm-section">Database Error Code: Unknown column 'contact_activity_source.is_deleted' in 'where clause', 1054</div>
                                        <div class="crm-section">Additional Details: <p><pre>Array
(
    [callback] =&gt; Array
        (
            [0] =&gt; CRM_Core_Error
            [1] =&gt; handle
        )

    [code] =&gt; -19
    [message] =&gt; DB Error: no such field
    [mode] =&gt; 16
    [debug_info] =&gt; SELECT count( DISTINCT ( civicrm_activity.id ) )  FROM civicrm_contact contact_a LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id  LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
                            AND civicrm_activity.is_deleted = 0
                            AND civicrm_activity.is_current_revision = 1 )  LEFT JOIN civicrm_group_contact `civicrm_group_contact-ACL` ON contact_a.id = `civicrm_group_contact-ACL`.contact_id   LEFT JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status') LEFT JOIN civicrm_option_value activity_status ON (civicrm_activity.status_id = activity_status.value
                               AND option_group_activity_status.id = activity_status.option_group_id )  LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') LEFT JOIN civicrm_option_value activity_type ON (civicrm_activity.activity_type_id = activity_type.value
                               AND option_group_activity_type.id = activity_type.option_group_id )  WHERE  (  civicrm_activity.status_id IN (1) AND contact_a.id = 651 AND  contact_activity_source.is_deleted = 0 AND contact_activity_source.sort_name LIKE '%%' AND  civicrm_activity_assignment.activity_id = civicrm_activity.id AND civicrm_activity_assignment.assignee_contact_id = contact_activity_source.id AND civicrm_activity.is_test = 0 )  AND  ( ( `civicrm_group_contact-ACL`.group_id IN (5) AND `civicrm_group_contact-ACL`.status IN (&quot;Added&quot;) ) )  AND  ( 1 )      [nativecode=1054 ** Unknown column 'contact_activity_source.is_deleted' in 'where clause']
    [type] =&gt; DB_Error
    [user_info] =&gt; SELECT count( DISTINCT ( civicrm_activity.id ) )  FROM civicrm_contact contact_a LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id  LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
                            AND civicrm_activity.is_deleted = 0
                            AND civicrm_activity.is_current_revision = 1 )  LEFT JOIN civicrm_group_contact `civicrm_group_contact-ACL` ON contact_a.id = `civicrm_group_contact-ACL`.contact_id   LEFT JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status') LEFT JOIN civicrm_option_value activity_status ON (civicrm_activity.status_id = activity_status.value
                               AND option_group_activity_status.id = activity_status.option_group_id )  LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') LEFT JOIN civicrm_option_value activity_type ON (civicrm_activity.activity_type_id = activity_type.value
                               AND option_group_activity_type.id = activity_type.option_group_id )  WHERE  (  civicrm_activity.status_id IN (1) AND contact_a.id = 651 AND  contact_activity_source.is_deleted = 0 AND contact_activity_source.sort_name LIKE '%%' AND  civicrm_activity_assignment.activity_id = civicrm_activity.id AND civicrm_activity_assignment.assignee_contact_id = contact_activity_source.id AND civicrm_activity.is_test = 0 )  AND  ( ( `civicrm_group_contact-ACL`.group_id IN (5) AND `civicrm_group_contact-ACL`.status IN (&quot;Added&quot;) ) )  AND  ( 1 )      [nativecode=1054 ** Unknown column 'contact_activity_source.is_deleted' in 'where clause']
    [to_string] =&gt; [db_error: message=&quot;DB Error: no such field&quot; code=-19 mode=callback callback=CRM_Core_Error::handle prefix=&quot;&quot; info=&quot;SELECT count( DISTINCT ( civicrm_activity.id ) )  FROM civicrm_contact contact_a LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id  LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
                            AND civicrm_activity.is_deleted = 0
                            AND civicrm_activity.is_current_revision = 1 )  LEFT JOIN civicrm_group_contact `civicrm_group_contact-ACL` ON contact_a.id = `civicrm_group_contact-ACL`.contact_id   LEFT JOIN civicrm_option_group option_group_activity_status ON (option_group_activity_status.name = 'activity_status') LEFT JOIN civicrm_option_value activity_status ON (civicrm_activity.status_id = activity_status.value
                               AND option_group_activity_status.id = activity_status.option_group_id )  LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') LEFT JOIN civicrm_option_value activity_type ON (civicrm_activity.activity_type_id = activity_type.value
                               AND option_group_activity_type.id = activity_type.option_group_id )  WHERE  (  civicrm_activity.status_id IN (1) AND contact_a.id = 651 AND  contact_activity_source.is_deleted = 0 AND contact_activity_source.sort_name LIKE '%%' AND  civicrm_activity_assignment.activity_id = civicrm_activity.id AND civicrm_activity_assignment.assignee_contact_id = contact_activity_source.id AND civicrm_activity.is_test = 0 )  AND  ( ( `civicrm_group_contact-ACL`.group_id IN (5) AND `civicrm_group_contact-ACL`.status IN (&quot;Added&quot;) ) )  AND  ( 1 )      [nativecode=1054 ** Unknown column 'contact_activity_source.is_deleted' in 'where clause']&quot;]
)

I remember that it stated somewhere that this might happen if the SQL-query is too long? However, what is the fix? Should I remove groups (this happened after I created two new groups)? However, having only max 4 groups is kind of limiting..

Do you know if this happens only in user dashboard?

Second issue, it seems like if you use ACL, then you cannot create normal groups without setting ACL permissions to them. At least, other people can't see these groups in search for instance, if you do not set any ACL permissions. Is this correct?

Any help on this matter would be highly appreciated!


I also tried https://fisheye2.atlassian.com/rdiff/CiviCRM/branches/v3.4/CRM/ACL/BAO/ACL.php?r1=36052&r2=37432&u&N patch and it did not fix the problem, even after clearing templates_c.

The first issue is wierd and seems like a bad query. I'm not sure if ACL's are even involved here. however i could not reproduce the error on civicrm.org, so not sure whats triggering the error

yes, your interpretation is right with regard to the second item

lobo

Thanks for your reply!

I could not reproduce the error either, however this does NOT happen when user is administrator, only with authenticated users.

I have been looking activity tables all day, and I do not seem to find any errors in them.

This error happens when user goes first time to civicrm/user or tries to join or leave a group in that page.

Here is the backtrace:

/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 159
, handle,
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 931
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 968
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 564
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/DB/DataObject.php, query, 2424
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/DB/DataObject.php, _query, 1610
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 152
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 912
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php, singleValueQuery, 3616
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Activity/Selector/Search.php, searchQuery, 222
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Core/Selector/Controller.php, getTotalCount, 232
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Activity/Form/Search.php, __construct, 391
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Activity/Form/Search.php, postProcess, 192
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Core/Form.php, preProcess, 325
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Display.php, buildForm, 99
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Core/Controller.php, handle, 284
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Activity/Page/UserDashboard.php, run, 63
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Activity/Page/UserDashboard.php, listActivities, 79
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Contact/Page/View/UserDashBoard.php, run, 182
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Contact/Page/View/UserDashBoard.php, buildUserDashBoard, 210
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/CRM/Core/Invoke.php, run, 224
/Applications/MAMP/htdocs/drupal-7.8/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 447
, civicrm_invoke,
/Applications/MAMP/htdocs/drupal-7.8/includes/menu.inc, call_user_func_array, 503
/Applications/MAMP/htdocs/drupal-7.8/index.php, menu_execute_active_handler, 20


I am not sure if this is related to this, but with authenticated users win searching for instance only organizations, sometimes the search shows double or triple amount of results! This is very confusing for the users.

Another issue was this:

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

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] =>
DELETE     pn, c
FROM       civicrm_cache c
INNER JOIN civicrm_prevnext_cache pn ON c.path = pn.cacheKey
WHERE      c.group_name = %1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )
 [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 '%1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )' at line 4]
    [type] => DB_Error
    [user_info] =>
DELETE     pn, c
FROM       civicrm_cache c
INNER JOIN civicrm_prevnext_cache pn ON c.path = pn.cacheKey
WHERE      c.group_name = %1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )
 [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 '%1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )' at line 4]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="
DELETE     pn, c
FROM       civicrm_cache c
INNER JOIN civicrm_prevnext_cache pn ON c.path = pn.cacheKey
WHERE      c.group_name = %1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )
 [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 '%1
AND        c.created_date < date_sub( NOW( ), INTERVAL 2 day )' at line 4]"]
)

This happened while a user was trying to modify information on already existing organization.

Could these all be somehow related to caching? Because in the first problem with activities, when user visits the /user page second time, then no error occurs..


For your information, here are the defined groups

Administrator, id 1, Access Control, users and user admin only
Private contacts, id 3, -, public pages
Private users, id 2, access control mailing list, user and user admin only
Set 1, id 6, -, User and user admin only
Set 2, id 7, -, User and user admin only
Shared contacts, id 5, public pages

ACL Roles

Administrator
Authenticated
Private Access


Assign Users to Roles

Administrators, assigned to Administrators
Private Access, assigned to Private users


Manage ACLs

Administrators, edit, group, All Groups
Authenticated, edit, group, Shared contacts
Private Access, edit, group, Private contacts


Currently there is no information Set 1 and Set 2 in ACL, however when I tested to set ACLs for those, it did not change the behavior.

I did some mild progress by comparing past CiviCRM databases. The previous problems of wrong number search result and the DB error while joining a group are connected to ACLs.

Some background: I switched to use multi-site installation, and now the default group is set to id 3 - so this is a multi-site setup.

There is no group nesting in our installation! I tried to create a branch of groups by having shared contacts as the most parent, then linking the rest of the groups to shared contacts. It did not help.

The wrong indexing number can be temporarily corrected by emptying group_contact_cache-table. But this is not really a fix, but maybe this helps in finding the real problem.

I suspect that one reason too many search results is that if I have a contact that belongs to group private contact AND shared contact, then it gets counted twice. To keep things short, I think I just have wrongly set ACL and groups (specially what is access control group and what is not).

To understand our ACL scheme, it goes basically like this: there is a huge pool of shared contacts, they should be available for all authenticated users. There is a group private users, who consists of only special authenticated users, who can then access private contacts.

Since I might had understood something wrong, could some of you please suggest a scheme of groups and ACLs for my need? It might actually solve this whole problem.

Thanks for the help!

We now tested a different kind of setting in our testing version. I'll report here my current situation, maybe someone will find this information useful. Please do tell if some information regarding CiviCRM/Drupal is invalid.

Multi-site is now disabled and permission to edit contacts is done using Drupal roles. Basically we have additional Drupal role that allows editing and viewing of all contacts. Editing and viewing is enabled in Drupal permissions and these should override CiviCRM ACL. This Drupal role, called Private access should be able to edit and view all contacts.

Now, we need to restrict a certain group of users only to access certain contacts. This is done by adding a CiviCRM ACL rule, that states only authenticated users can edit Shared contacts-group. Meanwhile in Drupal, authenticated users do NOT have editing and viewing all permissions, so ACL rules apply to them.

In other words, all authenticated users can only access Shared contacts-group. However, if authenticated/registered user is given Drupal Private access role, then CiviCRM ACL rules no longer apply, and they can access all the contacts existing.

The wrong number of search results still existed when user tried to search for contacts in Shared contacts-group. This error was solved, at least temporarily, by removing shared contacts-group and creating it again.

The another DB error I mentioned earlier might relate to this: http://forum.civicrm.org/index.php/topic,22640.0.html .

One issue still remains, authenticated users without Private access are not forced to assign Shared contacts-group to new contacts. This means that if user creates a new contact, then the user does not have permission to access this contact later on, since it is not in the Shared contacts group. I think this also happens automatically, if user enters nonexistent Current employer for a new contact, and then CiviCRM automatically creates that Employer without a group - which means user does not have access to automatically created employer organization. This has been discussed earlier here: http://forum.civicrm.org/index.php/topic,22337.msg93622.html , however if any of you have any additional suggestions for a solution or other comments, I am very interested in hearing them!