CiviCRM Community Forums (archive)

I have a mailing list group. It is currently not a smart group. I need to migrate everyone in that group (who has not unsubscribed) into a new smart group. However, I can't seem to do a search for just the subscribers.

For example, if I do an advanced search on that group, I get 2,719 results. If I go to CiviMail and do a new  mailing to that group, it says it will go out to 2,172 people. I assume those 600 or so extra results in the advanced search are the people who have unsubscribed. How can I find them and/or remove them from the group?

I did this, but am still confused because the numbers are still incorrect. So then I figured I would just try to make a new smart group from the group members and see if the right number come out when i create a mailing for them (thinking maybe it's do not call prefs, etc, that make the number less).

So I did this, created new smart group from members. Then I went to new mailing, chose the new smart group to send to, and I get the following error after trying to get to the second page of the wizard:
     Sorry. A non-recoverable error has occurred.

    DB Error: a515ac9c2796ca0e23adbe92c68fc9fc

    Database Error Code: Lost connection to MySQL server during query, 2013

    Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => INSERT IGNORE INTO  I_1 (email_id, contact_id)
                    SELECT DISTINCT     civicrm_email.id as email_id,
                                        contact_a.id as contact_id
                     FROM civicrm_contact contact_a LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)   LEFT JOIN civicrm_group_contact `civicrm_group_contact-44` ON contact_a.id = `civicrm_group_contact-44`.contact_id 
                    LEFT JOIN           X_1
                            ON          contact_a.id = X_1.contact_id
                    WHERE           
                                        contact_a.do_not_email = 0
                        AND             contact_a.is_opt_out = 0
                        AND             contact_a.is_deceased = 0
                        AND             (civicrm_email.is_bulkmail = 1 OR civicrm_email.is_primary = 1)
                        AND             civicrm_email.on_hold = 0
                                         AND ( `civicrm_group_contact-44`.group_id IN ( 44 ) AND `civicrm_group_contact-44`.status IN ("Added") )
                        AND             contact_a.id NOT IN (
                                          SELECT contact_id FROM civicrm_group_contact
                                          WHERE civicrm_group_contact.group_id = 92 AND civicrm_group_contact.status = 'Removed')
                        AND             X_1.contact_id IS null
                    ORDER BY civicrm_email.is_bulkmail [nativecode=2013 ** Lost connection to MySQL server during query]
    [type] => DB_Error
    [user_info] => INSERT IGNORE INTO  I_1 (email_id, contact_id)
                    SELECT DISTINCT     civicrm_email.id as email_id,
                                        contact_a.id as contact_id
                     FROM civicrm_contact contact_a LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)   LEFT JOIN civicrm_group_contact `civicrm_group_contact-44` ON contact_a.id = `civicrm_group_contact-44`.contact_id 
                    LEFT JOIN           X_1
                            ON          contact_a.id = X_1.contact_id
                    WHERE           
                                        contact_a.do_not_email = 0
                        AND             contact_a.is_opt_out = 0
                        AND             contact_a.is_deceased = 0
                        AND             (civicrm_email.is_bulkmail = 1 OR civicrm_email.is_primary = 1)
                        AND             civicrm_email.on_hold = 0
                                         AND ( `civicrm_group_contact-44`.group_id IN ( 44 ) AND `civicrm_group_contact-44`.status IN ("Added") )
                        AND             contact_a.id NOT IN (
                                          SELECT contact_id FROM civicrm_group_contact
                                          WHERE civicrm_group_contact.group_id = 92 AND civicrm_group_contact.status = 'Removed')
                        AND             X_1.contact_id IS null
                    ORDER BY civicrm_email.is_bulkmail [nativecode=2013 ** Lost connection to MySQL server during query]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT IGNORE INTO  I_1 (email_id, contact_id)
                    SELECT DISTINCT     civicrm_email.id as email_id,
                                        contact_a.id as contact_id
                     FROM civicrm_contact contact_a LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)   LEFT JOIN civicrm_group_contact `civicrm_group_contact-44` ON contact_a.id = `civicrm_group_contact-44`.contact_id 
                    LEFT JOIN           X_1
                            ON          contact_a.id = X_1.contact_id
                    WHERE           
                                        contact_a.do_not_email = 0
                        AND             contact_a.is_opt_out = 0
                        AND             contact_a.is_deceased = 0
                        AND             (civicrm_email.is_bulkmail = 1 OR civicrm_email.is_primary = 1)
                        AND             civicrm_email.on_hold = 0
                                         AND ( `civicrm_group_contact-44`.group_id IN ( 44 ) AND `civicrm_group_contact-44`.status IN ("Added") )
                        AND             contact_a.id NOT IN (
                                          SELECT contact_id FROM civicrm_group_contact
                                          WHERE civicrm_group_contact.group_id = 92 AND civicrm_group_contact.status = 'Removed')
                        AND             X_1.contact_id IS null
                    ORDER BY civicrm_email.is_bulkmail [nativecode=2013 ** Lost connection to MySQL server during query]"]


OK something else is going on here. Here's what I've done:

- Do search for ALL my contacts (total: 5500)
- Add all these contacts to 1) smart group w/ mailing list 2) non-smart group w/ mailing list
- Create a new mailing and choose to send it to the non-smart group with 5500 members. Works great, get to 2nd page of mailing wizard
- Create a new mailing and choose to send it to the smart group with 5500 members. After submitting first screen of wizard I get the above error every time.

My server can handle load query for 5500 people as you can see from the non-smart group. What is different about the smart group query that it can't handle?

I also tested sending to smaller smart groups. It does it to 200 contacts just fine.

I am on Civi 2.2 at present and demo is 3.0 so I can't test this there until I upgrade (soon, I hope). Plus the demo only has 155 contacts in it.

the server i'm running it on:
- dedicated Fedora Core only for Drupal / CiviCRM install (no one else shares it)
- 2x Intel Dual-Core Xeon 3.0Ghz CPU, 4MB L2 Cache, 1.3Ghz FSB, 4GB Corsair RAM, DDR2 667
- PHP mem limit 900MB

That thing has way more horsepower than my mac....if this server can't run it then it doesn't make any sense to me.

If I watch top while I try to do new mailing to smart group and hit 'next' button, the %CPU hits 3 and %mem hits 1.9 on mysql.

it's 2.2, but we're upgrading in a couple weeks. if it's easier for you, i can hold off on this until then. i'll assume yes.

Just one more thing. I turned on mysql logging and tracked down the query in the log that mysql is trying to run. The issue is, I can't really run it directly in mysql because it's referencing temporary tables that previous queries are creating (and thus failing because it can't find the table). So in the log I tracked it all way back to where the temp tables are created. It's many queries in between that and the one that the server is erroring out on. I don't know if this helps, but I'm pasting these queries here from the log. Is there any way to pull these together into the relevant query? The one it crashes on is the very last query.

41721 Query       CREATE TEMPORARY TABLE I_1
            (email_id int, contact_id int primary key)
            ENGINE=HEAP
  41721 Init DB     cctvcivicrm
  41721 Query       REPLACE INTO       I_1 (email_id, contact_id)
                    SELECT DISTINCT     civicrm_email.id as email_id,
                                        civicrm_contact.id as contact_id
                    FROM                civicrm_email
                    INNER JOIN          civicrm_contact
                            ON          civicrm_email.contact_id = civicrm_contact.id
                    INNER JOIN          civicrm_group_contact
                            ON          civicrm_contact.id = civicrm_group_contact.contact_id
                    INNER JOIN          civicrm_mailing_group
                            ON          civicrm_group_contact.group_id = civicrm_mailing_group.entity_id
                                AND     civicrm_mailing_group.entity_table = 'civicrm_group'
                    LEFT JOIN           X_1
                            ON          civicrm_contact.id = X_1.contact_id
                    WHERE           
                                       (civicrm_mailing_group.group_type = 'Include' OR civicrm_mailing_group.group_type = 'Base')
                        AND             civicrm_mailing_group.search_id IS NULL
                        AND             civicrm_group_contact.status = 'Added'
                        AND             civicrm_group_contact.email_id IS null
                        AND             civicrm_contact.do_not_email = 0
                        AND             civicrm_contact.is_opt_out = 0
                        AND             civicrm_contact.is_deceased = 0
                        AND            (civicrm_email.is_bulkmail = 1 OR civicrm_email.is_primary = 1)
                        AND             civicrm_email.on_hold = 0
                        AND             civicrm_mailing_group.mailing_id = 334
                        AND             X_1.contact_id IS null
                    ORDER BY civicrm_email.is_bulkmail
  41721 Init DB     cctvcivicrm
  41721 Query       REPLACE INTO       I_1 (email_id, contact_id)
                    SELECT DISTINCT     civicrm_email.id as email_id,
                                        civicrm_contact.id as contact_id
                    FROM                civicrm_email
                    INNER JOIN          civicrm_contact
                            ON          civicrm_email.contact_id = civicrm_contact.id
                    INNER JOIN          civicrm_mailing_event_queue
                            ON          civicrm_mailing_event_queue.contact_id = civicrm_contact.id
                    INNER JOIN          civicrm_mailing_job
                            ON          civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
                    INNER JOIN          civicrm_mailing_group
                            ON          civicrm_mailing_job.mailing_id = civicrm_mailing_group.entity_id AND civicrm_mailing_group.entity_table = 'civicrm_mailing'
                    LEFT JOIN           X_1
                            ON          civicrm_contact.id = X_1.contact_id
                    WHERE
                                       (civicrm_mailing_group.group_type = 'Include' OR civicrm_mailing_group.group_type = 'Base')
                        AND             civicrm_contact.do_not_email = 0
                        AND             civicrm_contact.is_opt_out = 0
                        AND             civicrm_contact.is_deceased = 0
                        AND            (civicrm_email.is_bulkmail = 1 OR civicrm_email.is_primary = 1)
                        AND             civicrm_email.on_hold = 0
                        AND             civicrm_mailing_group.mailing_id = 334
                        AND             X_1.contact_id IS null
                    ORDER BY civicrm_email.is_bulkmail
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT          civicrm_group.saved_search_id as saved_search_id,
                                    civicrm_group.id as id
                    FROM            civicrm_group
                    INNER JOIN      civicrm_mailing_group
                            ON      civicrm_mailing_group.entity_id = civicrm_group.id
                                AND civicrm_mailing_group.entity_table = 'civicrm_group'
                    WHERE               
                                    civicrm_mailing_group.group_type = 'Include'
                        AND         civicrm_mailing_group.search_id IS NULL
                        AND         civicrm_mailing_group.mailing_id = 334
                        AND         civicrm_group.saved_search_id IS NOT null
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT  id, form_values 
 FROM civicrm_saved_search
 
 WHERE (  civicrm_saved_search.id = 40 )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT  id, mapping_id 
 FROM civicrm_saved_search
 
 WHERE (  civicrm_saved_search.id = 40 )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT  id, form_values 
 FROM civicrm_saved_search
 
 WHERE (  civicrm_saved_search.id = 40 )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT  id, mapping_id 
 FROM civicrm_saved_search
 
 WHERE (  civicrm_saved_search.id = 40 )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_component
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_cache
 
 WHERE (  civicrm_cache.group_name  = 'contact fields' )  AND (  civicrm_cache.path  = 'exportableFields All 1' )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_cache
 
 WHERE (  civicrm_cache.group_name  = 'contact fields' )  AND (  civicrm_cache.path  = 'importableFields Individual' )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_dedupe_rule_group
 
 WHERE (  civicrm_dedupe_rule_group.contact_type  = 'Individual' )  AND (  civicrm_dedupe_rule_group.level  = 'Strict' ) AND (  civicrm_dedupe_rule_group.is_default = 1 )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_dedupe_rule
 
 WHERE (  civicrm_dedupe_rule.dedupe_rule_group_id = 4 )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT  id, title 
 FROM civicrm_custom_group
 
 WHERE (  civicrm_custom_group.is_active = 1 ) 
 
 
 ORDER BY title
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_cache
 
 WHERE (  civicrm_cache.group_name  = 'contact fields' )  AND (  civicrm_cache.path  = 'custom importableFields Participant_0_0_0_0__0_ec8abbd2' )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_cache
 
 WHERE (  civicrm_cache.group_name  = 'contact fields' )  AND (  civicrm_cache.path  = 'custom importableFields Contribution_0_0_0_0__0_ec8abbd2' )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_dedupe_rule_group
 
 WHERE (  civicrm_dedupe_rule_group.contact_type  = 'Individual' )  AND (  civicrm_dedupe_rule_group.level  = 'Strict' ) AND (  civicrm_dedupe_rule_group.is_default = 1 )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_dedupe_rule
 
 WHERE (  civicrm_dedupe_rule.dedupe_rule_group_id = 4 )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_cache
 
 WHERE (  civicrm_cache.group_name  = 'contact fields' )  AND (  civicrm_cache.path  = 'custom importableFields Membership_0_0_0_0__0_ec8abbd2' )
  41721 Init DB     cctvcivicrm
  41721 Query       SELECT *
 FROM civicrm_cache
 
 WHERE (  civicrm_cache.group_name  = 'contact fields' )  AND (  civicrm_cache.path  = 'custom importableFields Pledge_0_0_0_0__0_ec8abbd2' )
  41721 Init DB     cctvcivicrm
  41721 Query       INSERT IGNORE INTO  I_1 (email_id, contact_id)
                    SELECT DISTINCT     civicrm_email.id as email_id,
                                        contact_a.id as contact_id
                     FROM civicrm_contact contact_a LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)
                    LEFT JOIN           X_1
                            ON          contact_a.id = X_1.contact_id
                    WHERE           
                                        contact_a.do_not_email = 0
                        AND             contact_a.is_opt_out = 0
                        AND             contact_a.is_deceased = 0
                        AND             (civicrm_email.is_bulkmail = 1 OR civicrm_email.is_primary = 1)
                        AND             civicrm_email.on_hold = 0
                                         AND ( contact_a.contact_type IN ('Individual','Organization') AND contact_a.do_not_email != 1 AND contact_a.do_not_mail != 1 )
                        AND             contact_a.id NOT IN (
                                          SELECT contact_id FROM civicrm_group_contact
                                          WHERE civicrm_group_contact.group_id = 95 AND civicrm_group_contact.status = 'Removed')
                        AND             X_1.contact_id IS null
                    ORDER BY civicrm_email.is_bulkmail

hey emily:

can u email me the db

thanx

lobo