IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links
wiki:PS1_IPP_Czarlog_20190503

Version 9 (modified by tdeboer, 7 years ago) ( diff )

--

PS1 IPP Czar Logs for the week 2019.05.03 - 2019.05.09

    1. PS1 IPP Czar Logs for the week 2019.05.03 - 2019.05.09
      1. Friday : 2019.05.03
      2. Saturday : 2019.05.04
      3. Sunday : 2019.05.05
      4. Monday : 2019.05.06
      5. Tuesday : 2019.05.07
      6. Wednesday : 2019.05.08
      7. Thursday : 2019.05.09

(Up to PS1 IPP Czar Logs)

Friday : 2019.05.03

pstamp requests by JRF

Received some error emails about pstamp requests. checking out the pstamp server we can see that over 800,000 jobs have been submitted by QUB.

The errors come from a script run by the crontab on ippc26 (this is mentioned in the emails). The script checks if pstamp has over 100,000 jobs done as this is were pantasks becomes flaky.

Checking out the status of the pstamp pantasks keep an eye on: 

  request.finish.run

as we DO NOT want to restart if this is running as it will cause funkiness.

So let's restart pstamp, doing the usual: 

  pantasks> stop
  pantasks> shutdown now    --check that the jobs have finished first

  ~ippitc/pstamp> pantasks_server & pantasks_client
  pantasks> server input input
  pantasks> setup
  pantasks> run

I have added ippc76 to the hosts_apache group in the ~ippitc/ippconfig/pantasks_hosts.input file. This is so that when the nodes are allocated that ippc76 will be turned off when the apache group is removed.

Since over 800,000 requests were made the pstamp pantasks will need restarting about every hour or so.

  • JRF: ippc72 has died, sent an email to Haydn, left the PDU off. Also commented it out of the ~ippitc/ippconfig/nebservers.csh file.

CZAR HANDOVER by JRF

  • ipp139 has started having issues so there will likely be some liaising with hardware people to fix that.
  • ippc72 is down: details mentioned above, not sure what the next action will be (another hardware issue)
  • 'wait' state: the past week has seen some chip states set to 'wait' which is not a valid state. They can appear in IPP processing at times, and the quickest way to fix them is: change label + imfile update: 
      chiptool -dbname gpc1 -updaterun -state wait -set_state update -set_label update.LAP.PV3 -chip_id XXXXXX
  chiptool -dbname gpc1 -setimfiletoupdate -set_label update.LAP.PV3 -chip_id XXXXXX
    the update second part is needed as the change from wait to update isn't usually recognized.
  • More updates will need to be queued, details of how to do this are given in the Thursday 2019-05-02 czarlog
  • Guide for desperate diffs is in the czarlog for last week. HOWEVER, TdB likely has a better way of doing it by now
  • QUB have put in a lot of stamp requests, you may need to restart pstamp a couple more times today (when jobs done >100k).

CCL took over after 2pm

  • CCL: need to modify /home/panstarrs/ipp/local/bin/apachedisk_chk.sh as ipp on ippc19 to solve the crontab error message, i.e., remove 72 & add 76 for host list. 
    ssh: connect to host ippc72 port 22: No route to host
/home/panstarrs/ipp/local/bin/apachedisk_chk.sh: line 20: [: : integer expression expected
/home/panstarrs/ipp/local/bin/apachedisk_chk.sh: line 30: [: : integer expression expected
  • CCL: 20:30 pstamp over 100k jobs --> restart

ipp@ippc19 ==> ippc76 Host key verification failed. remove known_hosts related to ippc19, i.e., remove this line "10.10.20.74 ssh-rsa xxxxxx"

ipp107 disk usage > 97% , set it to be repair to avoid data storing. neb-host --state repair --host ipp107 --note 'CCL: up->repair: disk full > 97%' 

o5567g0384o	XY64	274464	1505781	update	update.LAP.PV3	LAP.PV3.20140730.ipp.20150211	LAP.ThreePi	2	PS1	GPC1	2011-01-06 15:42:26	171.377302	29.393080	ps1_11_0821	y.00000	30.00	1.0360	119.31	12.36	2176	5.38	135.3PI.y.QE-1 ps1_11_0821 visit 1
neb://ipp051.0/gpc1/20110106/o5567g0384o/o5567g0384o.ota64.burn.tbl size 0

    ipp_apply_burntool_fix.pl --exp_name o5567g0384o --class_id XY64 --verbose --dbname gpc1
    chiptool -dbname gpc1 -updaterun -set_state goto_cleaned -set_label goto_cleaned -chip_id 1505781	
    chiptool -dbname gpc1 -setimfiletoupdate -set_label update.LAP.PV3 -chip_id 1505781

Saturday : 2019.05.04

  • CCL: login to ipp113 as ippitc, but got this error 
    /usr/bin/xauth:  /data/ippc64.1/ippitc/.Xauthority not writable, changes will be ignored
/usr/bin/xauth:  /data/ippc64.1/ippitc/.Xauthority not writable, changes ignored

can be solved by removing .Xauthority file
mv .Xauthority to .Xauthority.20190504
  • CCL: 12:00 feed update.LAP.PV3 3000 jobs

Sunday : 2019.05.05

Monday : 2019.05.06

  • TdB: update regarding desperate diffs and testing: We want a testcase that does not involve live testing. Therefore, make use of the daily testquad. I ran a separate reduction of the quad under the label tdbdifftest. This results in the following exposures: 
    Exp Name 	Exp ID 	Chip ID Cam ID 	Fake ID Warp ID state 	label 	    data grp              Date/Time	          Object        Comment 
o6370g0351o 	590994 	2158115 2116160 2085063 2091717 full 	tdbdifftest tdbdifftest.20190503  2013-03-19 11:37:26	  ps1_14_2124	162.OSS.O.Q.w ps1_14_2124 visit 1
o6370g0373o 	591016 	2158118 2116155 2085069 2091722 full 	tdbdifftest tdbdifftest.20190503  2013-03-19 11:59:29	  ps1_14_2124	162.OSS.O.Q.w ps1_14_2124 visit 2
o6370g0395o 	591038 	2158116 2116151 2085064 2091718 full 	tdbdifftest tdbdifftest.20190503  2013-03-19 12:21:39	  ps1_14_2124	162.OSS.O.Q.w ps1_14_2124 visit 3
o6370g0417o 	591060 	2158117 2116154 2085067 2091720 full 	tdbdifftest tdbdifftest.20190503  2013-03-19 12:43:52	  ps1_14_2124	162.OSS.O.Q.w ps1_14_2124 visit 4
    We can then make the regular diffs with those: 
    difftool -dbname gpc1 -definewarpwarp -warp_id 2091717 -template_warp_id 2091722 -backwards  -set_workdir neb://@HOST@.0/gpc1/tdbdifftest.20190503 -set_dist_group NULL -set_label tdbdifftest -set_data_group tdbdifftest.20190503 -set_reduction SWEETSPOT -simple -rerun
difftool -dbname gpc1 -definewarpwarp -warp_id 2091718 -template_warp_id 2091720 -backwards  -set_workdir neb://@HOST@.0/gpc1/tdbdifftest.20190503 -set_dist_group NULL -set_label tdbdifftest -set_data_group tdbdifftest.20190503 -set_reduction SWEETSPOT -simple -rerun
    resulting in the following exposures: 
    diff_ID state label data grp dist grp Magicked tess ID both ways Time registered reduction workdir
1759513 full tdbdifftest tdbdifftest.20190503 NULL 0 RINGS.V3 1 2019-05-04 00:17:41 SWEETSPOT neb://@HOST@.0/gpc1/tdbdifftest.20190503         
1759514 full tdbdifftest tdbdifftest.20190503 NULL 0 RINGS.V3 1 2019-05-04 00:17:42 SWEETSPOT neb://@HOST@.0/gpc1/tdbdifftest.20190503
    Now then, we can simulate losing a warp exposure by changing its label and doing query only for the tdbdifftest label (in warpRun and diffRun in this case). In this case, lose the visit1 warp and the v1-v2 diff: 
    warptool -dbname gpc1 -updaterun -set_label tdbdifftest1 -warp_id 2091717
difftool -dbname gpc1 -updaterun -set_label tdbdifftest1 -diff_id 1759513
    Then, run a query to get the information: 
    mysql -hX -uX -pippuser gpc1 -B -e "SELECT selchunk.chunk,selchunk.object,MAX(CASE WHEN selchunk.visit=1 THEN selchunk.warp_id ELSE 0 END) as warp1,MAX(CASE WHEN selchunk.visit=2 THEN selchunk.warp_id ELSE 0 END) as warp2,MAX(CASE WHEN selchunk.visit=3 THEN selchunk.warp_id ELSE 0 END) as warp3,MAX(CASE WHEN selchunk.visit=4 THEN selchunk.warp_id ELSE 0 END) as warp4,MAX(CASE WHEN diffchunk.visit=1 THEN diffchunk.diff_id ELSE 0 END) as diff1,MAX(CASE WHEN diffchunk.visit=2 THEN diffchunk.diff_id ELSE 0 END) as diff2,MAX(CASE WHEN diffchunk.visit=3 THEN diffchunk.diff_id ELSE 0 END) as diff3,MAX(CASE WHEN diffchunk.visit=4 THEN diffchunk.diff_id ELSE 0 END) as diff4,selchunk.workdir,selchunk.label,selchunk.data_group,selchunk.reduction FROM (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit,object,warp_id,rawExp.workdir,chipRun.label,chipRun.data_group,rawExp.reduction FROM warpRun JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND warpRun.label LIKE 'tdbdifftest' ORDER BY warp_id DESC) as selchunk LEFT JOIN ((SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND diffRun.label LIKE 'tdbdifftest' AND stack2 IS NULL GROUP BY warp_id) UNION (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND diffRun.label LIKE 'tdbdifftest' AND stack2 IS NULL GROUP BY warp_id)) as diffchunk ON selchunk.warp_id=diffchunk.warp_id GROUP BY selchunk.object;"
    which returns: 
    chunk	        object	        warp1	warp2	warp3	warp4	diff1	diff2	diff3	diff4	workdir	label	data_group	reduction
162.OSS.O.Q.w	ps1_14_2124	0	2091722	2091718	2091720	0	0	1759514	1759514	neb://@HOST@.0/gpc1/20130319	tdbdifftest	tdbdifftest.20190503	SWEETSPOT
    That shows clearly that 1 warp is missing and two visits have been diffed. Running the desperate diffs creation script with the recipe from the PSNC_MOPS wiki page: 
    mysql -hX -uX -pippuser gpc1 -B -e "SELECT selchunk.chunk,selchunk.object,MAX(CASE WHEN selchunk.visit=1 THEN selchunk.warp_id ELSE 0 END) as warp1,MAX(CASE WHEN selchunk.visit=2 THEN selchunk.warp_id ELSE 0 END) as warp2,MAX(CASE WHEN selchunk.visit=3 THEN selchunk.warp_id ELSE 0 END) as warp3,MAX(CASE WHEN selchunk.visit=4 THEN selchunk.warp_id ELSE 0 END) as warp4,MAX(CASE WHEN diffchunk.visit=1 THEN diffchunk.diff_id ELSE 0 END) as diff1,MAX(CASE WHEN diffchunk.visit=2 THEN diffchunk.diff_id ELSE 0 END) as diff2,MAX(CASE WHEN diffchunk.visit=3 THEN diffchunk.diff_id ELSE 0 END) as diff3,MAX(CASE WHEN diffchunk.visit=4 THEN diffchunk.diff_id ELSE 0 END) as diff4,selchunk.workdir,selchunk.label,selchunk.data_group,selchunk.reduction FROM (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit,object,warp_id,rawExp.workdir,chipRun.label,chipRun.data_group,rawExp.reduction FROM warpRun JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND warpRun.label LIKE 'tdbdifftest' ORDER BY warp_id DESC) as selchunk LEFT JOIN ((SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND diffRun.label LIKE 'tdbdifftest' AND stack2 IS NULL GROUP BY warp_id) UNION (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND diffRun.label LIKE 'tdbdifftest' AND stack2 IS NULL GROUP BY warp_id)) as diffchunk ON selchunk.warp_id=diffchunk.warp_id GROUP BY selchunk.object;" | grep -v object | awk '{nwarp=0; if($3!=0) nwarp++; if($4!=0) nwarp++; if($5!=0) nwarp++; if($6!=0) nwarp++ ; ndiff=0; if($7!=0) ndiff++; if($8!=0) ndiff++; if($9!=0) ndiff++; if($10!=0) ndiff++ ; if(nwarp == 3 && nwarp !=ndiff) {if($7==0 && $3==0){w1=$4; w2=$5} else if($8==0 && $4==0){w1=$3; w2=$5} else if($9==0 && $5==0){w1=$4; w2=$6} else if($10==0 && $6==0){w1=$4; w2=$5}; print "difftool -dbname gpc1 -definewarpwarp -warp_id "w1" -template_warp_id "w2" -backwards -set_workdir "$11" -set_dist_group NULL -set_label "$12" -set_data_group "$13" -set_reduction "$14" -simple -rerun -set_note desperate diff  -pretend";}}'
    and returns: 
    difftool -dbname gpc1 -definewarpwarp -warp_id 2091722 -template_warp_id 2091718 -backwards -set_workdir neb://@HOST@.0/gpc1/20130319 -set_dist_group NULL -set_label tdbdifftest -set_data_group tdbdifftest.20190503 -set_reduction SWEETSPOT -simple -rerun -set_note desperate diff  -pretend
    This is the desired results!

---------------------------- Now, we know this is not something that happens in really. The first diffable pair will always be diffed. Simulate that instead by leaving the warpID label as is (first visit missing), but putting both diffRun labels to off. Then, make a v2-v3 diff: 

difftool -dbname gpc1 -definewarpwarp -warp_id 2091722 -template_warp_id 2091718 -backwards  -set_workdir neb://@HOST@.0/gpc1/tdbdifftest.20190503 -set_dist_group NULL -set_label tdbdifftest -set_data_group tdbdifftest.20190503 -set_reduction SWEETSPOT -simple -rerun

Then, we have the following diffs: 

diff_ID state label data grp dist grp Magicked tess ID both ways Time registered reduction workdir
1759513 full tdbdifftest1 tdbdifftest.20190503 NULL 0 RINGS.V3 1 2019-05-04 00:17:41 SWEETSPOT neb://@HOST@.0/gpc1/tdbdifftest.20190503         
1759514 full tdbdifftest1 tdbdifftest.20190503 NULL 0 RINGS.V3 1 2019-05-04 00:17:42 SWEETSPOT neb://@HOST@.0/gpc1/tdbdifftest.20190503  	
1760928 full tdbdifftest  tdbdifftest.20190503 NULL 0 RINGS.V3 1 2019-05-06 21:16:16 SWEETSPOT neb://@HOST@.0/gpc1/tdbdifftest.20190503

Now run the query script, which returns: 

chunk	        object	        warp1	warp2	warp3	warp4	diff1	diff2	diff3	diff4	workdir	label	data_group	reduction
162.OSS.O.Q.w	ps1_14_2124	0	2091722	2091718	2091720	0	1760928	1760928	0	neb://@HOST@.0/gpc1/20130319	tdbdifftest	tdbdifftest.20190503	SWEETSPOT

This looks like what we expect. Run the desperate diff creation: 

mysql -hX -uX -pippuser gpc1 -B -e "SELECT selchunk.chunk,selchunk.object,MAX(CASE WHEN selchunk.visit=1 THEN selchunk.warp_id ELSE 0 END) as warp1,MAX(CASE WHEN selchunk.visit=2 THEN selchunk.warp_id ELSE 0 END) as warp2,MAX(CASE WHEN selchunk.visit=3 THEN selchunk.warp_id ELSE 0 END) as warp3,MAX(CASE WHEN selchunk.visit=4 THEN selchunk.warp_id ELSE 0 END) as warp4,MAX(CASE WHEN diffchunk.visit=1 THEN diffchunk.diff_id ELSE 0 END) as diff1,MAX(CASE WHEN diffchunk.visit=2 THEN diffchunk.diff_id ELSE 0 END) as diff2,MAX(CASE WHEN diffchunk.visit=3 THEN diffchunk.diff_id ELSE 0 END) as diff3,MAX(CASE WHEN diffchunk.visit=4 THEN diffchunk.diff_id ELSE 0 END) as diff4,selchunk.workdir,selchunk.label,selchunk.data_group,selchunk.reduction FROM (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit,object,warp_id,rawExp.workdir,chipRun.label,chipRun.data_group,rawExp.reduction FROM warpRun JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND warpRun.label LIKE 'tdbdifftest' ORDER BY warp_id DESC) as selchunk LEFT JOIN ((SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND diffRun.label LIKE 'tdbdifftest' AND stack2 IS NULL GROUP BY warp_id) UNION (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND diffRun.label LIKE 'tdbdifftest' AND stack2 IS NULL GROUP BY warp_id)) as diffchunk ON selchunk.warp_id=diffchunk.warp_id GROUP BY selchunk.object;" | grep -v object | awk '{nwarp=0; if($3!=0) nwarp++; if($4!=0) nwarp++; if($5!=0) nwarp++; if($6!=0) nwarp++ ; ndiff=0; if($7!=0) ndiff++; if($8!=0) ndiff++; if($9!=0) ndiff++; if($10!=0) ndiff++ ; if(nwarp == 3 && nwarp !=ndiff) {if($7==0 && $3==0){w1=$4; w2=$5} else if($8==0 && $4==0){w1=$3; w2=$5} else if($9==0 && $5==0){w1=$4; w2=$6} else if($10==0 && $6==0){w1=$4; w2=$5}; print "difftool -dbname gpc1 -definewarpwarp -warp_id "w1" -template_warp_id "w2" -backwards -set_workdir "$11" -set_dist_group NULL -set_label "$12" -set_data_group "$13" -set_reduction "$14" -simple -rerun -set_note desperate diff  -pretend";}}'

which returns: 

difftool -dbname gpc1 -definewarpwarp -warp_id 2091722 -template_warp_id 2091718 -backwards -set_workdir neb://@HOST@.0/gpc1/20130319 -set_dist_group NULL -set_label tdbdifftest -set_data_group tdbdifftest.20190503 -set_reduction SWEETSPOT -simple -rerun -set_note desperate diff  -pretend

So, the 'usual' recipe does not work in all cases. i.e. it works off of a set strategy but does not check against existing (and therefore missing) diffs. For now, we can adjust the fixed recipe based on the fact that we know the first diffable pair will always be diffed. This affects the case of v1 or v2 being missing. v1 missing results in v2-v3 diff being made and v3-v4 being needed; v2 missing results in v1-v3 being made and v3-v4 being needed. Try this instead: 

mysql -hX -uX -pippuser gpc1 -B -e "SELECT selchunk.chunk,selchunk.object,MAX(CASE WHEN selchunk.visit=1 THEN selchunk.warp_id ELSE 0 END) as warp1,MAX(CASE WHEN selchunk.visit=2 THEN selchunk.warp_id ELSE 0 END) as warp2,MAX(CASE WHEN selchunk.visit=3 THEN selchunk.warp_id ELSE 0 END) as warp3,MAX(CASE WHEN selchunk.visit=4 THEN selchunk.warp_id ELSE 0 END) as warp4,MAX(CASE WHEN diffchunk.visit=1 THEN diffchunk.diff_id ELSE 0 END) as diff1,MAX(CASE WHEN diffchunk.visit=2 THEN diffchunk.diff_id ELSE 0 END) as diff2,MAX(CASE WHEN diffchunk.visit=3 THEN diffchunk.diff_id ELSE 0 END) as diff3,MAX(CASE WHEN diffchunk.visit=4 THEN diffchunk.diff_id ELSE 0 END) as diff4,selchunk.workdir,selchunk.label,selchunk.data_group,selchunk.reduction FROM (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit,object,warp_id,rawExp.workdir,chipRun.label,chipRun.data_group,rawExp.reduction FROM warpRun JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND warpRun.label LIKE 'tdbdifftest' ORDER BY warp_id DESC) as selchunk LEFT JOIN ((SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND diffRun.label LIKE 'tdbdifftest' AND stack2 IS NULL GROUP BY warp_id) UNION (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND diffRun.label LIKE 'tdbdifftest' AND stack2 IS NULL GROUP BY warp_id)) as diffchunk ON selchunk.warp_id=diffchunk.warp_id GROUP BY selchunk.object;" | grep -v object | awk '{nwarp=0; if($3!=0) nwarp++; if($4!=0) nwarp++; if($5!=0) nwarp++; if($6!=0) nwarp++ ; ndiff=0; if($7!=0) ndiff++; if($8!=0) ndiff++; if($9!=0) ndiff++; if($10!=0) ndiff++ ; if(nwarp == 3 && nwarp !=ndiff) {if($7==0 && $3==0){w1=$5; w2=$6} else if($8==0 && $4==0){w1=$5; w2=$6} else if($9==0 && $5==0){w1=$4; w2=$6} else if($10==0 && $6==0){w1=$4; w2=$5}; print "difftool -dbname gpc1 -definewarpwarp -warp_id "w1" -template_warp_id "w2" -backwards -set_workdir "$11" -set_dist_group NULL -set_label "$12" -set_data_group "$13" -set_reduction "$14" -simple -rerun -set_note desperate diff  -pretend";}}'

which returns: 

difftool -dbname gpc1 -definewarpwarp -warp_id 2091718 -template_warp_id 2091720 -backwards -set_workdir neb://@HOST@.0/gpc1/20130319 -set_dist_group NULL -set_label tdbdifftest -set_data_group tdbdifftest.20190503 -set_reduction SWEETSPOT -simple -rerun -set_note desperate diff  -pretend

That now works. But clearly, want to make an explicit check against the diff_ids available.

In conclusion, to create the correct desperate diffs for a given chunk, run: 

mysql -hX -uX -pippuser gpc1 -B -e "SELECT selchunk.chunk,selchunk.object,MAX(CASE WHEN selchunk.visit=1 THEN selchunk.warp_id ELSE 0 END) as warp1,MAX(CASE WHEN selchunk.visit=2 THEN selchunk.warp_id ELSE 0 END) as warp2,MAX(CASE WHEN selchunk.visit=3 THEN selchunk.warp_id ELSE 0 END) as warp3,MAX(CASE WHEN selchunk.visit=4 THEN selchunk.warp_id ELSE 0 END) as warp4,MAX(CASE WHEN diffchunk.visit=1 THEN diffchunk.diff_id ELSE 0 END) as diff1,MAX(CASE WHEN diffchunk.visit=2 THEN diffchunk.diff_id ELSE 0 END) as diff2,MAX(CASE WHEN diffchunk.visit=3 THEN diffchunk.diff_id ELSE 0 END) as diff3,MAX(CASE WHEN diffchunk.visit=4 THEN diffchunk.diff_id ELSE 0 END) as diff4,selchunk.workdir,selchunk.label,selchunk.data_group,selchunk.reduction FROM (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit,object,warp_id,rawExp.workdir,chipRun.label,chipRun.data_group,rawExp.reduction FROM warpRun JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND substr(comment, 1, position(' ' in comment)) LIKE 'OSSR.R10S1.14.Q.w%' AND rawExp.dateobs LIKE '`date -u "+%Y-%m-%d"`%' ORDER BY warp_id DESC) as selchunk LEFT JOIN ((SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND substr(comment, 1, position(' ' in comment)) LIKE 'OSSR.R10S1.14.Q.w%' AND rawExp.dateobs LIKE '`date -u "+%Y-%m-%d"`%' AND stack2 IS NULL GROUP BY warp_id) UNION (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND substr(comment, 1, position(' ' in comment)) LIKE 'OSSR.R10S1.14.Q.w%' AND rawExp.dateobs LIKE '`date -u "+%Y-%m-%d"`%' AND stack2 IS NULL GROUP BY warp_id)) as diffchunk ON selchunk.warp_id=diffchunk.warp_id GROUP BY selchunk.object;" | grep -v object | awk '{nwarp=0; if($3!=0) nwarp++; if($4!=0) nwarp++; if($5!=0) nwarp++; if($6!=0) nwarp++ ; ndiff=0; if($7!=0) ndiff++; if($8!=0) ndiff++; if($9!=0) ndiff++; if($10!=0) ndiff++ ; if(nwarp == 3 && nwarp !=ndiff) {if($7==0 && $3==0){w1=$5; w2=$6} else if($8==0 && $4==0){w1=$5; w2=$6} else if($9==0 && $5==0){w1=$4; w2=$6} else if($10==0 && $6==0){w1=$4; w2=$5}; print "difftool -dbname gpc1 -definewarpwarp -warp_id "w1" -template_warp_id "w2" -backwards -set_workdir "$11" -set_dist_group NULL -set_label "$12" -set_data_group "$13" -set_reduction "$14" -simple -rerun -set_note desperate diff  -pretend";}}' | bash

or for a whole night, using: 

mysql -hX -uX -pippuser gpc1 -B -e "SELECT selchunk.chunk,selchunk.object,MAX(CASE WHEN selchunk.visit=1 THEN selchunk.warp_id ELSE 0 END) as warp1,MAX(CASE WHEN selchunk.visit=2 THEN selchunk.warp_id ELSE 0 END) as warp2,MAX(CASE WHEN selchunk.visit=3 THEN selchunk.warp_id ELSE 0 END) as warp3,MAX(CASE WHEN selchunk.visit=4 THEN selchunk.warp_id ELSE 0 END) as warp4,MAX(CASE WHEN diffchunk.visit=1 THEN diffchunk.diff_id ELSE 0 END) as diff1,MAX(CASE WHEN diffchunk.visit=2 THEN diffchunk.diff_id ELSE 0 END) as diff2,MAX(CASE WHEN diffchunk.visit=3 THEN diffchunk.diff_id ELSE 0 END) as diff3,MAX(CASE WHEN diffchunk.visit=4 THEN diffchunk.diff_id ELSE 0 END) as diff4,selchunk.workdir,selchunk.label,selchunk.data_group,selchunk.reduction FROM (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit,object,warp_id,rawExp.workdir,chipRun.label,chipRun.data_group,rawExp.reduction FROM warpRun JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND rawExp.dateobs LIKE '`date -u "+%Y-%m-%d"`%' ORDER BY warp_id DESC) as selchunk LEFT JOIN ((SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND rawExp.dateobs LIKE '`date -u "+%Y-%m-%d"`%' AND stack2 IS NULL GROUP BY warp_id) UNION (SELECT SUBSTRING_INDEX(comment, ' ',1) AS chunk,SUBSTRING_INDEX(comment, ' ',-1) AS visit, object,warp_id,diff_id FROM diffRun JOIN diffInputSkyfile USING (diff_id) JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) WHERE rawExp.exp_name LIKE 'o%' AND rawExp.dateobs LIKE '`date -u "+%Y-%m-%d"`%' AND stack2 IS NULL GROUP BY warp_id)) as diffchunk ON selchunk.warp_id=diffchunk.warp_id GROUP BY selchunk.chunk,selchunk.object;" | grep -v object | awk '{nwarp=0; if($3!=0) nwarp++; if($4!=0) nwarp++; if($5!=0) nwarp++; if($6!=0) nwarp++ ; ndiff=0; if($7!=0) ndiff++; if($8!=0) ndiff++; if($9!=0) ndiff++; if($10!=0) ndiff++ ; if(nwarp == 3 && nwarp !=ndiff) {if($7==0 && $3==0){w1=$5; w2=$6} else if($8==0 && $4==0){w1=$5; w2=$6} else if($9==0 && $5==0){w1=$4; w2=$6} else if($10==0 && $6==0){w1=$4; w2=$5}; print "difftool -dbname gpc1 -definewarpwarp -warp_id "w1" -template_warp_id "w2" -backwards -set_workdir "$11" -set_dist_group NULL -set_label "$12" -set_data_group "$13" -set_reduction "$14" -simple -rerun -set_note desperate diff  -pretend";}}' | bash

Tuesday : 2019.05.07

Wednesday : 2019.05.08

Thursday : 2019.05.09

Note: See TracWiki for help on using the wiki.