Event-based deletion of old database entries - Revision history

Uwe: /* Adding events = */

2025-12-30T17:39:36Z

Adding events =

← Older revision		Revision as of 19:39, 30 December 2025
Line 131:		Line 131:
	</pre>		</pre>

	== Adding events ===		== Adding events ==
	The repository of the database contains a script <code>database_cleanup_setup.sh</code>.		The repository of the database contains a script <code>database_cleanup_setup.sh</code>.
	The script will set up the cleanup for the following tables:		The script will set up the cleanup for the following tables:

Uwe at 17:28, 30 December 2025

2025-12-30T17:28:22Z

@@ Line 130: / Line 130: @@
 Aug 02 08:00:18 raspberrypi /etc/mysql/debian-start[32732]: Checking for insecure root accounts.
 </pre>
 == View events in MariaDb ==

Uwe: /* Configuration of MaraDb */

2025-12-30T17:22:06Z

Configuration of MaraDb

← Older revision		Revision as of 19:22, 30 December 2025
Line 42:		Line 42:
	For this reason, the size needs to be limited by deleting old entries.		For this reason, the size needs to be limited by deleting old entries.

	== Configuration of ~~MaraDb~~ ==		== Configuration of MariaDb ==
	Activate the event scheduler by adding the two last lines of below example to MariaDb configuration file (usually <code>/etc/mysql/my.cnf</code>).		Activate the event scheduler by adding the two last lines of below example to MariaDb configuration file (usually <code>/etc/mysql/my.cnf</code>).

Uwe: /* View events in MariaDb */

2025-08-02T07:30:22Z

View events in MariaDb

← Older revision		Revision as of 09:30, 2 August 2025
Line 132:		Line 132:

	== View events in MariaDb ==		== View events in MariaDb ==
			You can display the configured events using <code>show events;</code>.

			<pre>
			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)
			</pre>

Uwe: /* Status of MariaDb */

2025-08-02T07:15:48Z

Status of MariaDb

← Older revision		Revision as of 09:15, 2 August 2025
Line 98:		Line 98:

	== Status of MariaDb ==		== Status of MariaDb ==
			In the terminal, execute <code>sudo systemctl status mariadb</code>.

			The output should look similar to the following:

			<pre>
			● 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.
			</pre>

	== View events in MariaDb ==		== View events in MariaDb ==

Uwe: /* Restart of MariaDb */

2025-08-02T07:14:39Z

Restart of MariaDb

← Older revision		Revision as of 09:14, 2 August 2025
Line 95:		Line 95:

	== Restart of MariaDb ==		== Restart of MariaDb ==
			In the terminal, execute <code>sudo systemctl restart mariadb</code>.

	== Status of MariaDb ==		== Status of MariaDb ==

	== View events in MariaDb ==		== View events in MariaDb ==

Uwe: /* User privileges */

2025-08-02T07:13:32Z

User privileges

← Older revision		Revision as of 09:13, 2 August 2025
Line 87:		Line 87:
	== User privileges ==		== User privileges ==
	Check if your database user account has sufficient privileges by running <code>show grants for user@localhost;</code>.		Check if your database user account has sufficient privileges by running <code>show grants for user@localhost;</code>.

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

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

	In both cases, run additionally <code>FLUSH PRIVILEGES;</code>.		In both cases, run additionally <code>FLUSH PRIVILEGES;</code>.

Uwe: /* User privileges */

2025-08-02T07:13:12Z

User privileges

← Older revision		Revision as of 09:13, 2 August 2025
Line 86:		Line 86:

	== User privileges ==		== User privileges ==
			Check if your database user account has sufficient privileges by running <code>show grants for user@localhost;</code>.
			You can assign specific privilege for executing events by running <code>GRANT ALL PRIVILEGES ON `your_database_name`.* TO 'your_username'@'localhost';</code>.
			Alternatively, you can assign all privileges to your database user account by running <code>GRANT ALL PRIVILEGES ON `your_database_name`.* TO 'your_username'@'localhost';</code>.
			In both cases, run additionally <code>FLUSH PRIVILEGES;</code>.

	== Restart of MariaDb ==		== Restart of MariaDb ==

Uwe at 07:08, 2 August 2025

2025-08-02T07:08:04Z

@@ Line 1: / Line 1: @@
 Over time, the size of the following four tables grows: <code>refill</code>, <code>ballingdosinglog</code>, <code>feedlog</code>, <code>data</code>.
@@ Line 41: / Line 42: @@
 For this reason, the size needs to be limited by deleting old entries.
 Activate the event scheduler by adding the two last lines of below example to MariaDb configuration file (usually <code>/etc/mysql/my.cnf</code>).
@@ Line 82: / Line 84: @@
 row in set (0.002 sec)
 </pre>

Uwe at 07:03, 2 August 2025

2025-08-02T07:03:51Z

@@ Line 70: / Line 70: @@
 [mysqld]
 event_scheduler=ON
 </pre>