Setting Up Local Calibration Database

To make Gleam entirely independent of the network:

• Make a local copy of calibration files. Depending on the application it may not be necessary to copy all files (if, for example, the analysis jobs to be run are always for a single instrument, such as cu06). For a complete mirror, it's best to automate the process, e.g. with rsync.

• Run a local MySQL server which has a copy of the metadata database, either via standard MySQL mirroring or from a one-time dump and restore. Since GSI running coincides precisely with the scheduled SCS outage, a one-time dump and restore is just as good as mirroring and simpler to implement.

These instructions are for a Linux RHEL3 machine, but most should carry over to other architectures and OS versions. They assume there is no MySQL server already running on the system.

Getting and Installing MySQL

We use MySQL version 4.1 (latest patch number available). You can get rpms for RHEL3 or RHEL4. On my RHEL3 laptop I downloaded and installed rpms for the Server, Client, Shared compatibility libraries, and Headers and libraries from the MySQL download page. They are probably not all necessary but you should install at least the first two.

Account management

If you install the rpms, the server will be started automatically and also will restart on its own after a reboot. There is no password initially for the root account; the first thing you should do is to set one using one of the methods described in the manual.

Next run the mysql client program as root and set up the standard calibration account glastreader, all that is required for running Gleam. It needs only the SELECT privilege.

mysql> grant SELECT on calib.* to 'glastreader'@'%' identified by '???';  
mysql> grant SELECT on calib.* to 'glastreader'@'localhost' identified by '???';  

where ??? is replaced by the standard glastreader password. If you might need to enter new calibrations after the initial dump from the SLAC master database, you should also create the standard calibrator account. calibrator needs SELECT, INSERT and UPDATE.

mysql> grant SELECT,INSERT,UPDATE on calib.* to 'calibrator'@'%' identified by '!!!';  
mysql> grant SELECT,INSERT,UPDATE on calib.* to 'calibrator'@'localhost' 
       identified by '!!!';  

where !!! has been replaced by the usual calibrator password.

Finally, to avoid using the all-powerful root account, you might want to create a maintainer account which has all privileges, but only on the calib database (or calib and calib_user if you decide you want both). This account will be used for the initial filling of the database from the dump:

mysql> grant all privileges on calib.* to 'dbmaint'@'%' identified by '***';  
mysql> grant all privileges on calib.* to 'dbmaint'@'localhost' identified by '***';  

Creating a Calib database

Use dbmaint or similar account if you've created it, otherwise root:

$ mysql -u dbmaint -p  
Enter password:  
Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is 1 to server version: 4.1.21-standard     


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.    


mysql> create database calib;  
Query OK, 1 row affected (0.03 sec)
     
mysql>   

Do the same for calib_user if you want a spare database to play with. In that case you should also have granted privileges on this database to glastreader and calibrator.

Filling the dbs

Dump contents of master database

This requires privileges on the SLAC calib database beyond what the standard calibration accounts have. Ask Joanne or Navid to do it.

Takes a command something like

mysqldump -h glastDB.slac.stanford.edu -p calib metadata_v2r1 > /scratch/junk/dump_calib.sql  

Restore locally

Assume the file created above has been copied to your working directory.

$ mysql -u dbmaint -p   
Enter password:  
Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is 1 to server version: 4.1.21-standard 
    
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.     


mysql> use calib;  
Database changed;
    
mysql> show tables;  
Empty set (0.00 sec)    

mysql> source dump_calib.sql;
    .
    .  
[a bunch of informational output]    

mysql> show tables;  
+-----------------+  
| Tables_in_calib |  
+-----------------+  
| metadata_v2r1   |  
+-----------------+  
1 row in set (0.00 sec)    

Copy files

Make a tarball of the files at SLAC that you'll need, and transfer it to a disk accessible to the machine where you'll be running Gleam. Something like the following picks up almost all CU06 files:

jrb@noric04 $ cd $LATCalibRoot  
jrb@noric04 $ pwd  
/afs/slac.stanford.edu/g/glast/ground/releases/calibrations
jrb$noric04 $ tar -cvf ~myusername/somedir/calibs.tar .  
jrb$noric04 $ cd ~myusername/somedir  jrb$noric04 $ gzip  calibs.tar

You might need to also pick up TKR default files, which are in $LATCalibRoot/TKR itself, not in the CU06 subdirectory.

Then unpack at the other end. As for any machine without access to SLAC afs space, you'll need to define an environment variable LATCalibRoot with value equal to the root directory where calibration files are stored.

Using an alternate server from Gleam

If the instructions above have been followed, accounts, database name and table name for the local server are the same as they are for the SLAC server. The only difference is the host name. You tell Gleam about this by means of the job options parameter Host. You'll need a line like this:

     CalibMySQLCnvSvc.Host="localhost";  

or

     CalibMySQLCnvSvc.Host="localhost.localdomain";  

If neither of those work, spell out the full name, e.g.

     CalibMySQLCnvSvc.Host="dhcp-130-205.slac.stanford.edu";  

When you run Gleam, look for a line in the log file saying that the job has successfully connected
to a MySQL host. LATCalibRoot has to be properly defined for the Gleam process to find the files.

Documentation of all calibration infrastructure job options can be found in the Doxygen mainpage for CalibSvc.

Using an alternate server from rdbGUI

Bring up rdbGUI and lick on File, Open DB Schema.. as usual. Then click on Session, Open connection and make a new profile. It can be just like the standard profile for glastreader (or calibrator if you need to make new entries) except glastDB.slac.stanford.edu should be replaced with whatever you've been using for host when you invoke mysql or when you run a Gleam job. Port is still 0. Be sure to click on the Log tab in the output window to confirm that the connection was successful.

References

Using the mysql client program

The program mysql allows you to issue sql commands to the server directly and interactively. It's not recommended for standard insert or update operations to a calibration metadata database—extra layers of software in rdbGUI do a better job of handling certain columns—but it can be very useful for initial set-up (described above) or for certain kinds of browsing.

Invocation options

The ones you are most likely to need are -h (long form --host=), -u (long form --user=)and -p. These options are also used for other utility programs like mysqldump.

host defaults to the machine you're logged onto. Depending on exactly how the server and accounts have been set up, you might have to specify a host of localhost orlocalhost.localdomain even if the server is on the same machine. user defaults to your unix user name, not what you want in this case because if you've followed the directions above for setting up accounts there is no mysql user with your unix user name.

If you specify -p with no value on the command line, you will be prompted for a password. This is the recommended way to invoke mysql. You may also supply a single parameter: the name of the database you will be browsing or manipulating. Alternatively you can specify this once you are in mysql with a use statement.

A few useful commands

mysql> create database calib;  
Query OK, 1 row affected (0.03 sec)

Make a new database; that is a container for a collection of tables which may refer to each other with foreign keys. It starts out without any tables in it

mysql> show databases;  
+-----------+  
| Database  |  
+-----------+  
| calib     |  
| mysql     |  
| test      |  
+-----------+  
3 rows in set (0.02 sec)
    
mysql> use calib;  
Database changed

Make the calib database the default for this session (until changed with another use).

mysql> source dump_calib.sql;

You can put a series of mysql commands in a file and run it with the source command. This file happens to be one which creates and fills a table.

mysql> show tables;  
+-----------------+  
| Tables_in_calib |  
+-----------------+  
| metadata_v2r1   |  
+-----------------+  
1 row in set (0.00 sec)
    
mysql> describe metadata_v2r1;  
+-------------+--------------------+------+-----+-----------+----------------+
| Field       | Type               | Null | Key | Default   | Extra          |
+-------------+--------------------+------+-----+-----------+----------------+
| ser_no      | mediumint(9)       |      | PRI | NULL      | auto_increment |
| instrument  | varchar(24)        |      |     |           |                |
| calib_type  | varchar(20)        |      |     |           |                |
     .   
     .   
     .  

23 rows in set (0.00 sec) 
   
mysql> select ser_no,flavor from metadata_v2r1 where instrument="CU06";  
+--------+---------+  
| ser_no | flavor  |  
+--------+---------+  
|    449 | vanilla |  
|    450 | vanilla |
     .
     .     
     .   |   
     557 | vanilla |  
+--------+---------+  
71 rows in set (0.00 sec)  

It's usually more convenient to do this kind of browsing from rdbGUI, but raw SQL fans can do it this way.

If you need to start over for some reason, you can issue commands which are destructive to varying degrees. Just be sure this is really what you want to do!

mysql> drop table metadata_v2r1;

mysql> drop database calib;

RTFM

MySQL is well-documented in the online manual. See especially chapters on Installing and Upgrading MySQL, Database Administration, Client and Utility Programs, and
Language Structure.

rdbGUI has a much more modest standard reference.