MariaDB: pipeline COM_STMT_PREPARE and COM_STMT_EXECUTE with statement id -1

[sidorares]

Summary

The first execute() of a new SQL text on a connection costs two round trips: COM_STMT_PREPARE, wait for the statement id, then COM_STMT_EXECUTE. Later executions hit the statement cache and cost one. MariaDB's protocol allows sending both in one flight: per the MariaDB COM_STMT_EXECUTE documentation, the statement id value -1 (0xFFFFFFFF) "can be used to indicate to use the last statement prepared on current connection if no COM_STMT_PREPARE has failed since". MariaDB Connector/J uses this. MySQL has no equivalent.

Priority: low. One round trip saved per distinct statement per connection, for MariaDB users only, against a non-trivial change to the command pipeline. Filed so the design notes and the probe are not lost.

Verified locally (Docker)

A probe built on the driver's own Prepare/Execute state machines sent both packets back to back and read both responses:

Server	Result
MariaDB 12.3.2	SELECT ? + ? AS sum, ? AS label prepared and executed in one flight returns { sum: 42, label: 'pipelined' }
MariaDB, after a failed prepare	COM_STMT_EXECUTE with -1 is rejected (Unknown prepared statement handler (4294967295)), so it can never run an older statement by mistake
MySQL 8.3.0 and 9.7.2	rejected outright: Unknown prepared statement handler (4294967295) given to mysql_stmt_precheck

What it saves

Exactly one round trip on the first execution of each distinct statement per connection. Nothing changes for cached statements, for query(), or for CPU. It matters for many distinct prepared statements per connection, short-lived connections, or high-latency links.

Why it is more than a small change

1. Parameter type hints. The execute packet has to be built before the prepare response exists, so the parameter definitions the driver now uses (integerHint, adopting the integer types the server reports) are unavailable for the pipelined first execution. Its parameters would be encoded by JavaScript type only, differently from every later execution of the same statement. Needs a rule, for example pipelining only when no parameter could benefit from a hint, or accepting the first-execution difference explicitly.
2. Command queue and sequence ids. The queue runs one command at a time and the sequence-id bookkeeping assumes one request per response stream. A combined command must send two requests (each starting at sequence 0), then drive the prepare state machine and the execute state machine in turn, resetting the expected sequence id at the boundary (both responses start at 1). The probe did this in about 80 lines by reusing Prepare.prototype.* and Execute.prototype.* state functions.
3. Errors. A failed prepare yields two error packets (prepare's and the execute's Unknown prepared statement handler); both must be consumed and only the first reported. A prepare that succeeds followed by an execute error must still cache the statement.
4. Gating. Only when connection._isMariaDB (already set from the handshake version string) and the statement is not cached; everything else keeps the current path.

Tests to add

• MariaDB: first execute() of a new statement issues both packets before any response (fake server or packet capture), results identical to the sequential path.
• Failed prepare: single error surfaced, connection remains usable, sequence ids intact.
• Parameter encoding of the first execution documented and asserted.
• MySQL: path never taken.

Probe

'use strict';
// Sends COM_STMT_PREPARE and COM_STMT_EXECUTE (id -1) back to back through
// the driver's own command state machines. Run from the repository root:
// MYSQL_PORT=3310 node probe.js
const mysql = require('./index.js');
const Command = require('./lib/commands/command.js');
const Prepare = require('./lib/commands/prepare.js');
const Execute = require('./lib/commands/execute.js');
const Packets = require('./lib/packets/index.js');
const ConnectionConfig = require('./lib/connection_config.js');

class PipelinedPrepareExecute extends Command {
  constructor(sql, values, callback) {
    super();
    this.sql = sql;
    this.prepare = new Prepare({ sql }, (err, statement) => {
      this.prepareError = err;
      this.statement = statement;
    });
    this.exec = new Execute({ sql, values }, (err, rows) =>
      callback(this.prepareError, err, rows)
    );
  }

  start(_packet, connection) {
    const clientFlags =
      connection.config.clientFlags & (connection.serverCapabilityFlags || 0);
    connection.writePacket(
      new Packets.PrepareStatement(this.sql, connection.config.charsetNumber).toPacket()
    );
    connection._resetSequenceId();
    this.exec._connection = connection;
    this.exec.options = ConnectionConfig.queryOptions(connection.config, { sql: this.sql });
    connection.writePacket(
      new Packets.Execute(0xffffffff, this.exec.parameters, connection.config.charsetNumber,
        connection.config.timezone, undefined, clientFlags, connection._isMariaDB, []).toPacket()
    );
    connection.sequenceId = 1; // both responses start at sequence 1
    this.step = Prepare.prototype.prepareHeader;
    this.prepare.key = 'probe';
    return this.prepareState;
  }

  prepareState(packet, connection) {
    if (packet.isError()) {
      this.prepareError = packet.asError(connection.clientEncoding);
      return this.switchToExecute(connection);
    }
    const next = this.step.call(this.prepare, packet, connection);
    if (next === null) {
      return this.switchToExecute(connection);
    }
    this.step = next;
    return this.prepareState;
  }

  switchToExecute(connection) {
    connection.sequenceId = 1;
    this.exec.statement = this.statement;
    this.step = Execute.prototype.resultsetHeader;
    return this.executeState;
  }

  executeState(packet, connection) {
    if (packet.isError()) {
      this.exec.onResult(packet.asError(connection.clientEncoding));
      return null;
    }
    const next = this.step.call(this.exec, packet, connection);
    if (next === null) {
      return null;
    }
    this.step = next;
    return this.executeState;
  }
}

const conn = mysql.createConnection({
  host: '127.0.0.1', port: Number(process.env.MYSQL_PORT || 3306), user: 'root', database: 'test',
});
conn.addCommand(
  new PipelinedPrepareExecute('SELECT ? + ? AS sum, ? AS label', [40, 2, 'pipelined'],
    (prepareError, execError, rows) => {
      console.log({ prepareError: prepareError?.code, execError: execError?.sqlMessage, rows });
      conn.end();
    })
);

The driver's "packets out of order" warnings during the probe are expected: it is not aware of the second request.