The Potential of Query Attributes in MySQL

Introduction

Query Attributes are a relatively new feature of the MySQL Protocol. This is availble since MySQL 8.0.23.

Before Query Attributes were introduced there already was already another similar feature: Connetion Attributes, which provides per-connection metadata in the form of key/value pairs.

This is what connection attributes look like:

mysql> SELECT ATTR_NAME, ATTR_VALUE
    -> FROM performance_schema.session_connect_attrs
    -> WHERE PROCESSLIST_ID=64;

+------------------+---------------------+
| ATTR_NAME        | ATTR_VALUE          |
+------------------+---------------------+
| _platform        | amd64               |
| _runtime_version | go1.23.4            |
| _client_version  | (devel)             |
| _client_role     | binary_log_listener |
| _client_name     | go-mysql            |
| _os              | linux               |
+------------------+---------------------+
6 rows in set (0.00 sec)

Connection attributes are set by connectors (with a name that is prefixed by _) and applications.

These connection attributes are very useful to get insight into what is connecting to your server. However there are some limitations due to these being linked to the connection and being send during connection establishment. So if a connection is used by a connection pool then an attribute about which page is being rendered doesn’t work as the same connection is used by the application to render multiple pages.

And this is where Query Attributes come into play. When client and server set the CLIENT_QUERY_ATTRIBUTES flag then some of the MySQL network packets are slightly changed. This allows connectors and applications to set key/value pairs on a per-query basis. These don’t replace connection attributes.

Changes in network packets

COM_STMT_EXECUTE

The changes for COM_STMT_EXECUTE are small.

This command is used to execute a previously prepared statement.

The first change is that COM_STMT_EXECUTE can now send a parameter_count to override the number of expected parameters. And the second change is that parameters can now have a parameter_name.

Basically the query attributes are just regular parameters for prepared statements, but they have a name.

COM_QUERY

For COM_QUERY the changes look much more extesive. This is because COM_QUERY used to be very simple as it only was sending a query string and now it has much more structure.

The changes basically let you send parameters with COM_QUERY, just as you would do with COM_STMT_EXECUTE.

Example

This is how a query with two query attributes look like in Wireshark and in the client.

mysql-9.2.0> query_attributes traceid 12345 workload batch
mysql-9.2.0> SELECT mysql_query_attribute_string('traceid') TraceID,
    -> mysql_query_attribute_string('workload') Workload;

+---------+----------+
| TraceID | Workload |
+---------+----------+
| 12345   | batch    |
+---------+----------+
1 row in set (0.00 sec)

Changes in the C API

mysql_bind_param() was introduced with MySQL 8.0.23 to allow you to send query attributes with regular queries.

mysql_stmt_bind_named_param() was introduced with MySQL 8.2.0 (but with a bug that made it unusable) and this allows you to set the query attributes for prepared statements.

Changes in other conectors

For MySQL Connector/Python MySQLCursor.add_attribute() and a few other functions were added.

And MySQL Conector/J got the ability to use setAttribute() on statements.

Changes in the MySQL Client

You can now use query_attributes <key> <value> [<key> <value> ...] to set query attributes for the queries that you’re executing with the MySQL Client.

Current use

So for what kind of things could you use Query Attributes?

• You might want to send a “Trace ID” if you’re using OpenTelemetry or a similar system to allow you to more easily link traces between systems.
• If you’re doing any kind of proxying you may want to set the user the request is for. Just like the Forwarded header in HTTP.
• Maybe include the page name, request ID, or session ID if you’re rendering web pages.
• If you’re a connector, you may want to add the filename and line the request is coming from.

Also it seems that MySQL Router can use query attributes in the configuration for read/write splitting.

Another common way to send metadata along with a query is to use comments. However this makes it difficult to extract, parse and handle them programatically. And depending on whether you add them in the beginning or the end of your query it might make it difficult to read the queries in dashboards etc.

Limitations

The query attributes are now visible in the protocol and can be queried with the mysql_query_attribute_string() function.

However they don’t yet end up in the binlogs, even with binlog_rows_query_log_events set.

They are also not yet in the general log or the slow query log.

Future possibilities

Building a string in your application and executing that directly opens up the risk of SQL Injection. The safer way is to use prepared statements and bind the parameters that you want to execute. But the drawback of prepared statements is that it adds a network roundtrip, which adds latency. So for this many connectors emulate the prepared statement in the connector to avoid this extra roundtrip. However that adds complexity in the connectors, and this has to be done for each of the connectors.

With the enhancements for COM_QUERY it is now possible to send a query with a set of parameters in a single command. This might make it possible in the future to have a safe way to execute queries without having complexity in the connector or having to accept the added latency of an extra roundtrip.

Other resources

• Passing query attributes through the MySQL protocol at FOSDEM 2021