Project

General

Profile

DBMauto at minos-db1.fnal.gov

Contacts:

Documentation

Nick West' documentation 05/2010

FNAL Master DB export-import-checksum

Master import into offline and clean temp database
Master export from offline
Master checksum on offline and publish logs

Cron jobs

# minsoft@minos-mysql2.fnal.gov ( aka minos-db1.fnal.gov )
0   * * * * /minos/app/dbmauto/scripts/run_dbmauto.sh import > /minos/app/dbmauto/scripts/run_dbmauto_import.log 2>&1

30   * * * * /minos/app/dbmauto/scripts/run_dbmauto.sh export > /minos/app/dbmauto/scripts/run_dbmauto_export.log 2>&1

20 21 * * * /minos/app/dbmauto/scripts/run_dbmauto.sh checksum > /minos/app/dbmauto/scripts/run_dbmauto_checksum.log 2>&1
/minos/app/dbmauto/work

# lena@minos-mysql2.fnal.gov ( aka minos-db1.fnal.gov )
45 * * * * /usr/krb5/bin/kcron '/usr/krb5/bin/aklog; rsync -rvt /minos/app/dbmauto/rsync/ /afs/fnal.gov/files/data/minos/d210/rsync'
#
0 * * * * /usr/krb5/bin/kcron 'rsync -rvt /minos/app/dbmauto/rsync/ /minos/data/mysql/rsync'

Soudan export-import-checksum

cron jobs:

 30  * * * * /usr/krb5/bin/kcron /data2/dbmauto/scripts/run_export.sh         2>&1 > /dev/null
  0  * * * * /usr/krb5/bin/kcron /data2/dbmauto/scripts/run_import.sh         2>&1 > /dev/null
 30 21 * * * /usr/krb5/bin/kcron /data2/dbmauto/scripts/run_checksum.sh

Log files:

/data2/dbmauto/work/*.log

Configuration

/data2/dbmauto/scripts/export.config
/data2/dbmauto/scripts/import.config
printenv
MANPATH=/data2/dbmauto/test_rel/man:/home/software/minossoft/releases/development/man: HOSTNAME=minos-db.minos-soudan.org MINOS_SOFT=/home/software TERM=xterm SHELL=/bin/bash MAKEFLAGS=-r -I/data2/dbmauto/test_rel/include/SRT_MINOS -I/home/software/minossoft/releases/development/include/SRT_MINOS -I/data2/dbmauto/test_rel/include -I/home/software/minossoft/releases/development/include HISTSIZE=1000 SSH_CLIENT=131.225.193.32 39818 22 CVSROOT=:pserver:anonymous@minoscvs.fnal.gov:/cvs/minoscvs/rep1 OLDPWD=/data2/dbmauto/scripts SSH_TTY=/dev/pts/1 USER=lena LD_LIBRARY_PATH=/data2/dbmauto/test_rel/lib/Linux2.6-GCC:/home/software/minossoft/releases/development/lib/Linux2.6-GCC:/afs/fnal.gov/ups/dcap/v2_26_f0213/Linux+2.4/lib:.:/home/software/root/v5_18_00b/Linux+2.6/lib:/usr/lib:/usr/lib/mysql LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35: MAIL=/var/spool/mail/lena PATH=/data2/dbmauto/test_rel/bin/Linux2.6-GCC:/home/software/minossoft/releases/development/bin/Linux2.6-GCC:/afs/fnal.gov/ups/dcap/v2_26_f0213/Linux+2.4/bin:/home/software/root/v5_18_00b/Linux+2.6/bin:/home/software/minossoft/releases/boot/bin/generic:/fnal/ups/prd/ups/v4_7_2/Linux-2/bin:/usr/krb5/bin:/home/software/minossoft/releases/boot/bin/generic:/home/software/minossoft/releases/boot/bin/generic:/usr/krb5/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/lena/bin:/afs/fnal.gov/ups/cern/2004/Linux+2.6/bin SRT_DIST=/home/software/minossoft UPS_SHELL=csh INPUTRC=/etc/inputrc PWD=/data2/dbmauto/test_rel DEFAULT_SRT_DIST=/home/software/minossoft LANG=en_US.UTF-8 dbm=/data2/dbmauto KRB5CCNAME=FILE:/tmp/krb5cc_6245_zpihV16009 SHLVL=4 HOME=/home/lena LOGNAME=lena CVS_RSH=ssh SSH_CONNECTION=131.225.193.32 39818 198.124.213.10 22 LESSOPEN=|/usr/bin/lesspipe.sh %s SETUPS_DIR=/fnal/ups/etc PRODUCTS=/home/software/db:/fnal/ups/db:/afs/fnal/ups/db MACH_OS=Linux G_BROKEN_FILENAMES=1 KRBTKFILE=/tmp/tkt6245_uExPKV MAKEFILES=SoftRelTools/preamble.mk _=/bin/csh HOSTTYPE=x86_64-linux VENDOR=unknown OSTYPE=linux MACHTYPE=x86_64 GROUP=e875 HOST=minos-db.minos-soudan.org REMOTEHOST=minos-mysql2.fnal.gov SETUP_UPS=ups v4_7_2 -f Linux+2 -z /fnal/ups/db UPS_DIR=/fnal/ups/prd/ups/v4_7_2/Linux-2 BFARCH=Linux2.6-GCC BFDIST=/home/software/minossoft BFCURRENT=development SRT_PRIVATE_CONTEXT=/data2/dbmauto/test_rel SRT_PUBLIC_CONTEXT=/home/software/minossoft/releases/development SRT_MAKEFILES=SoftRelTools/preamble.mk SRT_MAKEFLAGS=-r -I/data2/dbmauto/test_rel/include/SRT_MINOS -I/home/software/minossoft/releases/development/include/SRT_MINOS -I/data2/dbmauto/test_rel/include -I/home/software/minossoft/releases/development/include SRT_SAVE_MAKEFILES= SRT_SAVE_MAKEFLAGS= SRT_SAVE_CVSROOT= SRT_SUBDIR=Linux2.6-GCC SRT_PROJECT=MINOS SRT_ARCH=Linux2.6 SRT_ENV_SET=yes SRT_LOCAL=/data2/dbmauto/test_rel SRT_BASE_RELEASE=development SRT_CXX=GCC SRT_QUAL=default SRT_SUPER=false SETUP_ROOT=root v5_18_00b -f Linux+2.6 -z /home/software/db ROOT_DIR=/home/software/root/v5_18_00b/Linux+2.6 ROOTSYS=/home/software/root/v5_18_00b/Linux+2.6 ROOTVERS=v5_18_00b CERN_DIR=/afs/fnal.gov/ups/cern/2004/Linux+2.6 SETUP_CERN=cern 2004 -f Linux+2.6 -z /afs/fnal/ups/db CERN=/afs/fnal.gov/ups/cern CERN_LEVEL=2004/Linux+2.6 CRNLIB=/afs/fnal.gov/ups/cern/2004/Linux+2.6/lib PLIGLIB=X11,bsd GRAFLIB=graflib,grafX11 KXTERM=/afs/fnal.gov/ups/cern/2004/Linux+2.6/bin/kxterm CERNSOURCE_DIR=/afs/fnal.gov/ups/cernsource/2004/NULL SETUP_CERNSOURCE=cernsource 2004 -f NULL -z /afs/fnal/ups/db SETUP_DCAP=dcap v2_26_f0213 -f Linux+2.4 -z /afs/fnal/ups/db DCAP_DIR=/afs/fnal.gov/ups/dcap/v2_26_f0213/Linux+2.4 DCACHE_IO_TUNNEL=/afs/fnal.gov/ups/dcap/v2_26_f0213/Linux+2.4/lib/libgssTunnel.so MYSQL_DIR=/usr UNIXODBC_DIR=/usr ODBCPP_DIR=/usr SIGC_DIR=/usr ENV_TSQL_URL=mysql://minos-db.minos-soudan.org/offline ENV_TSQL_USER=writer ENV_TSQL_PSWD=siegfried ENV_TSQL_UPDATE_URL=mysql://minos-db.minos-soudan.org/offline ENV_TSQL_UPDATE_USER=writer ENV_TSQL_UPDATE_PSWD=siegfried DBM_HOST=Soudan DBM_UPDATE_NAMES=SOUDAN DBM_MODE=EXPORT DBM_COMMAND_OPTIONS=--Table=BFIELDCOILCURRENT,BFLDDBICOILSTATE,CAL*,DBILOGENTRY,DBU*,DCS*,FAB*,PULSER*,SPILLSERVERMON DBM_WORK_DIR=/data2/dbmauto/work DBM_PUBLISH_DIR=/afs/fnal.gov/files/data/minos/d210/rsync DBM_HEARTBEAT_FREQ=24 DBM_CONTACT_LIST=lena\@fnal.gov DBM_COORDINATOR_LIST=lena\@fnal.gov

Building a priming set for offline database

database and its access

database=offline
host=minos-db1.fnal.gov
user=root
password=XYZZY

cron job

lena@minos-db1.fnal.gov
MAILTO=lena@fnal.gov
33 0 * * 1 /usr/krb5/bin/kcron '/usr/krb5/bin/aklog; /minos/app/dbmauto/test_rel/DatabaseMaintenance/tools/run_build_priming_set.sh> /scratch/minos/lena/db/run_build_priming_set.log 2>&1'

Disk space to monitor

minos-db1:/home
minos-db1:/data
minos-db1:/minos/app
minos/db1:/minos/data

Disk space monitor

scripts review

driving script

/minos/app/dbmauto/test_rel/DatabaseMaintenance/tools/run_build_priming_set.sh

driving script runs setup and calls

/minos/app/dbmauto/test_rel/DatabaseMaintenance/tools/build_priming_set.py

build_priming_set.py functions

  1. Clean-up for all DBMauto activities
  2. Generates database size report
  3. Generates priming script
  4. Runs priming script
  5. Publishes new priming set on the web page

hardwired in build_priming_set.py:

#work directory
/minos/app/dbmauto

#config file:
fnal_export_test.config

#directory used to assemble individual priming tables:
/minos/app/dbmauto/db_priming_work

#minimum working directory size
40 GB

#The directory that will hold the final priming set
/minos/data/mysql

#minimum size of priming set directory
20GB

#html 
/afs/fnal.gov/files/expwww/numi/html/minwork/computing/priming_file_status.html
/afs/fnal.gov/files/expwww/numi/html/minwork/computing/database_size_report.html

#database setup file 
/grid/fermiapp/minos/minossoft/setup/setup_minossoft_FNALU.sh

# DBMauto configuration file (changed by lena from fnal_export.config to fnal_export_test.config, needed to convert to bash. 
# fnal_export.config is a link to export.config. 
# IMPORTANT: changes in export.config should propagate to fnal_export_test.config)
fnal_export_test.config

#script generating priming script: (changed by lena from  launch.csh  to launch.sh. 
# IMPORTANT: changes to launch.csh and launch.sh should be in sync)
launch.sh
launch.csh -> ../test_rel/DatabaseMaintenance/scripts/primer/launch.csh
../test_rel/DatabaseMaintenance/scripts/primer/launch.sh

#current list of contact persons
lena@fnal.gov

#launch.sh generates priming script according to the export.config
make_priming_set.sh
/minos/app/dbmauto/work/make_priming_set.sh

data flow

current set is contained here:

/minos/data/mysql/datasase_dumps/*

previous set is here:

/minos/data/mysql/datasase_dumps/previous_set

once you start running build_priming_set.py previous set is deleted. Current set will remain untouched if new set creation failed at any step of build_priming_set.py

new set is dumped file by file here

/minos/app/dbmauto/db_priming_work/*/*.*

and eventually copied to this directory

/minos/data/mysql/datasase_dumps/new_set

after successful make_priming_set.sh execution, current set is moved to previous_set location, new_set is moved to current, new_set directory is gone.

Priming sets status web page

/afs/fnal.gov/files/expwww/numi/html/minwork/computing/priming_file_status.html

served at

http://www-numi.fnal.gov/minwork/computing/priming_file_status.html

Fixup instructions (Robert's example)

On Jan 3, 2012, at 3:48 PM, Robert Hatcher wrote:

Okay, now I've poked around carefully in the details of
what's in the DCS_MAG_NEAR and VLD tables and I think I've
found the "missing" data.  It's there, just incorrectly
tagged with the wrong TIMESTART/TIMEEND times.

If one looks at the VLD entries one sees that there are
a series of SEQNO's that are interleaved with others in
a manner consistent with the time zone change being applied
incorrectly.

Here's my proposed fix:

[ssh minsoft@minos27.fnal.gov]

$ source shrc/minsoft_librarian.sh
$ setup_minos    # access to MySQL

$ cd /minos/app/dbmauto/work/
$ cat > dcsmagnear_2011-11.fixup << EOF
# Fix incorrect times for entries that occurred during
# the repeat hour of daylight savings time
# The last two conditionals are there to (doubly) ensure
# that this isn't applied twice.
# Robert Hatcher 2012-01-03

update DCS_MAG_NEARVLD SET TIMESTART=DATE_ADD(TIMESTART, INTERVAL 1 HOUR), TIMEEND=DATE_ADD(TIMEEND, INTERVAL 1 HOUR) where SEQNO>=799500839 and SEQNO<=799500850 and  TIMESTART<'2011-11-06 08:00:00' and TIMESTART=CREATIONTIME;

EOF

$ mysql --user=writer --password=$BLAH --host=minos-db1.fnal.gov --database=offline --exec "\. dcsmagnear_2011-11.fixup" 

Data flow notes

Alec T. Habig [habig@neutrino.d.umn.edu]
[Reply] [Reply All] [Forward]
Actions
To:
M
 Elena A Gerchtein 

Thursday, March 22, 2012 10:26 AM
        [Send Error Report]     [Copy error details to clipboard]
Elena A Gerchtein writes:
> I'm collecting data flow information, could you please share what cron
> jobs you running to fill minos-db' db,

At ND:

15 19 * * * /home/minos/dcswriter-neardet/filefinder_cronjobnear.sh 2>&1 > /dev/null

At FD:

15 7,19 * * * /home/minos/dcswriter/gaps/filefinder_cronjobfar.sh 2>&1 > /dev/null

What do these do?  They take the .root files of all the DCS data since
the last time it ran and fill the offline DB's DCS tables.  Mostly a 1-1
translation, except the Bfield tables are also condensed into the magnet
summary tables.

At ND, I'm writing to the main MINOS offline DB.  At FD, I'm writing to
the minos-db copy, which then DBMAuto's itself back to FNAL (and that
was where the problem was).

Andy Blake also has jobs running which create teh data quality tables,
but you should talk to hom about those details.

Other data entry into the offline DB happens sporadically rather than
cron-job'ly.  For example, when hardware is isntalled the hardware DB
gets updated.

How to run dbmauto dependency test

source /usr/local/etc/setups.sh 
export PRODUCTS=${PRODUCTS}:/home/minsoft/ups/db
export dbm=/minos/app/dbmauto
cd $dbm/scripts
source checksum.config
csh launch_test.csh --create_checksum checksum.config

ifdb01 dbmauto tests using minosdb account

# DEPENDENCIES TEST
# added missing libs to $HOME/lib:
# libcrypto.so.4  libncurses.so.5  libssl.so.4  libz.so.1
#
ssh -l minosdb ifdb01
source /usr/local/etc/setups.sh 
export PRODUCTS=${PRODUCTS}:/home/minsoft/ups/db
export dbm=/minos/app/dbmauto
cd $dbm/scripts
source checksum.config
csh launch_test.csh --create_checksum checksum.config
#
# RESULT: OK
#  CHECKSUM TEST
#
#follow setup as in previous section (as in run_dbmauto.sh)
#launch script and config file modified - machine name was hardcoded
ssh -l minosdb ifdb01
source /usr/local/etc/setups.sh 
export PRODUCTS=${PRODUCTS}:/home/minsoft/ups/db
export dbm=/minos/app/dbmauto
cd $dbm/scripts
source checksum.config
csh launch_ifdb01_test.csh  --create_checksum export_ifdb01_test.config
#
# RESULT: 256 from cp /minos/app/dbmauto/work/checksum_Fnal_*.log /minos/app/dbmauto/rsync
# need to add user minosdb to mysql group
#
# follow up:
# minosdb was added to mysql group, I checked that minosdb can write to  /minos/app/dbmauto/rsync
#  IMPORT TEST
#
#follow setup as in previous section (as in run_dbmauto.sh)
#launch script and config file modified - machine name was hardcoded
ssh -l minosdb ifdb01
source /usr/local/etc/setups.sh 
export PRODUCTS=${PRODUCTS}:/home/minsoft/ups/db
export dbm=/minos/app/dbmauto
cd $dbm/scripts
source source import_ifdb01_test.config
csh launch_ifdb01_test.csh  import_ifdb01_test.config
#
# RESULT: no errors reported

minos-mysql1 to ifdb01 switch : dbmauto to do list

1. Stop the minsoft/lena crontab jobs on July 18 - DONE 21:44
2. mv launch.sch launch.old.sh; mv launch_ifdb01_test.csh launch.sch
# Production machine will be ifdb03! need to modify test configs accordingly
3. mv import.config import.config.old ; mv import_ifdb01_test.config import.config
4. mv export.config export.config.old ; mv export_ifdb01_test.config export.config
5. After 1-4 I can restart import and checksum scripts
6. Work with Art on mysql setup in run_dbmauto.sh before starting export
#10:40 July19 - checksum test on ifdb03 started
csh launch_ifdb03_test.csh  --create_checksum checksum_ifdb03_test.config > ifdb03_checksum.log
# RESULT: 256 from cp /minos/app/dbmauto/work/checksum_Fnal_*.log /minos/app/dbmauto/rsync
# changed ownership of /minos/app/dbmauto/scripts/checksum_Fnal_*.log  and /minos/app/dbmauto/rsync/checksum_Fnal_*.log to minosdb
csh launch_ifdb03_test.csh  --create_checksum checksum_ifdb03_test.config > ifdb03_checksum.log.1
# RESULT: checksum script finished w/o errors; export summary for checksum option is identical to the last one from minos-mysql2.fnal.gov version 
#
# 2:57 July19 - import test ifdb03
csh launch_ifdb03_test.csh  import_ifdb03_test.config > ifdb03_import.log
csh launch_ifdb03_test.csh  import_ifdb03_test.config > ifdb03_import.log.1
# RESULT: checked /minos/app/dbmauto/work/SOUDAN_import.log - all updates since July 21:44 are picked up - OK
#
# July20 12:15 set up crontab jobs for import and checksum on ifdb03 under minosdb user