MySQL Query Attributes
Query attributes have in a long time been used to add metadata to queries to provide additional context such as where in the application the query was generated. The traditional way to do this is to add a comment at the start of the query with the attributes. Since the MySQL parser ignores the comment, it does not change the semantics of the query, but various tools can extract the comments.
This post looks into the reasons for using query attributes, how it is done using a comment, and the new component for MySQL 8.0.23 that provides native support for query attributes.
Why Query Attributes?
At the surface, it may seem that there is no major benefit from having metadata attached to a query – after all it is ignored by MySQL. However, there are good reasons. The manual page on query attributes lists two uses:
- For a web application where the query much track the URL that generated it.
- For use in plugins such as the query rewrite plugin and an audit log plugin.
In my opinion there are two even better reasons: aggregation of query statistics and for communication between teams. These two are related. For example, I work in a database team and when we encounter an incident, let's say high CPU, it is very convenient to be able to go into the monitoring solution and look at what kind query attributes were the most used during the incident. With the query attributes, it is easy to communicate the issue with the developers as they easily can see from the attribute values where in the code the queries were generated and which workflow they are part of. For small projects, this is not necessarily a big advantage, but for large projects that also abstracts the database layer, it is invaluable.
Adding Attributes in a Comment
It is very simple to add attributes in a comment. The exact syntax depends on the tool that parses the attributes, but common is that you add them using a comment with the syntax
/* .... */ at the start of the statement, for example:
/* src=get_city workflow=update_population */ SELECT id, Population FROM world.city WHERE CountryCode = 'AUS' AND Name = 'Sydney' AND District = 'New South Wales';
The query attributes are on a line by themselves here, but that is not required.
In this case, you can see that the query was generated in the
get_city function (the
src attribute) and it was used as part of a workflow to update the population. This suggest that it is part of a multi-statement transaction, maybe the first step to get the primary key and current population of the city. A subsequent statement then updated the population.
While using a comment to add the attributes is simple, it is also a hack that requires parsing the statement, and the attributes are not available inside MySQL Server. Also comments are stripped when the Performance Schema digest is created for the query. This for example means that if a monitoring solution needs to collect statistics aggregated by the query attributes, you need to capture the network traffic (or enable the
events_statements_history_long and poll that table) and analyse that. Capturing the network traffic in turn prevents enabling SSL.
So is there an alternative? Let us take a look at the new (as of 8.0.23) query attributes component.
The Query Attributes Component
In MySQL 8.0.23 you can enable native support for query attributes by installing the query attributes component. While this does have its advantages, there are also some limitations that I will go into.
You install the component by using the
INSTALL COMPONENT statement:
mysql> INSTALL COMPONENT "file://component_query_attributes"; Query OK, 0 rows affected (0.0362 sec)
This works the same on Windows, Linux, and macOS.
If you decide to not use the component any longer, you can uninstall it again using the
UNINSTALL COMPONENT statement like:
mysql> UNINSTALL COMPONENT "file://component_query_attributes"; Query OK, 0 rows affected (0.3016 sec)
Using the Component
At the time of writing there is little support for the component in MySQL's connectors. The C API implements it using the new mysql_bind_param() function. So, hopefully in the next few releases similar functionality will be implemented in MySQL Connector/Python, MySQL Connector/J, etc.
Fortunately, you can try out the attributes without having to compile a C program. If you connect to MySQL using the traditional command-line client (
mysql), you can set one or more attributes using the new
query_attributes command. Additionally, the component adds the
mysql_query_attribute_string() UDF function that you can use to retrieves the value of an attribute.
One important thing to be aware of is that all existing attributes for a connection are cleared each time a query is executed. This even applies if you just want to retrieve an attribute using the
mysql_query_attribute_string() function. If you are looking at using the new attributes, I recommend you to review the limitations in the manual to get the up to date list.
It is easier to understand how this works by looking at an example. Let's set the same two attributes as in the previous example and retrieve the values:
mysql> query_attributes src get_city workflow update_population; mysql> SELECT mysql_query_attribute_string('src') AS src, mysql_query_attribute_string('workflow') AS Workflow; +----------+-------------------+ | src | Workflow | +----------+-------------------+ | get_city | update_population | +----------+-------------------+ 1 row in set (0.00 sec) mysql> SELECT mysql_query_attribute_string('src') AS src, mysql_query_attribute_string('workflow') AS Workflow; +------+----------+ | src | Workflow | +------+----------+ | NULL | NULL | +------+----------+ 1 row in set (0.00 sec)
Notice how the attributes are unset after the first query. This also happens if you execute a normal query:
mysql> query_attributes src get_city workflow update_population; mysql> SELECT id, Population FROM world.city WHERE CountryCode = 'AUS' AND Name = 'Sydney' AND District = 'New South Wales'; +-----+------------+ | id | Population | +-----+------------+ | 130 | 3276207 | +-----+------------+ 1 row in set (0.00 sec) mysql> SELECT mysql_query_attribute_string('src') AS src, mysql_query_attribute_string('workflow') AS Workflow; +------+----------+ | src | Workflow | +------+----------+ | NULL | NULL | +------+----------+ 1 row in set (0.00 sec)
This leads us to the limitations of the current implementation.
There are several limitations that affect the usefulness of the query attributes component. Some important limitations that pops into my mind are:
- They cannot easily be used for monitoring (and communicated between database and developer teams) as there is currently no way to get aggregate data from the Performance Schema or to query attributes from another connection. You can in principle use a network sniffer but in that case, it is easier to just stick with the attributes in a comment. There is a feature request by Sveta Smirnova to expose the attributes through the Performance Schema.
- It requires an extra round trip between the application and server which will affect performance.
- There is currently very limited support in connectors including MySQL Shell.
So in which case would you want to use the new attributes? They are primarily of interest if you use a plugin that can use them or if a query somehow benefits from knowing the value of its own query attributes.
There is a workaround to persisting the attributes across multiple queries which as a side effect also exposes the attributes to another connection. It is a hack, but may be useful in some cases.
I just claimed that the attributes are cleared as soon as you execute a query. Usually you do not want to have the same attributes for several queries, so that makes sense. However, should you need to keep them, you have two options: assign the values to user variables or insert into a table. (Of course, you can argue that in that case, you do not need the feature at all as you can construct the queries with the attribute values directly.)
Let's look at assigning the values to user variables:
mysql> query_attributes src get_city workflow update_population; mysql> SET @attr_src = mysql_query_attribute_string('src'), @attr_workflow = mysql_query_attribute_string('workflow'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @attr_src, @attr_workflow; +-----------+-------------------+ | @attr_src | @attr_workflow | +-----------+-------------------+ | get_city | update_population | +-----------+-------------------+ 1 row in set (0.00 sec)
You can now get the values for as long as you need to. Since user variables are exposed in Performance Schema, you can also retrieve the values from another connection. First find the Performance Schema thread id of the connection with the attributes:
mysql> SELECT PS_CURRENT_THREAD_ID(); +------------------------+ | PS_CURRENT_THREAD_ID() | +------------------------+ | 55 | +------------------------+ 1 row in set (0.00 sec)
Then query the
user_variables_by_thread table from another connection:
Connection 2> SELECT Variable_name, CONVERT(Variable_value, CHAR CHARACTER SET UTF8MB4 ) AS Value FROM performance_schema.user_variables_by_thread WHERE Thread_id = 55; +---------------+-------------------+ | Variable_name | Value | +---------------+-------------------+ | attr_src | get_city | | attr_workflow | update_population | +---------------+-------------------+ 2 rows in set (0.00 sec)
Notice that you need to convert the value stored in the Performance Schema to UTF8. Otherwise, you will retrieve the value as a hex encoded value. But as said, you do not necessarily need query attributes to do this.
The new query attributes component in MySQL 8.0.23 looks promising, but it still has too many limitations to make it a practical alternative to query attributes in comments for most use cases. The feature is most useful when used together with plugins sucn as audit plugins and the query rewrite plugin. Hopefully, this is just a first step to a more flexible feature that can allow query statistics aggregated by attributes in your monitoring solution without the need to capture the network traffic.