check_mysql_unknow.sh.pl

check_mysql_unknow检查mysql unknow状态

#!/bin/sh
 
_result="/tmp/check_mysql_unknow.result"
_filelock="/tmp/p_check_mysql_unknow.lock"
_maillog="/tmp/p_check_mysql_unknow.mail"

cp_cove_id="/tmp/p_check_mysql_unknow_cp_cover_id"
cp_cove_id2="/tmp/p_check_mysql_unknow_cp_cover_id2"
cp_execution_id="/tmp/p_check_mysql_unknow_cp_execution_id"
excution_id="/tmp/p_check_mysql_unknow_execution_id"
excution_id2="/tmp/p_check_mysql_unknow_execution_id2"
order_id="/tmp/p_check_mysql_unknow_order_id"

maillog2="/tmp/p_check_mysql_unknow.mail2"
echo "懳屭媞幐攕:" > $maillog2
echo "CP_COVER_ID" >> $maillog2

maillog3="/tmp/p_check_mysql_unknow.mail3"
echo "懳屭媞惉岟:" > $maillog3
echo "CP_COVER_ID" >> $maillog3

maillog4="/tmp/p_check_mysql_unknow.mail4"
echo "-----------------------------惉岟揑屭媞忣曬-------------------------" > $maillog4
maillog5="/tmp/p_check_mysql_unknow.mail5"
echo "-----------------------------幐攕揑屭媞忣曬-------------------------" > $maillog5
#_mailto="[e-mail]"
#_mailcc="[e-mail]"
_mailto="[e-mail],[e-mail],[e-mail]"
_mailcc="[e-mail]" 
if [ -e $_filelock ];then
 
               # mail
               exit 1;
       else
 
               lockfile /tmp/p_check_mysql_unknow.lock
fi
 
mysql -uroot -pmj_abc -h10.99.1.176  MAIN -e 'select count(*)UNKNOW from  JHF_CP_EXECUTION where STATUS=0 and UPDATE_DATE between date_sub(NOW(),INTERVAL 5 MINUTE) and NOW();' > $_result 2>&1
 
 
if [ "`grep -v UNKNOW $_result`" == "0" ];then
 
                        echo OK!
        else
                 echo "To: $_mailto"                              >$_maillog
                echo "Cc: $_mailcc"                             >> $_maillog
                echo "From: jhfsys"                             >> $_maillog
                echo "Subject: MJ Proction System: JHF_CP_EXECUTION hava UNKOWN!"    >> $_maillog
                echo "Content-Type: text/plain; charset=Shift_JIS"  >> $_maillog
                echo "Content-Transfer-Encoding: 7bit"          >> $_maillog
                echo -e "\nFrom : 10.99.1.191\nState      :  Reprot\nReportBy   : /root/bin/check_mysql_unknow.sh\nDateTime   : `date +'%Y-%m-%d %H:%M:%S'` JST\n\n"         >> $_maillog
               
 
                cat $_result                                       >> $_maillog
                
                mysql -uroot -pmj_abc -h10.99.1.176  MAIN -e 'select CP_EXECUTION_ID,COUNTERPARTY_ID,CP_COVER_ID,CURRENCY_PAIR,FORCE_RELATION_ID,CONFIRM_DATETIME from  JHF_CP_EXECUTION where STATUS=0 and UPDATE_DATE between date_sub(NOW(),INTERVAL 5 MINUTE) and NOW();' > $cp_cove_id
                cat $cp_cove_id  >> $_maillog
                echo " "                                           >> $_maillog
 
                echo "Please note,Find JHF_CP_EXECUTION table data are UNKOWN!" >> $_maillog
                
                cat $cp_cove_id|awk '{print $3}'|sed /CP_COVER_ID/d > $cp_cove_id2
                scp 10.99.1.153:/jhfapp/app/hedger/hedger/logs/hedger_hedger.log /tmp/
                while read line
                do
                  mysql -uroot -pmj_abc -h10.99.1.176  MAIN -e "SELECT CP_EXECUTION_ID FROM JHF_CP_EXECUTION WHERE CP_COVER_ID ='$line'" >$cp_execution_id
                  mysql -uroot -pmj_abc -h10.99.1.176  MAIN -e "SELECT EXECUTION_ID FROM JHF_EXECUTION_BIND  WHERE CP_EXECUTION_ID = '`tail -n1 $cp_execution_id`'" > $excution_id
                  if [ `cat $excution_id |wc -l` = 0 ]
                  then 
                   echo $line >> $maillog2
                   grep  "$line" /tmp//hedger_hedger.log |grep "trade request send to gw is" > /tmp/hedger_hedger.log1
                   grep -B100 "$line" /tmp/hedger_hedger.log |grep "receive trader request from trader" |grep -i "`awk -F , '{print $4}' /tmp/hedger_hedger.log1`" |grep "`awk -F , '{print $8}' /tmp/hedger_hedger.log1`" |grep "`cut -c 1-19 /tmp/hedger_hedger.log1`" >/tmp/hedger_hedger.log2
                   order=`cat /tmp/hedger_hedger.log2 |awk -F "orderid=" '{print $2}' |awk -F "]" '{print $1}'`
                   echo "CP_COVER_ID:$line" >> $maillog5
                   mysql -uroot -pmj_abc -h10.99.1.176  MAIN -e "select a.CUSTOMER_ID,a.CUSTOMER_ORDER_NO,b.ORDER_STATUS,a.INPUT_DATE from JHF_CUSTOMER_ORDER_NO as a,JHF_ALIVE_ORDER as b where a.order_id='$order' and a.order_id=b.order_id" >> $maillog5
                   echo "" >> $maillog5
                  else
                   echo $line >> $maillog3
                   cat $excution_id |sed /EXECUTION_ID/d > $excution_id2
                   while read line2
                   do 
                      mysql -uroot -pmj_abc -h10.99.1.176  MAIN -e "SELECT ORDER_ID FROM JHF_EXECUTION WHERE EXECUTION_ID='$line2'" > $order_id
                      order_id2=`cat $order_id |sed /ORDER_ID/d`
                       echo "CP_COVER_ID:$line" >> $maillog4
                     mysql -uroot -pmj_abc -h10.99.1.176  MAIN -e "select a.CUSTOMER_ID,a.CUSTOMER_ORDER_NO,b.ORDER_STATUS,a.INPUT_DATE from JHF_CUSTOMER_ORDER_NO as a,JHF_ALIVE_ORDER as b where a.order_id='$order_id2' and a.order_id=b.order_id" >> $maillog4
                      #mysql -uroot -pmj_abc -h10.99.1.176  MAIN -e "select a.CUSTOMER_ID,a.CUSTOMER_ORDER_NO,b.ORDER_ID,b.ORDER_DATETIME,b.ORDER_AMOUNT,b.CURRENCY_PAIR,b.ORDER_STATUS,a.INPUT_DATE from JHF_CUSTOMER_ORDER_NO as a,JHF_ALIVE_ORDER as b where a.order_id='$order_id2' and a.order_id=b.order_id" >> $maillog4
                   echo "" >> $maillog4
                   done < $excution_id2
                  fi
                done < $cp_cove_id2
                
                echo "" >> $_maillog
                if [ `cat $maillog2|wc -l` = 2 ];then echo "柍">>$maillog2;fi
                cat $maillog2 >> $_maillog
                echo "" >> $_maillog
                if [ `cat $maillog3|wc -l` = 2 ];then echo "柍">>$maillog3;fi
                cat $maillog3 >> $_maillog
                echo "" >> $_maillog
                if [ `cat $maillog5|wc -l` = 1 ];then echo "柍">>$maillog5;fi
                cat $maillog5 >> $_maillog
                echo "" >> $_maillog
                if [ `cat $maillog4|wc -l` = 1 ];then echo "柍">>$maillog4;fi
                cat $maillog4 >> $_maillog
                
               # mail -s "MJ Proction System: JHF_CP_EXECUTION hava UNKOWN!" $_mailto $_mailme $_mailcc1 $_mailcc2 < $_maillog
               cat $_maillog | /usr/sbin/sendmail -t                
fi
 
rm -rf /tmp/p_check_mysql_unknow.lock