puppet leaking mysql connections

Puppet 25.4 and 25.5 leaks MySQL connections when using stored configurations and using MySQL as the database backend. This is a well known issue and appears to have been recently fixed for an upcoming release.

I want to implement stored configurations, but I can’t have leaking MySQL connections in production, I don’t want to wait until the fix gets released, nor do I want to integrate the patch and build my own RPM. I came up with a workaround….a simple script that will kill the hanging MySQL connections.

<?php

$db_host = 'localhost';
$db_user = 'username';
$db_pass = 'password';
$db_name = 'puppet_database_name';
$mysqladmin = '/usr/bin/mysqladmin';

# Connections caught sleeping after this many seconds will be terminated.
$timeout = 300;

$cmd = "$mysqladmin -u $db_user -p$db_pass processlist";
exec($cmd, $output, $status);

if($status != 0)
{
    echo "ERROR: unable to retrieve processlist\n";
    exit(1);
}

for($i=0; $i<count($output); $i++)
{
    if(preg_match("/\|\s+(\d+)\s+\| $db_user\s+\| .+ \| $db_name\s+\| Sleep\s+\| (\d+)\s+\|/", $output[$i], $matches))
    {
        if($matches[2] > $timeout)
        {
            $cmd = "$mysqladmin -u $db_user -p$db_pass kill $matches[1]";
            exec($cmd, $output2, $status);
            if($status != 0)
            {
                echo "ERROR: unable to kill MySQL thread $matches[1]\n";
                exit(1);
            }
        }
    }
}

What this script does is uses the fact that any mysql user can view and kill their own mysql connections (so it is important that you use the same connection information as defined in your puppet.conf file for this script). It uses the mysqladmin command processlist to retrieve the list of threads, and then kills any thread that has a Time greater than the value specified by timeout.

I would run this script from a crontab every 5 minutes.

Use this at your own risk. I will update this post with any caveats and additional thoughts that I find.