connections

// The connections of a MySQL or MariaDB server, by SSH and the mysql client on its server: the threads
// connected against max_connections, the threads running a query (Threads_running), the most ever connected
// at once, and the connections refused because max_connections was reached.
// The client connects over the local socket as the MySQL user named like the SSH account (auth_socket on
// MySQL, unix_socket on MariaDB), or as the user of that account's ~/.my.cnf: no password leaves banshee,
// none is written on a command line.
need('protocol:ssh');

output('connections', 'number', array('label' => 'Threads connected'));
output('connections-percent', 'number', array('label' => 'Connections against max_connections', 'unit' => '%'));
output('running', 'number', array('label' => 'Threads running'));
output('max-used', 'number', array('label' => 'Most connections at once since start'));
output('refused', 'number', array('label' => 'Connections refused at max_connections since start'));
output('aborted', 'number', array('label' => 'Failed connection attempts since start'));

watch.above('saturation', 'Connections near max_connections', 'connections-percent', array('warning' => 80, 'critical' => 90));
watch.increase('refused', 'Connections refused', 'refused', array('critical' => 0, 'window' => '1 h'));
watch.above('running', 'Many threads running', 'running', array('warning' => 50, 'for' => '3 runs'));
watch.increase('aborted', 'Failed connection attempts', 'aborted', array('warning' => 100, 'window' => '1 h', 'enabled' => false));

// One run of the mysql client, its answer tab-separated, without column names. LC_ALL=C keeps its messages in
// English. A server whose socket refuses, or that does not answer (ERROR 2002, 2003), is not running: critical.
function query($sql) {
    $read = ssh.run('LC_ALL=C mysql --batch --skip-column-names', arguments=array('-e', $sql));
    if (list.count(text.match($read['errors'], 'ERROR 200[23] ')) > 0) {
        fail('critical', 'MySQL is not running', $read['errors']);
    }
    if ($read['exit'] != 0) {
        fail('misconfigured', 'mysql failed', $read['errors']);
    }
    return $read['output'];
}

// One line per figure, "Threads_connected<TAB>23". The global status needs no privilege.
$answer = query("SHOW GLOBAL STATUS; SHOW GLOBAL VARIABLES LIKE 'max_connections'");
$status = array();
foreach (text.lines($answer) as $line) {
    $f = text.split($line, "\t");
    if (list.count($f) == 2) {
        $status[$f[0]] = $f[1];
    }
}
// Connection_errors_max_connections exists from MySQL 5.6 and MariaDB 10.0 on.
foreach (array('max_connections', 'Threads_connected', 'Threads_running', 'Max_used_connections', 'Connection_errors_max_connections', 'Aborted_connects') as $name) {
    if (!list.has($status, $name)) {
        fail('misconfigured', 'The server does not give ' . $name, 'MySQL 5.6 or MariaDB 10.0 and later are needed.');
    }
}

value('connections', $status['Threads_connected']);
value('connections-percent', number.round(100 * $status['Threads_connected'] / $status['max_connections'], 1));
value('running', $status['Threads_running']);
value('max-used', $status['Max_used_connections']);
value('refused', $status['Connection_errors_max_connections']);
value('aborted', $status['Aborted_connects']);