Php + mysql - connection dies consistantly after 66,946 chars of query input

Question

I've been trying to debug a randomly dropping mysql connection in my php project.

Things I've discovered so far:

• Once the input over a single mysql connection (total chars used in queries over a single mysql connection) passes 66,946 chars, php just locks up indefinitely on the query that caused it to go over.
• If I reconnect the mysql server occasionally rather than re-using the existing connection for the whole time, the mysql connection won't drop as long as I don't go over 66946 chars in the input for any single mysql connection.
• It doesn't matter whether it's a single query, or a bunch of little queries. As soon as the "66947" threshold is passed on a single mysql connection, php hangs indefinitely.
• It's not a memory issue. Php is only taking 10Mb of memory at most, and it has a max memory of 512Mb.
• The mysql connection is remote, if that matters.
• This exact code works on my local dev environment (with any length of query, same remote connection), but not on the production server with queries adding up to over 66,946 chars in length
• The php version & config files for my dev environment and the live environment are identical, and both are running Ubuntu (well, locally it's technically WSL)
• Switching between mysqli and PDO doesn't make a significant difference (just a different number of input chars before it crashes, less chars with PDO than mysqli)
• (update): I also tried this script on another similar ubuntu host with the same version of PHP, using the same mysql host. It worked flawlessly there... so I'm really lost as to what the issue could be.

I've narrowed it down to this minimal reproduction case:

<?php
if ( ! empty($argv[1])) {
    $count = intval($argv[1]);
}
if (empty($count) || $count < 34) {
    $count = 34;
}

$longText         = str_repeat("a", $count - 34);
$query            = "select * from abcdef limit 1/* {$longText} */"; // where "abcdef" is my table name
$mysqliConnection = mysqli_connect("my_host", "my_username", "my_password", "my_database");
echo "query length: " . strlen($query);
$result = mysqli_query($mysqliConnection, $query);
echo "

Success!

";

providing the argument 66946 returns "success!" instantly, and 66947 just makes the production server hang indefinitely (but works fine in my local box!).

Why is there some mysterious input limit on my php mysql connections? How can I stop it (whatever "it" is) from limiting my php mysql connection input length?

question from:https://stackoverflow.com/questions/65713105/php-mysql-connection-dies-consistantly-after-66-946-chars-of-query-input

Answer 1

You're probably looking for max_allowed_packet. It's defined in my.cnf.

Use SHOW VARIABLES LIKE 'max_allowed_packet'; to check it.