CiviCRM Community Forums (archive)

This is really worrying - it seems that for custom field groups we're missing some entries, even at the more recent end. Has anyone encountered this before?

mysql> select count(distinct(entity_id)) from log_civicrm_value_contact_reference_9;
+----------------------------+
| count(distinct(entity_id)) |
+----------------------------+
|                      70071 |
+----------------------------+
1 row in set (0.25 sec)

mysql> select count(distinct(entity_id)) from civicrm_value_contact_reference_9;
+----------------------------+
| count(distinct(entity_id)) |
+----------------------------+
|                      99997 |
+----------------------------+
1 row in set (0.44 sec)

mysql> select min(entity_id) from civicrm_value_contact_reference_9;
+----------------+
| min(entity_id) |
+----------------+
|           3881 |
+----------------+
1 row in set (0.00 sec)

mysql> select min(entity_id) from log_civicrm_value_contact_reference_9;
+----------------+
| min(entity_id) |
+----------------+
|           3881 |
+----------------+
1 row in set (0.00 sec)

mysql> select max(entity_id) from log_civicrm_value_contact_reference_9;
+----------------+
| max(entity_id) |
+----------------+
|         128791 |
+----------------+
1 row in set (0.17 sec)

mysql> select max(entity_id) from civicrm_value_contact_reference_9;
+----------------+
| max(entity_id) |
+----------------+
|         129328 |
+----------------+
1 row in set (0.00 sec)

Hi Totten,

Many thanks for your response. We're working on getting you those answers now.

It ranges from 0.31% - 100% of logs missing for a given custom values table.

We have an extension (https://civicrm.org/extensions/do-not-contact-until) that drops and then readds triggers. Could that be causing/impacting this? The tables involved in that have a seemingly high number of missing logs.

I've attached a spreadsheet indicating which tables are experiencing the most missing entries. We have even more custom tables than are here - 39 more - one for each school with custom questions.

J

I found out that a big part of the logs is missing between specific date intervals

17/09/2014 to 05/10/2014
05/10/2014 to 09/10/2014
09/10/2014 to 24/10/2014
24/10/2014 to 04/11/2014

Between these dates the logs didn't record anything.
There may be other holes in the logs, these are the ones I detected so far.

I also checked and the the log table for the contact is working OK for these dates, the custom field groups are the ones being affected.


EDIT: I just found out that the civicrm_email table also has missing logs. They appear to be consistent with the dates I described above.

Thank you
VitorFF

Dear totten,

That's correct.

Thanks for responding!

@DaveFF - when does the "Do Not Contact Until" extension call dropTriggers and rebuildTriggers? During initial installation? During a cron task? Some other time?

During a daily cron job. dropTriggers is called with the name of the extension's custom field table, a couple of queries are done, then rebuildTriggers is called on the same table. See here.

Hi Joanne,

Was that no logging at all, or only no logging on some tables?

Cheers,

Just noticed another curiosity about the logs.

When there is a hole where the logs are missing the row id of the log table isn't sequential.

E.g. here we can see where the log stops at the 4th and resumes at the 10th.


+--------+-----------+---------------------+
| id     | entity_id | log_date            |
+--------+-----------+---------------------+
| 135234 |    128021 | 2014-11-04 19:03:43 |
| 135232 |    128020 | 2014-11-04 19:04:22 |
| 135234 |    128021 | 2014-11-04 19:05:04 |
| 135234 |    128021 | 2014-11-04 19:07:09 |
| 136444 |    128685 | 2014-11-10 10:39:01 |
| 136444 |    128685 | 2014-11-10 10:40:26 |
| 136444 |    128685 | 2014-11-10 10:42:22 |
| 136448 |    128692 | 2014-11-10 11:05:02 |

Notice how both the ID and the entity id suffer a bump. This would be expected from the entity_id, is it normal that the id also takes this sudden leap?

Could this mean that the log is being written and then deleted?

Hello all,

Dave J. from Circle Interactive did some brilliant investigation and fount this out :

I can see that the logging triggers are not in place for civicrm_address, civicrm_email, civicrm_phone or civicrm_value_contact_reference_9 (as an example of a civicrm_value table that extends Contact). So that explains why those tables are not logging and this fits with the other site where we saw the problem.
I have run /civicrm/menu/rebuild&reset=1&triggerRebuild=1 and verified that this has restored the logging triggers for the above tables. So this should get the logging going again - but our experience with the other site suggests the triggers may get disabled again. In all the cases we have examined on the other site, this happened following admin operations on custom fields (creating or editing fields).
On the other site too, the problems began after upgrading from 4.3 to 4.4 .

I hope it sheds some light on the issue.

Thanks,
VitorFF