tags:

views:

1781

answers:

4

I have been asked to audit any/all changes in a MySQL table. Does anyone know of any tools out there to help me do this or do I need to write my own solution?

If I write my own audting, my initial thought it to make a separate table and build a string of changes in the PHP code. Something like "fieldname1 -> oldvalue | fieldname2 -> oldvalue, ...". If you see a major problem with this method, please let me know.

+2  A: 

Use a trigger to detect changes and to write before/after values to a log table.

Ed Guiness
Sadly, I am stuck using MySQL 4. I'll edit my question for clarification. Great suggestion though.
Haabda
+2  A: 

The only sure-fire way to capture all changes to a DB table is to use triggers on the Server. The risk of modifing your own code to audit the changes is that changes from another application/user etc will not be captured.

Having said that, I'm not sure that MySQL 4 had trigger support.

Matt
I guess I'm just going to have to implement my back-asswards solution. Maybe I can get permission to update to MySQL 5.
Haabda
Triggers were added in MySQL 5.0. At this point there really isn't a good reason not to upgrade from 4 to 5. 5.1 is in release candidate, and 6.0 is in Alpha development.
acrosman
A: 

SoftTree DB Audit has MySQL support, might do what you're after:

http://www.softtreetech.com/idbaudit.htm

Keith Lawrence
A: 

If you wind up hand-rolling a solution due lack of trigger support, I strongly recommend that you don't just dump the changes in a string blob. One day you will be asked to query that data and you will wind up having to do a bunch of string parsing to get your data back out. (I speak from experience here.)

The simplest approach is just to create a shadow audit table that has all of the same columns as the original table, plus a change date column and a sequential id. Then you have the entire history at hand to reconstruct in whatever format you need, and you can query it at will.

Jason DeFontes