Event-based deletion of old database entries

Problem description

Over time, the size of the following four tables grows: refill, ballingdosinglog, feedlog, data.

You can query the size in the database with the following commands:

MariaDB [aquarium]> select count(*) from refill;
+----------+
| count(*) |
+----------+
|      689 |
+----------+
1 row in set (0.001 sec)

MariaDB [aquarium]> select count(*) from ballingdosinglog;
+----------+
| count(*) |
+----------+
|    13362 |
+----------+
1 row in set (0.018 sec)

MariaDB [aquarium]> select count(*) from feedlog;
+----------+
| count(*) |
+----------+
|      263 |
+----------+
1 row in set (0.001 sec)

MariaDB [aquarium]> select count(*) from data;
+----------+
| count(*) |
+----------+
|   213043 |
+----------+
1 row in set (0.166 sec)

Excessive database size impacts the performance and hence the stability of the aquarium control. For this reason, the size needs to be limited by deleting old entries.

Configuration of MaraDb

Activate the event scheduler by adding the two last lines of below example to MariaDb configuration file (usually /etc/mysql/my.cnf).

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[mysqld]
event_scheduler=ON

You can check the status of the event schedule by running the query SHOW VARIABLES LIKE 'event_scheduler';

MariaDB [(none)]> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.002 sec)

User privileges

Check if your database user account has sufficient privileges by running show grants for user@localhost;.

You can assign specific privilege for executing events by running GRANT ALL PRIVILEGES ON `your_database_name`.* TO 'your_username'@'localhost';.

Alternatively, you can assign all privileges to your database user account by running GRANT ALL PRIVILEGES ON `your_database_name`.* TO 'your_username'@'localhost';.

In both cases, run additionally FLUSH PRIVILEGES;.

Restart of MariaDb

In the terminal, execute sudo systemctl restart mariadb.

Status of MariaDb

In the terminal, execute sudo systemctl status mariadb.

The output should look similar to the following:

● mariadb.service - MariaDB 10.3.39 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: active (running) since Sat 2025-08-02 08:00:18 CEST; 1h 14min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 32624 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
  Process: 32625 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 32627 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 32717 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 32719 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
 Main PID: 32681 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 42 (limit: 1592)
   CGroup: /system.slice/mariadb.service
           └─32681 /usr/sbin/mysqld

Aug 02 08:00:17 raspberrypi systemd[1]: Starting MariaDB 10.3.39 database server...
Aug 02 08:00:18 raspberrypi systemd[1]: Started MariaDB 10.3.39 database server.
Aug 02 08:00:18 raspberrypi /etc/mysql/debian-start[32721]: Upgrading MySQL tables if necessary.
Aug 02 08:00:18 raspberrypi /etc/mysql/debian-start[32724]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Aug 02 08:00:18 raspberrypi /etc/mysql/debian-start[32724]: Looking for 'mysql' as: /usr/bin/mysql
Aug 02 08:00:18 raspberrypi /etc/mysql/debian-start[32724]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Aug 02 08:00:18 raspberrypi /etc/mysql/debian-start[32724]: This installation of MariaDB is already upgraded to 10.3.39-MariaDB.
Aug 02 08:00:18 raspberrypi /etc/mysql/debian-start[32724]: There is no need to run mysql_upgrade again for 10.3.39-MariaDB.
Aug 02 08:00:18 raspberrypi /etc/mysql/debian-start[32724]: You can use --force if you still want to run mysql_upgrade
Aug 02 08:00:18 raspberrypi /etc/mysql/debian-start[32732]: Checking for insecure root accounts.

View events in MariaDb

You can display the configured events using show events;.

MariaDB [aquarium]> show events;
+----------+----------------------------------+--------------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db       | Name                             | Definer            | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+----------+----------------------------------+--------------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| aquarium | cleanup_old_ballingdosinglog_60d | aquarium@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2025-08-02 07:57:19 | NULL | ENABLED |          1 | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
| aquarium | cleanup_old_data_30d             | aquarium@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2025-08-02 07:57:19 | NULL | ENABLED |          1 | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
| aquarium | cleanup_old_feedlog_90d          | aquarium@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2025-08-02 07:57:19 | NULL | ENABLED |          1 | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
| aquarium | cleanup_old_refill_60d           | aquarium@localhost | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2025-08-02 07:57:19 | NULL | ENABLED |          1 | utf8mb4              | utf8mb4_general_ci   | utf8mb4_general_ci |
+----------+----------------------------------+--------------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
4 rows in set (0.002 sec)