#!/usr/lib64/linuxfabrik-monitoring-plugins/venv/bin/python
# -*- coding: utf-8; py-indent-offset: 4 -*-
#
# Author:  Linuxfabrik GmbH, Zurich, Switzerland
# Contact: info (at) linuxfabrik (dot) ch
#          https://www.linuxfabrik.ch/
# License: The Unlicense, see LICENSE file.

# https://github.com/Linuxfabrik/monitoring-plugins/blob/main/CONTRIBUTING.md

"""See the check's README for more details."""

import argparse
import sys

import lib.args
import lib.base
import lib.db_mysql
import lib.db_sqlite
import lib.human
import lib.txt
from lib.globals import STATE_OK, STATE_UNKNOWN, STATE_WARN

__author__ = 'Linuxfabrik GmbH, Zurich/Switzerland'
__version__ = '2026051004'

SQLITE_DB = 'linuxfabrik-monitoring-plugins-mysql-connections.db'

DESCRIPTION = """Checks the connection usage rate and the rate of aborted connections in MySQL/MariaDB.
Also verifies whether name resolution is enabled for new connections, which can impact
performance.
Alerts when connection usage or abort rates exceed acceptable levels."""

DEFAULT_DEFAULTS_FILE = '/var/spool/icinga2/.my.cnf'
DEFAULT_DEFAULTS_GROUP = 'client'
DEFAULT_IGNORE_NAME_RESOLUTION = False
DEFAULT_TIMEOUT = 3


def parse_args():
    """Parse command line arguments using argparse."""
    parser = argparse.ArgumentParser(description=DESCRIPTION)

    parser.add_argument(
        '-V',
        '--version',
        action='version',
        version=f'%(prog)s: v{__version__} by {__author__}',
    )

    parser.add_argument(
        '--always-ok',
        help=lib.args.help('--always-ok'),
        dest='ALWAYS_OK',
        action='store_true',
        default=False,
    )

    parser.add_argument(
        '--defaults-file',
        help='MySQL/MariaDB cnf file to read user, host and password from (instead of specifying them on the command line). '
        'Example: `/var/spool/icinga2/.my.cnf`. '
        'Default: %(default)s',
        dest='DEFAULTS_FILE',
        default=DEFAULT_DEFAULTS_FILE,
    )

    parser.add_argument(
        '--defaults-group',
        help=lib.args.help('--defaults-group') + ' Default: %(default)s',
        dest='DEFAULTS_GROUP',
        default=DEFAULT_DEFAULTS_GROUP,
    )

    parser.add_argument(
        '--ignore-name-resolution',
        help='Suppress the warning about active name resolution. Default: %(default)s',
        dest='IGNORE_NAME_RESOLUTION',
        action='store_true',
        default=DEFAULT_IGNORE_NAME_RESOLUTION,
    )

    parser.add_argument(
        '--timeout',
        help=lib.args.help('--timeout') + ' Default: %(default)s (seconds)',
        dest='TIMEOUT',
        type=int,
        default=DEFAULT_TIMEOUT,
    )

    args, _ = parser.parse_known_args()
    return args


def get_vars(conn):
    # Do not implement `get_all_vars()`, just fetch the ones we need for this check.
    # Without the GLOBAL modifier, SHOW VARIABLES displays the values that are used for
    # the current connection to MariaDB.
    sql = """
        show global variables
        where variable_name like 'interactive_timeout'
            or variable_name like 'max_connections'
            or variable_name like 'skip_name_resolve'
            or variable_name like 'wait_timeout'
            ;
          """
    return lib.base.coe(lib.db_mysql.select(conn, sql))


def get_status(conn):
    # Do not implement `get_all_vars()`, just fetch the ones we need for this check.
    # Without the GLOBAL modifier, SHOW STATUS displays the values that are used for
    # the current connection to MariaDB.
    sql = """
        show global status
        where variable_name like 'Aborted_connects'
            or variable_name like 'Connections'
            or variable_name like 'Max_used_connections'
            or variable_name like 'Max_used_connections_time'
            or variable_name like 'Threads_connected'
            or variable_name like 'Threads_running'
            ;
          """
    return lib.base.coe(lib.db_mysql.select(conn, sql))


def main():
    """The main function. This is where the magic happens."""

    # logic taken from mysqltuner.pl:mysql_stats(), section # Connections,
    # verified in sync with MySQLTuner (the 3% aborted-connections
    # threshold and the 85% connection-usage threshold are unchanged
    # upstream since the original port).
    #
    # Intentional deviation: mysqltuner alerts only on the LIFETIME PEAK
    # (Max_used_connections / max_connections > 85%), but for continuous
    # monitoring that signal fires once and stays - it's not actionable.
    # We instead alert on the CURRENT usage (Threads_connected /
    # max_connections > 85%) so spikes show up while they're happening.
    # The peak value is still computed and shown for context, and the
    # corresponding ratio is emitted as the perfdata
    # mysql_pct_max_connections_used so admins can graph both signals.

    # parse the command line
    try:
        args = parse_args()
    except SystemExit:
        sys.exit(STATE_UNKNOWN)

    # fetch data
    mysql_connection = {
        'defaults_file': args.DEFAULTS_FILE,
        'defaults_group': args.DEFAULTS_GROUP,
        'timeout': args.TIMEOUT,
    }
    conn = lib.base.coe(lib.db_mysql.connect(mysql_connection))
    lib.base.coe(lib.db_mysql.check_privileges(conn))

    myvar = lib.db_mysql.lod2dict(get_vars(conn))
    mystat = lib.db_mysql.lod2dict(get_status(conn))
    lib.db_mysql.close(conn)

    # init some vars
    state = STATE_OK
    perfdata = ''
    mycalc = {}
    # Short facts are accumulated as `; `-separated items and rendered on a
    # single line; recommendations land in their own bulleted block at the end.
    facts = []
    # All recommendations from all WARN paths land here and render once at the
    # end as a `Recommendations:\n* ...` bulleted block, regardless of which
    # combinations of WARN paths fire.
    recommendations = []

    # analyze data
    # Aborted Connections
    aborted_connections_state = STATE_OK
    mycalc['pct_connections_aborted'] = round(
        int(mystat['Aborted_connects']) / int(mystat['Connections']) * 100, 1
    )
    if mycalc['pct_connections_aborted'] > 3:
        aborted_connections_state = STATE_WARN
        state = lib.base.get_worst(state, aborted_connections_state)
        recommendations.append(
            'Reduce or eliminate unclosed connections and network issues'
        )
    aborted = lib.human.number2human(mystat['Aborted_connects'])
    total_conn = lib.human.number2human(mystat['Connections'])
    aborted_str = lib.base.state2str(aborted_connections_state, prefix=' ')
    facts.append(
        f'{mycalc["pct_connections_aborted"]}% aborted connections'
        f' ({aborted}/{total_conn}){aborted_str}'
    )

    # currently used Connections
    cur_connections_state = STATE_OK
    mycalc['pct_connections_used'] = round(
        int(mystat['Threads_connected']) / int(myvar['max_connections']) * 100, 1
    )
    mycalc['pct_connections_used'] = min(
        100, mycalc['pct_connections_used']
    )  # value cannot be higher than 100
    if mycalc['pct_connections_used'] > 85:
        cur_connections_state = STATE_WARN
        state = lib.base.get_worst(state, cur_connections_state)
        recommendations.append(
            f'Reduce or eliminate persistent connections to reduce connection'
            f' usage (raise `max_connections` > {myvar["max_connections"]},'
            f' lower `wait_timeout` < {myvar["wait_timeout"]} and/or lower'
            f' `interactive_timeout` < {myvar["interactive_timeout"]})'
        )
    threads_plr = lib.txt.pluralize('thread', mystat['Threads_running'])
    cur_str = lib.base.state2str(cur_connections_state, prefix=' ')
    facts.append(
        f'current {mycalc["pct_connections_used"]}% used'
        f' ({mystat["Threads_connected"]}/{myvar["max_connections"]},'
        f' {mystat["Threads_running"]} {threads_plr} running){cur_str}'
    )

    # Max. used Connections
    mycalc['pct_max_connections_used'] = round(
        int(mystat['Max_used_connections']) / int(myvar['max_connections']) * 100, 1
    )
    mycalc['pct_max_connections_used'] = min(
        100, mycalc['pct_max_connections_used']
    )  # value cannot be higher than 100
    peak_time = (
        f' at {mystat["Max_used_connections_time"]}'
        if mystat.get('Max_used_connections_time')
        else ''
    )
    facts.append(
        f'peak {mycalc["pct_max_connections_used"]}% used'
        f' ({mystat["Max_used_connections"]}/{myvar["max_connections"]})'
        f'{peak_time}'
    )

    i_timeout = lib.human.seconds2human(myvar['interactive_timeout'])
    w_timeout = lib.human.seconds2human(myvar['wait_timeout'])
    facts.append(
        f'`interactive_timeout` = {i_timeout}, `wait_timeout` = {w_timeout}'
    )

    # name resolution: alert when MySQL/MariaDB does a reverse DNS lookup per connection.
    # mysqltuner skips the test if `skip_networking == ON` (no TCP, so name resolution is
    # moot) or if `skip_name_resolve` is undefined (very old MySQL). It also accepts both
    # the textual `ON`/`OFF` and the numeric `1`/`0` forms that some versions return.
    if not args.IGNORE_NAME_RESOLUTION:
        skip_networking = myvar.get('skip_networking')
        skip_name_resolve = myvar.get('skip_name_resolve')
        if skip_networking in ('ON', '1'):
            pass  # MySQL not on TCP, name resolution irrelevant
        elif skip_name_resolve is None:
            pass  # variable does not exist on this version
        elif skip_name_resolve not in ('ON', '1'):
            name_resolution_state = STATE_WARN
            state = lib.base.get_worst(state, name_resolution_state)
            nr_str = lib.base.state2str(name_resolution_state, prefix=' ')
            facts.append(f'Name resolution is active{nr_str}')
            recommendations.append(
                'A reverse name resolution is made for each new connection and'
                ' can reduce performance. Configure your accounts with ip or'
                ' subnets only, then update your configuration with'
                ' `skip-name-resolve=ON`'
            )

    perfdata += lib.base.get_perfdata(
        'mysql_interactive_timeout',
        myvar['interactive_timeout'],
        uom='s',
        _min=0,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_max_connections',
        myvar['max_connections'],
        _min=0,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_wait_timeout',
        myvar['wait_timeout'],
        uom='s',
        _min=0,
    )

    # Per-CONTRIBUTING: emit Aborted_connects and Connections as in-plugin per-second
    # deltas (against a local SQLite cache) instead of cumulative `c` counters that
    # force Grafana to do non_negative_difference() per panel.
    rates = lib.db_sqlite.per_second_deltas(
        SQLITE_DB,
        'mysql-connections',
        {
            'aborted_connects': int(mystat['Aborted_connects']),
            'connections': int(mystat['Connections']),
        },
    )
    if rates is not None:
        perfdata += lib.base.get_perfdata(
            'mysql_aborted_connects_per_second',
            int(rates['aborted_connects']),
            _min=0,
        )
        perfdata += lib.base.get_perfdata(
            'mysql_connections_per_second',
            int(rates['connections']),
            _min=0,
        )

    perfdata += lib.base.get_perfdata(
        'mysql_max_used_connections',
        mystat['Max_used_connections'],
        _min=0,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_threads_connected',
        mystat['Threads_connected'],
        _min=0,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_threads_running',
        mystat['Threads_running'],
        _min=0,
    )

    perfdata += lib.base.get_perfdata(
        'mysql_pct_connections_aborted',
        mycalc['pct_connections_aborted'],
        uom='%',
        _min=0,
        _max=100,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_pct_connections_used',
        mycalc['pct_connections_used'],
        uom='%',
        _min=0,
        _max=100,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_pct_max_connections_used',
        mycalc['pct_max_connections_used'],
        uom='%',
        _min=0,
        _max=100,
    )

    # build the message
    sections = ['; '.join(facts)]
    if recommendations:
        sections.append(
            'Recommendations:\n' + '\n'.join(f'* {r}' for r in recommendations)
        )
    msg = '\n\n'.join(sections)

    # over and out
    lib.base.oao(msg, state, perfdata, always_ok=args.ALWAYS_OK)


if __name__ == '__main__':
    try:
        main()
    except Exception:
        lib.base.cu()
