MySQL audit logging using triggers
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, we are going to see how we can implement an audit logging mechanism using MySQL database triggers to store the old and new row states in JSON column types.
Database tables
Let’s assume we have a library application that has the following two tables:
The book
table stores all the books that are found in our library, and the book_audit_log
table stores the CDC (Change Data Capture) events that happened to a given book
record via an INSERT, UPDATE, or DELETE DML statement.
The book_audit_log
table is created like this:
CREATE TABLE IF NOT EXISTS book_audit_log ( book_id BIGINT NOT NULL, old_row_data JSON, new_row_data JSON, dml_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL, dml_timestamp DATETIME NOT NULL, dml_created_by VARCHAR(255) NOT NULL, trx_timestamp timestamp NOT NULL, PRIMARY KEY (book_id, dml_type, dml_timestamp) )
The book_audit_log
columns store the following info:
- The
book_id
column stores the identifier of thebook
row that has been either created, updated, or deleted. - The
old_row_data
is a JSON column that will capture the state of thebook
record prior to executing an INSERT, UPDATE, or DELETE statement. - The
new_row_data
is a JSON column that will capture the state of thebook
record after executing an INSERT, UPDATE, or DELETE statement. - The
dml_type
is an enumeration column that stores the DML statement type that created, updated, or deleted a givenbook
record. - The
dml_timestamp
stores the DML statement execution timestamp. - The
dml_created_by
stores the application user who issued the INSERT, UPDATE, or DELETE DML statement. - The
trx_timestamp
stores the transaction timestamp in which the associated DML statement was executed.
The Primary Key of the book_audit_log
is a composite of the book_id
, dml_type
, and dml_timestamp
since a book
row can have multiple associated book_audit_log
records.
MySQL audit logging triggers
To capture the INSERT, UPDATE, and DELETE DML statements, we need to create 3 database triggers that are going to insert records in the book_audit_log
table.
MySQL AFTER INSERT trigger
To intercept the INSERT statements on the book
table, we will create the book_insert_audit_trigger
:
CREATE TRIGGER book_insert_audit_trigger AFTER INSERT ON book FOR EACH ROW BEGIN INSERT INTO book_audit_log ( book_id, old_row_data, new_row_data, dml_type, dml_timestamp, dml_created_by, trx_timestamp ) VALUES( NEW.id, null, JSON_OBJECT( "title", NEW.title, "author", NEW.author, "price_in_cents", NEW.price_in_cents, "publisher", NEW.publisher ), 'INSERT', CURRENT_TIMESTAMP, @logged_user, @transaction_timestamp ); END
The book_insert_audit_trigger
is executed after a record is inserted in the book
table.
The NEW
keyword references the record values that were just inserted, so we can use it to extract the book
column values.
Only the new_row_data
column is set since there is no old record state to set in the old_row_data
column.
The JSON_OBJECT
MySQL function allows us to create a JSON object that takes the provided key-value pairs.
The dml_type
column is set to the value of INSERT
, and the dml_timestamp
value is set to the CURRENT_TIMESTAMP
.
The dml_created_by
column is set to the value of the @logged_user
MySQL session variable, which was previously set by the application with the currently logged user:
Session session = entityManager.unwrap(Session.class); Dialect dialect = session.getSessionFactory() .unwrap(SessionFactoryImplementor.class) .getJdbcServices() .getDialect(); session.doWork(connection -> { update( connection, String.format( "SET @logged_user = '%s'", ReflectionUtils.invokeMethod( dialect, "escapeLiteral", LoggedUser.get() ) ) ); });
MySQL AFTER UPDATE trigger
To capture the UPDATE statements on the book
records, we will create the following book_update_audit_trigger
:
CREATE TRIGGER book_update_audit_trigger AFTER UPDATE ON book FOR EACH ROW BEGIN INSERT INTO book_audit_log ( book_id, old_row_data, new_row_data, dml_type, dml_timestamp, dml_created_by, trx_timestamp ) VALUES( NEW.id, JSON_OBJECT( "title", OLD.title, "author", OLD.author, "price_in_cents", OLD.price_in_cents, "publisher", OLD.publisher ), JSON_OBJECT( "title", NEW.title, "author", NEW.author, "price_in_cents", NEW.price_in_cents, "publisher", NEW.publisher ), 'UPDATE', CURRENT_TIMESTAMP, @logged_user, @transaction_timestamp ); END
Every time a book
record is updated, the book_update_audit_trigger
is executed, and a book_audit_log
row will be created to capture both the old and the new state of the modifying book
record.
MySQL AFTER DELETE trigger
To intercept the DELETE statements on the book
table rows, we will create the following book_delete_audit_trigger
:
CREATE TRIGGER book_delete_audit_trigger AFTER DELETE ON book FOR EACH ROW BEGIN INSERT INTO book_audit_log ( book_id, old_row_data, new_row_data, dml_type, dml_timestamp, dml_created_by, trx_timestamp ) VALUES( OLD.id, JSON_OBJECT( "title", OLD.title, "author", OLD.author, "price_in_cents", OLD.price_in_cents, "publisher", OLD.publisher ), null, 'DELETE', CURRENT_TIMESTAMP, @logged_user, @transaction_timestamp ); END
As you can see, only the old_row_data
column is set since there is no new record state.
Testing time
When executing an INSERT statement on the book
table:
INSERT INTO book ( id, author, price_in_cents, publisher, title ) VALUES ( 1, 'Vlad Mihalcea', 3990, 'Amazon', 'High-Performance Java Persistence 1st edition' )
We can see that a record is inserted in the book_audit_log
that captures the INSERT statement that was just executed on the book
table:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by | |---------|--------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------| | 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-07-29 13:40:15 | Vlad Mihalcea |
When updating the book
table row:
UPDATE book SET price_in_cents = 4499 WHERE id = 1
We can see that a new record is going to be added to the book_audit_log
by the AFTER UPDATE trigger on the book
table:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by | |---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------| | 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-07-29 13:40:15 | Vlad Mihalcea | | 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE | 2020-07-29 13:50:48 | Vlad Mihalcea |
When deleting the book
table row:
DELETE FROM book WHERE id = 1
A new record is added to the book_audit_log
by the AFTER DELETE trigger on the book
table:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by | |---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------| | 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-07-29 13:40:15 | Vlad Mihalcea | | 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE | 2020-07-29 13:50:48 | Vlad Mihalcea | | 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | | DELETE | 2020-07-29 14:05:33 | Vlad Mihalcea |
If you want to transform the JSON-based audit log object to the initial relational table record, you can use the MySQL
JSON_TABLE
function, as explained in this article.
Awesome, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
When it comes to implementing an audit logging mechanism, there are many options to choose from. If you are using Hibernate, the simplest solution is to use Hibernate Envers.
If you are not using Hibernate or if you want to capture the CDC events no matter how the DML statements are generated (e.g., plain JDBC, via the SQL console), then a database trigger solution is very easy to implement. Using JSON columns to store the old and new state of the row that gets created, updated, or deleted is much better than listing all the columns in the audit log table.
Another option is to use Debezium and extract the CDC events from the Binary Log. This solution can work asynchronously, therefore having no impact on the OLTP transactions that trigger the CDC events.
