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.