#!/bin/bash
# ======================================================================
#
# Check MYSQL / MARIADB SERVER
#
# requirements:
# - mysql client
#
# installation:
# - execute check_mysqlserver -i
#
# ----------------------------------------------------------------------
#
# for mysql query cache:
#   https://www.techpaste.com/2013/01/query-cache-mysql-server/
#
# ----------------------------------------------------------------------
# 2020-08-xx  v0.0  <axel.hahn@iml.unibe.ch>
# 2021-06-08  v0.1  <axel.hahn@iml.unibe.ch>  update HOME .. better implement a param and ENV var
# 2021-06-24  v0.2  <axel.hahn@iml.unibe.ch>  check if query cache is enabled as a shared function
# 2023-06-06  v0.3  <axel.hahn@unibe.ch>      some shell fixes
# 2023-07-28  v1.0  <axel.hahn@unibe.ch>      update help page; fix [client]: command not found
# ======================================================================

. $( dirname $0 )/inc_pluginfunctions

export self_APPVERSION=1.0

# --- set HOME
HOME=/etc/icingaclient

# --- other vars...
cfgfile=$HOME/.my.cnf
myuser=icingamonitor
datafile=/tmp/mysqlvars.out

# new line
NL="
"

lastvalue=
out=" "

# ----------------------------------------------------------------------
# FUNCTIONS
# ----------------------------------------------------------------------

# uninstall database user
function _uninstall(){
    echo UNINSTALL ...
    mysql -e "DROP user $myuser@localhost ;"
}

# (re)install database user for monitoring
function _install(){
    echo INSTALLING ...
    local pwlength=64
    
    echo "- check mysql connection..."
    mysql -e ";"
    if [ $? -ne 0 ]; then
        echo "ERROR: mysql connect without password failed."
        exit 1
    fi

    echo "- creating mysql user $myuser@localhost with random password ($pwlength chars)..."
    mypw=$( head /dev/urandom | tr -dc A-Za-z0-9 | head -c $pwlength )

    mysql -e "CREATE USER $myuser@localhost IDENTIFIED BY '$mypw';"
    if [ $? -ne 0 ]; then
        echo "ERROR: mysql command to create user failed."
        exit 1
    fi
    echo "- grant SELECT on mysql tables ..."
    mysql -e "GRANT SELECT ON mysql.* TO $myuser@localhost;"
    if [ $? -ne 0 ]; then
        echo "ERROR: mysql command to grant permissions failed."
        exit 1
    fi

    echo "- flush privileges ..."
    mysql -e "FLUSH PRIVILEGES;"

    echo "- creating config file $cfgfile ... "
    cat  >$cfgfile <<EOF
#
# generated on $(date)
#
[client]
user=$myuser
host=localhost
password=$mypw

EOF
    ls -l $cfgfile
    if [ $? -ne 0 ]; then
        echo "ERROR: creation of config file failed."
        exit 1
    fi
}

# show help text
function showHelp(){
    local _self; _self=$(basename $0)
cat <<EOF
$( ph.showImlHelpHeader )

USAGE:
  $_self [OPTIONS] -m METHOD

OPTIONS:
  -h  this help
  -i  install monitoring user (must be executed as root)
  -u  uninstall monitoring user (must be executed as root)

PARAMETERS:
  -m  method; valid methods are:
      connections     current/ max connections
      connects        connects per min and aborted connections/ clients
      commands        current running statements insert, select, ...
      qcache-blocks   query cache blocks total vs free
      qcache-queries  cached / non cached queries; low memeory prunes 
      qcache-hits     query cache efficiency: cached vs non cached and inserted

EXAMPLES:
  $_self -i
  $_self -m commands

EOF
}

function _mysqlreadvars(){
    mysql -e "SHOW GLOBAL VARIABLES ;" --skip-column-names >$datafile
    mysql -e "SHOW STATUS ;"           --skip-column-names >>$datafile
}

function _mysqlgetvar() {
    local sVarname=$1
    lastvalue=$(grep "^${sVarname}[^_a-z]" ${datafile} | awk '{ print $2 }')
    echo "$lastvalue"
}
# get a value from mysql status output
# param  string  variable name
# param  string  prefix to remove from beginning of variable
# param  (set)   flag if it is a delta value
function _mysqlrendervar() {
    local sVarname=$1
    local sRemove=$2
    local sDeltaUnit=$3
    local sFloat=$4

    # local iValue=`grep "^$sVarname[^_a-z]" ${datafile} | awk '{ print $2 }'`
    local iValue=$(_mysqlgetvar "$sVarname")
    if [ "$iValue" = "" ]; then
            ph.abort "no value for ${sVarname}"
    fi

    # get label for perfdata
    local sLabel=$(echo ${sVarname} | sed "s#^${sRemove}##g")
    if [ ! -z $sDeltaUnit ]; then
        local iSpeed=$( ph.perfdeltaspeed "mysql-${sVarname}" ${iValue} $sDeltaUnit $sFloat)
        out=$out$(printf "%25s: %10s %s \n" "${sLabel}" "${iValue}" "... delta = ${iSpeed} per $sDeltaUnit${NL}")
        ph.perfadd "${sLabel}"   "${iSpeed}"
        lastvalue=$iSpeed
    else
        out=${out}$(printf "%25s: %10s \n" "${sLabel}" "${iValue}${NL}")
        ph.perfadd "${sLabel}" "${iValue}"
        lastvalue=$iValue
    fi

}

function _mysqlrenderdelta() {
    local deltaUnit=$3
    test -z "$deltaUnit" && deltaUnit="sec"
    _mysqlrendervar "$1" "$2" "$deltaUnit" "$4"
}

# helper function for caching checks
# The function aborts the check ending with OK.
function _verify_cache(){
    _mysqlgetvar "have_query_cache" | grep "YES" >/dev/null
    if [ $? -ne 0 ]; then
        echo "INFO: Query cache (have_query_cache) is not active."
        ph.exit
    fi
    _mysqlgetvar "query_cache_type" | grep "OFF" >/dev/null
    if [ $? -eq 0 ]; then
        echo "INFO: Query cache (have_query_cache) is active but query_cache_type is OFF."
        ph.exit
    fi
}
# ----------------------------------------------------------------------
# MAIN
# ----------------------------------------------------------------------

bOptInstall=$(   ph.hasParamoption "i" "$@")
bOptUninstall=$( ph.hasParamoption "u" "$@")
bOptHelp=$(      ph.hasParamoption "h" "$@")

if [ $bOptHelp -eq 1 -o $# -lt 1 ]; then
    showHelp
    exit 0
fi

# --- check required tools
ph.require mysql

# --- install
if [ $bOptInstall -eq 1 -a "$( whoami )" = "root" ]; then
    if [ -f $cfgfile ]; then
        ph.status "SKIP installation. config file already exists: $cfgfile."
        ph.exit
    fi
    HOME=/root
    export HOME

    _uninstall
    _install

    ph.status "Installation was done"
    ph.exit
fi

# --- uninstall
if [ $bOptUninstall -eq 1 -a "$( whoami )" = "root" ]; then
    HOME=/root

    _uninstall
    rm -f $cfgfile

    ph.status "Uninstalled."
    ph.exit
fi


# --- check installation
if ! grep $myuser $cfgfile >/dev/null 2>&1; then
    ph.abort "MYSQL access not possible yet. You need to install the monitoring user first: as root execute $(basename $0) -i"
fi

# ----------------------------------------------------------------------

# --- get data
_mysqlreadvars
if [ $? -ne 0 ]; then
    rm -f $datafile
    ph.abort "MYSQL access not possible. Maybe Mysql is not running??"
fi

# --- set optional limits
typeset -i iWarnLimit=$(     ph.getValueWithParam 0 w "$@")
typeset -i iCriticalLimit=$( ph.getValueWithParam 0 c "$@")

sMode=$(ph.getValueWithParam '' m "$@")

case "${sMode}" in

    "connections")
        descr="current/ max connections"

        _mysqlrendervar    max_connections
        typeset -i iMaxConn=$lastvalue

        _mysqlrendervar    Max_used_connections
        typeset -i iMaxUsed=$lastvalue

        typeset -i iMaxPercent=$iMaxUsed*100/$iMaxConn
        out=$out"                                 --> $iMaxPercent % ${NL}${NL}"

        _mysqlrendervar    Threads_connected
        typeset -i iThreads=$lastvalue
        typeset -i iUsage=$iThreads*100/$iMaxConn
        out=$out"                                 --> $iUsage % ${NL}${NL}"
        if [ $iWarnLimit -gt 0 -a $iCriticalLimit -gt 0 ]; then
            ph.setStatusByLimit $iUsage $iWarnLimit $iCriticalLimit
        fi
        _mysqlrendervar    Questions

        ;;

    "connects")
        descr="connects per min and aborted connections/ clients"
        _mysqlrenderdelta  Connections            "" min float
        _mysqlrenderdelta  Aborted_clients        "" min float
        _mysqlrenderdelta  Aborted_connects       "" min float
        ;;

    "commands")
        descr="currently executed commands"
        _mysqlrendervar    Com_delete  "Com_"
        _mysqlrendervar    Com_insert  "Com_"
        _mysqlrendervar    Com_replace "Com_"
        _mysqlrendervar    Com_select  "Com_"
        _mysqlrendervar    Com_update  "Com_"
        ;;

    "qcache-blocks")
        descr="query cache blocks"
        _verify_cache
        _mysqlrendervar    Qcache_total_blocks "Qcache_"
        _mysqlrendervar    Qcache_free_blocks  "Qcache_"
        ;;

    "qcache-queries")
        descr="query cache data"
        _verify_cache
        _mysqlrendervar    Qcache_queries_in_cache  "Qcache_"
        _mysqlrenderdelta  Qcache_not_cached        "Qcache_"  min  float
        _mysqlrenderdelta  Qcache_lowmem_prunes     "Qcache_"  min  float
        ;;

    "qcache-hits")
        descr="query cache hits"
        _verify_cache
        _mysqlrenderdelta  Qcache_hits        "Qcache_"  min
        _mysqlrenderdelta  Qcache_inserts     "Qcache_"  min
        _mysqlrenderdelta  Qcache_not_cached  "Qcache_"  min
        ;;

    *)
        echo ERRROR: [${sMode}] is an INVALID mode
        showHelp
        ph.abort

esac

ph.status "Mysql $sMode :: $descr"
echo "$out"

rm -f $datafile
ph.exit

# ----------------------------------------------------------------------