TL:DR of entire project: 30-Year-old Charity migrates to CiviCRM from an archaic bespoke legacy database. Although we were warned that Drupal 7 is the preferable CMS for Civi, we decide on WordPress for staff skill and market longevity reasons. Documenting our journey and any challenges, watchpoints and lessons learnt. Solution uses CiviCRM, WordPress, memberships with portal, donations, GoCardless, Stripe, Mosaico, SparkPost & GiftAid. You can follow our progress at https://medium.com/@simonjohnparker
When I added our Membership Newsletter, Smart Group, it only showed 199 recipients to send to:
We have 812 members, 650 or so have email addresses:
The same thing was happening for smart groups and groups, so it led me to believe it could be issue with the contacts rather than the groups.
With the help of the community, I tried:
- Disabling the GDPR plugin to ensure no suppressions were happening from those who hadn’t opted in
- Checking if any contacts had ‘bounced’
- Checking if any contacts had ‘Email On Hold’
A special thanks to eileen, mikeymjco and babu on the civicrm.org Mattermost who helped me with the above process of elimination.
Listing a few helpful links that also alluded to the above also:
Unfortunately, these didn’t solve the issue. A bit more research led me to find this:
"I finally gave up and forwarded this our developer who found that indeed, the issue was the is_deceased flag which had become NULL of their own accord!
It could be noted that manually setting all the flags via the GUI does not resolve this issue.
It would be interesting at some point to work out whether there was a specific action on the website that caused these flags to become NULL vice 0."
I went into our PHPMyAdmin and looked for the contact is_deceased column and low and behold:
The correct values should be: 0 = is not deceased and 1 = deceased
I did an advanced search in CiviCRM just to test:
Out of 900 contacts, apparently only 359 were not deceased!
Out of 900 contacts, 4 were deceased!
Clearly, that isn’t adding up.
mikeymjco helped me further to run the correct DB queries to correct those NULL fields.
SELECT is_deceased, id FROM civicrm_contact WHERE is_deceased IS NULL;
for me, that returned 537 rows.
Moment of truth. I then ran the update command:
UPDATE civicrm_contact SET is_deceased = 0 WHERE is_deceased IS NULL;
I returned to CiviCRM and Mosaico mailing and…
This was a real odd one. I can only think the way we’ve imported contacts may have caused this but I guess I’ll never know for sure.
Hope this helps others with this issue.
Thanks for the write up!
Another related issue, aimed more improving the journey for this type of problem: https://lab.civicrm.org/dev/mail/issues/51
If you hit this there is an anomaly in your database.
In 4.7 the following ran
-- CRM-17147 People with empty deceased-flag ('is null') get removed from recipient list of a mailing UPDATE civicrm_contact SET is_deceased = 0 WHERE is_deceased IS NULL; ALTER TABLE civicrm_contact ALTER COLUMN is_deceased SET DEFAULT 0;
New installs should have a civicrm_contact table that looks like
There has been a suggestion that importing with a column 'is deceased' but no data in that column would set it to null. My understanding is that this is no longer the case but I have not tested