Answers.Body: <p>For me, the only way to deal with this problem was to remove as many smart groups as I could from the system. In the process I also rewrote all the dashlet reports I could into Drupal views. There are a few dashlets left that are CiviCRM reports and still rely on smart group. I believe that people who use these reports still have problems with data "disappearing" which are the effect of deadlocks. But they are so used to it that they don't bother me about it any more!</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-07-10T11:53:02.457Z
Answers.LastActivityDate: 2018-07-10T11:53:02.457Z
Answers.UserDisplayName: naomi
Answers.Body: <p>I cant answer everything but I think I can help clarify a few things;</p>
  
  <blockquote>
    <p>How can there be a difference between when the cache is flushed and when it is built, anyway? Surely refilling the cache means first you flush it, then you fill it. They cannot be separate</p>
  </blockquote>
  
  <p>The answer to this lies in the state of the cache, there are in fact two states;</p>
  
  <ol>
  <li>Current - does not need clearing or rebuilding</li>
  <li>Invalid - this is either empty (as an operation flushed it or expired due to time/cron)</li>
  </ol>
  
  <p>Important bit of information is that an action may chose to flush the cache without rebuilding it - as its not needed by that action but the action would affect it. A good example is a new contact being created - the ACL may be affected but in the create action the cache only needs to be flushed so that the ACL action can then build it.</p>
  
  <blockquote>
    <p>And for one last extremely confusing thing: I turned off the cron job. Why is the ACL cache still trying to update?</p>
  </blockquote>
  
  <p>All caching has a expiration time, regardless of the cron if that time expires the next action will try to rebuild the cache. The idea of the cron is to carry out that action offline therefore reducing the time taken on the user action.</p>
  
  <p>I'd like to work through the issue with you so please do keep this conversation going - we have some large customers running ACL without major cache issues so it should be a good case to establish exactly whats going wrong.</p>
  
  <p>Perhaps you could share some numbers i.e. How many ACL groups/rules, smart groups, size of contacts etc. All of these factors will play in important part of the diagnosis.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-10T16:26:58.393Z
Answers.Text: Thank you, this is helpful. From this, it seems that that I did the right thing in turning off the cron job, as there is no point having it given that the cache is set to 0 . Is that correct? I will get back to you with the information you requested, in the next few days. I would love it if we could get to the bottom of this!
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-16T11:07:24.487Z
Answers.Text: Amount of contacts: 7333
  Amount of smart groups: 42
  Amount of ACL groups: 9
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-16T11:35:44.317Z
Answers.Text: Sorry, amount of ACL groups is 22
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-16T11:42:58.117Z
Answers.Text: Amount of ACL roles: 30. Amount of ACLs : 175 (seems excessive, but that's the result of "select count(*) from civicrm_acl where is_active = 1"). Amount of said ACLs whose object is a smart group: 10. My next test will be to rejig things so that no ACLs rely on smart groups at all, and then see if this resolves the issue.
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-16T20:30:55.293Z
Answers.Text: I think thats the right approach, smart groups are heavy on cache refreshes especially if constantly being required for ACLs
Answers.UserDisplayName: Parvez Saleh
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-24T08:14:26.613Z
Answers.Text: I've changed all the ACL target groups to normal groups . None use smart groups at all. But we are still getting the deadlocks. It seems that the ACL cache is getting rebuilt every time the smart groups change, even though there is no need whatsoever for that to happen any more
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-24T08:16:32.490Z
Answers.Text: The only way I am managing to deal with this is to replace all the dashlets with Drupal Views. They work much better than CiviCRM report dashlets, but I don't like the security implications of giving so much access to the CiviCRM website to the Drupal user. It's not suitable for our setup at all because we have a public facing website with very confidential data in Civi. I am rather upset about this
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-09T16:05:31.217Z
Answers.LastActivityDate: 2017-10-09T16:05:31.217Z
Answers.UserDisplayName: Parvez Saleh
Answers.Body: <p>We have a very similar setup, with the smart group cache timeout set to 0. We also get a lot of deadlocks, but ours are with civicrm_group_contact_cache. They're a mixture of delete, insert, insert ignore, and create temporary table.</p>
  
  <p>Like you I can't quite understand what's happening, or why it results in a deadlock (why doesn't it just queue?). And I'm very interested in seeing other answers here! But I've recently had some luck hacking around the problem by adding some try...catch loops around the SQL queries in question. On ours it now waits a second, then retries the query - and does this up to 10 times. I expect proper programmers will react in horror, but nonetheless this has solved 99% of our deadlocks.</p>
  
  <p>So the original line in Contact/BAO/GroupContactCache.php was:</p>
  
  <pre><code>CRM_Core_DAO::executeQuery($query, $params);
  </code></pre>
  
  <p>and we made it into:</p>
  
  <pre><code>    $attempts = 0;
      while ($attempts &lt; 10) {
          try {
              CRM_Core_DAO::executeQuery($query, $params);
          } catch ( \Exception $e ) {
              if ( $attempts &lt; 10 ) {
                  sleep( 1 );
                  $attempts += 1;
                  continue;
              } else {
                  throw $e;
              }
          }
          break;
      }
  </code></pre>
  
  <p>I had to do this with 4 queries in Contact/BAO/GroupContactCache.php. You need at least PHP5.5, I think.</p>
  
  <p>I put in some logging, and I can see that it almost always works on the second try.</p>
  
  <p>It's not elegant, and obviously the actual problem is elsewhere. But at least it bypasses it while we figure out the underlying issue. Maybe something similar would work for the ACL cache.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-10T18:18:48.343Z
Answers.Text: Thanks, I have put this in, and will see how it goes. All proper programmers have duct tape in their first aid kit :)
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-10T19:59:25.063Z
Answers.Text: I think I must be going crazy, but this code is not catching exceptions for me! The exception is being thrown anyway, immediately rather than after 10 seconds. And I put a logging statement right at the top of the catch block, which is never reached. Most confusing
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-11T22:49:04.860Z
Answers.Text: Is it definitely \Exception and not just Exception? I had the same issue till I added the backslash.
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-16T10:48:00.490Z
Answers.Text: Yes, it's exactly as you have it.
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-16T10:54:27.720Z
Answers.Text: What version of Civi are you on? I'm on 4.7.24
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-22T15:59:46.833Z
Answers.Text: That's so odd! We're 4.7.17 so a little behind you.
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-09T18:00:40.973Z
Answers.LastActivityDate: 2017-10-09T18:14:31.720Z
Answers.LastEditDate: 2017-10-09T18:14:31.720Z
Answers.UserDisplayName: Andrew West
Answers.Body: <p>We have over 60,000 contacts and dozens of smart groups and were seeing a MySQL deadlock with smart group cache processes. I changed the CiviCRM cronjob to run every 30 minutes (previously and for other reasons) and edited the scheduled job in CiviCRM for smart group caching so that the parameter of the job is <code>limit=20</code> which allows only 20 smart groups to be cached per cron run. After killing the last batch of stuck processes in MySQL I haven't seen this problem pop up. </p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-11T14:01:37.653Z
Answers.Text: Thanks but, if I understand you correctly, this would mean smart groups don't immediately update? I need them to update immediately (see my question)
Answers.UserDisplayName: naomi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-11T17:31:22.643Z
Answers.Text: It will cache the mailing group when you compose the mailing and add them to the recipients.
Answers.UserDisplayName: Christia
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-11-30T07:33:01.777Z
Answers.LastActivityDate: 2017-11-30T07:33:01.777Z
Answers.UserDisplayName: Christia
Body: <p>I know that the Civi devs have been struggling with this issue so I am not complaining or asking for it to be fixed - I just want to understand how it works, so that I can work aroud it.</p>
  
  <p>I am constantly getting complaints from users that data is missing from their dashboard, and when I look in the logs it is invariably due to deadlocks in <code>civicrm_acl_contact_cache</code>. The logs show that these deadlocks happen when the system is trying to <code>INSERT</code> into that table. Only rarely is it trying to delete. This is the opposite to what Eileen says here, i.e. that flushing the cache is more problematic than building: <a href="https://civicrm.org/blog/eileen/478-group-contact-cache-deadlocks-improvement" rel="nofollow noreferrer">https://civicrm.org/blog/eileen/478-group-contact-cache-deadlocks-improvement</a></p>
  
  <p>Users have reports on their dashboards that rely on smart groups for their filters. For this reason I set the smart group cache timeout to 0, so that   when, for example, someone completes an activity (and the target contact goes into a different smart group as a result) the resulting change is immediately visible. Otherwise users will worry that the change they have made has "not saved" or "not worked".</p>
  
  <p>I followed Eileen;s advice (in the blog post I link to above) and turned off opportunistic caching. But I found I did not have to run the cache flush as a scheduled job either - everything seems to work without it, presumably because the cache timeout is zero. </p>
  
  <p>It has not helped with the deadlocks, though.</p>
  
  <p>Is a timeout of 0 simply not supported?</p>
  
  <p>I think I would be able to deal with this much better if I understood how  the ACL cache relates to the smart group cache. Does the smart group timeout affect both? When does the ACL cache get flushed / filled? Do I have control over that? If someone could explain this paragraph from Eileen's blog it would help: </p>
  
  <blockquote>
    <p>If your smart group cache time out is every 5 minutes and your cron
    only runs every 10 minutes your cache will be 15 minutes old most of
    the time. I would expect you would want to run the cron every minute
    (&amp; possibly reduce your time out by a minute).</p>
  </blockquote>
  
  <p>I am confused about two aspects of this:
  1. She is talking about a cron job that flushes the caches, but that paragraph seems to relate to building them
  2. It makes sense that the ACL cache build will depend on the smart group cache. So yes, if the smart group cache is old, and the ACL cache gets built, it will have old data in it. But that assumes that the cron setting is about the ACL cache, not the smart groups. Yet the setting that turns off the opportunistic caching pertains to smart groups, and it is that setting that she says we can replace with the cron.</p>
  
  <p>How can there be a difference between when the cache is flushed and when it is built, anyway? Surely refilling the cache means first you flush it, then you fill it. They cannot be separate</p>
  
  <p>And for one last extremely confusing thing: I turned off the cron job. Why is the ACL cache still trying to update?</p>
  
Tags: <drupal><4.7><smartgroups><acl><caching>
Title: Can someone help me understand smart group caching so I can prevent these deadlocks?
ThreadText: # Can someone help me understand smart group caching so I can prevent these deadlocks?
  
  (Asked by: username_0 on 2017-10-02)
  
  I know that the Civi devs have been struggling with this issue so I am not complaining or asking for it to be fixed - I just want to understand how it works, so that I can work aroud it.
  
  I am constantly getting complaints from users that data is missing from their dashboard, and when I look in the logs it is invariably due to deadlocks in `civicrm_acl_contact_cache`. The logs show that these deadlocks happen when the system is trying to `INSERT` into that table. Only rarely is it trying to delete. This is the opposite to what Eileen says here, i.e. that flushing the cache is more problematic than building: <https://civicrm.org/blog/eileen/478-group-contact-cache-deadlocks-improvement>
  
  Users have reports on their dashboards that rely on smart groups for their filters. For this reason I set the smart group cache timeout to 0, so that when, for example, someone completes an activity (and the target contact goes into a different smart group as a result) the resulting change is immediately visible. Otherwise users will worry that the change they have made has "not saved" or "not worked".
  
  I followed Eileen;s advice (in the blog post I link to above) and turned off opportunistic caching. But I found I did not have to run the cache flush as a scheduled job either - everything seems to work without it, presumably because the cache timeout is zero.
  
  It has not helped with the deadlocks, though.
  
  Is a timeout of 0 simply not supported?
  
  I think I would be able to deal with this much better if I understood how the ACL cache relates to the smart group cache. Does the smart group timeout affect both? When does the ACL cache get flushed / filled? Do I have control over that? If someone could explain this paragraph from Eileen's blog it would help:
  
  > If your smart group cache time out is every 5 minutes and your cron
  > only runs every 10 minutes your cache will be 15 minutes old most of
  > the time. I would expect you would want to run the cron every minute
  > (& possibly reduce your time out by a minute).
  
  I am confused about two aspects of this:
  1. She is talking about a cron job that flushes the caches, but that paragraph seems to relate to building them
  2. It makes sense that the ACL cache build will depend on the smart group cache. So yes, if the smart group cache is old, and the ACL cache gets built, it will have old data in it. But that assumes that the cron setting is about the ACL cache, not the smart groups. Yet the setting that turns off the opportunistic caching pertains to smart groups, and it is that setting that she says we can replace with the cron.
  
  How can there be a difference between when the cache is flushed and when it is built, anyway? Surely refilling the cache means first you flush it, then you fill it. They cannot be separate
  
  And for one last extremely confusing thing: I turned off the cron job. Why is the ACL cache still trying to update?
  
  **Tags:** drupal, 4.7, smartgroups, acl, caching
  
  
  ---
  
  ## 4 answers
  
  ### Answer by username_0 on 2018-07-10. Score: 1 | Accepted answer
  
  For me, the only way to deal with this problem was to remove as many smart groups as I could from the system. In the process I also rewrote all the dashlet reports I could into Drupal views. There are a few dashlets left that are CiviCRM reports and still rely on smart group. I believe that people who use these reports still have problems with data "disappearing" which are the effect of deadlocks. But they are so used to it that they don't bother me about it any more!
  
  
  ---
  
  ### Answer by username_1 on 2017-10-09. Score: 1
  
  I cant answer everything but I think I can help clarify a few things;
  
  > How can there be a difference between when the cache is flushed and when it is built, anyway? Surely refilling the cache means first you flush it, then you fill it. They cannot be separate
  
  The answer to this lies in the state of the cache, there are in fact two states;
  
  1. Current - does not need clearing or rebuilding
  2. Invalid - this is either empty (as an operation flushed it or expired due to time/cron)
  
  Important bit of information is that an action may chose to flush the cache without rebuilding it - as its not needed by that action but the action would affect it. A good example is a new contact being created - the ACL may be affected but in the create action the cache only needs to be flushed so that the ACL action can then build it.
  
  > And for one last extremely confusing thing: I turned off the cron job. Why is the ACL cache still trying to update?
  
  All caching has a expiration time, regardless of the cron if that time expires the next action will try to rebuild the cache. The idea of the cron is to carry out that action offline therefore reducing the time taken on the user action.
  
  I'd like to work through the issue with you so please do keep this conversation going - we have some large customers running ACL without major cache issues so it should be a good case to establish exactly whats going wrong.
  
  Perhaps you could share some numbers i.e. How many ACL groups/rules, smart groups, size of contacts etc. All of these factors will play in important part of the diagnosis.
  
  > **Comments:**
  > **username_0**: Thank you, this is helpful. From this, it seems that that I did the right thing in turning off the cron job, as there is no point having it given that the cache is set to 0 . Is that correct? I will get back to you with the information you requested, in the next few days. I would love it if we could get to the bottom of this!  
  > **username_0**: Amount of contacts: 7333 Amount of smart groups: 42 Amount of ACL groups: 9  
  > **username_0**: Sorry, amount of ACL groups is 22  
  > **username_0**: Amount of ACL roles: 30. Amount of ACLs : 175 (seems excessive, but that's the result of "select count(*) from civicrm_acl where is_active = 1"). Amount of said ACLs whose object is a smart group: 10. My next test will be to rejig things so that no ACLs rely on smart groups at all, and then see if this resolves the issue.  
  > **username_1**: I think thats the right approach, smart groups are heavy on cache refreshes especially if constantly being required for ACLs  
  > **username_0**: I've changed all the ACL target groups to normal groups . None use smart groups at all. But we are still getting the deadlocks. It seems that the ACL cache is getting rebuilt every time the smart groups change, even though there is no need whatsoever for that to happen any more  
  > **username_0**: The only way I am managing to deal with this is to replace all the dashlets with Drupal Views. They work much better than CiviCRM report dashlets, but I don't like the security implications of giving so much access to the CiviCRM website to the Drupal user. It's not suitable for our setup at all because we have a public facing website with very confidential data in Civi. I am rather upset about this  
  
  ---
  
  ### Answer by username_2 on 2017-10-09. Score: 1
  
  We have a very similar setup, with the smart group cache timeout set to 0. We also get a lot of deadlocks, but ours are with civicrm\_group\_contact\_cache. They're a mixture of delete, insert, insert ignore, and create temporary table.
  
  Like you I can't quite understand what's happening, or why it results in a deadlock (why doesn't it just queue?). And I'm very interested in seeing other answers here! But I've recently had some luck hacking around the problem by adding some try...catch loops around the SQL queries in question. On ours it now waits a second, then retries the query - and does this up to 10 times. I expect proper programmers will react in horror, but nonetheless this has solved 99% of our deadlocks.
  
  So the original line in Contact/BAO/GroupContactCache.php was:
  
  ```
  CRM_Core_DAO::executeQuery($query, $params);
  
  ```
  
  and we made it into:
  
  ```
      $attempts = 0;
      while ($attempts < 10) {
          try {
              CRM_Core_DAO::executeQuery($query, $params);
          } catch ( \Exception $e ) {
              if ( $attempts < 10 ) {
                  sleep( 1 );
                  $attempts += 1;
                  continue;
              } else {
                  throw $e;
              }
          }
          break;
      }
  
  ```
  
  I had to do this with 4 queries in Contact/BAO/GroupContactCache.php. You need at least PHP5.5, I think.
  
  I put in some logging, and I can see that it almost always works on the second try.
  
  It's not elegant, and obviously the actual problem is elsewhere. But at least it bypasses it while we figure out the underlying issue. Maybe something similar would work for the ACL cache.
  
  > **Comments:**
  > **username_0**: Thanks, I have put this in, and will see how it goes. All proper programmers have duct tape in their first aid kit :)  
  > **username_0**: I think I must be going crazy, but this code is not catching exceptions for me! The exception is being thrown anyway, immediately rather than after 10 seconds. And I put a logging statement right at the top of the catch block, which is never reached. Most confusing  
  > **username_2**: Is it definitely \Exception and not just Exception? I had the same issue till I added the backslash.  
  > **username_0**: Yes, it's exactly as you have it.  
  > **username_0**: What version of Civi are you on? I'm on 4.7.24  
  > **username_2**: That's so odd! We're 4.7.17 so a little behind you.  
  
  ---
  
  ### Answer by username_3 on 2017-11-30. Score: 0
  
  We have over 60,000 contacts and dozens of smart groups and were seeing a MySQL deadlock with smart group cache processes. I changed the CiviCRM cronjob to run every 30 minutes (previously and for other reasons) and edited the scheduled job in CiviCRM for smart group caching so that the parameter of the job is `limit=20` which allows only 20 smart groups to be cached per cron run. After killing the last batch of stuck processes in MySQL I haven't seen this problem pop up.
  
  > **Comments:**
  > **username_0**: Thanks but, if I understand you correctly, this would mean smart groups don't immediately update? I need them to update immediately (see my question)  
  > **username_3**: It will cache the mailing group when you compose the mailing and add them to the recipients.  
  
  ---
  
  