Answers.Body: <p><em>Warning: half an answer at best!</em></p>
  <blockquote>
  <pre><code>CONSTRAINT `FK_civicrm_acl_contact_cache_user_id` 
  FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact` (`id`) 
  ON DELETE CASCADE
  </code></pre>
  </blockquote>
  <p>This suggests that the user_id value doesn't exist in the contact table.</p>
  <p>And this makes sense because the user_id here is 0; and there will not be a contact with ID 0.</p>
  <p>But I don't understand when or why <code>user_id</code> 0 specified?</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-22T08:54:33.540Z
Answers.Text: Thanks for taking a look, that's a good point! I'll do some more digging
Answers.UserDisplayName: Rose Lanigan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-28T10:46:34.967Z
Answers.Text: (wonders if this has anything to do with it https://github.com/civicrm/civicrm-core/pull/26627)
Answers.UserDisplayName: artfulrobot
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-20T14:24:20.660Z
Answers.LastActivityDate: 2023-06-20T14:24:20.660Z
Answers.UserDisplayName: artfulrobot
Body: <p>We have a Drupal 9 site with approx 50 active users and some relatively complex ACLs (&gt;100 rules). For the vast majority of users, all works fine but we have a very small number of users who it fails for - one user in particular is affected almost daily and a handful of others are affected every couple of weeks or so. It's triggered when the user logs in, and they cannot see a particular group of contacts. To 'fix' it, a colleague has to remove them from the group and re-add them - and then the correct access is restored. However, it's intermittent and doesn't happen every time.</p>
  <p>We've got as far as identifying something in the error logs -</p>
  <pre><code>21267 (
  21268     [callback] =&gt; Array
  21269         (
  21270             [0] =&gt; CRM_Core_Error
  21271             [1] =&gt; exceptionHandler
  21272         )
  21273
  21274     [code] =&gt; -3
  21275     [message] =&gt; DB Error: constraint violation
  21276     [mode] =&gt; 16
  21277     [debug_info] =&gt; INSERT INTO civicrm_acl_contact_cache (user_id, contact_id, operation) SELECT 0 as user_id, contact_a.id as contact_id, 'View' as operation  FROM civicrm_contact contact_a WHERE     ( (
  21278             `contact_a`.id IN (
  21279                SELECT contact_id FROM civicrm_group_contact WHERE group_id IN (154) AND status = 'Added'
  21280
  21281              )
  21282           ) )  AND (contact_a.is_deleted = 0) GROUP BY contact_a.id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`d9`.`civicrm_acl_contact_cache`, CONSTRAINT `FK_civicrm_acl_contact_cache_us      er_id` FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
  21283     [type] =&gt; DB_Error
  21284     [user_info] =&gt; INSERT INTO civicrm_acl_contact_cache (user_id, contact_id, operation) SELECT 0 as user_id, contact_a.id as contact_id, 'View' as operation  FROM civicrm_contact contact_a WHERE     ( (
  21285             `contact_a`.id IN (
  21286                SELECT contact_id FROM civicrm_group_contact WHERE group_id IN (154) AND status = 'Added'
  21287
  21288              )
  21289           ) )  AND (contact_a.is_deleted = 0) GROUP BY contact_a.id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`d9`.`civicrm_acl_contact_cache`, CONSTRAINT `FK_civicrm_acl_contact_cache_us      er_id` FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
  21290     [to_string] =&gt; [db_error: message=&quot;DB Error: constraint violation&quot; code=-3 mode=callback callback=CRM_Core_Error::exceptionHandler prefix=&quot;&quot; info=&quot;INSERT INTO civicrm_acl_contact_cache (user_id, contact_id, operation) SELECT 0       as user_id, contact_a.id as contact_id, 'View' as operation  FROM civicrm_contact contact_a WHERE     ( (
  21291             `contact_a`.id IN (
  21292                SELECT contact_id FROM civicrm_group_contact WHERE group_id IN (154) AND status = 'Added'
  21293
  21294              )
  21295           ) )  AND (contact_a.is_deleted = 0) GROUP BY contact_a.id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`d9`.`civicrm_acl_contact_cache`, CONSTRAINT `FK_civicrm_acl_contact_cache_us      er_id` FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]&quot;]
  21296 )
  21297 
  </code></pre>
  <p>As well as <code>[debug] $API Request Authorization failed</code> at the same time. Can someone help me figure out where to look?</p>
  
Tags: <acl><debugging>
Title: Intermittent failure to grant correct ACLs
ThreadText: # Intermittent failure to grant correct ACLs
  
  (Asked by: username_0 on 2023-05-17)
  
  We have a Drupal 9 site with approx 50 active users and some relatively complex ACLs (>100 rules). For the vast majority of users, all works fine but we have a very small number of users who it fails for - one user in particular is affected almost daily and a handful of others are affected every couple of weeks or so. It's triggered when the user logs in, and they cannot see a particular group of contacts. To 'fix' it, a colleague has to remove them from the group and re-add them - and then the correct access is restored. However, it's intermittent and doesn't happen every time.
  
  We've got as far as identifying something in the error logs -
  
  ```
  21267 (
  21268     [callback] => Array
  21269         (
  21270             [0] => CRM_Core_Error
  21271             [1] => exceptionHandler
  21272         )
  21273
  21274     [code] => -3
  21275     [message] => DB Error: constraint violation
  21276     [mode] => 16
  21277     [debug_info] => INSERT INTO civicrm_acl_contact_cache (user_id, contact_id, operation) SELECT 0 as user_id, contact_a.id as contact_id, 'View' as operation  FROM civicrm_contact contact_a WHERE     ( (
  21278             `contact_a`.id IN (
  21279                SELECT contact_id FROM civicrm_group_contact WHERE group_id IN (154) AND status = 'Added'
  21280
  21281              )
  21282           ) )  AND (contact_a.is_deleted = 0) GROUP BY contact_a.id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`d9`.`civicrm_acl_contact_cache`, CONSTRAINT `FK_civicrm_acl_contact_cache_us      er_id` FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
  21283     [type] => DB_Error
  21284     [user_info] => INSERT INTO civicrm_acl_contact_cache (user_id, contact_id, operation) SELECT 0 as user_id, contact_a.id as contact_id, 'View' as operation  FROM civicrm_contact contact_a WHERE     ( (
  21285             `contact_a`.id IN (
  21286                SELECT contact_id FROM civicrm_group_contact WHERE group_id IN (154) AND status = 'Added'
  21287
  21288              )
  21289           ) )  AND (contact_a.is_deleted = 0) GROUP BY contact_a.id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`d9`.`civicrm_acl_contact_cache`, CONSTRAINT `FK_civicrm_acl_contact_cache_us      er_id` FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
  21290     [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="INSERT INTO civicrm_acl_contact_cache (user_id, contact_id, operation) SELECT 0       as user_id, contact_a.id as contact_id, 'View' as operation  FROM civicrm_contact contact_a WHERE     ( (
  21291             `contact_a`.id IN (
  21292                SELECT contact_id FROM civicrm_group_contact WHERE group_id IN (154) AND status = 'Added'
  21293
  21294              )
  21295           ) )  AND (contact_a.is_deleted = 0) GROUP BY contact_a.id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`d9`.`civicrm_acl_contact_cache`, CONSTRAINT `FK_civicrm_acl_contact_cache_us      er_id` FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]"]
  21296 )
  21297 
  
  ```
  
  As well as `[debug] $API Request Authorization failed` at the same time. Can someone help me figure out where to look?
  
  **Tags:** acl, debugging
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2023-06-20. Score: 1
  
  *Warning: half an answer at best!*
  
  > ```
  > CONSTRAINT `FK_civicrm_acl_contact_cache_user_id` 
  > FOREIGN KEY (`user_id`) REFERENCES `civicrm_contact` (`id`) 
  > ON DELETE CASCADE
  >
  > ```
  
  This suggests that the user\_id value doesn't exist in the contact table.
  
  And this makes sense because the user\_id here is 0; and there will not be a contact with ID 0.
  
  But I don't understand when or why `user_id` 0 specified?
  
  > **Comments:**
  > **username_0**: Thanks for taking a look, that's a good point! I'll do some more digging  
  > **username_1**: (wonders if this has anything to do with it https://github.com/civicrm/civicrm-core/pull/26627)  
  
  ---
  
  