As hinted previously, CiviCRM 3.3 introduces a new feature, a low-level tracking of everything that happens with a given installation’s data: who does what exact change (and when).
Configuration
Currently the configuration is either non-existent (if you’re ok with having the log tables in the same database as the rest of CiviCRM tables) or requires setting CIVICRM_LOGGING_DSN in your civicrm.settings.php file (if you want to use a separate database for logging tables).
Enabling/disabling
Logging can be enabled (and, later, disabled) by going to Administer → Configure → Global Settings … Miscellaneous and flipping the Logging Yes/No option. Enabling creates the log tables and the relevant triggers (see below) and populates the log tables with initial data. Disabling removes the triggers, keeping the log tables intact. Re-enabling drops and recreates the log tables and re-establishes the relevant triggers.
Underlying implementation
Logging is implemented by creating a set of tables that mirror the schema of almost every existing CiviCRM table (except *_cache and civicrm_import_job_* tables), with four additional columns: log_date, log_conn_id, log_user_id and log_action. Each mirrored table gets a set of ON INSERT/UPDATE/DELETE triggers that populate the relevant log table accordingly. The log tables are append-only, and so use the ARCHIVE engine, which is quite fast and shouldn’t have any visible impact on the daily operations on the logged CiviCRM installation. On the initial table creation the log tables are populated with the current database contents, with the log_action column set to ‘Initialization’. On subsequent INSERTs, UPDATEs and DELETEs the row in question is duplicated into the log table with log_action set to ‘Insert’, ‘Update’ or ‘Delete’, respectively. The log_date, log_conn_id and log_user_id columns are populated with the timestamp of when the change was made, the database connection id used (so changes can be grouped across tables) and the civicrm_contact.id of the user/operator who did the change.
Reporting
Once logging is enabled, two example report templates are enabled (and two matching report instances are created) – ‘Contact Logging Report (Summary)’ and ‘Contact Logging Report (Detail)’. Currently these reports track only changes to the civicrm_contact table, but in the future releases they will be more feature-full.
Current limitations
Due to its heavy trigger (ab)use, currently logging cannot be used with multilingual CiviCRM installations; this will be addressed in a future release (either 3.3.x or 3.4). Also, the reported diffs are limited to the civicrm_contact table only (changes to things like custom data are tracked, but not yet reported) and show raw database changes (hence prefix change is reported as a numerical change of a prefix_id). These limitations will be addresses in 3.3.x releases – but do feel free to jump in and help (refer to the CRM/Report/Form/Contact/Logging* files).
Issues
If you find any issues with the current logging infrastructure or reports, please do comment in the relevant !
Comments
Good starting point, nice job. I will surely feel tempted to jump in, but need to do some API stuff first.....or Eileen will threaten to drown me in warm beer.
this will make at least one of my clients very happy ... thanks
as you said, 'Re-enabling drops and recreates the log tables and re-establishes the relevant triggers' , I think keeping the log tables with data intact after re-enabiling will be more useful, which will allow to maintain the log tables data, and also keep it disable for particular time and again continue logging when enabled.
+1 in agreement here. There are plenty of reasons why organisations may want to disable logging while still being interested in their log data. For example they might disable logging temporarily for a few weeks to boost performance while arranging a server upgrade, or they might want to try turning logging on and off to test impact on performance when judging if they need an upgrade.
If the worry is that there will be gaps in a log report that give the impression that no changes were made during the period when logging was disabled, could these be filled in minus detail using the regular CiviCRM changelog?
Great stuff! Once you find the report in 'Report summary' it's really clean and intuitive.
Two quick thoughts:
1) It'd be great if we could make the 'View Changelog' link on each contact summary page into a reliable source of all logged information about changes to that contact. Are there plans to have the 'View changelog' link on contact summary pages point to the Contact Logging Report for the appropriate contact when logging is enabled? And what would be the possibility of a combined report for this, mixing new-style log report records with any old-style changelog records from before logging was enabled? (with the empty 'Action' fields reading "No record [help icon --> help popup: "Detailed logging was not available when this change took place" ]" ) This seems more attractive than training all the regular staff who can view logs to remember the technical differences between the two logging systems!
2) On some versions of MySQL (5.0 to 5.16 I believe), enabling logging needs the 'Super' privilege for the MySQL user for the triggers to work, which can be harder than it sounds to do on some server setups, e.g. some cPanel setups make it a pain. I'm wrestling with that one at the moment - tips, existing documentation, or a place to drop documentation when I figure it out are all welcome!
Just in case anyone is reading this old post an interested in it:
Enabling/disabling the function does not cause CiviCRM to drop any tables (on 4.3.8)