• View
• Edit
• History
• Print

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)