Hi! 👋 We are doing a big documentation refresh. Help us improve — what's missing or could be better? Let us know! Simply send an email or start a conversation in Google Groups!

MySQL Prepared Statement Lifecycle

This page walks through the complete journey of a MySQL prepared statement as it moves through ProxySQL — from the initial COM_STMT_PREPARE sent by a client, through one or more executions, and finally to close. The MySQL binary protocol is covered at each phase.

Preparation Phase

When a client sends COM_STMT_PREPARE, ProxySQL intercepts the request before it reaches any backend server.

Hash Computation

ProxySQL computes a content hash that uniquely identifies the prepared statement within a specific context. For MySQL, the hash is derived from:

  • The SQL query text
  • The logged-in username
  • The target schema (database)

This hash is the key used to look up and store entries in the global prepared statement cache.

Global Cache Lookup

After computing the hash, ProxySQL performs a lookup against the global prepared statement cache — a process-wide structure shared across all client connections.

Cache hit — the statement was previously prepared in an equivalent context:

  1. Retrieve the existing global entry and its global ID.
  2. Assign a new client-side numeric statement ID that points to the existing global entry.
  3. Increment the client reference count on the global entry.
  4. Return the cached metadata (column count, parameter count, field descriptions) directly to the client.

No backend connection is contacted. The client receives a valid COM_STMT_PREPARE response immediately.

Cache miss — no matching entry exists:

  1. Forward the COM_STMT_PREPARE request to a backend connection selected via query rules (or the default hostgroup).
  2. Receive the backend’s response: metadata describing parameters and result columns, plus the backend-side numeric statement ID assigned by the MySQL server.
  3. Create a new global entry in the cache, storing the SQL text, parameter metadata, and a newly assigned global ID.
  4. Record the backend-side numeric ID in the backend map for that specific backend connection (mapping global ID to backend statement ID).
  5. Map the client-side numeric ID to the global ID in the per-connection client map.
  6. Return the metadata to the client.

In both cases the client receives the same COM_STMT_PREPARE response format. It has no way to determine whether the response came from the cache or from a live backend interaction.

Execution Phase

When a client sends COM_STMT_EXECUTE, ProxySQL performs several steps before any parameter data reaches a backend.

ID Translation and Routing

  1. Translate client-side ID to global ID: Look up the client-side numeric statement ID in the per-connection client map to obtain the global ID. If no mapping exists, the statement was never prepared in this session and an error is returned.

  2. Re-evaluate routing: Since v2.1.0, ProxySQL does not lock execution to the hostgroup used during preparation. Instead, query rules are re-evaluated at execution time using the statement’s SQL text and the current session context. This ensures that a statement prepared on a reader hostgroup is correctly re-routed to a writer hostgroup when executed inside a transaction.

  3. Select a backend connection: Choose a connection from the connection pool of the resolved target hostgroup.

Backend Statement Check and Lazy Preparation

Before forwarding the execute, ProxySQL checks whether the selected backend connection has the statement already prepared:

  • Backend has the statement: Translate the global ID to the backend-side numeric ID stored in the backend map for this connection, then forward COM_STMT_EXECUTE with the correct backend ID and the parameter bindings provided by the client.

  • Backend does not have the statement: Perform lazy preparation transparently before executing. See the subsection below.

Once the backend ID is confirmed, ProxySQL forwards the parameter bindings and relays the result rows back to the client exactly as received.

Lazy Preparation

Lazy preparation is a key optimisation in ProxySQL’s prepared statement handling.

When a statement is present in the global cache but has not yet been prepared on a specific backend connection, ProxySQL sends COM_STMT_PREPARE to that backend on demand at execution time rather than requiring all backend connections to pre-prepare every statement upfront. This matters because:

  • Backend connections are numerous and transient. Requiring each new connection to prepare every known statement would create significant overhead.
  • A hostgroup change between prepare and execute (enabled since v2.1.0) means execution may land on a backend that has never seen the statement.
  • Connection pool events — such as a backend connection being recycled — can invalidate backend-side state independently of the global cache.

The lazy preparation sequence is transparent to the client:

  1. Using the metadata stored in the global cache entry, send COM_STMT_PREPARE to the backend.
  2. Receive the backend’s new numeric statement ID.
  3. Add the mapping (global ID to backend-side numeric ID) to the backend map for this connection.
  4. Proceed with the execute as if the statement had been prepared from the beginning.

The client’s COM_STMT_EXECUTE response arrives without any indication that an additional prepare was performed.

Close Phase

When a client sends COM_STMT_CLOSE, ProxySQL handles teardown at the client level without immediately affecting the backend.

Client-Side Cleanup

  1. Remove the client-side numeric ID from the per-connection client map.
  2. Recycle the client-side ID so it can be reused for future prepare operations within the same session.
  3. Decrement the client reference count on the corresponding global cache entry.

Backend Statement Retention

Backend connections are not contacted during a client close. The statement remains prepared on any backend connections that have it in their backend map. This is intentional:

  • Other active client connections may be executing the same statement concurrently.
  • Future executions from other clients will be able to route to those backends without incurring a lazy preparation cost.
  • Keeping the backend statement alive avoids a COM_STMT_PREPARE / COM_STMT_CLOSE round-trip that would add latency for little gain.

Global Entry Eviction

The global cache entry is retained as long as either reference count is greater than zero:

ConditionResult
Client ref count > 0Entry is actively used by at least one client session; retained.
Backend ref count > 0Entry is prepared on at least one backend connection; retained.
Both ref counts reach zeroEntry becomes eligible for eviction from the global cache.

Once evicted, the next prepare for an equivalent statement (same SQL, user, and schema) will go through the cache-miss path again.

Long Data Handling

MySQL’s COM_STMT_SEND_LONG_DATA command allows clients to stream large parameter values in chunks before sending the final COM_STMT_EXECUTE. ProxySQL handles this at the session layer:

  1. As each COM_STMT_SEND_LONG_DATA packet arrives, ProxySQL accumulates the data chunk and associates it with the correct parameter index for the given statement.
  2. No data is forwarded to a backend during this accumulation phase.
  3. When COM_STMT_EXECUTE is subsequently received, ProxySQL attaches the accumulated long data to the corresponding parameter bindings and forwards the complete execute to the backend.
  4. After execution completes, the accumulated long data for that statement is cleared. A new execution requires a fresh sequence of COM_STMT_SEND_LONG_DATA calls if large parameters are needed again.

This approach ensures that parameter data sent across multiple packets is reassembled correctly before reaching the backend, regardless of which backend connection is ultimately selected by routing.

Error Scenarios and Edge Cases

Backend Connection Lost

If a backend connection is dropped — due to a network failure, server restart, or connection pool eviction — the global cache entry is unaffected. The backend map entry for the lost connection is discarded along with the connection itself. On the next COM_STMT_EXECUTE that routes to a new backend connection from the same hostgroup, lazy preparation runs automatically using the metadata in the global cache. From the client’s perspective, execution proceeds normally after any transparent reconnection.

Schema Changes After Preparation

If a DDL statement (such as ALTER TABLE or DROP COLUMN) is executed against a table referenced by a cached prepared statement, the stored metadata in the global cache may no longer match the backend’s current schema. When ProxySQL detects a metadata mismatch — indicated by an error response from the backend during COM_STMT_EXECUTE — it refreshes the cached metadata by re-preparing the statement on the backend via a new COM_STMT_PREPARE and calling update_metadata to update the global entry. Subsequent executions use the refreshed metadata.

Hostgroup Changes Between Prepare and Execute

Since v2.1.0, routing is re-evaluated at execution time rather than being fixed at preparation time. If query rules, hostgroup weights, or session state cause the execution to land on a different hostgroup than the one used during preparation, ProxySQL:

  1. Selects a connection from the newly determined hostgroup.
  2. Checks the backend map for that connection.
  3. If the statement is not yet prepared on that connection, performs lazy preparation using the global cache metadata.
  4. Forwards COM_STMT_EXECUTE normally.

This allows correct routing of prepared statements into transactions, to read replicas, or to sharded hostgroups, without requiring the client to re-prepare explicitly.