Detects infinite loops containing Mysqli prepared statements by comparing the number of prepared statements run by the current process to those still active count on the DB across all processes.

MysqliInfiniteLoopDetector.php

<?php

/**
 * Detects infinite loops containing Mysqli prepared statements
 * by comparing the number of prepared statements run by the current process
 * to those still active count on the DB across all processes.
 * 
 * This class can easily be unit tested by something like this:
    public function testYourQueryFunctionForInfinitLoops()
    {
        $this->expectExceptionMessage(MysqliInfiniteLoopDetector::EXCEPTION_MESSAGE);

        $recurse = function() use (&$recurse){
            // We must assign the result to a variable in order for it to remain in memory
            $result = yourQueryFunction("select ?", 1);
            $recurse();
        };

        $recurse();
    }
 */
class MysqliInfiniteLoopDetector{
    const EXCEPTION_MESSAGE = 'An infinite loop was detected';

    private $connection;
    private $processCountThreshold;
    private $checkIncrement;
    private $systemCountThreshold;
    private $statementCount = 0;
    private $maxPreparedStmtCount = 16382; // Set to MySQL's default value to start.

    function __construct(
        $connection,
        $processCountThreshold = .9,
        $checkIncrement = 1000,
        $systemCountThreshold = .9
    ){
        $this->connection = $connection;
        $this->processCountThreshold = $processCountThreshold;
        $this->checkIncrement = $checkIncrement;
        $this->systemCountThreshold = $systemCountThreshold;
    }

    function onPreparedStatement(){
        $statementCount = ++$this->statementCount;

        if(
             /**
             * Has the current process run enough queries that (if still in memory) would account for 
             * a high enough percentage of max_prepared_stmt_count to be concerning,
             * and warrant additional queries to check Prepared_stmt_count?
             * It is important to stress that this is an educated guess.
             * We do not have a way of definitively knowing what percentage
             * of statements run by the current process are still in memory,
             * and would count toward Prepared_stmt_count.
             */
            $statementCount < ($this->maxPreparedStmtCount * $this->processCountThreshold)
            /**
             * Run only after so many queries (not on every query)
             */
            || $statementCount % $this->checkIncrement !== 0
        ){
            return;
        }

        $systemCount = $this->queryIntValue("SHOW GLOBAL STATUS WHERE variable_name = 'Prepared_stmt_count'");
        if(
            /**
             * Are we approaching max_prepared_stmt_count?
             */
            $systemCount < ($this->maxPreparedStmtCount * $this->systemCountThreshold)
        ){
            return;
        }

        /**
         * We've assumed the default max_prepared_stmt_count value up until now to avoid an extra query.
         * Make sure that value is correct or hasn't changed prior to throwing an exception.
         */
        $maxPreparedStmtCount = $this->queryIntValue("SHOW VARIABLES WHERE Variable_name = 'max_prepared_stmt_count'");
        if($this->maxPreparedStmtCount !== $maxPreparedStmtCount){
            $this->maxPreparedStmtCount = $maxPreparedStmtCount;
            return;
        }

        // The current process is very likely responsible.
        throw new \Exception(static::EXCEPTION_MESSAGE);
    }

    private function queryIntValue($sql){
        $result = mysqli_query($this->connection, $sql);
        return (int) $result->fetch_assoc()['Value'];
    }
}