Body: <p>First time writing a question on a forum, hope i don't oversee any rule from the code of conduct :)</p>
  <p>I am trying to send a test mail to a smart group(creating a massmail based on an advanced search result) from which I am excluding contacts that are in a static group.
  The advances search criterias are:</p>
  <ul>
  <li>Belonging to a static group</li>
  <li>having a specific relationship type</li>
  <li>Specific age range</li>
  </ul>
  <p>When i try to send a test mail, I get this error message &quot;Error in call to Mailing_send_test : Error in call to MailingJob_create : DB Error: already exists&quot;.
  This workflow (excluding contacts from a search result) is a regular use case in my organisation, which makes me think that it is maybe due to the contacts in both these groups (the smart group and the exclude group). Trying to exlude another set of contacts from the smart group and sending a test mail works totally fine, and confirms then my previous thought, I guess.</p>
  <p>Here is the error log message:</p>
  <pre><code>[info] $Fatal Error Details = Array
  (
      [callback] =&gt; Array
          (
              [0] =&gt; CRM_Core_Error
              [1] =&gt; exceptionHandler
          )
  [code] =&gt; -5
  [message] =&gt; DB Error: already exists
  [mode] =&gt; 16
  [debug_info] =&gt; INSERT INTO        X_34108 (contact_id)
                  SELECT  DISTINCT    civicrm_group_contact.contact_id
                  FROM                civicrm_group_contact
                  INNER JOIN          civicrm_mailing_group
                          ON          civicrm_group_contact.group_id = civicrm_mailing_group.entity_id AND civicrm_mailing_group.entity_table = 'civicrm_group'
                  WHERE
                                      civicrm_mailing_group.mailing_id = 4140
                      AND             civicrm_group_contact.status = 'Removed'
                      AND             civicrm_mailing_group.group_type = 'Base' [nativecode=1062 ** Duplicate entry '367141' for key 'PRIMARY']
  [type] =&gt; DB_Error
  </code></pre>
  <p>It seems to me like CiviMail is writing the contact IDs from the exclude table into a temporary table, which is creating a conflict of duplicate IDs. I could proof this by removing the contact with the ID in the log from the exclude group, and then the next error would be for another contact/CiviCRM ID.</p>
  <p>Trying with another group didn't help. Clearing the cache (advice from other articles) also didn't help.</p>
  <p>Thanks for reading and for any thoughts :)</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-12T14:35:10.827Z
Comments.Text: Can you send the mailing itself? Is the problem sending the test mail specifically or both sending the test mail and the mailing?
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-13T08:48:00.497Z
Comments.Text: I unfortunately can not test that in the real prod use case.
  But when reproducing a similar scenario, i could not send the mail and get as well the error "DB Error: already exists".
  Additionally, i just noticed that when clicking on the link that gives an estimation of the number of recipients, i get the error "Error in call to MailingJob_create : DB Error: already exists"
Comments.UserDisplayName: siraj
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-13T14:26:40.470Z
Comments.Text: Makes sense that it would be in all cases.
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-13T14:28:07.863Z
Comments.Text: Can you check your database to see if you do have more than one row for that contact_id with the same group_id and status=Removed in civicrm_group_contact?
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-14T12:57:04.627Z
Comments.Text: There is only one row for each group_id.
Comments.UserDisplayName: siraj
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-14T14:22:57.713Z
Comments.Text: And what's the unsubscribe group? That's what it is tripping up on, adding members of the unsub group to a temp table.
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-14T16:09:26.353Z
Comments.Text: It's a static group. 
  
  For now I could only suspect the size of group as a cause of the problem(which does not sound plausible to me). 
  
  I am having this thought, because when trying to use other static groups that have less contacts and then CiviMail seems to work normally (sending of test mails, estimation of contacts & sending of the mass mail). On the other hand, with other groups that have a high number of contacts, I get the same error.
Comments.UserDisplayName: siraj
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-14T16:36:10.513Z
Comments.Text: Is there some relationship between the unsubscribe group and the exclude group?
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-19T15:14:26.553Z
Comments.Text: If by relationship you mean that one group is a parent-group to the other one, or that one group is a dynamic group based on the other one, then no.
  I would also mention that the advanced search has as criteria, that the contacts belong to the same group that is set as unsubscribe group. But that shouldn't be a problem.
Comments.UserDisplayName: siraj
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-19T15:34:54.357Z
Comments.Text: I suspect that is the problem (though I agree that it shouldn't be a problem, the error you posted points to it). Can you try it exactly the same but without including the unsubscribe group in the advanced search?
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-19T16:03:07.060Z
Comments.Text: I just tried and I get the same error.
  What I also tried is removing the unsubscribe group from the advanced search criterias as well as removing the exclude group, and in this case I also get a warning that I find weird: I can see that there is 109 potential reicipients(in the estimation field) but when trying to send a test mail, I get the warning message "Could not identify any recipients. Perhaps the group is empty?" and no test mail is sent.
Comments.UserDisplayName: siraj
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-19T17:10:16.007Z
Comments.Text: OK, try just removing the unsub group from the mailing, switch it with a empty group.
  It is weird that your error says INSERT INTO when it should say INSERT IGNORE INTO. What version are you using?
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-20T09:25:00.240Z
Comments.Text: I get the same error with an empty group.  
  I am using version 4.7.
Comments.UserDisplayName: siraj
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-20T13:20:21.723Z
Comments.Text: That's more than six years old! Time to upgrade.
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-20T13:50:49.847Z
Comments.Text: This problem was fixed four years ago.
Comments.UserDisplayName: Lars SG
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-20T14:14:02.923Z
Comments.Text: Yeah I know, it's due to many circumstances, resources and decisions that we are still using this version. But we are in parallel working on the update and it's just a matter of time until we're up-to-date!
Comments.UserDisplayName: siraj
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-07-20T14:14:43.857Z
Comments.Text: Thanks so much for your support and patience! I hope it doesn't feel like it was a waste of time. I guess it was a beginner's error not to mention the CiviCRM version already from the start..
Comments.UserDisplayName: siraj
Tags: <civimail><advanced-search>
Title: Error when sending a test mail to a smart group with an exclude group
ThreadText: # Error when sending a test mail to a smart group with an exclude group
  
  (Asked by: username_0 on 2023-07-12)
  
  First time writing a question on a forum, hope i don't oversee any rule from the code of conduct :)
  
  I am trying to send a test mail to a smart group(creating a massmail based on an advanced search result) from which I am excluding contacts that are in a static group.
  The advances search criterias are:
  
  * Belonging to a static group
  * having a specific relationship type
  * Specific age range
  
  When i try to send a test mail, I get this error message "Error in call to Mailing\_send\_test : Error in call to MailingJob\_create : DB Error: already exists".
  This workflow (excluding contacts from a search result) is a regular use case in my organisation, which makes me think that it is maybe due to the contacts in both these groups (the smart group and the exclude group). Trying to exlude another set of contacts from the smart group and sending a test mail works totally fine, and confirms then my previous thought, I guess.
  
  Here is the error log message:
  
  ```
  [info] $Fatal Error Details = Array
  (
      [callback] => Array
          (
              [0] => CRM_Core_Error
              [1] => exceptionHandler
          )
  [code] => -5
  [message] => DB Error: already exists
  [mode] => 16
  [debug_info] => INSERT INTO        X_34108 (contact_id)
                  SELECT  DISTINCT    civicrm_group_contact.contact_id
                  FROM                civicrm_group_contact
                  INNER JOIN          civicrm_mailing_group
                          ON          civicrm_group_contact.group_id = civicrm_mailing_group.entity_id AND civicrm_mailing_group.entity_table = 'civicrm_group'
                  WHERE
                                      civicrm_mailing_group.mailing_id = 4140
                      AND             civicrm_group_contact.status = 'Removed'
                      AND             civicrm_mailing_group.group_type = 'Base' [nativecode=1062 ** Duplicate entry '367141' for key 'PRIMARY']
  [type] => DB_Error
  
  ```
  
  It seems to me like CiviMail is writing the contact IDs from the exclude table into a temporary table, which is creating a conflict of duplicate IDs. I could proof this by removing the contact with the ID in the log from the exclude group, and then the next error would be for another contact/CiviCRM ID.
  
  Trying with another group didn't help. Clearing the cache (advice from other articles) also didn't help.
  
  Thanks for reading and for any thoughts :)
  
  **Tags:** civimail, advanced-search
  
  > **Comments:**
  > **username_1**: Can you send the mailing itself? Is the problem sending the test mail specifically or both sending the test mail and the mailing?  
  > **username_0**: I unfortunately can not test that in the real prod use case. But when reproducing a similar scenario, i could not send the mail and get as well the error "DB Error: already exists". Additionally, i just noticed that when clicking on the link that gives an estimation of the number of recipients, i get the error "Error in call to MailingJob_create : DB Error: already exists"  
  > **username_1**: Makes sense that it would be in all cases.  
  > **username_1**: Can you check your database to see if you do have more than one row for that contact_id with the same group_id and status=Removed in civicrm_group_contact?  
  > **username_0**: There is only one row for each group_id.  
  > **username_1**: And what's the unsubscribe group? That's what it is tripping up on, adding members of the unsub group to a temp table.  
  > **username_0**: It's a static group.   For now I could only suspect the size of group as a cause of the problem(which does not sound plausible to me).   I am having this thought, because when trying to use other static groups that have less contacts and then CiviMail seems to work normally (sending of test mails, estimation of contacts & sending of the mass mail). On the other hand, with other groups that have a high number of contacts, I get the same error.  
  > **username_1**: Is there some relationship between the unsubscribe group and the exclude group?  
  > **username_0**: If by relationship you mean that one group is a parent-group to the other one, or that one group is a dynamic group based on the other one, then no. I would also mention that the advanced search has as criteria, that the contacts belong to the same group that is set as unsubscribe group. But that shouldn't be a problem.  
  > **username_1**: I suspect that is the problem (though I agree that it shouldn't be a problem, the error you posted points to it). Can you try it exactly the same but without including the unsubscribe group in the advanced search?  
  > **username_0**: I just tried and I get the same error. What I also tried is removing the unsubscribe group from the advanced search criterias as well as removing the exclude group, and in this case I also get a warning that I find weird: I can see that there is 109 potential reicipients(in the estimation field) but when trying to send a test mail, I get the warning message "Could not identify any recipients. Perhaps the group is empty?" and no test mail is sent.  
  > **username_1**: OK, try just removing the unsub group from the mailing, switch it with a empty group. It is weird that your error says INSERT INTO when it should say INSERT IGNORE INTO. What version are you using?  
  > **username_0**: I get the same error with an empty group.   I am using version 4.7.  
  > **username_1**: That's more than six years old! Time to upgrade.  
  > **username_1**: This problem was fixed four years ago.  
  > **username_0**: Yeah I know, it's due to many circumstances, resources and decisions that we are still using this version. But we are in parallel working on the update and it's just a matter of time until we're up-to-date!  
  > **username_0**: Thanks so much for your support and patience! I hope it doesn't feel like it was a waste of time. I guess it was a beginner's error not to mention the CiviCRM version already from the start..  
  
  ---
  
  ## 0 answers
  
  