#!/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__ = '2026101401'

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. The abort rate is only
evaluated once the server has accepted at least `--min-connections` connections (default 100),
because a handful of aborted connections out of a few dozen is no rate."""

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


def parse_args():
    """Parse command line arguments using argparse."""
    parser = argparse.ArgumentParser(
        description=DESCRIPTION,
        epilog=lib.args.epilog(__file__),
        formatter_class=lib.args.HelpFormatter,
    )

    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. '
        'Example: `--defaults-file=/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.',
        dest='IGNORE_NAME_RESOLUTION',
        action='store_true',
        default=DEFAULT_IGNORE_NAME_RESOLUTION,
    )

    parser.add_argument(
        '--min-connections',
        help='Minimum number of connections the server has accepted since '
        'its start before the percentage of aborted connections is '
        'evaluated. The percentage is a lifetime average, so right after a '
        'restart a single aborted connection out of a few dozen would '
        'exceed the threshold. Below the minimum the percentage is reported '
        'but does not raise WARN. '
        'Example: `--min-connections=1000`. '
        'Default: %(default)s',
        dest='MIN_CONNECTIONS',
        type=int,
        default=DEFAULT_MIN_CONNECTIONS,
    )

    parser.add_argument(
        '--no-perfdata',
        help=lib.args.help('--no-perfdata'),
        dest='NO_PERFDATA',
        action='store_true',
        default=False,
    )

    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
    )
    # A lifetime percentage over a few dozen connections is noise: right after a
    # restart 3 aborted connections out of 29 read as "10.3%" and raised a WARN.
    # mysqltuner does not skip this case; we do (Linuxfabrik addition).
    too_few_connections = int(mystat['Connections']) < args.MIN_CONNECTIONS
    if mycalc['pct_connections_aborted'] > 3 and not too_few_connections:
        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}'
    )
    if too_few_connections:
        facts[-1] += (
            f' (not evaluated below {lib.human.number2human(args.MIN_CONNECTIONS)}'
            f' connections)'
        )

    # 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, no_perfdata=args.NO_PERFDATA
    )


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