MySQL: Creating a Fixed-Size Table by Using Triggers

Understand the Basic Concepts

Managing database tables effectively is a crucial skill for any backend developer. A particular scenario where this becomes important is in the creation and maintenance of a fixed-size table in MySQL. This can be used for various purposes such as maintaining a limited size audit log, system messages or even caching recent data. In this tutorial, we’ll deep dive into how to create such a table by harnessing the power of MySQL triggers.

What Fixed-Size Tables?

Fixed-size tables are often used where you want to only keep the most recent entries up to a certain limit. Once this limit is reached, older data should be displaced by newer rows. Implementing this involves automatically deleting older entries to make room for newer ones, which can be achieved with triggers.

What Are Triggers in MySQL?

Triggers are database ‘callbacks’ that upon specific conditions being met – such as an INSERT, UPDATE, or DELETE – execute defined SQL code. These are particularly useful when you want to automate the maintenance of database integrity or implement business logic directly within your database schema.

Setting Up the Scene

Before implementing our solution, let’s define our example table and requirements. We’ll be working with a simple audit log table audit_log that should maintain only the latest 1000 entries.

CREATE TABLE audit_log (
	id INT AUTO_INCREMENT PRIMARY KEY,
	event_type VARCHAR(50),
	event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	details TEXT
);

Designing the Trigger

Now that we have a table, our next step is to create a trigger that will enforce our fixed-size limit.

Writing the Trigger Logic

Our trigger will be attached to the INSERT event. Every time a new row is inserted, the trigger will check the count of the records. If the limit is surpassed, it will remove the oldest entries.

DELIMITER $

CREATE TRIGGER fixed_size_trigger BEFORE INSERT ON audit_log
FOR EACH ROW
BEGIN
	DECLARE n INT;
	SET n = (SELECT COUNT(*) FROM audit_log);
	IF n >= 1000 THEN
		DELETE FROM audit_log ORDER BY event_time ASC LIMIT n - 999;
	END IF;
END;$

DELIMITER ;

Understanding the Trigger

Let’s dissect the trigger logic:

• We start with changing the DELIMITER so we can include semicolons inside the trigger definition without ending the statement.
• We create a new trigger named fixed_size_trigger associated with the BEFORE INSERT event on the audit_log table.
• We declare a variable n which will hold the current count of rows in the table.
• We then check if n is greater or equal to our predefined size (1000 in this case).
• If true, the trigger deletes the oldest entries exceeding the 1000-row limit. The ORDER BY and LIMIT clauses ensure only the oldest rows beyond our limit are removed.

Testing the Trigger

With our trigger in place, let’s do some insert operations to see how it works.

INSERT INTO audit_log (event_type, details) VALUES ('UserLogin', 'User 123 logged in.');
-- Repeat the above SQL until the audit_log has over 1000 rows --

After crossing the 1000 rows threshold, each subsequent insert will trigger our fixed_size_trigger. As new records are added, the oldest will be removed maintaining a consistent table size.

Alternatives: Cron job

For cases where the highest possible performance is essential, it might be more efficient to use a scheduled Event or Cron job to clean up old rows periodically rather than using a trigger.

For example, If you want to clean up old rows in a MySQL database on Ubuntu using a scheduled event or Cron job instead of a trigger, you can follow these steps:

Step 1: Create a Script for Cleaning Up Old Rows

First, create a script that contains the SQL commands to delete old rows from your MySQL table. Save this script as a .sql file. For example, let’s call it cleanup.sql. Here’s an example of what the script might look like:

-- cleanup.sql
DELETE FROM your_table WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAYS);

In this script, we’re deleting rows from your_table where the created_at timestamp is older than 30 days. Adjust the table name and criteria according to your specific requirements.

Step 2: Create a Shell Script (Optional)

You can create a shell script that runs the MySQL command to execute the cleanup.sql script. This step is optional, but it can make it easier to automate the process later. Create a shell script, e.g., cleanup.sh, with the following content:

#!/bin/bash

# Define MySQL credentials
MYSQL_USER="your_username"
MYSQL_PASSWORD="your_password"
MYSQL_DATABASE="your_database"

# Run the MySQL script
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" < /path/to/cleanup.sql

Make sure to replace your_username, your_password, and your_database with your actual MySQL credentials.

Step 3: Set Up a Scheduled Event or Cron Job

Now, you can set up a scheduled event using MySQL’s event scheduler or a Cron job in Ubuntu to run the script periodically.

Using MySQL Event Scheduler:

If you prefer using MySQL’s event scheduler, you can create a MySQL event to execute the script. Here’s an example SQL command to create a daily event:

DELIMITER //
CREATE EVENT cleanup_event
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
  -- Run the cleanup script
  source /path/to/cleanup.sql;
END;
//
DELIMITER ;

This event will run the cleanup.sql script every day. Adjust the schedule and event name as needed.

Using Cron Job:

Alternatively, you can schedule a Cron job to execute the shell script. Open your Crontab file:

crontab -e

Add a line to schedule the script execution daily. For example, to run the script every day at midnight:

0 0 * * * /bin/bash /path/to/cleanup.sh

Save and exit the Crontab file.

Now, the cleanup script will run at the specified schedule to clean up old rows in your MySQL database. Adjust the schedule and paths according to your requirements.

Notes

Performance

Implementing a fixed-size table using triggers can have some performance implications due to the extra DELETE operations that may run on each INSERT. Monitor the performance following implementation, especially if your table receives a high volume of inserts.

Maintaining Index

Ensure that appropriate indexes are in place (primarily on the column used for sorting during the DELETE operation) to keep performance optimal.

Storage Engines

The performance and behavior may slightly change depending on the storage engine (e.g. InnoDB versus MyISAM) due to how they handle transactions and row locking.

Conclusion

In this tutorial, we have seen how to maintain a fixed-size MySQL table using triggers. This approach is valuable for scenarios where you want to cap data storage without manual intervention. While there are many uses for triggers beyond this example, they represent a powerful feature of MySQL that can help automate database tasks and maintain consistency.