Adding a time dimension to MySQL cells

Question

Is there a way to keep a timestamped record of every change to every column of every row in a MySQL table? This way I would never lose any data and keep a history of the transitions. Row deletion could be just setting a "deleted" column to true, but would be recoverable.

I was looking at HyperTable, an open source implementation of Google's BigTable, and this feature really wet my mouth. It would be great if could have it in MySQL, because my apps don't handle the huge amount of data that would justify deploying HyperTable. More details about how this works can be seen here.

Is there any configuration, plugin, fork or whatever that would add just this one functionality to MySQL?

Answer 1

I do this in a custom framework. Each table definition also generates a Log table related many-to-one with the main table, and when the framework does any update to a row in the main table, it inserts the current state of the row into the Log table. So I have a full audit trail on the state of the table. (I have time records because all my tables have LoggedAt columns.)

No plugin, I'm afraid, more a method of doing things that needs to be baked into your whole database interaction methodology.

Answer 2

Create a table that stores the following info...

CREATE TABLE MyData (
    ID INT IDENTITY,
    DataID INT )

CREATE TABLE Data (
    ID INT IDENTITY,
 MyID INT,
    Name VARCHAR(50),
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)

Now create a sproc that does this...

INSERT Data (MyID, Name)
VALUES(@MyID,@Name)

UPDATE MyData SET DataID = @@IDENTITY
WHERE ID = @MyID

In general, the MyData table is just a key table. You then point it to the record in the Data table that is the most current. Whenever you need to change data, you simply call the sproc which Inserts the new data into the Data table, then updates the MyData to point to the most recent record. All if the other tables in the system would key themselves off of the MyData.ID for foreign key purposes.

This arrangement sidesteps the need for a second log table(and keeping them in sync when the schema changes), but at the cost of an extra join and some overhead when creating new records.

Answer 3

I've implemented this in the past in a php model similar to what chaos described.

If you're using mysql 5, you could also accomplish this with a stored procedure that hooks into the on update and on delete events of your table.

http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html

Answer 4

Do you need it to remain queryable, or will this just be for recovering from bad edits? If the latter, you could just set up a cron job to back up the actual files where MySQL stores the data and send it to a version control server.