Online Recipes

Here are some recipies, fault-tree, how-to, to get some of the online database routines/services working again

Please Call the DB expert first: 356-2257

  • Restore RunLog Browser
  • Restore Migration Code
  • Restore Online Daemons

 

 

MySQL trigger for oversubscription protection

How-to enable total oversubscription check for Shift Signup (mysql trigger) :

delimiter |

CREATE TRIGGER stop_oversubscription_handler BEFORE INSERT ON Shifts
FOR EACH ROW BEGIN

SET @insert_failed := "";
SET @shifts_required := (SELECT shifts_required FROM ShiftAdmin WHERE institution_id = NEW.institution_id);
SET @shifts_exist := (SELECT COUNT(*) FROM Shifts WHERE institution_id = NEW.institution_id);

IF ( (@shifts_exist+1) >= (@shifts_required * 1.15)) THEN
SET @insert_failed := "oversubscription protection error";
SET NEW.beginTime := null;
SET NEW.endTime := null;
SET NEW.week := null;
SET NEW.shiftNumber := null;
SET NEW.shiftTypeID := null;
SET NEW.duplicate := null;
END IF;
END;

|

delimiter ;

Restore Migration Macros

The Migration Macros are monitored here, if they have stopped the page will display values in red.

  1. Call Database Expert 356-2257
  2. Log onto onlinux6.starp.bnl.gov - as stardb: Ask Michael, Wayne, Gene, Jerome for password
  3. check for runaway processes - for example, these are run as crons so a process starts before its previous exectution finishes, the process never ends and they build up
      If this is the case kill each of the processes
  4. cd to dbcron

Restore RunLog Browser

If the Run Log Browser is not Updating...

  1. make sure all options are selected and deselect "filter bad"
    often - people will complain about missing runs and they are just not selected
  2. Call DB EXPERT: 349-2257
  3. Check onldb.star.bnl.gov
    • log onto node as stardb (password known by Michael, Wayne, Jerome, Gene)
    • df -k ( make sure a disk did not fill) - If it did: cd into the appropriate data directory (i.e. /mysqldata00/Run_7/port_3501/mysql/data) and copy /dev/null into the large log file onldb.starp.bnl.gov.log
      `cp /dev/null > onldb.starp.bnl.gov.log`
    • make sure back-end runlog DAEMON is running
      1. execute /online/production/database/Run_7/dbSenders/bin/runWrite.sh status
        Running should be returned
      2. restart deamon - execute /online/production/database/Run_7/dbSenders/bin/runWrite.sh start
        Running should be returned
      3. if Not Running is returned there is a problem with the code or with daq
        1. contact DAQ expert to check their DB sender system
        2. refer to the next section below as to debugging/modifying recompiling code
  4. Make sure Database is running
    • mysql -S /tmp/mysql.3501.sock
    • To restart db
      1. cd to /online/production/database/config
      2. execute `mysql5.production start 3501`
      3. try to connect
  5. debug/modify Daemon Code (be careful and log everything you do)
    • check log file at /online/production/database/Run_7/dbSenders/run this may point to an obvious problem
    • Source code is located in /online/production/database/Run_7/dbSenders/online/Condition/run
    • GDB is not Available usless the code is recompiled NOT as a daemon
      1. COPY Makefile_debug to Makefile (remember to copy Makefile_good back to Makefile when finished)
      2. setenv DAEMON RunLogSender
      3. make
    • executable is at /online/production/database/Run_7/dbSenders/bin
    • gdb RunLog