Mysql – Implementing a Record Keeper/Rollback System for a MySQL Database

database-designinnodbMySQLrecordrollback

What's the best way to create a record keeping table for all actions in my database?

I have a number of tables inside one database and I want to create another that records all movements in every other table – adds, removals, delete and edits, which can then be reviewed by a user with top-level administrative privileges and reversed if necessary at the click of a button.

Is there a tried and tested method for this or a native feature of (InnoDB) MySQL useful for this?

If possible, can you point me to resources specific to this please. I'm working on my first database so I apologise if this has been asked here before, but I couldn't find it.

Best Answer

The idea of being able to roll back individual changes to a database is problematic. Records are related to other records in RDBMS, so rolling back one change may cause referential integrity problems. This means that any kind of rollback mechanism is going to have to be smart enough to know what the referential integrity constraints are and either cascade rollbacks or prevent rollbacks that would cascade (depending on your business rules).

Another approach which you might want to consider instead is to put all changes into a work queue and let the administrator approve them before they are written into the database. In this way the contents of your database would be "moderated". If there is a very high risk of data changes being rejected then the pre-approval approach may be a good one for you.

On the other hand, if it would be rare for a change to be rejected, then really what you want is an auditing system that records changes. Rolling back a change is difficult, so many it just needs to be done manually. The important thing is knowing what changed and how - and perhaps who changed it so you can avoid undesirable changes in the future.

A common approach for auditing changes to data is to have a history/audit table for each table that you are tracking. The structure of the history/audit table is the same as the main table, but it adds auditing columns, typically:

Date/Time of the change
User who made the change
Change action (insert/update/delete)
Source of the change (if multiple systems/sources can change the data)

Essentially every time there is a write into the main table, the new main table values are also recorded in the audit table, with the extra columns filled out. In the case of a delete, use the last known values. Sometimes the audit record is written automatically by a trigger, sometimes people write application logic to record this information.

Related Solutions

Database Design – Implementing a Voting System Like Reddit

What you have seems pretty solid. How about

create table vote (
   user_id int not null,
   content_id int not null,
   time_recorded datetime not null,
   vote int not null,
   primary key (user_id, content_id),
   foreign key (user_id) references `user`(user_id)
      on delete cascade,
   foreign key (content_id) references content(content_id)
      on delete cascade,
   check (vote = 1 or vote = -1)
);

A composite primary key with those two keys makes perfect sense. They absolutely should be foreign keys as well. I threw in to cascade a delete from the parent tables; if a user deletes, the "like" will be removed from the content (which, IIRC, is what happens in StackExchange), and if the content is removed, there is no real point in keeping the votes on it. I also implemented the vote column as an integer which must be either 1 or -1, rather than a boolean. That way you can get an overall tally of up and down votes with a simple sum(vote), rather than two select count(*).

MySQL – Shut Down During InnoDB Rollback After Crash

No, rollbacks cannot be done in the background. Both that and the double write checks have to be done before accepting connections again (meaning that if you kill it, you will have to start again on restart). REDO process, however, can and is done in the background.

What you can do is kill mysql (again) and restart it skipping that step:

/etc/init.d/mysqld start --innodb_force_recovery = 3

Then drop the offending table and recover it from the backups, that may be faster than performing a huge rollback.

For the future, try to avoid huge changes on a single transaction precisely to avoid rollbacks, which can be several times more expensive that the actual commit. I can provide you with some tools to help you do this, if they are administrative changes. If the error was (by) a developer, I can only recommend you the option --i-am-a-dummy. :-)

Best Answer

Related Solutions

Database Design – Implementing a Voting System Like Reddit

MySQL – Shut Down During InnoDB Rollback After Crash

Related Question