History: configuring

What gets recorded

mlogger records history data from two places in the ODB (see Data sources):

• the Variables directory of each equipment whose Common/Log history is greater than 0, recorded when it is written but at most once every Log history seconds
• the links in each subdirectory of /History/Links (virtual events), recorded every time one of the linked keys is written

Only numbers are recorded: strings and 64-bit integers are skipped.

mlogger does notice when the size of the data it records changes (e.g. an array in Variables gets more elements, or a key is added to Variables when using per-equipment history). The next time Variables is written, it redefines all the history events, and logs a harmless "struct size mismatch" error. With per-variable history, a new key in Variables doesn't change the size of any recorded data, so it isn't recorded until mlogger restarts.

If you make an array in Variables longer, you must make its Settings/Names array (if any) longer first. Otherwise mlogger stops with an "Array size mismatch" error as soon as the array is written, and refuses to start again until the names are fixed.

Per-variable vs per-equipment

When recording history data in files (rather than an SQL database), you can choose whether to record all your equipment's variables at once, or only record the variables that are written to.

Per-variable history

If Variables/x is modified but Variables/y is not, only Variables/x will get recorded in the binary file. You get a history "event definition" for each variable separately.

Per-equipment history

If Variables/x is modified but Variables/y is not, both Variables/x and Variables/y will get recorded in the binary file. The history "event definition" includes all the variables for the equipment.

For the file-based history, you can choose which option is used using the ODB key /Logger/History/xxx/PerVariableHistory (where xxx is MIDAS or FILE).

Recording other ODB keys (virtual events)

To record ODB keys outside /Equipment, create a subdirectory in /History/Links. Each subdirectory becomes a "virtual" history event, named after the subdirectory.

Each subdirectory should contain symlinks to other ODB keys that you wish to log. The links can point to individual numbers or arrays, but may NOT link to other directories.

These "virtual" events are recorded whenever an ODB key is written to - there is no built-in throttling by mlogger. You should make sure that you do not update the keys too frequently, so that data storage does not grow too large.

mlogger will complain (and refuse to start) if the target of a link at /History/Links/xxx/yyy does not exist.

If you have an equipment called Trigger, mlogger creates a System subdirectory, with links to the event rate and data rate of that equipment. You can delete it, but mlogger creates it again the next time it starts.

Data destinations (history drivers)

History data can be stored either as binary files on disk or in an SQL database. There are multiple drivers in the history system that determine where the data gets logged. You can enable multiple drivers if you wish (e.g. logging to both binary files and an SQL database).

Driver	Type	Notes	Supported by web interface	Supported for analysis on main midas host	Supported for analysis on other machines
FILE	File (.dat)	Default			Limited
Midas	File (.hst/.idx/.idf)	Deprecated			Limited
MYSQL	SQL (MySQL)
PGSQL	SQL (PostgreSQL)
SQLITE	SQL (SQLite)
ODBC	SQL (any)

Settings for all drivers are found in the ODB at /Logger/History/xxx. The exact keys differ (and are explained below), but all of them contain a boolean /Logger/History/xxx/Active key that enables/disables the driver.

History files

There are 2 history drivers that write to binary files - the legacy midas driver creates .hst, .idx and .idf files, while the FILE driver just creates .dat files.

Where to write files

There are three ways to specify where history files should get written. The first ODB entry that is not an empty string gets used:

1. /Logger/History/FILE/History dir (for the FILE driver) or /Logger/History/MIDAS/History dir (for the midas driver).
2. /Logger/History dir
3. /Logger/Data dir

File names

SQL databases

There are 4 history drivers available for writing to SQL databases:

• MYSQL writes to a MySQL or MariaDB database. MariaDB is an open-source fork of MySQL.
• PGSQL writes to a PostgreSQL database.
• SQLITE writes to an SQLite database. SQLite is very low overhead but does not support remote connections - it is not very suitable if you want to run SQL queries from a separate analysis machine. It is fine if you only plan to query from the main midas machine or via the web interface.
• ODBC can (in principle) write to any database engine that supports Open Database Connectivity. But see the important warning below.

Installing the database server

How you install the database software depends on your operating system. For PostgreSQL, SQLite and ODBC, see their own documentation. For MySQL, these are the steps on two common systems:

Creating the database and users

CREATE DATABASE IF NOT EXISTS history;
CREATE USER history_reader@'localhost' IDENTIFIED BY 'CHANGE_THIS_PASSWORD!';
GRANT SELECT ON history.* TO history_reader@'localhost';
CREATE USER 'history_writer'@'localhost' IDENTIFIED BY 'CHANGE_THIS_PASSWORD!';
GRANT SELECT,INSERT,CREATE,ALTER,INDEX ON history.* TO 'history_writer'@'localhost';
FLUSH PRIVILEGES;

mysql -u history_reader -p
mysql -u history_writer -p

CREATE DATABASE history;
\connect history
CREATE USER history_reader WITH PASSWORD 'CHANGE_THIS_PASSWORD!';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO history_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO history_reader;
CREATE USER history_writer WITH PASSWORD  'CHANGE_THIS_PASSWORD!';
GRANT SELECT,INSERT ON ALL TABLES IN SCHEMA public TO history_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT ON TABLES TO history_writer;
GRANT CREATE ON SCHEMA public TO history_writer;

Re-compiling midas if needed

If you installed your database software after compiling midas, you may need to re-compile midas so that it includes support for your database.

CMake prints a line for each database it finds, e.g.:

-- MIDAS: Found MySQL version 8.0.41

If it prints e.g. MySQL not found instead, midas is built without support for that database.

Defining the connection

server=127.0.0.1
port=3306
database=history
user=history_reader
password=CHANGE_THIS_PASSWORD!

mh2sql --mysql mysql_writer.txt

database=history
server=localhost
port=5432
user=history_reader
password=CHANGE_THIS_PASSWORD!

[ODBC Data Sources]
history_writer = Midas History Writer DSN
history_reader = Midas History Reader DSN

[history_writer]
Driver       = /usr/local/lib/libmyodbc9w.so
SERVER       = localhost
USER         = history_writer
Password     = CHANGE_THIS_PASSWORD!
Database     = history
OPTION       = 3

[history_reader]
Driver       = /usr/local/lib/libmyodbc9w.so
SERVER       = localhost
USER         = history_reader
Password     = CHANGE_THIS_PASSWORD!
Database     = history
OPTION       = 3

Enabling the driver

If you no longer want to write the history with the driver you used before (e.g. FILE), set its Active key to false. Then restart mlogger.

Check the message log for any errors. If mlogger can't connect to the database, it will print a message but will continue logging data to midas files and other active history drivers.

The first time mlogger connects to a new database, it creates the tables and columns it needs, and prints a message for each one. With MySQL, it also prints an error because the history index table doesn't exist yet, which you can ignore:

[Logger,ERROR] [history_schema.cxx:1285:Mysql::Prepare,ERROR] mysql_query(SELECT event_name, table_name, itimestamp FROM _history_index WHERE table_name!='' AND column_name IS NULL;) error 1146 (Table 'history._history_index' doesn't exist)
[Logger,INFO] Adding SQL table "myperiodicequipment_pydt"
[Logger,INFO] Adding SQL table "_history_index"
[Logger,INFO] Adding column "event_name" to SQL table "_history_index", status 1
...

To check that data is being written, log in to the database and select some recent values. Each table has an _i_time column with the time as a Unix timestamp, and a column for each variable (see SQL databases for the full schema):

mysql> SELECT _i_time, pydt_0 FROM myperiodicequipment_pydt ORDER BY _i_time DESC LIMIT 3;
+------------+---------+
| _i_time    | pydt_0  |
+------------+---------+
| 1793319815 | 5.89511 |
| 1793319814 | 6.63168 |
| 1793319813 | 5.96647 |
+------------+---------+

To show the history plots from the database, see Default history driver to read from.

If you change your connection settings, you must restart mlogger for it to pick up the new settings.

Database schema

Details of the database schema that midas creates is found in History file formats and database schema.

Default history driver to read from

There are several ways to read data from the history database, including a graphical web interface for displaying plots, embedding plots in your own custom webpages, and command-line tools for dumping data. These all seamlessly handle reading data from any of the history drivers.

If you are writing to multiple destinations, you can choose which one is read from using the /History/LoggerHistoryChannel ODB key.

If this ODB key is empty, the first entry in /Logger/History that has the Active key set to true will be used.

Web display history panels

Panels are usually created and edited on the settings page of the web interface (see Defining plot groups and panels), and are stored in the ODB in /History/Display/<group>/<panel>. The same settings page is used for the History and OldHistory pages, but some settings only affect one of them.

Panel-wide settings

The Show factor setting has no effect on either plot. It is only used by the old settings page (the Edit in old editor button), to show the factor and offset columns.

Per-variable settings

Display formula

You will often want to display multiple related variables that have very different numerical ranges. For example, you may have a voltage that ranges from 0-1200V and a temperature that ranges from 20-30C. Both the "old history" and "new history" plots allow you to manipulate the displayed values, but in different ways:

plotted_value = offset + factor * (history_value - voffset)

Other ways to define a panel

As well as using the settings page, you can also create panels by:

• copying/pasting an existing panel using the ODB tools (e.g. the odbedit command-line tool or the ODB web interface)
• writing code, as described in History: programming

See /History/Display for the full list of ODB keys in a panel.

Converting legacy history files to another format (mh2sql)

mh2sql allows you to convert .hst files written by the MIDAS driver into another format - either .dat files from the FILE driver, or an SQL database. See mh2sql in the reference for the full list of options.

The --odbc, --sqlite, --mysql --pgsql and --file arguments determine where the data gets written to. If you do not specify any of these, the default history driver as specified in the ODB is used.

Note that you must manually specify the .hst files to process. If converting a large number of files, you should ensure you process them in the order that they were created.

SQL history database surgery

The MySQL/Postgres history databases may sometimes contain extra columns or data that you don't want.

The databases contain tables for each equipment, with a column for each variable. It also contains an extra table called _history_index that tracks which tables/columns were active at given times (e.g. tracking when variables were added/removed from the history system).

Background on how mlogger parses the history database

The relevant code lives in SqlHistory::hs_define_event() in history_odbc.cxx.

When mlogger connects to the database (hs_connect()), it does this:

status = fSql->ListTables(&tables);
for (unsigned i=0; i<tables.size(); i++) {
    if (tables[i] == "_history_index") {
        fHaveIndex = true;
        break;
    }
}

It scans the list of tables from MySQL only to check whether _history_index itself exists — it does not read the contents of _history_index into the in-memory global index (gHistoryIndex) at this point. That global index starts completely empty after hs_connect().

When mlogger encounters a new equipment, it calls hs_define_event(). The function uses a lazy-read strategy:

1. Cleans up any stale in-memory Event* objects for that event name.
2. Creates a new Event* and, if needed, issues CREATE TABLE _history_index (...) — handling DB_KEY_EXIST gracefully if it's already there.
3. Then it looks for an IndexEntry for this event name in gHistoryIndex. Since the global index was not pre-populated at connect time, this lookup returns nothing for a new equipment.
4. It then calls ReadIndex(fSql, event_name) to query _history_index by event name. For a genuinely new equipment that has never been logged before, this query also returns nothing.
5. Since no index entry is found, the code concludes that this equipment has no table and proceeds to issue CREATE TABLE <table_name> — without IF NOT EXISTS.

Detecting duplicate timestamps

Display all duplicate timestamps and the number of occurrences in the table

SELECT _i_time, COUNT(_i_time) FROM table_name GROUP BY _i_time HAVING COUNT(_i_time) > 1;

Deleting a table from the database

If we just delete the table, mlogger will complain that the table does not exist, because the table is still present in the _history_index.

1. View the entries related to a table called "mytable", useful so we don't delete anything unwarranted by accident

   SELECT * FROM _history_index WHERE table_name="mytable";
   

2. Delete those entries, now that we've verified which rows will be deleted:

   DELETE FROM _history_index WHERE table_name="mytable";
   

3. Drop the table from the database

   DROP TABLE mytable;
   

Rename a table in the database

In this section we cover how to rename a table in the database. This is equivalent to changing the name of the equipment (say, if you make a typo or decide on a better naming scheme).

1. Stop the logger and the epics frontend.

2. Rename the equipment in the ODB

   odbedit -c 'rename "/Equipment/old_name" "new_name"'
   

3. Rename the equipment in the frontend and recompile (if needed)

4. Rename the table in the database

   RENAME TABLE old_name TO new_name;
   

5. Change entries in the history index

   UPDATE _history_index SET table_name = "new_name" WHERE table_name = "old_name";
   

   Check that values changed:

   SELECT * FROM _history_index WHERE table_name="new_name";
   

6. Restart equipment frontend and mlogger. You might get some errors from mlogger, something like Cannot define event "EpicsTest/TEST", hs_define_event() status 702. If so, restart it again and the errors should clear.

7. Check that values in the table are updating (most recent value printed at the top)

   SELECT * FROM new_name ORDER BY _i_time DESC LIMIT 10;
   

8. Check for odd column or table names (suffix with a timestamp) - there should be messages from mlogger warning you about the create of these when the program is started.

   SHOW COLUMNS FROM new_name;
   SHOW TABLES;
   

If these exist, stop the logger, delete the columns. Delete or rename the corresponding entries in _history_index (as needed), then restart the logger.

Recreating the history index file

If you really screw up the history index file you can recreate it from scratch:

1. Stop mlogger

2. Delete the history index file

   DROP TABLE _history_index;
   

3. Start then stop mlogger. This will recreate the _history_index file properly, but all the table names will have timestamps appended to them, since the tables already exist.

4. Change the table names in the index

   UPDATE _history_index SET table_name = "new_name" WHERE table_name = "old_name";
   

5. Drop the bad tables

   DROP TABLE old_name;
   

6. You might have to do something similar for the column names, if it's a problem. In addition to the above commands, the following may be useful:

   ALTER TABLE table_name DROP COLUMN column_name;
   

7. You need to ensure that itimestamp in _database_index predates the earliest timestamp in each column

   Get earliest timestamp in the table

   SELECT MIN(_i_time) FROM table_name;
   

   Set that time (or ideally a slightly earlier one) in the schema table:

   UPDATE _history_index set itimestamp = timestamp_value where table_name = "table_name";
   

8. When everything looks clean, restart the logger and pay attention to the messages. If everything goes smoothly, there should be no errors. Check the database again for oddities.

Merging columns from different tables

In this scenario we want to reorganize our tables: we will move columns from multiple tables into one, then assign that new table to an equipment. We assume that the table we are copying into is not yet in the _history_info schema - i.e. mlogger has yet to try to write to the table. We can summarize the steps as follows:

1. Create the new equipment, run it and mlogger to set up the _history_info schema and the table.
2. Delete all the data out of the table (optional).
3. Copy the new data into the table.
4. Clean up the timestamps (optional)
5. Drop duplicate rows
6. Clean up the schema info in _history_info.
7. Start up the equipment and logger again.

DELETE FROM new_table;

INSERT INTO 
    new_table
        (_t_time, 
        _i_time, 
        column1, 
        --  ...
        columnN) 
SELECT
    _t_time,
    _i_time, 
    column1,
    -- ...
    columnN
FROM 
    old_table;

+------------+-----------------------------+-----------------------------+
| _i_time    | ucn2_he3_ts109_rdtempk_test | ucn2_he4_pt202_rdpress_test |
+------------+-----------------------------+-----------------------------+
| 1778002480 |                        NULL |                     875.671 |
| 1778002481 |                     291.293 |                        NULL |
| 1778002490 |                        NULL |                     853.388 |
| 1778002491 |                     291.293 |                        NULL |
| 1778002500 |                        NULL |                     1011.77 |
| 1778002501 |                     291.293 |                        NULL |
+------------+-----------------------------+-----------------------------+

UPDATE new_table 
SET 
    _t_time = FLOOR(_t_time/10)*10,
    _i_time = FLOOR(_i_time/10)*10; 

ALTER TABLE new_table ADD COLUMN id INT NOT NULL AUTO_INCREMENT KEY;

UPDATE new_table v1
INNER JOIN new_table v2 ON v1._i_time = v2._i_time AND v1.id != v2.id
SET 
    v1.column1 = COALESCE(v1.column1, v2.column1), 
    -- ...
    v1.columnN = COALESCE(v1.columnN, v2.columnN);

DELETE v1 FROM new_table v1
INNER JOIN new_table v2
WHERE
    v1.id > v2.id AND 
    v1._i_time = v2._i_time;

ALTER TABLE new_table DROP COLUMN id;

SELECT MIN(_i_time) FROM table_name;

UPDATE _history_index set itimestamp = timestamp_value where table_name = "table_name";