MySQL Connector/Java supports connection attributes since version 5.1.25. This projects useful metadata about the client environment into the database, where MySQL administrators can query PERFORMANCE_SCHEMA tables to remotely survey application deployment environments. One useful piece of information exposed is the version and vendor of the JVM in use by the client. This very short blog demonstrates how to get this information from PERFORMANCE_SCHEMA.
The metadata including the Java runtime environment version and vendor can be found in PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS table. Here’s the full contents of that table for a single connection from Connector/Java:
mysql> SELECT * -> FROM PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS -> WHERE processlist_id = 31\G *************************** 1. row *************************** PROCESSLIST_ID: 31 ATTR_NAME: _runtime_version ATTR_VALUE: 1.6.0_45 ORDINAL_POSITION: 0 *************************** 2. row *************************** PROCESSLIST_ID: 31 ATTR_NAME: _client_version ATTR_VALUE: 5.1.38-SNAPSHOT ORDINAL_POSITION: 1 *************************** 3. row *************************** PROCESSLIST_ID: 31 ATTR_NAME: _client_name ATTR_VALUE: MySQL Connector Java ORDINAL_POSITION: 2 *************************** 4. row *************************** PROCESSLIST_ID: 31 ATTR_NAME: _client_license ATTR_VALUE: GPL ORDINAL_POSITION: 3 *************************** 5. row *************************** PROCESSLIST_ID: 31 ATTR_NAME: _runtime_vendor ATTR_VALUE: Sun Microsystems Inc. ORDINAL_POSITION: 4 5 rows in set (0.00 sec)
As seen above, both the JRE version and vendor are displayed in separate rows. The following SQL gets just the information we really care about, and makes the output a little bit more tidy:
mysql> SELECT -> v1.processlist_id, -> v1.attr_value AS JRE_version, -> v2.attr_value AS JRE_vendor, -> t.processlist_user AS user, -> t.processlist_host AS host -> FROM -> performance_schema.session_connect_attrs v1 -> JOIN -> performance_schema.session_connect_attrs n -> ON (v1.processlist_id = n.processlist_id) -> JOIN -> performance_schema.session_connect_attrs v2 -> ON (v2.processlist_id = n.processlist_id) -> JOIN -> performance_schema.threads t -> ON (t.processlist_id = n.processlist_id) -> WHERE -> n.attr_name = '_client_name' -> AND n.attr_value = 'MySQL Connector Java' -> AND v1.attr_name = '_runtime_version' -> AND v2.attr_name = '_runtime_vendor'\G *************************** 1. row *************************** processlist_id: 30 JRE_version: 1.6.0_45 JRE_vendor: Sun Microsystems Inc. user: root host: localhost 1 row in set (0.00 sec)
Visibility into the JRE in use by clients is meaningful information to MySQL administrators. For example, TLSv1.2 is only supported in relatively recent browsers. Knowing which applications are connecting using older Java runtime libraries that don’t support TLSv1.2 is useful for DBAs to know before disabling TLSv1.0 and TLSv1.1.
As a quick note, I found a bug while trying to access JRE information – it turns out that connecting without a default database in the JDBC URL causes the connection attribute information to get lost. I think most people use a default database, so this is unlikely to affect many people, but I reported it here.
This is handy information for MySQL admins to have access to, and while it’s not exactly earth-shattering, I find myself having to dig into PERFORMANCE_SCHEMA table definitions to remember how to build useful queries. This blog post is a post-it note for my own future reference, and I hope you may also find it useful.
Nice to see how connection attributes and TLS work together.
Would be nice if this info was also in the audit logs (or other logs)
https://bugs.mysql.com/bug.php?id=71839
I also noted that the _runtime_* attributes are not fully documented
https://bugs.mysql.com/bug.php?id=79620
Hi Daniël,
I very much agree – I’ve been wanting to see P_S connection attributes recorded in logs as well. I’m working with the Docs team and the other connectors teams to standardize and document the attributes exposed by each.
Thanks for the feedback!