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.