Using Triggers To Keep Track Of MySQL Table Changes

There are many ways to log changes in MySQL tables:

  • MySQL server query log files;
  • Dumps combined with versioning;
  • Log created by the application;
  • Database diff tools;
  • Table triggers.

All of them are very useful but have different characteristics and drawbacks.

Server query log files is by far the easiest method, just enable it in the configuration file. The problem with this method is that it logs all queries to all tables of the database, so if your MySQL server is somewhat loaded, the performance can be severely affected.

I’ve found that triggers can enable the finest grain of control on table changes. Triggers allows to record only the changes you need to see, by whom and when the modifications were made.

Applying this technique can be moderately tricky, so I’m sharing my experience with it here.

I’ve created a log table like this:

CREATE TABLE logtable
(
serialnum INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
fwd_sql VARCHAR(512) DEFAULT "",
backwd_sql VARCHAR(512) DEFAULT "",
modtable VARCHAR(64) NOT NULL,
moduser VARCHAR(64) DEFAULT "",
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The fwd_sql field records the modifier query and the backwd_sql keeps an undo query statement. The modtable field keeps the modified table name. The fields serialnum and ts are automatically created for each record.

An example logged table is:

CREATE TABLE loggedtable
(
primaryfield INTEGER PRIMARY KEY NOT NULL,
intfield1 INTEGER NOT NULL, 
charfield2 VARCHAR(512) DEFAULT "",
charfield3 VARCHAR(512) DEFAULT ""
);

It’s necessary 3 triggers for each table that needs logging (exemplified as loggedtable), for inserts, deletes and updates, like this:

DROP TRIGGER IF EXISTS loggedtable_after_insert;
DELIMITER $$
CREATE TRIGGER loggedtable_after_insert                 -- trigger name
AFTER INSERT ON loggedtable                             -- table being triggered after insert queries
FOR EACH ROW
BEGIN
 INSERT INTO logtable                                   -- table that records the changes
   ( fwd_sql, backwd_sql, modtable, moduser ) 
 VALUES
   (
   CONCAT(
     "INSERT INTO loggedtable (primaryfield, intfield1, charfield2, charfield3 ) VALUES (",
     CAST( NEW.primaryfield AS CHAR ), ",",
     CAST( NEW.intfield1 AS CHAR ), ",",
     "'", NEW.charfield2, "'", ",",
     "'", NEW.charfield3, "'",
     ")" 
   ),                                                                                         -- modifying operation
   CONCAT( "DELETE FROM loggedtable WHERE primaryfield=", CAST( NEW.primaryfield AS CHAR ) ), -- undo operation
   "loggedtable",                                                                             -- table affected
   user()                                                                                     -- modifier user
   );
END $$
DELIMITER ;
DROP TRIGGER IF EXISTS loggedtable_after_delete;
DELIMITER $$
CREATE TRIGGER loggedtable_after_delete
AFTER DELETE ON loggedtable
FOR EACH ROW
BEGIN
 INSERT INTO logtable 
   ( fwd_sql, backwd_sql, modtable, moduser ) 
 VALUES 
   ( 
   CONCAT( "DELETE FROM loggedtable WHERE primaryfield=", CAST( OLD.primaryfield AS CHAR ) ),
   CONCAT(
   "INSERT INTO loggedtable (primaryfield, intfield1, charfield2, charfield3) VALUES (", 
      CAST( OLD.primaryfield AS CHAR ), ",",
      CAST( OLD.intfield1 AS CHAR ), ",",
      "'", OLD.charfield2, "'", ",",
      "'", OLD.charfield3, "'",
      ")" 
   ),
 "loggedtable",
 user() 
 );
END $$
DELIMITER ;
DROP TRIGGER IF EXISTS loggedtable_after_update;
DELIMITER $$
CREATE TRIGGER loggedtable_after_update
AFTER UPDATE ON loggedtable
FOR EACH ROW
BEGIN
 INSERT INTO logtable
   ( fwd_sql, backwd_sql, modtable, moduser )
 VALUES
   (
   CONCAT( 
     "UPDATE loggedtable SET ",
     if( NEW.intfield1 = OLD.intfield1, "", CONCAT( "intfield1=", CAST( NEW.intfield1 AS CHAR ), "," ) ),
     if( NEW.charfield2 = OLD.charfield2, "", CONCAT( "charfield2=", "'", CAST( NEW.charfield2 AS CHAR ), "'," ) ),
     if( NEW.charfield3 = OLD.charfield3, "", CONCAT( "charfield3=", "'", CAST( NEW.charfield3 AS CHAR ), "'," ) ),
     CONCAT( "primaryfield=", CAST( NEW.primaryfield AS CHAR ) ),
   " WHERE ", "primaryfield=", CAST( NEW.primaryfield AS CHAR )
   ),
   CONCAT(
     "UPDATE loggedtable SET ",
     if( NEW.intfield1 = OLD.intfield1, "", CONCAT( "intfield1=", CAST( OLD.intfield1 AS CHAR ), "," ) ),
     if( NEW.charfield2 = OLD.charfield2, "", CONCAT( "charfield2=", "'", CAST( OLD.charfield2 AS CHAR ), "'," ) ),
     if( NEW.charfield3 = OLD.charfield3, "", CONCAT( "charfield3=", "'", CAST( OLD.charfield3 AS CHAR ), "'," ) ),
     CONCAT( "primaryfield=", CAST( OLD.primaryfield AS CHAR ) ),
   " WHERE ", "primaryfield=", CAST( OLD.primaryfield AS CHAR )
   ),
   "loggedtable",
   user()
   );
END $$
DELIMITER ;

The NEW and OLD are special objects inside triggers containing the new and old state of the row that is being modified. The function user() retrieves the user name, in the form “user@host“.

If record fields of the logged table can contain NULL, a COALESCE operation must be applied like COALESCE(OLD.charfield2, “”), or the log record may not be inserted as desired.

In MySQL, the CREATE TRIGGER command requires special rights (SUPER privilege) to be executed.

In other databases servers systems like PostgreSQL and Oracle, things should be accomplished in a similar way. Even SQLite has triggers.

Ricardo Olsen in-2c-14px, MEng. :: https://dscsys.com

A SCADA/EMS Source Database Management System

To manage a supervisory control for a power system with 60 substations each with at least one RTU and local HMI, 50000 points of supervision, 7 regional control centers and one main center of operation, interconnection with other generation, transmission and distribution utilities plus the independent system operator (ISO) is not an easy task.

There are power system expansions happening in a monthly basis that must be handled in a fast and precise way. All the systems in the substation and in the control centers must be updated at once to support the expansions as the supervisory data must be delivered to the ISO before the energization of the new equipment.

Maintaining each system configuration apart from each other is an herculean task as there are many different systems, from different vendors, at many levels, that must be updated simultaneously and frequently.

To manage all these systems we developed a single source database that models the SCADA systems and their interconnections and also the EMS representation.

There is a single database implemented with MySQL that are manipulated by 2 applications, one for the SCADA model and one for the EMS model and a set of scripts, one for each type of system that must have its configurations generated.

source_database
The database consists of SCADA tables and EMS tables.

SCADA tables are:

  • Supervision Nodes – intelligent components of the SCADA system (IED) that have logical points and data connections with other nodes. Can be a RTU, a data concentrator, a protection relay, an HMI or a control center system.
  • Connections – are the data connection that have the physical points interchanged between the supervision nodes. Nodes of origin and destination, type of protocol and link addresses are attributes of the data connection.
  • Logical Points (of Nodes) – lists of points that are contained in each node.
  • Physical Points (of Connections) – lists of physical points presents in each connection between nodes. Addresses, ASDU types and conversion factors are attributes of the physical points.

EMS tables are:

  • Stations – substations and power plants.
  • Bays – sets (arrangement) of interconnected equipments of substations.
  • Connectors – switchgears and breakers.
  • Supervision Points – point definitions, type, tag, to which connector the point belongs.
  • Point Type – what kind of measures or states a point can be, kV, MW, breaker state, etc.
  • Calculations.

simplified_model

EMS Editor

This application allows to model substations organized by bay and connectors, enter parameters for equipment like transformers, transmission lines, reactors and capacitor banks. Make connections (LIGS in the SAGE system idiom) between equipments and connectors. Create virtual (not yet instantiated anywhere) typed point definitions pertaining to connectors and bays.

edicao-de-pontos

edicao-de-modulos

The point list can alternatively be imported by a special worksheet prepared by the substation project staff. The worksheet is imported by a PHP script into the database system, and after this operation the worksheet is discarded.

SCADA Editor

In this application are modeled the many SCADA nodes and connections of the supervisory system.

edicao_nohs

edicao-de-pontos-fisicos

This software presents lists of points that can be filtered by many criteria to be selected and inserted into nodes and connections as well as cut and paste tools for connection data.

Generator Scripts

The scripts simply makes appropriated queries to the (MySQL) database and process the resulting data to put it in the form expected by the systems that are to be configured.

I prefer PHP for creating scripts as it is already used by our intranet site but many other languages can be used.

List of generated data by scripts we use now:

  • SAGE system DAT files for the main Transmission Operation Center (more than 2 million lines of text files).
  • SAGE System customized tabular screens for all substations.
  • Historical data recording system configuration.
  • Substation local HMI files system for 50 substations.
  • Substation local HMI initial screens, all standard objects created and linked. Drawings must be adjusted by hand.
  • Substation local data concentrator configuration files.
  • Regional telecontrol centers data concentrator and HMI configuration files.

To add a new type of SCADA system to be managed is mostly studying its configuration files and creating a new generator script for it.

Concurrency of editing

The applications can be used by many people simultaneously, e.g. one can edit the EMS model of a substation while another can edit de SCADA model of other substation system. Only must be avoided to edit the same model of the same substation by more than one person at a time. This scheme greatly speed things up. When everybody finished the editings, the scripts can be used to create the configuration and point database files for each desired system.

Database Control

The database is backed up and versioned by its dumps. Also we use triggers to monitor and register modifications (what, when and who) to the main tables of the database. A hot backup server is at times synchronized to the main server by means of the excellent SQLYog SJA tool.

We keep all the source tables inside the Innodb memory buffer for better performance.

MySQL has a plethora of free tools to edit, manage, model, synchronize, backup, etc. and has excellent support by many scripting languages and development tools.

Consistency

The database referential integrity, the data entry applications and the generator scripts are three additional levels of data integrity and consistency checks before the final configuration files are delivered to the runtime systems. This is a significative factor that avoid many type of errors and problems that are commonly made when editing configuration files manually. Also all the systems managed at all levels are guaranteed to be consistent and standardized.