TestDAQ Database Synchronization

1. Download the synchronization software and untar it wherever you expect the final software to live. You will end up with some jar files in a lib/ subdirectory and some shell scripts in a bin subdirectory.
   
2. If you haven't yet set up a TestDAQ database, follow these instructions.
   
   
3. Otherwise, you'll need to do some pre-synchronization preparation of your existing database:
   • Back up your database in case the pre-synchronization steps do something horribly wrong -- I've tested them as well as I could, but you may discover that they do something unexpected, so better safe than sorry.
     
     For mysql, you can do this by running:

                 mysqldump --quick --add-drop-table -p domprodtest > dpt.dump
               

     and entering your password when prompted. Note that the database can be HUGE, so make sure you've got enough diskspace. You can compress the dump by piping it to bzip2 -c or gzip -c:

                 mysqldump --quick --add-drop-table -p domprodtest | bzip2 -c > dpt.dump
               

   • Before synchronizing with the master database, you need to run a couple of utility scripts to clean up your local database:
     • bin/find_dup_products.sh finds and removes duplicate or unreferenced Product and/or Assembly rows
     • bin/find_orphans.sh finds and removes references to any nonexistent IDs in the entire database
     
     By default, these programs don't make any changes to the database. You must specify the -d option to enable them to make changes to the database.
     
     These programs can also be run with the -v option to provide a description of the actions each program is taking.
     
     For example, to see how the find_dup_products.sh utility will change the database, you can run:

                 bin/find_dup_products.sh -v
               

     Once you're confident that the programs won't do any harm, run bin/find_dup_products.sh -v -d and bin/find_orphans.sh -v -d. This first pass may cause other unreferenced rows to be exposed, so you should rerun both programs until no more duplicate/unreferenced rows are found.
     
     There are situations which these programs cannot handle, so you may need to delete some Assembly rows by hand. If you're not comfortable doing this, please contact Dave Glowacki.
     
     It's worth noting that bin/find_dup_products.sh is the really important program to run.
     
     While bin/find_orphans.sh is nice to run, it can also take a long time to run. If you'd like, you can run bin/list_table_sizes.sh -utester -ppassword to list the number of rows in each table in the database, and then use the -i option to disable checking of the largest tables, like:

                 bin/find_orphans.sh -v -d -i STFResultParameter -i STFResult
               

4. Add a master entry to your domprodtest.properties file. Currently we are synchronizing with glacier.lbl.gov, but this will change to a master machine in icecube.wisc.edu at some point:

             database.master.driver: org.gjt.mm.mysql.Driver
             database.master.url: jdbc:mysql://glacier.lbl.gov/domprodtest
             database.master.user: XXX
             database.master.password: YYYZZZ
           

   You'll need to ask Dave Glowacki for the master database user and password and use those to fill in the XXX and YYYZZZ entries above.
5. The bin/sync-domprodtest.sh application is used to synchronize the local database with the master database. Unlike the find_dup_products.sh and find_orphans.sh applications, the sync-domprodtest.sh application makes changes to the database by default. If you'd like to see what would be done without any changes being made to the database, specify the -t option:

             bin/sync-domprodtest.sh -v -t
           

   NOTE: This script will instruct you to modify it the first time it is run. Once these changes are made, these modification messages should disappear.
   
   Once you're ready to synchronize the local database, run

             bin/sync-domprodtest.sh -v
           

   This will update your local database with the information for all the DOMs which are in the master database, and simultaneously add to the master database any DOMs in your local database which it doesn't know about.
   
   When the first run has completed, rerun sync-domprodtest.sh. Any output from this second run will fall into one of the following categories:

   == Missing table "XXX"

   This indicates a table which is present in the master database but is missing in the local database. You should probably add the specified table by hand.

   == Extra table "XXX"

   This indicates a table which is present in the local database but is missing in the master database. If this is to be used in a project-wide program, you should notify Dave Glowacki.

   == master.XXX does not match local.XXX

   This indicates that master table XXX is somehow different from the local table. You should probably dump the local table, recreate the table using the master description, and then reload the local data

   == Missing index "YYY" on XXX

   This indicates an index which is present in the master database but is missing in the local database. You should probably create the specified index.

   == Extra index "YYY" on XXX

   This indicates an index which is present in the local database but is missing in the master database. If this not intended only for local use, you should notify Dave Glowacki.

   == master.XXX index "YYY" does not match local.XXX index "YYY"

   This indicates that the master index on table XXX is somehow different from the local index. You should probably drop the local index and create the master index.

6. Add bin/sync-domprodtest.sh as a cron job which is run every night, so that your database continues to stay synchronized. (Note that you'll need to specify the full path to sync-domprodtest.sh when adding it to cron.)