Poorman’s MySQL table audit information

Today, somebody asked me how he could track the creation of a record (who created it and when) and who and when it was last modified.

Usually, this is performed in a different table, called audit table. In MySQL you can create an audit table and populate it using triggers.

But of course, this person had some constraints, otherwise it would have been too easy:

  1. no other table could be used/created
  2. the application should keep working without any changes

He was desperate for help…

MySQL 8.0 Invisible Columns

The answer is : Invisible Columns.

Let’s have a look at a simple table, containing an ID as primary key, a first name and a last name. And of course the application must work as expected… and unfortunately, it seems the application is abusing select * from:

mysql> select * from demo;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | Frederic  | Descamps |
|  2 | Kenny     | Gryp     |
+----+-----------+----------+

Let’s have a look at the table’s definition:

CREATE TABLE `demo` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

We need to add some extra information to this table:

  • created_at : the timestamp when the record was inserted
  • created_by : the user who inserted the record
  • updated_at : the timestamp of the last update of the record
  • updated_by : the last user who modified the record

To default the timestamp fields, it’s easy and we also need to not forget to set those extra field as invisible:

mysql> alter table demo 
         add created_at timestamp 
             default current_timestamp invisible, 
         add created_by varchar(60) not null 
             default '' invisible,
         add updated_at timestamp 
             on update current_timestamp invisible, 
         add updated_by varchar(60) not null 
             default '' invisible ;

If we let it like this, the user information won’t be filled. We still need to create triggers:

mysql> create trigger demo_insert before insert on demo 
        for each row
            set new.created_by=session_user();

mysql> create trigger demo_update before update on demo 
        for each row
            set new.updated_by=session_user();

Let’s test

mysql> select * from demo;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | frederic  | descamps |
|  2 | kenny     | gryp     |
+----+-----------+----------+
2 rows in set (0.0010 sec)

mysql> select *, created_at, created_by, updated_at, updated_by 
       from demo;
+----+-----------+----------+-----------------+---+--------+--------+
| id | firstname | lastname | created_at      |by | updated| by     |
+----+-----------+----------+-----------------+---+--------+--------+
|  1 | frederic  | descamps | 2022-04-14 23.. |   | NULL   |        |
|  2 | kenny     | gryp     | 2022-04-14 23.. |   | NULL   |        |
+----+-----------+----------+-----------------+---+--------+--------+

We can notice that the hidden columns are displayed only when we specifically request so.

Now let’s add a new record:

mysql> insert into demo (firstname, lastname) 
       values ('miguel', 'araujo');

mysql> select * from demo;
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | frederic  | descamps |
|  2 | kenny     | gryp     |
|  3 | miguel    | araujo   |
+----+-----------+----------+
3 rows in set (0.0002 sec)

mysql> select *, created_at, created_by, updated_at, updated_by 
       from demo where id=3\G
*************************** 1. row ***************************
        id: 3
 firstname: miguel
  lastname: araujo
created_at: 2022-04-14 23:22:25
created_by: root@localhost
updated_at: NULL
updated_by: 

Now from another session:

mysql> update demo set firstname='fred' where id=1;

mysql> select id,  updated_at, updated_by from demo;
+----+---------------------+----------------+
| id | updated_at          | updated_by     |
+----+---------------------+----------------+
|  1 | 2022-04-14 23:30:20 | fred@localhost |
|  2 | NULL                |                |
|  3 | NULL                |                |
+----+---------------------+----------------+

mysql> insert into demo (firstname, lastname) 
       values ('dimitri', 'k');

mysql> select id, firstname, created_by, updated_by from demo;
+----+-----------+----------------+----------------+
| id | firstname | created_by     | updated_by     |
+----+-----------+----------------+----------------+
|  1 | fred      |                | fred@localhost |
|  2 | kenny     |                |                |
|  3 | miguel    | root@localhost |                |
|  4 | dimitri   | fred@localhost |                |
+----+-----------+----------------+----------------+

And of course we can use the new hidden column to also query the records:

mysql> select * from demo 
       where created_by like 'fred%' 
          or updated_by like 'fred%';
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
|  1 | fred      | descamps |
|  4 | dimitri   | k        |
+----+-----------+----------+

JSON

Then I had in mind to provide the audit information as JSON. Let’s create a second table with the following triggers:

mysql> CREATE TABLE `demo2` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `audit_info` json DEFAULT NULL /*!80023 INVISIBLE */,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

mysql> create trigger demo2_insert before insert on demo2 
         for each row 
          set new.audit_info=json_object('created_at', now(), 
                               'created_by', session_user());

mysql> create trigger demo2_update before update on demo2 
         for each row
          set new.audit_info=json_merge_patch(old.audit_info,
              json_object('update_at', now(), 
                          'updated_by', session_user())); 

And let’s test it:

mysql> insert into demo2 (firstname, lastname) 
       values ('frederic', 'descamps'), ('kenny', 'gryp');

mysql> select *, audit_info from demo2;
+----+-----------+----------+---------------------------------------+
| id | firstname | lastname | audit_info                            |
+----+-----------+----------+---------------------------------------+
|  3 | frederic  | descamps | {"created_at": "2022-04-15            |
|                           |    00:03:37.000000",                  |
|                           |  "created_by": "fred@localhost"}      |
|  4 | kenny     | gryp     | {"created_at": "2022-04-15            |
|                           |    00:03:37.000000",                  |
|                           |  "created_by": "fred@localhost"}      |
+----+-----------+----------+---------------------------------------+

mysql> update demo2 set firstname='fred' where id=3;

mysql> select *, json_pretty(audit_info) from demo2\G
*************************** 1. row ***************************
                     id: 3
              firstname: fred
               lastname: descamps
json_pretty(audit_info): {
  "update_at": "2022-04-15 00:04:57.000000",
  "created_at": "2022-04-15 00:03:37.000000",
  "created_by": "fred@localhost",
  "updated_by": "fred@localhost"
}
*************************** 2. row ***************************
                     id: 4
              firstname: kenny
               lastname: gryp
json_pretty(audit_info): {
  "created_at": "2022-04-15 00:03:37.000000",
  "created_by": "fred@localhost"
}

There is something even more interesting when using a JSON data type for the audit information, we can keep track of all updates.

Let’s replace the demo2_update trigger by this one:

mysql> create trigger demo2_update before update on demo2 
         for each row
            set new.audit_info=json_merge_preserve(old.audit_info,
                json_object('update_at', now(), 
                    'updated_by', session_user())); 

We used the function json_merge_preserve() instead of json_patch() and this is the result:

mysql> update demo2 set firstname='lefred' where id=3;

mysql> select *, json_pretty(audit_info) from demo2 where id=3\G
*************************** 1. row ***************************
                     id: 3
              firstname: lefred
               lastname: descamps
json_pretty(audit_info): {
  "update_at": [
    "2022-04-15 00:04:57.000000",
    "2022-04-15 00:14:47.000000"
  ],
  "created_at": "2022-04-15 00:03:37.000000",
  "created_by": "fred@localhost",
  "updated_by": [
    "fred@localhost",
    "fred@localhost"
  ]
}

Cool isn’t it ? We have the time of all modifications and by who. Of course you can extend this solution by adding even more information like old and new values…

Conclusion

As you could see, MySQL 8.0 invisible columns provide the possibility to have some audit information in the same table.

That information can be simple hidden fields or even a single JSON containing all the information yo need.

Enjoy MySQL !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.