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.
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.
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.