The log tables can go in the same DB but are tidier in a separate one. I did read a separate one might still support ACID but need to be sure
|Open||None||T77938 [epic] Dedupe CiviCRM|
|Resolved||None||T111702 [epic] Dedupe exact matches|
|Duplicate||None||T124695 Civi: can the Change Log be used to recover contribs that have been inadvertently deleted?|
|Open||None||T117630 Dedupe merge failed to relink a contribution?|
|Resolved||None||T117466 Q3 GOALS! (January-March) Keep at top of Q3 column|
|Resolved||None||T111704 [epic] Make deduping reversible|
|Resolved||awight||T122411 Changing email address in Civi should be exported to Silverpop|
|Resolved||Eileenmcnaughton||T132056 Turn logging on on live|
|Resolved||Eileenmcnaughton||T130163 Finalise sql & process for turning logging on on live|
|Resolved||Eileenmcnaughton||T131231 Resolve whether putting the logging tables in a separate DB will cause ACID compliance /rollback issues|
Based on the final comment here http://stackoverflow.com/questions/2239810/multiple-database-and-transactions
I figured the best way was to try it out for myself. Through API explorer I issued this command
$result = civicrm_api3('Contact', 'create', array(
'sequential' => 1, 'contact_type' => "Individual", 'first_name' => "sep_db_inno", 'api.Email.create' => array(),
Since that chains an invalid email create the contact create is rolled back. It is removed from civicrm_contact & the test is to see if it leaves a trace in the log_civicrm_contact table.
The results locally are that IF the table is INNODB the transaction is rolled back, if it is ARCHIVE it isn't. Locally it doesn't seem to matter if it is in the same DB or not, which is inline with the comments made on that stackexchange thread. In fact it's defined as a separate DSN, within CiviCRM. I thought that might complicate things but there was no evidence of that. If a different user were used I expect it would, however it's the DSN of the trigger command that matters I expect.
Question: what effect does the replication on live have on this conclusion?
Note that per IRC discussion an imcomplete rollback of logging data may NOT be a bad thing. On other sites (with Archive tables) I have been able to diagnose situations where a rollback has been done inappropriately as a result of the rollback being incomplete.
That sounds good--but if that's our desired behavior, we should be systematic about never rolling back the logging table, and adding some kind of annotation that the main db changes were rolled back. Probably low-normal priority to do so, though.