6

For regulatory and fraud requirements, we need to record every change to most of the tables in the DB. The way we did this very successfully with a previous project is to have a copy of each table, identical except that:

  1. 4 additional columns: DateChanged, UserId, Action (Create, Update, delete), and IP
  2. The Id pk no longer is unique.
  3. all other constraints (FK, unique indexes etc) removed.
  4. the audit tables are in a separate audit schema

At a previous job, the Oracle DBAs wrote a script to automatically generate all this, it did the following:

  1. Created a new schema called audit if not existing
  2. Iterate over each table t in the normal schema:
    1. created a new table in the audit schema with the same table name except prefixed it with a_, e.g. a_t
    2. Added all the same columns as the original table, plus the 4 additional columns (DateChanged, UserId, IP Address and Action)
    3. generated and added triggers to the original table (if not already there) to:
    4. If updating, write a new row to the corresponding a_ table, with action of "Update" containing the pre-updated (old) values in all the columns (the main table will have the new values)
    5. If deleting, Add a row which is a copy of the main tables row, but action = "Delete".
    6. If inserting, add the row to the audit schema

NOTE:

  1. All tables have a ID PK.
  2. A few tables need to be excluded for performance or because not needed (e.g. the balance table which is updated by a trigger)

The beauty of this system is you can query what changed and when, or who made changes to what, and see the record before and after the change. changes are at the DB row level, not the individual column level.

Has anyone come across something like this for MYSQL 5.6? We don't have a DBA on our team who could write something like this from scratch, but we know enough to modify something similar.

3
  • 1
    For me, this isn't a scalable solution, you are pretty much doubling the workload and this puts constraints on how you can grow and convolutes troubleshooting, replication topology and perhaps more. FKs and triggers may limit what tools, storage engines that can be used in the environment. Considerations for schema changes are not factored into the solution, what if you decide to drop a column? Will you require review of the audit tables, triggers, foreign keys? The audit tables are prone to problems such as rogue updates on the audit tables by someone covering their tracks?
    – eroomydna
    Jan 27, 2015 at 13:32
  • @eroomydna True, your schema update scripts need to also update the audit tables. This was not an issue for us with 400+ tables and monthly releases using liquibase. Rogue updates are not possible, as no single user has access to edit the audit tables, only the trigger can do this. If they are root on the db, they can disable triggers, but root password is in a safe. This worked with 100m+ rows, without scalability issues, as most of the tables which change (such as player details) change rarely. The issue with not indexing the changes is there, but querying is only done for fraud invest. Jan 27, 2015 at 16:54
  • And the fact that it is work to implement does not change the legal requirement to audit every change, unfortunately. There will not be any performance issues, as the critical tables such as transaction are insert only, so dont generate any audit log. Jan 27, 2015 at 16:56

2 Answers 2

6

I just wrote this earlier today. It's a select statement working off the information_schema database, which produces the schema for the audit tables and the triggers.

SET GLOBAL group_concat_max_len = 1000;

SET @dbName = "[[[your_db_name_here]]]";

SELECT concat("DROP TABLE IF EXISTS `", @dbName, "`.`", table_data.audit_table, "`;\r",
          "CREATE TABLE `", @dbName, "`.`", table_data.audit_table, "`\r",
          "(\r",
          "  `auditAction` ENUM ('INSERT', 'UPDATE', 'DELETE'),\r",
          "  `auditTimestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,\r",
          "  `auditId` INT(14) AUTO_INCREMENT,",
          column_defs, ",\r"
          "  PRIMARY KEY (`auditId`),\r",
          "  INDEX (`auditTimestamp`)\r",
          ")\r",
          "  ENGINE = InnoDB;\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.insert_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.insert_trigger, "`\r",
          "  AFTER INSERT ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('INSERT',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.update_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.update_trigger, "`\r",
          "  AFTER UPDATE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('UPDATE',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.delete_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.delete_trigger, "`\r",
          "  AFTER DELETE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('DELETE',", table_data.OLDcolumn_names, ");\r\r"
)
FROM (
   # This select builds a derived table of table names with ordered and grouped column information in different
   # formats as needed for audit table definitions and trigger definitions.
   SELECT
     table_order_key,
     table_name                                                                      AS db_table,
     concat("audit_", table_name)                                                    AS audit_table,
     concat(table_name, "_inserts")                                                  AS insert_trigger,
     concat(table_name, "_updates")                                                  AS update_trigger,
     concat(table_name, "_deletes")                                                  AS delete_trigger,
     group_concat("\r  `", column_name, "` ", column_type ORDER BY column_order_key) AS column_defs,
     group_concat("`", column_name, "`" ORDER BY column_order_key)                   AS column_names,
     group_concat("`NEW.", column_name, "`" ORDER BY column_order_key)               AS NEWcolumn_names,
     group_concat("`OLD.", column_name, "`" ORDER BY column_order_key)               AS OLDcolumn_names
   FROM
     (
       # This select builds a derived table of table names, column names and column types for
       # non-audit tables of the specified db, along with ordering keys for later order by.
       # The ordering must be done outside this select, as tables (including derived tables)
       # are by definition unordered.
       # We're only ordering so that the generated audit schema maintains a resemblance to the
       # main schema.
       SELECT
         information_schema.tables.table_name        AS table_name,
         information_schema.columns.column_name      AS column_name,
         information_schema.columns.column_type      AS column_type,
         information_schema.tables.create_time       AS table_order_key,
         information_schema.columns.ordinal_position AS column_order_key
       FROM information_schema.tables
         JOIN information_schema.columns
           ON information_schema.tables.table_name = information_schema.columns.table_name
       WHERE information_schema.tables.table_schema = @dbName
             AND information_schema.columns.table_schema = @dbName
             AND information_schema.tables.table_name NOT LIKE "audit\_%"
     ) table_column_ordering_info
   GROUP BY table_name
 ) table_data
ORDER BY table_order_key
INTO OUTFILE "[[[your_output_file]]]"
5
  • Awsome! This solves the issue of creating the initial tables and triggers. Then we just have to remember to modify the audit tables when we modify the normal tables in our liquibase scripts. May 24, 2017 at 8:02
  • I'm new to this, but I suppose you could create triggers on inserts, updates and deletes to the information_schema table itself, with a where clause for your database, and table names "NOT LIKE" audit table names (to avoid creating audit tables on audit tables), and INTO OUTFILE with a derived unique name based on timestamp. So every alter to a table generates the corresponding alter to the audit table, and generates drop triggers and new create triggers.
    – vanyo
    May 24, 2017 at 19:31
  • I've updated it to read better, and to generate an audit schema where the ordering matches the ordering of the original schema.
    – vanyo
    May 25, 2017 at 12:25
  • getting this erroR: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table_column_ordering_info.table_order_key' which is not functionally dependent on columns in GROUP BY clause; Mar 18, 2018 at 3:59
  • Would it make sense to add a column named AuditUser which contains the username of the user who made the change to the data as requested by the OP. This would be someting like:" `auditUser` VARCHAR(100) DEFAULT USER(),\r",
    – Ruut
    May 28, 2019 at 12:57
1

Vanyo's answer almost worked for me. It was fine once I moved the backtick in the NEW and OLD references (so instead of `NEW.column1` I get NEW.`column1`):

group_concat("NEW.`", column_name, "`" ORDER BY column_order_key)               AS NEWcolumn_names,
group_concat("OLD.`", column_name, "`" ORDER BY column_order_key)               AS OLDcolumn_names

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.