Page MenuHomePhabricator

Resolve whether putting the logging tables in a separate DB will cause ACID compliance /rollback issues
Closed, ResolvedPublic1 Story Points

Description

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

Event Timeline

atgo removed a subscriber: atgo.Mar 30 2016, 10:05 PM
Eileenmcnaughton added a comment.EditedApr 4 2016, 11:44 PM

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?

No actual code to review or deploy here - just a bit of investigation

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.

awight added a comment.Apr 5 2016, 4:22 PM

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.

yeah - my belief is we actually will have full rollback - but it could be different on live. Partial rollback should be very unusual case

Eileenmcnaughton closed this task as Resolved.Apr 27 2016, 9:39 PM

discussed - seems OK...

mmodell removed a subscriber: awight.Jun 22 2017, 9:50 PM