Tracking MySQL Database Changes

Question

I'm running MySQL 5.1.36 and have a database used for a web-based help desk system. The database has three tables I'd like to track changes for:

CREATE TABLE IF NOT EXISTS `tickets` (
  `TicketNum` int(11) unsigned NOT NULL,
  `SubmittedFromIP` tinyblob,
  `SubmittedFromDevice` varchar(255) DEFAULT NULL,
  `EntryDate` datetime DEFAULT NULL,
  `ClosedDate` datetime DEFAULT NULL,
  `LastName` varchar(50) DEFAULT NULL,
  `FirstName` varchar(50) DEFAULT NULL,
  `Email` varchar(50) DEFAULT NULL,
  `Location` varchar(4) DEFAULT NULL,
  `InventoryNumber` varchar(50) DEFAULT NULL,
  `DeviceName` varchar(50) DEFAULT NULL,
  `Description` text,
  `Notes` text,
  `Agent_ID` smallint(5) unsigned NOT NULL DEFAULT '1',
  `TotalHoursSpent` float NOT NULL DEFAULT '0',
  `Status` smallint(5) unsigned NOT NULL DEFAULT '1',
  `Priority` tinyint(4) NOT NULL DEFAULT '0',
  `LastUpdatedByAgent_ID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`TicketNum`),
  KEY `ClosedDate` (`ClosedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ticketsolutions` (
  `Entry_ID` int(10) unsigned NOT NULL,
  `TicketNum` mediumint(8) unsigned DEFAULT NULL,
  `EntryDateTime` datetime DEFAULT NULL,
  `HoursSpent` float DEFAULT NULL,
  `Agent_ID` smallint(5) unsigned DEFAULT NULL,
  `EntryText` text,
  `LastUpdatedByAgent_ID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`Entry_ID`),
  KEY `TicketNum` (`TicketNum`),
  KEY `EntryDateTime` (`EntryDateTime`),
  KEY `HoursSpent` (`HoursSpent`),
  KEY `Rating` (`Rating`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tickettagsmap` (
  `TicketNum` int(11) unsigned NOT NULL,
  `Tag_ID` int(10) unsigned NOT NULL,
  `AddedByAgent_ID` smallint(5) unsigned NOT NULL,
  `DateTimeAdded` datetime NOT NULL,
  PRIMARY KEY (`TicketNum`,`Tag_ID`),
  KEY `Tag_ID` (`Tag_ID`),
  KEY `fk_AgentID` (`AddedByAgent_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Based on what I've read, the best way to handle this is to create duplicate tables, only with two extra fields per table:

ModifiedDateTime
Action

Is this really the best way? Every time even the slightest change is made to a record, the entire record is inserted into its corresponding history table. It seems like a huge waste of space. Is there a better way to do this?

Answer 1

One simple way to do this is

1. Enable MySql logging.
2. Check for updates in the logs.

Enable logging in MySQL:

Type the following in mysql backend console.

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

Check for updates with queries: (you can customize this to your need)

select argument from mysql.general_log where argument REGEXP '*INSERT*';