MySQL msg_log: Automate truncation

As a systems administrator, I would like us to automate our currently manual truncation of the msg_log table.

Acceptance Criteria

[ ] Existing users of the IOC Log feature are consulted on their use cases and the age of data they require to be available (see below) and we agree on a retention period
[ ] An automated process is created which runs regularly (<=daily) and checks the msg_log table for entries older than the retention period. A process similar to the current truncation script is then followed with them being removed from the table. This needs to be able to run silently from the perspective of the instrument scientist - not interfering with data taking.
[ ] Appropriate checks are in place that this script is running appropriately
[ ] The process is documented in the developer wiki

Extra Information

We currently have a truncate database script which we run manually when disk space is running out on Instrument PCs, or between cycles as part of our housekeeping. This dumps the table contents to a file, then removes them from the DB. This takes up development/support time and its automation would increase efficiency.

By regularly running a process to remove data over x days old, we will give certainty of what age of detail will be available in the Ibex GUI, whilst reducing the effort required in system maintenance.

In order to define how long we keep records in the DB, we need to balance the needs of the users against the disk available. It is believed the current users of the IBEX GUI feature this table supports (IOC Log) are the Muon beam-lines and technical groups. As such we should understand their needs before making the change. It is suggested a good starting point for consultation would be retaining records up to 30 days old (increasing this if there is a clear use case, decreasing it if we find this still results in manual intervention being required to reduce disk usage).

This ticket is a result of the discussion that took place as part of #8321 .

How to Test

verbose instructions for reviewer to test changes (Add before making a PR)

Discussed ~00:43 20/06/24

There is a fairly comprehensive README.md file inC:\Instrument\Apps\EPICS\ISIS\IocLogServer\master\tests

For new systems with no pre-exiting database, the described test procedure can be invoked once the msg_log database has been built by running: C:\Instrument\Apps\EPICS\SystemSetup\confg_mysql.bat.

For systems with an existing msg_log database, there is a SQL script which simply creates the SQL event and procedures necessary for the automatic truncation, without affecting any existing database content: cd C:\Instrument\Apps\EPICS\ISIS\IocLogServer\master C:\Instrument\Apps\MySQL\bin\mysql.exe -u root --password=<db root password> < log-truncation-schema.sql

Note that the checks that the script is running correctly is by examining a new database table debug_messages, which is emptied before each truncation process. Details of the process are recorded in the table as simple text, which includes information on the progress of the binary search procedure.

ISISComputingGroup / IBEX