CiviCRM Community Forums (archive)

Hi,

I have an old civicrm that started in 2008 was upgraded up to 4.2 At one point, something did happen or someone did something, and now about 1/2 of the contacts don't have any record into civicrm_log (even the creation record).

Looking at the logs, I have still some records from 2008 and records from all years.

select year(modified_date),count(*) from civicrm_log group by year(modified_date);
+---------------------+----------+
| year(modified_date) | count(*) |
+---------------------+----------+
|                2008 |      291 |
|                2009 |     9326 |
|                2010 |     9099 |
|                2011 |    11438 |
|                2012 |     6016 |
|                2013 |      336 |
+---------------------+----------+

I've checked which contacts are without log, didn't notice a pattern, it's a mix of individual and organisations, recent and old contacts

select c.id, c.display_name from civicrm_contact as c where not exists (select * from civicrm_log where c.id=civicrm_log.entity_id and entity_table= "civicrm_contact");
I did run that query on several databases on several servers. It seems to be a common issue shared, but only on old install that have been upgraded.

It seems that "best groomed" databases (where users merge regularly contacts for instance) are more affected than others, comparatively to the size of their databases.

Could you try running the above query on your database, see if you are affected as well?

More importantly: is this a known bug and is this fixed already?

We discovered the issue as part of a spring cleaning, any suggestion to try to identify when a contact that has lost the log record has been changed last?

Hi,

moving the log to the trigger level seems to be the good approach indeed. Will put that on my "to dig more" pile for the next sprint.

Still on my quest, the query  below gives a list of when there is a group subcription (or removal), but nothing in the log, so far using the subscription history as a proxy to the log is the best I could find.

select distinct c.id,c.sort_name from civicrm_contact as c left join civicrm_subscription_history on contact_id= c.id where group_id != 344 and date >= date("2011-01-01") and not exists (select * from civicrm_log where c.id=civicrm_log.entity_id and entity_table= "civicrm_contact");