Livepage Database
The accumulated live page data is stored in several tables in mySQL databases that
are located on fourier. The two databases are called mt_pleasant_26 and ceduna_30.
For each database there are two tables: drives and telescope. The drives tables
appear to be unused (it used to be a table containing information about the state of the drives of each telescope, and was
updated once every 30 to 40 seconds). The telescope table contains information about
where the telescope is pointing, what it is doing, the weather etc., and is update once
every 5 seconds.
To access the database, you must log in to fourier and give the command: mysql -u root -p
The mysql server will prompt you for a password: enter the root password for fourier.
To select the database you wish to access, once you are in the mysql console, give the
command use mt_pleasant_26, or use ceduna_30.
To view the structure of the tables, give the command describe drives; or
describe telescope;. This will give you information about the fields in the tables, along
with their datatypes.
Please note: 'time_ut_date' appears to be local date not UT date and therefore should not be used in combination with 'time_utc' for determining the date and time of an entry. It is best to use the 'time_julian_day' or 'time_ut_day_number' + 'time_utc' instead.
This wiki page will not go into detail about how to make a general mySQL query - there are plenty of web pages out there for that. Instead we will give some commonly used queries that are useful.
The most common query will return the last entry in the telescope table. The important thing to know
is that every time a new table entry is made by the telmon_interface clients on newsmerd
and sille, the id field in the telescope table is automatically incremented by 1; the latest
entry in the table will therefore always have the largest id number. (The id field is also
present in the drives table and works the same way). Thus, to get all the latest data from the
telescope table, issue the following query:SELECT * FROM telescope ORDER BY id DESC LIMIT 1;
This query says that it should return all fields (SELECT *) from the table telescope (FROM telescope),
and that it should sort the entries by the id number in a descending order (ORDER BY id DESC), and that
it should only return the first result (LIMIT 1).
If you know what data you're interested in you can select only that. Say you want to know what the temperature
is doing, then you might issue the query:SELECT time_ut_day_number,time_utc,weather_temperature FROM telescope ORDER BY id DESC LIMIT 12;
This should give us the temperature over the last minute (since each entry is 5 seconds apart), and the
output looks like this:
+--------------------+----------+---------------------+ | time_ut_day_number | time_utc | weather_temperature | +--------------------+----------+---------------------+ | 260 | 06:14:09 | 10 | | 260 | 06:14:03 | 10 | | 260 | 06:13:58 | 10 | | 260 | 06:13:52 | 10 | | 260 | 06:13:46 | 10 | | 260 | 06:13:41 | 10 | | 260 | 06:13:35 | 10 | | 260 | 06:13:30 | 10 | | 260 | 06:13:24 | 10 | | 260 | 06:13:19 | 10 | | 260 | 06:13:13 | 10 | | 260 | 06:13:08 | 10 | +--------------------+----------+---------------------+
Obviously this is of limited use for temperature though. More useful is to instead take only every 100th
entry, which would give us the temperature for the past 100*12*5 seconds = 6000 seconds = 1h 40m.SELECT time_ut_day_number,time_utc,weather_temperature FROM telescope WHERE right(id,2)=00 ORDER BY id DESC LIMIT 12;gives:
+--------------------+----------+---------------------+ | time_ut_day_number | time_utc | weather_temperature | +--------------------+----------+---------------------+ | 260 | 23:06:24 | 8 | | 260 | 22:57:01 | 7.6 | | 260 | 22:47:39 | 7.2 | | 260 | 22:38:13 | 6.9 | | 260 | 22:28:52 | 6.6 | | 260 | 22:19:30 | 6.3 | | 260 | 22:10:09 | 5.8 | | 260 | 22:00:49 | 5.3 | | 260 | 21:51:26 | 4.9 | | 260 | 21:42:03 | 4.7 | | 260 | 21:32:39 | 4.5 | | 260 | 21:23:14 | 4.4 | +--------------------+----------+---------------------+
These are just some simple queries to get you started.
The databases and the tables themselves are stored as files in the location /var/lib/mysql/ceduna_30/* and /var/lib/mysql/mt_pleasant_26/*. Copy the files in these directories for backup purposes.
Permissions on the live page database tables are quite restrictive. As expected the root
user can log in from anywhere and alter any of the tables in any way. For the client machines,
insertions to the telescope tables can only be made by the antmon user from either
newsmerd (to the mt_pleasant_26 database) or from sille (to the ceduna_30 database).
Likewise, insertions to the drives tables can only be made by the drivesmon user from
either hobaldor (to the mt_pleasant_26 database) or from cdbaldor (to the ceduna_30
database). Querying can only be done from ares by the livepage user. The passwords for these
users can be found.
If you see an error such as "database is marked as crashed and must be repaired", (say after a crash of ares), then to repair the databases, login to ares as root cd /var/lib/mysql/mt_pleasant_26 and then issue the command myisamchk -r -q telescope.MYI or drives.MYI
This will ultimately all stop working in 2021
Currently the last table is telescope_2021_Dec and so the live pages will stop working at the end of 2021. To create future tables, in mysql type: create table telescope_2022_Jan like telescope_2016_Dec; and then do this another 11 times for the entire year. (/snark-on That's right, build in a failure so the programmer has to be re-employed to fix it, and while doing this also make your months use letters not numbers to make scripting painful. /snark-off)