Introduced in 5.6.6, connection attributes haven’t been mentioned in any of the many overviews of new 5.6 features that I’ve seen.  You might not have even known they exist – there’s not really a “feature page” in the documentation that tells you what this is all about.  The best documentation is found in the description of the PERFORMANCE_SCHEMA tables which expose these connection attributes:

As of MySQL 5.6.6, application programs can provide key/value connection attributes to be passed to the server at connect time, using the mysql_options() and mysql_options4() C API functions. The Performance Schema provides tables that expose this information through SQL statements[.]

Here’s what that means in practice:  With connectors which support this feature, certain client information can be exposed via PERFORMANCE_SCHEMA tables, making it easier to get useful information about clients from the MySQL Server directly.

The standard MySQL clients (mysql, mysqldump, mysqladmin, etc.) send certain connection attributes by default to servers supporting this.  Here’s what this looks like on my MySQL 5.6.10 installation (on Windows XP):
mysql> SELECT * FROM performance_schema.session_connect_attrs
-> WHERE processlist_id = CONNECTION_ID();
+----------------+-----------------+------------+------------------+
| PROCESSLIST_ID | ATTR_NAME       | ATTR_VALUE | ORDINAL_POSITION |
+----------------+-----------------+------------+------------------+
|             94 | _os             | Win32      |                0 |
|             94 | _client_name    | libmysql   |                1 |
|             94 | _pid            | 6120       |                2 |
|             94 | _thread         | 4740       |                3 |
|             94 | _platform       | x86        |                4 |
|             94 | program_name    | mysql      |                5 |
|             94 | _client_version | 5.6.10     |                6 |
+----------------+-----------------+------------+------------------+
7 rows in set (0.08 sec)

Attributes starting with an underscore are reserved – libmysql will overwrite any application-supplied values that conflict.  The above example also shows some of the useful information that can be obtained.  Want to know what client version a certain connection is using?  You can do that here.  Are you wondering if that long-running process is mysqldump?  Now you don’t have to hope to catch an identifying statement in SHOW PROCESSLIST output at just the right time to tell.  Curious whether somebody is using an application account to connect via the mysql command-line client?  This can help sort that out.

It’s worth pointing out that the list of standard attributes is subject to some change – in implementing support for this new 5.6 feature in MySQL Connector Java, the following observations were made:

The possible values for _os and _platform should be standardized across all client libraries, for uniformity in auditing and querying.  It’s no good to have one client library say the _os value is “Win32” and another say “Windows XP (32-bit)” – consistency is needed.

The values for _os and _platform are currently defined at compile-time via CMake for libmysql-based clients, so they represent the compilation environment rather than the deployment environment (e.g., a 32-bit binary deployed on a 64-bit OS will indicate it is 32-bit).  It would be more useful to expose the deployment environment details.

Not every libmysql-based client specifies the program_name attribute.  I’ve identified two:  FEDERATED and replication slave connections.  If you think of more, let me know via comments.

The standard MySQL command-line clients should provide a mechanism to supply user-defined connection attributes.

All libmysql-based clients should provide an option to disable sending connection attributes if that behavior is problematic for certain deployment scenarios.

Connection attributes are defined during the connection handshake process, and are immutable.  That means that you can’t use connection attributes to store transient information, such as “processed through row 10000 of 25000”, or whether the connection is in a transaction, or what line of application code is being executed.  That also has implications for certain application environments.  For example, JDBC connections are frequently pooled – meaning the thread which creates the Connection object is very likely different from the thread which is using it at any given time.  For that reason, some drivers will implement different standard attributes.  An example of this can be seen in the contents of performance_schema.session_connect_attrs as populated by MySQL Connector Java (patched in upcoming 5.1.25):
mysql> SELECT * FROM performance_schema.session_connect_attrs
-> WHERE processlist_id = 242\G
*************************** 1. row ***************************
PROCESSLIST_ID: 242
ATTR_NAME: _runtime_version
ATTR_VALUE: 1.5.0_22
ORDINAL_POSITION: 0
*************************** 2. row ***************************
PROCESSLIST_ID: 242
ATTR_NAME: _client_version
ATTR_VALUE: 5.1.25
ORDINAL_POSITION: 1
*************************** 3. row ***************************
PROCESSLIST_ID: 242
ATTR_NAME: _client_name
ATTR_VALUE: MySQL Connector Java
ORDINAL_POSITION: 2
*************************** 4. row ***************************
PROCESSLIST_ID: 242
ATTR_NAME: _client_license
ATTR_VALUE: commercial
ORDINAL_POSITION: 3
*************************** 5. row ***************************
PROCESSLIST_ID: 242
ATTR_NAME: _runtime_vendor
ATTR_VALUE: Sun Microsystems Inc.
ORDINAL_POSITION: 4
5 rows in set (0.00 sec)

Of the above five attributes, only 2 of them match attributes supplied by libmysql.  I’ve added _client_license, and suggested that this also be implemented for libmysql.  This will make it easier for you to audit your own deployments, knowing what software with which licenses are in use.  I also added _runtime_version and _runtime_vendor – that’s frequently useful information needed to diagnose JDBC-related problems (not to mention, information which can be difficult for sysadmins or DBAs to collect).  I left out _os and _platform as implemented by libmysql for the time being, until we define uniform values for implementation by all of our drivers.  I also left out _pid and _thread, because neither is very relevant in typical JDBC deployments.

The Connector Java implementation also allows users the ability to define custom attributes:
props.setProperty("connectionAttributes", "testing:this,another:test");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test", props);

Here’s the result:


mysql> SELECT * FROM performance_schema.session_connect_attrs
-> WHERE processlist_id = 258\G
*************************** 1. row ***************************
PROCESSLIST_ID: 258
ATTR_NAME: _runtime_version
ATTR_VALUE: 1.5.0_22
ORDINAL_POSITION: 0
*************************** 2. row ***************************
PROCESSLIST_ID: 258
ATTR_NAME: another
ATTR_VALUE: test
ORDINAL_POSITION: 1
*************************** 3. row ***************************
PROCESSLIST_ID: 258
ATTR_NAME: _client_version
ATTR_VALUE: 5.1.25
ORDINAL_POSITION: 2
*************************** 4. row ***************************
PROCESSLIST_ID: 258
ATTR_NAME: testing
ATTR_VALUE: this
ORDINAL_POSITION: 3
*************************** 5. row ***************************
PROCESSLIST_ID: 258
ATTR_NAME: _client_name
ATTR_VALUE: MySQL Connector Java
ORDINAL_POSITION: 4
*************************** 6. row ***************************
PROCESSLIST_ID: 258
ATTR_NAME: _client_license
ATTR_VALUE: commercial
ORDINAL_POSITION: 5
*************************** 7. row ***************************
PROCESSLIST_ID: 258
ATTR_NAME: _runtime_vendor
ATTR_VALUE: Sun Microsystems Inc.
ORDINAL_POSITION: 6
7 rows in set (0.00 sec)


Additionally, all connection attributes can be disabled by setting connectionAttributes=none.  In addition to being useful for deployments sensitive to exposing any of the information above, the option to skip connection attributes may also be useful in environments where speed to establish a new connection is critical.  In my limited testing (note again that I’m running Windows XP, on hardware that’s as old as you might expect for that OS), establishing and closing (not using) 1000 new connections took 7703ms (so 7.7ms each) with standard connection attributes vs. 6813ms (6.8ms each) when connection attributes are disabled.  As noted previously, though, JDBC deployments frequently use connection pools, so the extra 0.9ms time to create a new Connection rarely affects anything at the application layer.

For community driver developers looking to implement support for connection attributes in their own drivers, there’s a couple of things to note:

1. Make sure you check the server capabilities flag, and send the client capabilities flag (0x00100000) in your response packet.  The server will ignore the connection attributes you send if the flag is not set properly.
2. The handshake response packet is well-documented and includes details regarding connection attributes.  The connection attributes are effectively double length-encoded strings – you need to write the length of the collection of length-encoded keys and values, followed by the length-encoded keys and values themselves.

Hopefully this information will prove useful in leveraging this new 5.6 feature.