1

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.

Alex the Marrch Ca'at
  • 11
  • 1
  • Ignoring the language, [does the answers to this question help?](https://stackoverflow.com/q/16459990/5897602). Seems less of a programmatic issue, and more of a network issue. – Jaquarh Sep 22 '21 at 18:19
  • I would assume that the packet has more than 7 bytes of overhead. – Sammitch Sep 22 '21 at 19:12
  • Thank you for the idea, @Jaquarh, but I don't think so. The error message I get specifies error code 1153 and mentiones exceeding max_allowed_packet bytes length. Also, it's a production system with lots of queries per second and everything works fine 99.99% of the time; to say more, the script I'm running is working fine too --- for some time, and fails only on some specific data sets that make the query get quite close to the limit. – Alex the Marrch Ca'at Sep 22 '21 at 21:09
  • I probably could pretend that the max_size is a bit lower than the actual limit, say (int) $d['Value'] - 256 or (int) ($d['Value'] * 0.9), and that would work. But I want to understand the causes of the problem: why does the real limit differ from the max_allowed_packet ? What's the difference and what does the difference value depend on? That's why I asked my question with hope someone has already solved it and could answer it. – Alex the Marrch Ca'at Sep 22 '21 at 21:14

2 Answers2

2

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).

W S
  • 362
  • 3
  • 4
  • I know that strlen() doesn't handle MB chars correctly, but its problem is that it counts bytes instead of character, not the opposite as you say. If you want to count characters, you have to use mb_strlen(), but I needed bytes, indeed. See https://3v4l.org/KZuHo – Alex the Marrch Ca'at Sep 22 '21 at 20:56
  • Did you try to scpecify encoding? $bytes = mb_strlen($a, '8bit'); – W S Sep 22 '21 at 21:14
  • It's possible, of course :) But there is no need to, since strlen() calculates size of the string in bytes, exactly as I need. And well, (1) there is no multibyte characters in the query at all, and (2) I've captured the query I have problem with, saved it to a file and its size is exactly 33554425, so it's the correct value. – Alex the Marrch Ca'at Sep 22 '21 at 21:28
  • Okay, then what happen if 1) you increace the limit of max_allowed_packet? what value will be acceptable? point is to calc how many bytes we lose, exact 7 or more? 2) what if you save your query in mysql row, and read from this cell, what size we get? – W S Sep 22 '21 at 21:35
  • I can't afford reconfiguring production DB just to test, but I did some other tests. 1) When I save the query to a file and execute it via mysql console client, it works fine; and it still works if I add 5 more bytes to the query data, and when I change any part with the overall size remaining the same -- it works too, so the real query limit is always just 2 byte less then max_allowed_packed. This makes sense since there should be either query size or something like zero-character at the end of string. – Alex the Marrch Ca'at Sep 22 '21 at 22:33
  • 2) When I read the same file to a string and execute it via PDO, it fails. So the problem is that PDO adds some overhead. – Alex the Marrch Ca'at Sep 22 '21 at 22:34
  • That make sense, becouse line ending add 1 extra byte: http://sandbox.onlinephpfunctions.com/code/fe9334b6df8a5c45ba5766a7d3aa5f4081834d47 - are you sure that you have 2 bytes less? – W S Sep 22 '21 at 22:39
  • Yes, I've re-checked that several times. I'll try to find the precise difference between real query size limit and max_allowed_packet in case of PDO, but it's obviously more than 2 bytes (7 bytes at least) and I still can't understand why it's so... – Alex the Marrch Ca'at Sep 22 '21 at 23:15
0

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!

Alex the Marrch Ca'at
  • 11
  • 1