#!/usr/bin/env bash
#
# JbTecWiz Support Centre -- generated fix script
#
# Fault : MySQL: "Too many connections" or "Access denied for user"
# Fix   : Find what is holding the connections
# Source: https://jbtecwiz.com/support/lnx-web-mysql-connections
#
# Run as     : Root shell
# Expect     : 30 minutes
# Risk       : medium
# Reversible : yes
#
# WHEN THIS IS THE RIGHT FIX
#   Error 1040.
#
# HOW TO UNDO IT
#   SET GLOBAL max_connections back to the previous value; remove the
#   configuration file to revert permanently.
#
# Walks the fix one step at a time and asks before each. Steps with no
# command are yours to do -- it prints those and waits. DRYRUN=1 prints
# without executing; UNATTENDED=1 does not ask.
#
# --------------------------------------------------------------------
# NO WARRANTY - USE AT YOUR OWN RISK
#
# This script is provided by JbTecWiz as-is and with no warranty of any
# kind, express or implied. You run it entirely at your own risk.
#
# JbTecWiz accepts no liability for any loss or damage arising from its
# use, including but not limited to data loss, downtime, or configuration
# changes that turn out to be wrong for your system.
#
# You are responsible for reading this script before running it, for
# satisfying yourself that it suits the machine in front of you, and for
# having a working backup first. Some steps cannot be undone.
# --------------------------------------------------------------------

set -uo pipefail
DRYRUN="${DRYRUN:-0}"
UNATTENDED="${UNATTENDED:-0}"
failed=0

if [ "$(id -u)" -ne 0 ]; then
  echo "  This fix is documented as needing root. Re-run with sudo." >&2
  exit 3
fi

rule() { printf "\n%s\n" "$(printf '-%.0s' $(seq 1 70))"; if [ $# -gt 0 ]; then echo "$1"; fi; }
prose() { echo "$1" | fold -s -w 74 | sed "s/^/  /"; }

# Returns 0 when the caller should run the command, 1 when it should not.
# A manual step always returns 1 -- there is nothing for the caller to run.
step() {  # step <n> <do> <why> <manual|cmd> [command lines...]
  local n="$1" dotext="$2" why="$3" mode="$4"; shift 4
  rule "  Step $n of 6"
  prose "$dotext"
  if [ -n "$why" ]; then echo; prose "$why"; fi
  if [ "$mode" = "manual" ]; then
    echo; echo "  -> Do this yourself, then press Enter to carry on."
    if [ "$UNATTENDED" = "0" ] && [ "$DRYRUN" = "0" ]; then read -r _; fi
    return 1
  fi
  echo; printf "    %s\n" "$@"; echo
  if [ "$DRYRUN" = "1" ]; then echo "  (dry run -- not executed)"; return 1; fi
  if [ "$UNATTENDED" = "0" ]; then
    read -r -p "  Run this step? [Y]es / [S]kip / [Q]uit " a
    case "$a" in
      [Qq]*) echo "  Stopped at your request."; exit 0 ;;
      [Ss]*) echo "  Skipped."; return 1 ;;
    esac
  fi
  return 0
}

rule
echo "  MySQL: "Too many connections" or "Access denied for user""
echo "  Find what is holding the connections"
echo
echo "  Risk: medium   Reversible   30 minutes"
echo
prose 'No warranty. Use at your own risk - JbTecWiz accepts no liability. Read it before you run it, and have a backup.'
rule
echo
if [ "$UNATTENDED" = "0" ] && [ "$DRYRUN" = "0" ]; then
  read -r -p "  Ready? [y/N] " go
  case "$go" in [Yy]*) ;; *) echo "  Nothing was changed."; exit 0;; esac
fi

if step 1 'Look at what is connected and in what state.' 'A long list of connections in the Sleep state with a high time value is an application leaking connections, not a database that needs a bigger limit. Raising max_connections there just delays the same outage and adds memory pressure.' cmd 'mysql -e "SHOW STATUS LIKE '\''Threads_connected'\''; SHOW STATUS LIKE '\''Max_used_connections'\''; SHOW VARIABLES LIKE '\''max_connections'\'';"' 'mysql -e "SELECT user,host,db,command,time,state FROM information_schema.processlist ORDER BY time DESC LIMIT 20"'; then
  mysql -e "SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections'; SHOW VARIABLES LIKE 'max_connections';"
  mysql -e "SELECT user,host,db,command,time,state FROM information_schema.processlist ORDER BY time DESC LIMIT 20"
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 1 failed. The rest of the fix may depend on it." >&2
  fi
fi

if step 2 'Count connections by host to identify the offending application.' '' cmd 'mysql -e "SELECT SUBSTRING_INDEX(host,'\'':'\'',1) AS h, COUNT(*) c FROM information_schema.processlist GROUP BY h ORDER BY c DESC"'; then
  mysql -e "SELECT SUBSTRING_INDEX(host,':',1) AS h, COUNT(*) c FROM information_schema.processlist GROUP BY h ORDER BY c DESC"
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 2 failed. The rest of the fix may depend on it." >&2
  fi
fi

step 3 'Fix the application -- enable connection pooling with a sensible maximum, or make sure connections are closed. Persistent connections in PHP with a large FPM pool are a frequent cause.' '' manual || true

if step 4 'Raise the limit if the load is genuine, accounting for memory per connection.' '' cmd 'mysql -e "SET GLOBAL max_connections = 300"'; then
  mysql -e "SET GLOBAL max_connections = 300"
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 4 failed. The rest of the fix may depend on it." >&2
  fi
fi

if step 5 'Make it permanent and restart during a window.' '' cmd 'printf '\''[mysqld]\nmax_connections = 300\n'\'' | sudo tee /etc/mysql/mysql.conf.d/99-connections.cnf'; then
  printf '[mysqld]\nmax_connections = 300\n' | sudo tee /etc/mysql/mysql.conf.d/99-connections.cnf
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 5 failed. The rest of the fix may depend on it." >&2
  fi
fi

if step 6 'Note that MySQL reserves one extra connection for a SUPER user, which is how to get in when it is full.' '' cmd 'mysql -u root -p --protocol=socket'; then
  mysql -u root -p --protocol=socket
  if [ $? -ne 0 ]; then
    failed=$((failed+1))
    echo "  Step 6 failed. The rest of the fix may depend on it." >&2
  fi
fi

rule "  Confirm it worked"
prose 'Threads_connected stays below the limit at peak and Max_used_connections stops climbing.'
if [ "$DRYRUN" = "0" ]; then
  mysql -e "SHOW STATUS LIKE 'Max_used_connections%'"
fi

rule
if [ "$failed" -gt 0 ]; then
  echo "  Finished with $failed failed step(s)."
  echo "  Read the full write-up at https://jbtecwiz.com/support/lnx-web-mysql-connections"
else
  echo "  Finished."
fi
echo
prose 'To undo: SET GLOBAL max_connections back to the previous value; remove the configuration file to revert permanently.'
rule