MySQL complains about max_allowed_packet violation with query shorter than the allowed limit-CodePudding

Doing a multi-insert query, I receive error "SQLSTATE[08S01]: Communication link failure: 1153 Got a packet bigger than 'max_allowed_packet' bytes". I have a complex class that composes multi-insert queries thoroughly checking against the limit, so it's really strange, but well, we're all human, so after some tries to find the problem I just caught and logged the exception details and can't understand what's on. The code (PHP 7.4, PDO, MariaDB 10.5.10):

        $this->pdoInstance = sql_get_connect($this->connection);

        $stmt = $this->pdoInstance->query("show variables like 'max_allowed_packet'");
        $d = sql_fetch($stmt);
        $stmt->closeCursor();
        $this->max_size = (int) $d['Value'];
...
        try {
            $stmt = $this->pdoInstance->query($query);
        } catch (CDbException $e) {
            if (strpos($e->getMessage(), 'max_allowed_packet') !== false) {
                log ([
                    'total_size' => strlen($query),
                    'max_size' => $this->max_size,
                ];
            }
            throw $e;
        }

And what I get is:

total_size => 33554425
max_size => 33554432

So the query I send is actually 7 bytes less than the limit. But the problem is reproduced stably. What could cause that? Probably there's some headers sent with the query or anything, but how could I determine their size? I've found nothing about it nor in MySQL neither in MariaDB docs.

CodePudding user response：

strlen() is not handling multibyte characters correctly, as it assumes 1 char equals 1 byte, which is simply invalid for unicode.

Try to use mb_strlen() function instead (mb stands for multi byte).

CodePudding user response：

Well, the problem appeared to be simple as an egg. There is a PHP Extension installed on our app servers that interferes the PDO query execution, adding special comments there. It's a part of complex intrusion detection system or something like that, but it was never documented that it affects the query length; now I know that.

Sorry for disturbing and thanks to everyone for trying to help!