Page History

...

java -Dorg.hps.conditions.url=jdbc:mysql://hpsdb.jlab.org:3306/hps_conditions \  
    -Dorg.hps.conditions.user=hpsuser \
    -Dorg.hps.conditions.password=darkphoton [...]

...

The default database connection uses a read-only replica of the primary MySQL conditions database at Jefferson Lab.  Therefore, when connecting from a computer which is outside of the jlab.org domain, you will  not be able to make any changes to this database.  If you need to insert records into the database, i.e. for new calibrations, then it must be done behind the JLab firewall, and you must provide valid credentials that allow writing to the database (they are not provided here!).

Using

...

the SLAC Database

Local jobs can be run without an internet connection using the built-in support for SQLite.

A db file compatible with sqlite3 may be obtained by using the following commandsThe following connection properties can be used to connect to the SLAC conditions database:

https://github.com/JeffersonLab/hps-conditions-backup/raw/master/hps_conditions.db.tar.gz
tar -zxvf hps_conditions.db.tar.gz

The local db file can be used by using this option when running Java:

java -Dorg.hps.conditions.url=jdbc:sqlite:hps_conditions.db [...]

No username or password is required when connecting locally in this way.

java -Dorg.hps.conditions.url=jdbc:mysql://mysql-node03.slac.stanford.edu:3306/rd_hps_cond \  
    -Dorg.hps.conditions.user=rd_hps_cond_ro \
    -Dorg.hps.conditions.password=2jumpinphotons. [...] 

This database is updated only periodically, not automatically, so you may need to check if it is up-to-date before using it for your jobs.

Using a Local Conditions Database

Local jobs can be run without an internet connection using the built-in support for SQLite.

A db file compatible with sqlite3 may be obtained by using the following commands:

https://github.com/JeffersonLab/hps-conditions-backup/raw/master/hps_conditions.db.tar.gz
tar -zxvf hps_conditions.db.tar.gz

This file This file may or may not be up to date with the current master in the JLab conditions database!

Creating a Local SQLite Database

In order to create a local SQLite database, you will need to create a snapshot of the MySQL database and then convert it to a SQLite db file.   

This converter script can be used to produce the db file.

It can be downloaded using these commands:

  wget https://raw.githubusercontent.com/dumblob/mysql2sqlite/master/mysql2sqlite
  chmod +x mysql2sqlite

You can create a dump of the current conditions database using this command: 

The local db file can be used by using this option when running Java:

java -Dorg.hps.conditions.url=jdbc:sqlite:hps_conditions.db [...]

No username or password is required when connecting locally in this way.

Creating a Local SQLite Database

In order to create a local SQLite database, you will need to create a snapshot of the MySQL database and then convert it to a SQLite db file.   

This converter script can be used to produce the db file.

It can be downloaded using these commands:

  wget https://raw.githubusercontent.com/dumblob/mysql2sqlite/master/mysql2sqlite
  chmod +x mysql2sqlite

You can create a dump of the current conditions database using this command: 

mysqldump --skip-extended-insert --compact -u hpsuser --password=darkphoton -h hpsdb.jlab.org --extended-insert=false --lock-tables=false hps_conditions > hps_conditions.mysql

Now, you can load the database dump into sqlite3 as follows:

  mysql2sqlite hps_conditions.mysql | sqlite3 hps_conditions.db

You should now have an up to date copy of the master conditions database locally that can be specified on the command line.

Administration

Creating a Conditions Database Backup

The conditions database can be backed up using a command similar to the following:

mysqldump -h hpsdb.jlab.org -u$USER -p$PASSWORD hps_conditions &> hps_conditions.sql

... where $USER is replaced by your account name with the proper permissions and $PASSWORD with your password.

Loading a Conditions Database Backup

To load the database from a backup, the following command would be used.

mysql

mysqldump --skip-extended-insert --compact -u hpsuser --password=darkphoton -h hpsdb.jlab.org -u $USER --lock-tables=falsep$PASSWORD hps_conditions >< hps_conditions.mysql

Now, you can load the database dump into sqlite3 as follows:

  mysql2sqlite hps_conditions.mysql | sqlite3 hps_conditions.db

You should now have an up to date copy of the master conditions database locally that can be specified on the command line.

Administration

Creating a Conditions Database Backup

The conditions database can be backed up using a command similar to the following:

sql

The above command is for informational purposes only.  Fully restoring the database from a backup would need to go through a JLAB CCPR, as the accounts we have access to do not have all the proper permissions for doing this.

Replicating the Database to SLAC

This is a general outline of dumping the database and replicating it to the SLAC MySQL database, which is used for releases, as connecting from SLAC to the JLab database is not reliable enough.

First, from a JLab machine such as ifarm, a SQL dump should be created that includes all required statements for dropping tables (the default configuration of mysqldump should be fine).

mysqldump -h hpsdb.jlab.org -ujeremym -pXXXXXXu$USER -p$PASSWORD --disable-lock-tables hps_conditions &> hps_conditions_for_slac.sql

This file should then be copied over to SLAC.

scp hps_conditions_for_slac.sql $USER@rhel6-64.slac.

...

stanford.edu:/nfs/slac/g/hps/someDir

Finally, the SQL dump should be loaded into the SLAC database using a command similar to the following.

mysql -D rd_hps_cond -h mysql-node03.slac.stanford.edu -P 3306 -u $USER -p $PASSWORD

Loading a Conditions Database Backup

To load the database from a backup, the following command would be used.

mysql -h hpsdb.jlab.org -u jeremym -pXXXXXX hps_conditions < hps_conditions_for_slac.sql

The above command is for informational purposes only.  Fully restoring the database from a backup would need to go through a JLAB CCPR, as the accounts we have access to do not have all the proper permissions for doing thiscommand may take awhile to execute.  If it is successful, the SLAC database should contain an exact replica of the primary conditions database from JLab.

Configuring Detector Conditions for a Job

...