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.

Advice

Query attributes are also known as query tags.

Example using the query attributes component.

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.

Installation

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.

Information

The INSTALL COMPONENT statement is not yet supported by the X Protocol (port 33060 by default). To execute it, you must connect using the classic protocol (port 3306 by default). If you use MySQL Shell, you can for example use: mysqlsh --user=root --port=3306 --mysql --sql.

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.

Information

At the time of writing, MySQL Shell does not support the query_attributes command.

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.

Limitations

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.

Persisting Attributes

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.

Conclusion

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.

I have worked with MySQL databases since 2006 both as an SQL developer, a database administrator, and for more than eight years as part of the Oracle MySQL Support team. I have spoken at MySQL Connect and Oracle OpenWorld on several occasions. I have contributed to the sys schema and four Oracle Certified Professional (OCP) exams for MySQL 5.6 to 8.0. I have written four books, all published at Apress.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.