CODEWITHSUNDEEP
mysqldebuggingchangelog
siryx
siryx

Reputation: 125

How to create changelog for table?

I need to create a change history of table rows when a certain field is changed. So what I wanted to do is create a trigger on table update. When the field txta changes, I want the whole row to get copied over to debugwhich is a cloned version of msser_210 with an added column for datetime at the end, without data. I would like to add NOW() on change so I would have a timestamp. This is what I have tried to far:

DELIMITER $$
CREATE TRIGGER history_trigger
BEFORE UPDATE ON msser_210
    FOR EACH ROW
        BEGIN
        IF OLD.txta != NEW.txta
        THEN
            INSERT INTO `debug_history` (`idpm`,`posn`,`prnb`,`doid`,`ofcr`,`pidm`,`hitm`,`sitm`,`item`,`dsca`,`igid`,`kitm`,`leng`,`widt`,`hght`,`thik`,`radi`,`quas`,`wght`,`effc`,`colr`,`bdat`,`edat`,`back`,`cuid`,`intb`,`aggr`,`unqu`,`oqua`,`unsq`,`stoc`,`allo`,`hall`,`tqan`,`bqan`,`pkey`,`pric`,`cvqs`,`unsp`,`disc`,`dart`,`ksid`,`anhg`,`txta`,`txti`,`mndn`, `changedate`) VALUES (OLD.idpm,OLD.posn,OLD.prnb,OLD.doid,OLD.ofcr,OLD.pidm,OLD.hitm,OLD.sitm,OLD.item,OLD.dsca,OLD.igid,OLD.kitm,OLD.leng,OLD.widt,OLD.hght,OLD.thik,OLD.radi,OLD.quas,OLD.wght,OLD.effc,OLD.colr,OLD.bdat,OLD.edat,OLD.back,OLD.cuid,OLD.intb,OLD.aggr,OLD.unqu,OLD.oqua,OLD.unsq,OLD.stoc,OLD.allo,OLD.hall,OLD.tqan,OLD.bqan,OLD.pkey,OLD.pric,OLD.cvqs,OLD.unsp,OLD.disc,OLD.dart,OLD.ksid,OLD.anhg,OLD.txta,OLD.txti, OLD.mndn, NOW());
    END IF;
END;
$$

Why I want to do this is because we are having (probably) a php script with a bug that writes the same text string into every field of the database but we don't know when or why it happens neither which script it does. Is there maybe a more elegant solution?

UPDATE: I found the option to "Track Changes" in phpMyAdmin, but apparently it does not track our programs php-issued UPDATE queries, the DROP and CREATE TABLE statements from PHP are tracked though. If I issue an UPDATE via phpMyAdmin, it is tracked though. Long story short I went back to my original plan with the trigger.

UPDATE2: found the answer out myself

Upvotes: 2

Views: 2508

Answers (2)

siryx
siryx

Reputation: 125

The debug_history is a cloned via pypMyAdmin from the original table. It got an additional changedate column appended manually.

ALTER TABLE debug_history ADD COLUMN changedate DATETIME DEFAULT NULL;

I decided because there was no other way that I would have to type all the names myself. Because I am lazy I got a recent SQL dump, copied an INSERT INTO-Statement from the file that is used to rebuild msser_210 and altered the values.

I added an extra row with an autoincrement line, dropped the primary key and set the new primary key to the new row.

ALTER TABLE debug_history DROP PRIMARY KEY;
ALTER TABLE debug_history ADD COLUMN changenumber INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

I now have a working changelog, triggered on change in txta field (Please see the question for the trigger with the original format). I renamed the txta column in the debug_history to txta_old and created a new column txta_new.

ALTER TABLE debug_history CHANGE txta txta_old TEXT NOT NULL $$
ALTER TABLE debug_history ADD COLUMN txta_new TEXT NOT NULL AFTER txta_old $$

Afterwards I had to modify the trigger because I manually had to copy all the names..

DROP TRIGGER history_trigger
DELIMITER $$
CREATE TRIGGER history_trigger
BEFORE UPDATE ON msser_210
    FOR EACH ROW
        BEGIN
        IF OLD.txta != NEW.txta
        THEN
            INSERT INTO `debug_history` (`idpm`,`posn`,`prnb`,`doid`,`ofcr`,`pidm`,`hitm`,`sitm`,`item`,`dsca`,`igid`,`kitm`,`leng`,`widt`,`hght`,`thik`,`radi`,`quas`,`wght`,`effc`,`colr`,`bdat`,`edat`,`back`,`cuid`,`intb`,`aggr`,`unqu`,`oqua`,`unsq`,`stoc`,`allo`,`hall`,`tqan`,`bqan`,`pkey`,`pric`,`cvqs`,`unsp`,`disc`,`dart`,`ksid`,`anhg`,`txta_old`,`txta_new`,`txti`,`mndn`, `changedate`) VALUES (OLD.idpm,OLD.posn,OLD.prnb,OLD.doid,OLD.ofcr,OLD.pidm,OLD.hitm,OLD.sitm,OLD.item,OLD.dsca,OLD.igid,OLD.kitm,OLD.leng,OLD.widt,OLD.hght,OLD.thik,OLD.radi,OLD.quas,OLD.wght,OLD.effc,OLD.colr,OLD.bdat,OLD.edat,OLD.back,OLD.cuid,OLD.intb,OLD.aggr,OLD.unqu,OLD.oqua,OLD.unsq,OLD.stoc,OLD.allo,OLD.hall,OLD.tqan,OLD.bqan,OLD.pkey,OLD.pric,OLD.cvqs,OLD.unsp,OLD.disc,OLD.dart,OLD.ksid,OLD.anhg,OLD.txta,NEW.txta,OLD.txti, OLD.mndn, NOW());
        END IF;
    END;
$$

Upvotes: 0

wally
wally

Reputation: 3602

Update: As per the OP's comment, clearly the context is very specific. An infrastructure team without access to (or the ability to feedback and direct the development team's) code needs a mechanism by which to log table changes on a production database.

Warnings about using triggers:

Triggers can be tricky to debug, not least because they're transparent and it is never obvious to someone new looking at your code that a trigger is performing some action behind the scenes. (I speak from experience.) They can also cause issues on replicated, multi-master and clustered installations. (Again, I speak from experience.) Also if they fail for some unrelated reason (e.g. the table they write to is broken), the entire transaction can/will fail (InnoDB) - which might not be what you want. (Especially with non-essential "debug" functions.)

Otherwise, triggers are a perfectly valid tool. And in your specific scenario, probably the best bet available to you.

There are several other options available to you, two of which I would highlight:

Stored procedures as an access layer to data

If you're very data centric and you already have business logic inside the database - (a hotly debated topic, I'm not here arguing that you should or should not have business logic in the database) then reading and writing to the database through stored procedures has a clear advantage.

Any transactionally tied logic can be inserted into these stored procedures such that the transactionally unsafe caller (PHP, being a common example) only needs to call 1 query (call sp_insert_tablename(123, 'abc')) and transactional safety can be enforced by the database.

Temporary debug logic can be added to these stored procedures and enabled/disabled by a flag in a settings table, session variable, final argument, whatever you please.

Data abstraction layer/library

Similar principle. Find a data abstraction layer for your client (assuming you have access to alter it's internals). For a PHP or .NET web app there are several popular choices, all of which allow you to override (extend through code inheritance) the save/delete operations to perform any additional actions you want - exactly as for stored procedures (but with the logic maintained inside models in the client).

If you want a specific example, you'll need to give us more information on what stack/language/framework(s) you're using

With both options, make sure you appropriately handle error scenarios.

Upvotes: 1

Related Questions