Project

General

Profile

Online Tracker Databases : Hardware and SlowControl

At present we have two databases:

  • mysql/gm2sc postgres/gm2tracker_sc : storing the slow control data and a subset of the MIDAS ODB variables e.g. run start & end times, run comments. The latter should probably ultimately migrate to an experiment wide solution.
  • mysql/gm2hardware postgres/gm2tracker_hw : this stores the largely fixed quantities for al the hardware eg the serial numbers of hardware, where it is physically located and what piece of hardware is connected to what e.g. which straws each ASDQ is connected to and which flexi the ASDQ is connected to and so on, all the way through to the AMC-13 and the PCIE card in the DAQ PC.

There are mysql instances on gm2straw6, g2tracker0, daq1 and the postgres instance is on ifdb04.fnal.gov. The SQL is vanilla and the same SQL is used for both databases with different connection parameters.

Connecting

The easiest way to connect is via an ssh tunnel to gm2straw6 or g2tracker0. You will need to ensure your kerberos username is in the .k5login of the gm2 (gm2straw6) or the daq (g2tracker0) account and your ssh credentials in ~/.ssh/config are something like:

Host                 gm2straw6 
HostName             gm2straw6.fnal.gov
User                 gm2

Host                 g2tracker0 
HostName             g2tracker0.fnal.gov
User                 daq

Host gm2straw6 g2tracker0
GSSAPIAuthentication      yes
ForwardX11Trusted         yes
ForwardX11                yes
GSSAPIDelegateCredentials yes

and that you have a valid kerberos ticket and then create the tunnel to the mysql database with the command:

ssh -L 3306:localhost:3306 gm2straw6 -N  # or g2tracker0

and the tunnel to postgres database with the command:
ssh -L 55556:ifdb04.fnal.gov:5445 gm2straw6 -N  # or g2tracker0

The postgres database can be accessed directly from any .fnal.gov domain. But the above ssh tunnel allows access from everywhere via a DB connection to localhost:55556.

You can then talk to the database using:

mysql --user=gm2user -h 127.0.0.1 -P 3306 --password=xxxxx --default-character-set=utf8 --database=database_name [ie gm2sc or gm2hardware]  

psql -U gm2_reader -d gm2_conditions_dev -h localhost -p 55556 # connect via ssh tunnel
psql -U gm2_reader -d gm2_conditions_dev -h ifdb04 -p 5445 # connect directly if inside fnal.gov

For postgres the password is stored in the ~/.pgpass file. There is an example on gm2straw6. If you do not have a .pgpass file or --password= is not used then you will be prompted for a password. For postgres there are also gm2_admin and gm2_writer accounts.

If you are on a mac and have the latest mySQL client software installed via homebrew then you can create a secure login to the mySQL database via:

 
mysql_config_editor set --login-path=gm2sc_127.0.0.1_gm2user --host=127.0.0.1 --user=gm2user --password

and this is stored in ~/.mylogin.cnf and you can then login without specifying a password with the command of the form:

mysql --login-path=gm2sc_127.0.0.1_gm2user --database=gm2sc --default-character-set=utf8

Unfortunately this only comes with the later mySQL clients which can't be installed on the SLF6 version we have on gm2straw6.

Python connection

You have to ensure you have the ssh tunnel setup to the database and then you can have the following in your code:

# import the SQL helper functions for the slow control database
from SlowControls.DB.SQL import SQL

# or the hardware database: from HardwareDB.SQL import SQL

# Database connection details 

sql_options = ["mysql", host, "gm2user", "alpha_2pi", port, "gm2sc"] # e.g. host=127.0.0.1, port=3306
# or for the postgres instance
sql_options = ["postgres", host, "gm2_reader", "******", port, "gm2_conditions_dev"] # e.g. host=localhost, port=55556

# Connect to database and get a handle. The first argument sets the debug flag to True or False.
sql = SQL(False,sql_options)

# The file gm2trackerdaq/software/python/SlowControls/DB/SQL.py has a lot of utility routines that can then be called e.g.

times = sql.getRunTimes() # returns a dictionary of the start and end times of all the MIDAS runs.

# The file gm2trackerdaq/software/python/HardwareDB/SQL.py has the helper functions for the hardwareDB

Slow Control DB Web Display

This is a python Flask application that creates a web-server on the machine where the python is run. So it can be run from a laptop but in general we will have it running continually on gm2straw6 and creating the web instance: http://gm2straw6.fnal.gov:5000/

To view the gm2straw6 or g2tracker0 webpage you must proxy your browser to gm2straw6 (g2tracker0) via:

ssh -Nn -D 55555 gm2straw6

and set the proxy settings to socks v5, host: localhost, port: 55555 and make sure you have a kerberos ticket and the ssh tunnel to the gmstraw6 database setup. Note in the above "gm2straw6" is defined via the .ssh/config detailed above.

The web server and web-page instance is started by:

python gm2trackerdaq/software/python/Flask/DisplaySlowControls.py --dbType=postgres --dbHost=localhost:55556  # if using ssh tunnel
python gm2trackerdaq/software/python/Flask/DisplaySlowControls.py --dbType=postgres --dbHost=ifdb04:5445  # if connecting from g2tacker0 
python gm2trackerdaq/software/python/Flask/DisplaySlowControls.py --dbType=mysql --dbHost=localhost:3306  

If you run the server locally then simply connect to http://0.0.0.0:5000/

Schema for the Databases

This is in git as:
gm2trackerdaq/software/python/HardwareDB/gm2hardware-schema.sql for the HardwareDB with gm2hardware-SetDefaultValues.sql setting up the constant identifiers used and as:
gm2trackerdaq/software/python/SlowControls/DB/gm2sc-schema-mysql.sql or gm2sc-schema-psql.sql for the slowcontrol database.

The HardwareDB ER is https://cdcvs.fnal.gov/redmine/attachments/download/36162/DB-Schema.png and the SlowControlDB ER https://cdcvs.fnal.gov/redmine/attachments/download/36161/DB-Schema.png.