Mysqlnd: packet headers bigger than buffer (4096) miss DB errors and put driver in inconsistent state

[wiebeytec]

Description

The following was observed when using MySQL to communicate with Vitess, a MySQL database sharding layer. It is much more verbose in its errors (it prints the query back), so the following is triggered. I realize it's with Vitess, but mysqlnd shouldn't rely on conciseness of errors to keep from failing.

The situation:

The mysqlnd driver reserves 4096 bytes for the header buffer and when it's too big, it gives an error only visible in debug tracing. See here.

You then get a PHP warning "Error reading result set's header". This is a warning, not an exception, even though PDO is set to exceptions.

Because it didn't finish parsing the packet, it now doesn't realize this query failed. Vitess rejected a NULL in a NOT NULL column. All php did was print a warning about the header, and then next queries complain about active queries in unbuffered mode (which is required for Vitess).

The following code triggers it but only on Vitess:

create table mychars(val text not null);

<?php

// Won't fail on stock MySQL, but will on Vitess

function connect_to_database_base($host, $user, $password, $dbname) : PDO {
  $db = new PDO('mysql:host=' . $host . ';dbname=' . $dbname, $user, $password, array(
    PDO::ATTR_ERRMODE              =>  PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND   =>  'SET NAMES utf8 COLLATE utf8_unicode_ci',
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
  ));
  return $db;
}

function connect_to_database() : PDO {
  return connect_to_database_base("172.31.4.54", "user_dev", "password", "dbname");
  
}

$db = connect_to_database();

// Create a long query, with invalid data.
$myquery = "INSERT INTO mychars(val) VALUES ('first'), ('bla ";
for ($i = 0; $i < 800; $i++) {
  $myquery .= " bla ";
}
$myquery .= "'), (NULL)";

print("I'm going to perform this query:\n\n$myquery\n\n");

$db->exec($myquery);

print("\nAs you can see, it generated an uncatchable 'warning'. PDO will now not see that it was an error.\n");

print("The only reason this error is detected, because we can't keep using the unbuffered connection. Doing a select:\n\n");
$db->query('SELECT * FROM mychars LIMIT 1');

print("End\n");
?>

Resulted in this output:

I'm going to perform this query:

INSERT INTO mychars(val) VALUES ('first'), ('bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla  bla '), (NULL)


Warning: PDO::exec(): Error reading result set's header in /root/reproduce_truncated_result_header_longtext.php on line 27

As you can see, it generated an uncatchable 'warning'. PDO will now not see that it was an error.
The only reason this error is detected, because we can't keep using the unbuffered connection. Doing a select:


Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. in /root/reproduce_truncated_result_header_longtext.php:32
Stack trace:
#0 /root/reproduce_truncated_result_header_longtext.php(32): PDO->query('SELECT * FROM m...')
#1 {main}
  thrown in /root/reproduce_truncated_result_header_longtext.php on line 32

But I expected this instead:

I'm going to perform this query:

INSERT INTO mychars(val) VALUES ('first'), ('bla  bla  bla '), (NULL)


Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 target: vitess_production_clone.0.primary: vttablet: rpc error: code = InvalidArgument desc = Column 'val' cannot be null (errno 1048) (sqlstate 23000) (CallerID: userData1): Sql: "insert into mychars(val) values (:vtg1), (:vtg2), (null)", BindVars: {vtg1: "type:VARCHAR value:\"first\""vtg2: "type:VARCHAR value:\"bla  bla  bla \""} in /root/reproduce_truncated_result_header_longtext.php:27
Stack trace:
#0 /root/reproduce_truncated_result_header_longtext.php(27): PDO->exec('INSERT INTO myc...')
#1 {main}
  thrown in /root/reproduce_truncated_result_header_longtext.php on line 27

• I expect that the fact that the query gave an error was properly registered (it is not now)
• I also expect that the connection doesn't keep a cursor open and so doesn't fail on subsequent queries.

Tested with 8.1.11 tarball, compiled with debugging.

PHP Version

PHP 8.1.11, compiled debug version

Operating System

Ubuntu 22.04

[kamil-tekiela]

The value 4096 is the minimum. mysqlnd allows you to set higher value if you desire. The problem is that while mysqli exposes this as an option $mysqli->options(MYSQLI_OPT_NET_CMD_BUFFER_SIZE, 2**20);, PDO does not.

@cmb69 I changed it from mysqlnd to PDO_MySQL bug, but maybe there is a way to solve it at the mysqlnd level too. But I don't know how. At the moment, the simplest fix would be to expose this option via PDO options.

[kamil-tekiela]

As a workaround, you can just increase the buffer size in the php.ini file. Change mysqlnd.net_cmd_buffer_size to something larger than 4096

[wiebeytec]

I can try that. It would have to be pretty big if Vitess is going to echo my long multi-value statements back.

But the problem is that a user won't know they have to do this? The driver just misses information and the user will never see it. Shouldn't SET_CLIENT_ERROR be called here?

As a side note, now that you mention net_cmd_buffer_size, we found an earlier report: 78797 mysqlnd cannot read large OK/ERR packets

[kamil-tekiela]

@wiebeytec I will look into it. You may be right that there should either be a hard error or a better way to handle this case.

[kamil-tekiela]

I took a deeper look into the code and you are right. There's absolutely no reason for mysqlnd to choke up like that. We should resize the buffer automatically as we do with an outgoing packet. The problem with this is that the code structure doesn't allow us to do that easily. So we either need to refactor this piece of code or find a smart way to work around it. If anyone wants to have a go at fixing this, feel free to do so.

I looked into adding a client error. I'm not sure we need that. While it would be nice to inform the developer of what exactly went wrong, it won't have much effect. If PDO is in silent mode or in warning mode, the code will still not know how to proceed with a partially read result header. If we accept that this situation is a bug then telling the developer about a bug is not a very smart move. Instead of adding a client error, we should make it so that the buffer resizes.

But, assuming that most people use exception mode and refactoring this code probably won't happen soon, I think it's not a terrible idea to add a client error anyway even if just as a temporary workaround. It doesn't solve the problem, but makes it a nicer experience for developers running into this bug.