Connector/Python Connection Attributes

MySQL Server has since version 5.6 supported connection attributes for the clients. This has allowed a client to provide information such as which program or connector the client is, the client version, the license, etc. The database administrator can use this information for example to verify whether all clients have been upgraded, which client is executing a particular query, and so forth.

In MySQL 8.0.16 this feature has been included for the X DevAPI in the MySQL connectors as well, including MySQL Connector/Python which I will cover in this blog. First though, let’s take a look at how the attributes are exposed in MySQL Server.

Example of the built-in connection attributes provided by MySQL Connector/Python
The built-in MySQL Connector/Python connection attributes

Connection Attributes in MySQL Server

The connection attributes are made available in MySQL Server through two tables within the Performance Schema: session_account_connect_attrs and session_connect_attrs. The two tables have the same definition – the only difference is for which connections they show the connection attributes.

The session_account_connect_attrs table includes the attributes for connections using the same user account as for the one querying the table. This is useful if you want to grant permission for a user to check the attributes for their own connections but not for other connections.

On the other hand, session_connect_attrs shows the attributes for all connections. This is useful for the administrator to check the attributes for all users.

Information

It is up to the client what attributes they want to expose and the values they provide. In that sense, you can only trust the attributes to the same extend that you trust the clients to submit correct values.

The tables have four columns:

  • PROCESSLIST_ID: This is the same ID as in SHOW PROCESSLIST or the PROCESSLIST_ID column in performance_schema.threads.
  • ATTR_NAME: This is the name of the attribute, for example _client_name.
  • ATTR_VALUE: This is the value for the attribute, for example mysql-connector-python.
  • ORDINAL_POSITION: The attributes have an order. The ordinal position specifies the position of the attribute. The first attribute for the connection has ordinal position 0, the next 1, and so forth.

The PROCESSLIST_ID and ATTR_NAME together form the primary key of the tables.

Now that you know how to query the table, let’s take a look at how it works with MySQL Connector/Python.

Connector/Python Attributes

There are essentially three different ways to use the connection attributes from MySQL Connector/Python. You can have them disabled. This is the default and means that no attributes will be provided. You can enable them and use the default attributes. And finally, there is support for providing custom attributes. Let’s look at each of the two cases where attributes are enabled.

MySQL Connector/Python Revealed

Book

If you want to learn more about MySQL Connector/Python, then I have written MySQL Connector/Python Revealed published by Apress. The book both covers the traditional Python Database API (PEP 249) and the X DevAPI which is new as of MySQL 8.

The book is available from Apress (print and DRM free ePub+PDF), Amazon (print and Kindle), Barnes & Noble (print), and others.

Enabled with Default Attributes

If you want your Python program to connect to MySQL using the default attributes, all you need to do is to set the connection-attributes option to True. You can do that in one of two ways depending on how you set the connection arguments

If you use a dictionary of arguments, you add connection-attributes as a key with the value set to True:

import mysqlx

connect_args = {
    "host": "127.0.0.1",
    "port": 33060,
    "user": "pyuser",
    "password": "Py@pp4Demo",
    "connection-attributes": True,
};

db = mysqlx.get_session(**connect_args)
p_s = db.get_schema("performance_schema")
attr = p_s.get_table("session_account_connect_attrs")
stmt = attr.select()
stmt.where("PROCESSLIST_ID = CONNECTION_ID()")
stmt.order_by("ORDINAL_POSITION")
result = stmt.execute()

print("+------+-----------------+------------------------+---------+")
print("| P_ID | ATTR_NAME       | ATTR_VALUE             | ORDINAL |")
print("+------+-----------------+------------------------+---------+")
fmt = "| {0:4d} | {1:<15s} | {2:<22s} | {3:7d} |"
for row in result.fetch_all():
    print(fmt.format(row[0], row[1], row[2], row[3]))
print("+------+-----------------+------------------------+---------+")

db.close()

The program creates the connection, then queries the performance_schema.session_account_connect_attrs table using the crud methods. Finally, the result is printed (note that the PROCESSLIST_ID and ORDINAL_POSITION columns have had their names shortened in the output to make the output less wide – the process list ID will differ in your output):

+------+-----------------+------------------------+---------+
| P_ID | ATTR_NAME       | ATTR_VALUE             | ORDINAL |
+------+-----------------+------------------------+---------+
|   45 | _pid            | 19944                  |       0 |
|   45 | _platform       | x86_64                 |       1 |
|   45 | _os             | Windows-10.0.17134     |       2 |
|   45 | _source_host    | MY-LAPTOP              |       3 |
|   45 | _client_name    | mysql-connector-python |       4 |
|   45 | _client_version | 8.0.16                 |       5 |
|   45 | _client_license | GPL-2.0                |       6 |
+------+-----------------+------------------------+---------+

Notice that all of the attribute names start with an underscore. That means it is a built-in attribute. Attribute names starting with an underscore are reserved and can only be set by MySQL itself.

You can also connect using a URI, in that case the connection is made like the following example:

import mysqlx
import urllib

uri = "mysqlx://{0}:{1}@{2}:{3}/?connection-attributes=True".format(
    "pyuser",
    urllib.parse.quote('Py@pp4Demo', safe=''),
    "127.0.0.1",
    "33060"
)
db = mysqlx.get_session(uri)

The username, password, host, and port are added through the format() function to make the code less wide. The important thing here is the connection-attributes=True. You can also leave out =True as mentioning the connection-attributes option without any value is the same as enabling it.

What do you do, if you want to add your own customer attributes? That is supported as well.

Custom Attributes

This far the connection-attributes option has just been set to True. However, it also supports taking a list or dictionary as the argument. That can be used to set your own custom attributes.

The easiest way to understand this is to see an example:

import mysqlx

attributes = {
    "application": "my_test_app",
    "foo": "bar",
    "foobar": "",
}
connect_args = {
    "host": "127.0.0.1",
    "port": 33060,
    "user": "pyuser",
    "password": "Py@pp4Demo",
    "connection-attributes": attributes,
};

db = mysqlx.get_session(**connect_args)
p_s = db.get_schema("performance_schema")
attr = p_s.get_table("session_account_connect_attrs")
stmt = attr.select()
stmt.where("PROCESSLIST_ID = CONNECTION_ID()")
stmt.order_by("ORDINAL_POSITION")
result = stmt.execute()

print("+------+-----------------+------------------------+---------+")
print("| P_ID | ATTR_NAME       | ATTR_VALUE             | ORDINAL |")
print("+------+-----------------+------------------------+---------+")
fmt = "| {0:4d} | {1:<15s} | {2:<22s} | {3:7d} |"
for row in result.fetch_all():
    value = row[2] if row[2] is not None else ""
    print(fmt.format(row[0], row[1], value, row[3]))
print("+------+-----------------+------------------------+---------+")

db.close()

Notice in line 29 that it is checked whether the attribute value is None (NULL in SQL). When the attribute value is an empty string or no value like for the foobar attribute, it is returned as None in the result set.

Alternatively, you can specify the same three connection attributes using a list:

attributes = [
    "application=my_test_app",
    "foo=bar",
    "foobar",
]
connect_args = {
    "host": "127.0.0.1",
    "port": 33060,
    "user": "pyuser",
    "password": "Py@pp4Demo",
    "connection-attributes": attributes,
};

You can also use an URI of course. You need to use the list syntax for that:

uri = "mysqlx://{0}:{1}@{2}:{3}/" \
    + "?connection-attributes=[application=my_test_app,foo=bar,foobar]".format(
    "pyuser",
    urllib.parse.quote('Py@pp4Demo', safe=''),
    "127.0.0.1",
    "33060",
)

These all give the same result (the process list ID will differ):

+------+-----------------+------------------------+---------+
| P_ID | ATTR_NAME       | ATTR_VALUE             | ORDINAL |
+------+-----------------+------------------------+---------+
|   74 | _pid            | 20704                  |       0 |
|   74 | _platform       | x86_64                 |       1 |
|   74 | _os             | Windows-10.0.17134     |       2 |
|   74 | _source_host    | MY-LAPTOP              |       3 |
|   74 | _client_name    | mysql-connector-python |       4 |
|   74 | _client_version | 8.0.16                 |       5 |
|   74 | _client_license | GPL-2.0                |       6 |
|   74 | application     | my_test_app            |       7 |
|   74 | foo             | bar                    |       8 |
|   74 | foobar          |                        |       9 |
+------+-----------------+------------------------+---------+

Notice that the built-in attributes are still included and the custom attributes have been added at the end.

That concludes this introduction to connection attributes with the MySQL Connector/Python X DevAPI. I will recommend to enable them by default even if just for the built-in attributes. The attributes can be very handy when debugging issues on the server.

MySQL Server 8.0.16: Thanks for the Contributions

MySQL 8.0.16 was released last week and includes many great features including support for CHECK constraints and upgrades without the need of mysql_upgrade. As usual there are also several contributions from the community. These are the ones, I would like to highlight in this blog to say “thank you for the contributions”.

Facebook has contributed with another two patches and so has Daniel Black. Additionally, there are patches from Yuhui Wang, Wei Zhao, Yan Huang, and Dirkjan Bussink. The contributions are:

  • mysql-test-run.pl now supports the MTR_UNIQUE_IDS_DIR environment variable, which may be set to specify a unique-IDs directory to be used as the common location for all chroot environments by multiple simultaneous mysql-test-run.pl instances. This enables those instances to avoid conflicts when reserving port numbers. Thanks to Facebook for the contribution. (Bug #29221085, Bug #93950)
  • Changes to the all_persisted_variables test. Thanks to the Facebook team for the contribution. (Bug #29013375, Bug #93478)
  • InnoDB: Write-ahead did not work as expected due to an incorrectly initialized variable. Thanks to Yuhui Wang for the contribution. (Bug #29028838, Bug #93442)
  • InnoDB: A Linux AIO handler function failed to check if completed I/O events succeeded. Thanks to Wei Zhao for the contribution. (Bug #27850600, Bug #90402)
  • InnoDB: A function called by a CREATE TABLE thread attempted to access a table object after it was freed by a background thread. Thanks to Yan Huang for the patch. (Bug #27373959, Bug #89126)
  • Replication: If the WAIT_FOR_EXECUTED_GTID_SET() function was used with a timeout value including a fractional part (for example, 1.5), an error in the casting logic meant that the timeout was rounded down to the nearest whole second, and to zero for values less than 1 second (for example, 0.1). The casting logic has now been corrected so that the timeout value is applied as originally specified with no rounding. Thanks to Dirkjan Bussink for the contribution. (Bug #29324564, Bug #94247)
  • Determination of the number of online CPUs available to the mysqld process is now more accurate. Thanks to Daniel Black for the contribution. (Bug #28907677, Bug #93144)
  • Made a comparison in the internal method Item_result::item_cmp_type() more efficient. Our thanks to Daniel Black for the contribution. (Bug #92784, Bug #28796107)

It sometimes happen that we receive a contribution that we really like, but – for one reason or another – choose not to use the patch. One such case is the new feature to allow reconfiguration of the SSL settings at runtime. Thank you Facebook for submitting a contribution for a similar feature even though we ended up not using the patch.

Thank you for your contributions. Feel free to keep submitting ideas to the MySQL bugs database with ideas how to improve MySQL