Bug #79612 Connection attributes lost when connecting without default database
Submitted: 12 Dec 2015 0:05 Modified: 4 Jan 2018 18:05
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.38 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[12 Dec 2015 0:05] Todd Farmer
When using Connector/Java to connect to MySQL Server 5.7, I noticed that the expected connection attributes were not registered in the PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS table.  I enabled traceProtocol to see whether they were being sent to the server, and saw that they were.  However, the server sent a subsequent AuthSwitchRequest, asking C/J to authenticate using mysql_native_password.  This was odd, because mysql_native_password was the authentication plugin specified in the HandshakeV10 and HandshakeResponse packets.  It seemed that the AuthResponse packet was not being processed as expected.

When I supplied a default database in the connection URL, this problem ceased - the expected attributes appeared in the PERFORMANCE_SCHEMA table, and the protocol dump indicated the HandshakeResponse did not trigger an AuthSwitchRequest from the server.

Looking at the code, it seems that the CLIENT_CONNECT_WITH_DB flag is set dynamically - if no default database is supplied, the flag is left unset, and if one was supplied, it is set.  However, code review indicates that the database name - or null terminator - is sent regardless of the CLIENT_CONNECT_WITH_DB flag status:

                    if (this.useConnectWithDb) {
                        last_sent.writeString(database, enc, this.connection);
                    } else {
                        /* For empty database */
                        last_sent.writeByte((byte) 0);

This differs from other code:

            if (localUseConnectWithDb) {
            } else {
                //Not needed, old server does not require \0

It seems that the combination of no CLIENT_CONNECT_WITH_DB flag with a null terminator placeholder causes the server to read from the wrong offset, and think that the authentication plugin (the next protocol field) is a blank string value.  This in turn prompts the server to send AuthSwitchRequest, which the connector completes successfully, and is logged in.

The end result is that there is one extra network round trip to connect when no default database is specified, and handshake data after the default database in the protocol packet is discarded - most notably, connection attributes.

By commenting out the writing of \0, the connection happens normally (no extra round-trip) and connection attributes are properly populated.

How to repeat:
1.  Start 5.7 Server
2.  Attempt connection using no default database via Connector/J with traceProtocol=true.
4.  Observe extra round trips (server asking for mysql_native_password plugin).
5.  Observe PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS is not populated for the current connection.

Suggested fix:
Don't send null string for database name when CLIENT_CONNECT_WITH_DB is not set.
[14 Dec 2015 11:27] Filipe Silva
Thanks Todd!
[4 Jan 2018 18:05] Daniel So
Posted by developer:
Added the following entry to the Connector/J 5.1.46 changelog:

"When the default database to was not specified for a connection, the connection attributes did not get stored in the session_connect_attrs table in the Performance Schema of the MySQL Server."