Database Distribution: Database Priming

DBMauto distributes updates only, so you need to have a database that is already primed with existing tables.

Contents

The Priming Files

At weekly intervals all the tables distributed by DBMauto are dumped as gzipped tar files onto the directory:- 
  /afs/fnal.gov/files/data/minos/d210/database_dumps
The file naming convention used is:- <table>_nnnnnn.tar.gz where:- <table> is the table name e.g. PLEXPINDIODETOLED nnnnnn the update number e.g. 001234 when the tar file was created For example, the priming file:- 
  PLEXPINDIODETOLED_001234.tar.gz
will replace the PLEXPINDIODETOLED table with the version that existed in the FNAL database after it issued update 1234.

Where a table priming file could possibly exceed a 2GB file limit, which is typical for a lot of AFS clients, it is split into multiple files that extend the syntax:-

<table>_nnnnnn_iiiiiiiii_jjjjjjjjj.tar.gz where:- iiiiiiiii Minimum SEQNO in set jjjjjjjjj Maximum SEQNO in set For example, the priming file:- 
  BEAMMONSPILL_700152600_700156199_00042768.tar.gz
Do not apply these updates on Master Databases as this could result in the loss of data.

The fact that the priming files completely replace the existing data means that a crude way of keep your database in step would be just to apply these priming files at intervals rather than set up DBMauto and run regularly. In general the priming files are very large so this is only worth considering if it is acceptable to run with a database that could be several months out of date.

For the date of the latest set of priming files see: DBMauto Priming File Status

The Priming Tool: load_tables.sh

In the directory:- 
$SRT_PUBLIC_CONTEXT/DatabaseMaintenance/tools/
you will find the tool:- 
load_tables.sh
which is used to apply the updates. The command syntax is:- 
   load_tables.sh {options} <table|tar-file|list-file>
 
 Options are:-
 
 --database  <db>      Database name.  Default: Offline
 --debug               Print some debug info.
 --directory  <dir>    Directory for work space.  Default: ./
 --host <host>         Host server.  Default: Current host.
 --server_v3           MySQL server is version 3.  Default: server compatible with client
                        (worth trying this if you get SQL errors)
 --password <pswd>     Password.  "" means none. Default: ask
 --suffix  <suffix>    Add suffix to file name. Default: none
 --tar_dir             Directory holding tar dir.
                       Default: /afs/fnal.gov/files/data/minos/d210/database_dumps
 --username <user>     Username.  Default: writer
 
<table|tar-file|list-file> is one of:-
 
o  A table e.g. CALGAINDRIFT.
 
   Load_table looks for a file (or files in the case of a large table) named <table>_*.tar.gz
 
o  A tar-file.
 
   Load_table looks for that specific file.
 
o  A list e.g. prime.
 
   Load_table looks for the file <list>_nnnnnn.lis and uses the tables it contains.
 
   Each of the main groups of tables has a list:-
 
        bfield bflddbi beammon (includes SPILLTIME*) cal dbu dcs fab plex pulse range ugli and prime

Where: nnnnnnnn is the number in FNAL_nnnnnnnn.dbm to which the tar file
       will synchronise.

The Priming Procedure

To prime or re-prime the database proceed as follows.
  1. If you do not have access to 
      /afs/fnal.gov/files/data/minos/d210/database_dumps
    then use a machine that has to copy the required files to a directory to which you do have access. Alternatively consider installing lufs

  2. If you are already running DBMauto then suspend the CRON Jobs.

  3. Decide which tables you want to prime (not all are essential). If you want to prime all the tables required for offline production then you can use the list held in the file:- 
      prime_nnnnnn.lis
    So, if running on the machine that hosts the local MySQL server, and with afs access to the priming directory, to prime the offline database using the writer account:- 
      load_tables.sh --password ....  prime
    will unpack each required table update in turn and apply it.

    Other examples:-

    1. Apply all calibration table priming files onto MySQL server 'my_server' 
        load_tables.sh --password .... --host my_server  cal

    2. Re-prime the CALGAINDRIFT table using a copy of the priming file on the disk 'local-dir' 
        load_tables.sh --password ....  --tar_dir local-dir CALGAINDRIFT

    3. Compose own priming list based on the 'prime' list 
        cp /afs/fnal.gov/files/data/minos/d210/database_dumps/prime*.lis my_prime.lis
  vi ./my_prime.lis [make required changes]
  for table in `cat ./my_prime.lis`
  do
    load_table.sh <account details> $table
  done

  4. If already running DBMauto:-
    1. Set the Update Number in the FNAL_import.context to nnnnnn.
    2. Start up DBMauto cron job.
    This will cause updates since nnnnn to be re-imported which will ensure that any changes made to tables you have re-primed do not miss any updates that have occurred since the priming tar file was created. Note that it does no harm to any other database table to reapply old updates; when considering replacing existing rows, DBMauto always takes data with later creation date so there is no danger that obsolete data will get imported.
Return to the top-level Database Distribution document
Last Modified: $Date: 2008/07/01 05:02:33 $