New problem on MySQL, called “tmc” – “Too Many Connections”. In case of environment with many db users, different applications/services and load balancing the investigation is not simple.

First of all I have to clarify some question: there are two types of such event.

Error: 1040 SQLSTATE: 08004 (ER_CON_COUNT_ERROR) – this concerns global connections.
Error: 1203 SQLSTATE: 42000 (ER_TOO_MANY_USER_CONNECTIONS) – this concerns only one user connections limit.
A very useful information is that your MySQL server limits number of connections to max_connections+1 (the extra connection is reserved for use by accounts that have the SUPER privilege).

After some analysis I realized that problem is incoming from one of www servers so I had to trace it. Cause it was from Apache I was able to profit from module “status_module” (plse take care about ExtendedStatus On).

Below I present ready-to-use script which catches list of process of database instance and apaches – it helped me a lot to identify the problem. As a result you additionaly obtain list of system processes of http user and further history of database process list (next 10 s).

How to use it. Named it tmc.sh and bring it to cron with parameter keep-alive, e.g.:
*/2 * * * * /root/scripts/cron/tmc.sh keep-alive > /dev/null 2>&1

#!/bin/bash

# @desc: Script is intended to find and log events when error 1040 (too many connections) occures.

MYSQL_HOMEDIR="/usr/local/services/mysql/bin/"
MYSQL_CMD="mysql"

# User w/o Super_priv
MY_PASSWD="YourPassword";
MY_USER="YourUser";

# User with Super_priv
ROOT_PSSWD="RootPassword";
ROOT_USER="root";

LOCKFILE="/tmp/tmc.lock";

# To be set
HOMEDIR="";
LOGDIR="tmc/";
fileExec="";

# Block functions

# This function gives you more info about unix processes
# until you put there to index.php file lines
# &1`;
    TOO_MANY_CONNECTION=`echo $CONNECTION_INFO | grep '1040' | sed 's/\s//g'`;
    if [ ! -z "$TOO_MANY_CONNECTION" ]; then
	subdir=$(date +"%Y%m%d_%H%M%S");
	subdir=$LOGDIR$subdir;
	mkdir $subdir;
	$MYSQL_HOMEDIR$MYSQL_CMD --protocol=tcp -N -u$ROOT_USER --password=$ROOT_PSSWD  -e "SELECT * FROM information_schema.PROCESSLIST;" > $subdir/process_list 2>&1;
	log_events $subdir "192.168.1.20";
	log_events $subdir "192.168.1.21";
	log_events $subdir "192.168.1.22";
	log_events $subdir "192.168.1.23";
	
	
	log_processes $subdir "192.168.1.20";
	log_processes $subdir "192.168.1.21";
	log_processes $subdir "192.168.1.22";
	log_processes $subdir "192.168.1.23";
	LOG_PS_CONTINOUS=10;
    fi

    if [ "$LOG_PS_CONTINOUS" -gt "0" ]; then
	postfix=$(date +"%M%S");
	$MYSQL_HOMEDIR$MYSQL_CMD --protocol=tcp -N -u$ROOT_USER --password=$ROOT_PSSWD  -e "SELECT * FROM information_schema.PROCESSLIST;" > $subdir/process_list_$postfix 2>&1;
	((LOG_PS_CONTINOUS--));
    fi;

    if [ ! -f $LOCKFILE ]; then
	break;
    fi

    sleep 1;
    done;
}

function kill_process {
    info="";
    rm -v -f $LOCKFILE;
    sleep 2;
    check_pid;
    retval=$?;
    if [ "$retval" -gt "0" ]; then
	kill -9 $DAEMON_PID;
	info="Proces was killed";
    else 
	info="Process was stopped";
    fi;    
    echo $info;    
}

case "$1" in
    status)
        check_pid;
        echo $?;
        ;;
    'kill')
	kill_process;
	;;
    keep-alive)
	check_pid;
	retval=$?;
	if [ "$retval" = "0" ]; then
	    start_process;
	fi
	;;
    *)
    echo "Usage: $0 (status|kill|keep-alive)"
    ;;
esac

exit 0;

You can kill this process with option “kill”.
End.

ps. in my case the cause was in php mysql_connect function with option new_link set on true.