MySQL :: MySQL Internals Manual :: 14.2.5 Connection Phase Packets

MySQL Internals Manual / ... / Connection Phase Packets

14.2.5 Connection Phase Packets

Protocol::Handshake

Initial Handshake Packet

When the client connects to the server the server sends a handshake packet to the client. Depending on the server version and configuration options different variants of the initial packet are sent.

To permit the server to add support for newer protocols, the first byte defines the protocol version.

Since 3.21.0 the Protocol::HandshakeV10 is sent, while it was still supporting Protocol::HandshakeV9 with a compile time option.

Payload

1              protocol_version
...

Protocol::HandshakeV10

Initial Handshake Packet - protocol version 10

Payload

1              [0a] protocol version
string[NUL]    server version
4              connection id
string[8]      auth-plugin-data-part-1
1              [00] filler
2              capability flags (lower 2 bytes)
  if more data in the packet:
1              character set
2              status flags
2              capability flags (upper 2 bytes)
  if capabilities & CLIENT_PLUGIN_AUTH {
1              length of auth-plugin-data
  } else {
1              [00]
  }
string[10]     reserved (all [00])
  if capabilities & CLIENT_SECURE_CONNECTION {
string[$len]   auth-plugin-data-part-2 ($len=MAX(13, length of auth-plugin-data - 8))
  if capabilities & CLIENT_PLUGIN_AUTH {
string[NUL]    auth-plugin name
  }

Fields

protocol_version (1) -- 0x0a protocol_version
server_version (string.NUL) -- human-readable server version
connection_id (4) -- connection id
auth_plugin_data_part_1 (string.fix_len) -- [len=8] first 8 bytes of the auth-plugin data
filler_1 (1) -- 0x00
capability_flag_1 (2) -- lower 2 bytes of the Protocol::CapabilityFlags (optional)
character_set (1) -- default server character-set, only the lower 8-bits Protocol::CharacterSet (optional)

This “character set” value is really a collation ID but implies the character set; see the Protocol::CharacterSet description.
status_flags (2) -- Protocol::StatusFlags (optional)
capability_flags_2 (2) -- upper 2 bytes of the Protocol::CapabilityFlags
auth_plugin_data_len (1) -- length of the combined auth_plugin_data, if auth_plugin_data_len is > 0
auth_plugin_name (string.NUL) -- name of the auth_method that the auth_plugin_data belongs to

Note

Due to Bug#59453 the auth-plugin-name is missing the terminating NUL-char in versions prior to 5.5.10 and 5.6.2.

Returns

Protocol::HandshakeResponse from the client

Implemented By

send_server_handshake_packet()

Example

36 00 00 00 0a 35 2e 35    2e 32 2d 6d 32 00 0b 00    6....5.5.2-m2...
00 00 64 76 48 40 49 2d    43 4a 00 ff f7 08 02 00    ..dvH@I-CJ......
00 00 00 00 00 00 00 00    00 00 00 00 00 2a 34 64    .............*4d
7c 63 5a 77 6b 34 5e 5d    3a 00                      |cZwk4^]:.

If CLIENT_PLUGIN_AUTH is set the server sends the name of the Authentication Method that the auth_plugin_data belongs to:

50 00 00 00 0a 35 2e 36    2e 34 2d 6d 37 2d 6c 6f    P....5.6.4-m7-lo
67 00 56 0a 00 00 52 42    33 76 7a 26 47 72 00 ff    g.V...RB3vz&Gr..
ff 08 02 00 0f c0 15 00    00 00 00 00 00 00 00 00    ................
00 2b 79 44 26 2f 5a 5a    33 30 35 5a 47 00 6d 79    .+yD&/ZZ305ZG.my
73 71 6c 5f 6e 61 74 69    76 65 5f 70 61 73 73 77    sql_native_passw
6f 72 64 00                                           ord

Note

The auth-plugin-data is the concatenation of strings auth-plugin-data-part-1 and auth-plugin-data-part-2.

Note

Only the fields up to the filler after the auth_plugin_data_part_1 are required, all other fields are optional.

Protocol::HandshakeV9:

Initial Handshake Packet - Protocol Version 9

Payload

1              [09] protocol_version
string[NUL]    server_version
4              connection_id
string[NUL]    scramble

Fields

protocol_version (1) -- 0x09 protocol_version
server_version (string.NUL) -- human-readable server version
connection_id (4) -- connection id
auth_plugin_data (string.NUL) -- auth plugin data for Authentication::Old

Returns

Protocol::HandshakeResponse320

Protocol::HandshakeResponse:

Depending on the servers support for the CLIENT_PROTOCOL_41 capability and the clients understanding of that flag the client has to send either a Protocol::HandshakeResponse41 or Protocol::HandshakeResponse320.

Protocol::HandshakeResponse41:

Handshake Response Packet sent by 4.1+ clients supporting CLIENT_PROTOCOL_41 capability, if the server announced it in its Initial Handshake Packet. Otherwise (talking to an old server) the Protocol::HandshakeResponse320 packet must be used.

Payload

4              capability flags, CLIENT_PROTOCOL_41 always set
4              max-packet size
1              character set
string[23]     reserved (all [0])
string[NUL]    username
  if capabilities & CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA {
lenenc-int     length of auth-response
string[n]      auth-response
  } else if capabilities & CLIENT_SECURE_CONNECTION {
1              length of auth-response
string[n]      auth-response
  } else {
string[NUL]    auth-response
  }
  if capabilities & CLIENT_CONNECT_WITH_DB {
string[NUL]    database
  }
  if capabilities & CLIENT_PLUGIN_AUTH {
string[NUL]    auth plugin name
  }
  if capabilities & CLIENT_CONNECT_ATTRS {
lenenc-int     length of all key-values
lenenc-str     key
lenenc-str     value
   if-more data in 'length of all key-values', more keys and value pairs
  }

Fields

capability_flags (4) -- capability flags of the client as defined in Protocol::CapabilityFlags
max_packet_size (4) -- max size of a command packet that the client wants to send to the server
character_set (1) -- connection's default character set as defined in Protocol::CharacterSet.
username (string.fix_len) -- name of the SQL account which client wants to log in -- this string should be interpreted using the character set indicated by character set field.
auth-response (string.NUL) -- opaque authentication response data generated by Authentication Method indicated by the plugin name field.
database (string.NUL) -- initail database for the connection -- this string should be interpreted using the character set indicated by character set field.
auth plugin name (string.NUL) -- the Authentication Method used by the client to generate auth-response value in this packet. This is an UTF-8 string.

Example

On MySQL 5.5.8 with CLIENT_PROTOCOL_41 CLIENT_PLUGIN_AUTH, CLIENT_SECURE_CONNECTION, and CLIENT_CONNECT_WITH_DB set, it may look like:

54 00 00 01 8d a6 0f 00    00 00 00 01 08 00 00 00    T...............
00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
00 00 00 00 70 61 6d 00    14 ab 09 ee f6 bc b1 32    ....pam........2
3e 61 14 38 65 c0 99 1d    95 7d 75 d4 47 74 65 73    >a.8e....}u.Gtes
74 00 6d 79 73 71 6c 5f    6e 61 74 69 76 65 5f 70    t.mysql_native_p
61 73 73 77 6f 72 64 00                               assword.

Starting with MySQL 5.6.6 the client may send attributes if CLIENT_CONNECT_ATTRS is set:

b2 00 00 01 85 a2 1e 00    00 00 00 40 08 00 00 00    ...........@....
00 00 00 00 00 00 00 00    00 00 00 00 00 00 00 00    ................
00 00 00 00 72 6f 6f 74    00 14 22 50 79 a2 12 d4    ....root.."Py...
e8 82 e5 b3 f4 1a 97 75    6b c8 be db 9f 80 6d 79    .......uk.....my
73 71 6c 5f 6e 61 74 69    76 65 5f 70 61 73 73 77    sql_native_passw
6f 72 64 00 61 03 5f 6f    73 09 64 65 62 69 61 6e    ord.a._os.debian
36 2e 30 0c 5f 63 6c 69    65 6e 74 5f 6e 61 6d 65    6.0._client_name
08 6c 69 62 6d 79 73 71    6c 04 5f 70 69 64 05 32    .libmysql._pid.2
32 33 34 34 0f 5f 63 6c    69 65 6e 74 5f 76 65 72    2344._client_ver
73 69 6f 6e 08 35 2e 36    2e 36 2d 6d 39 09 5f 70    sion.5.6.6-m9._p
6c 61 74 66 6f 72 6d 06    78 38 36 5f 36 34 03 66    latform.x86_64.f
6f 6f 03 62 61 72                                     oo.bar

Caution

Currently, multibyte character sets such as UCS2, UTF16 and UTF32 are not supported.

Note

If client wants to have a secure SSL connection and sets CLIENT_SSL flag it should first send the SSL Request Packet and only then, after establishing the secure connection, it should send the Handshake Response Packet.

Protocol::HandshakeResponse320:

Old Handshake Response Packet used by old clients or if the server doesn't support CLIENT_PROTOCOL_41 capability.

Payload

2              capability flags, CLIENT_PROTOCOL_41 never set
3              max-packet size
string[NUL]    username
  if capabilities & CLIENT_CONNECT_WITH_DB {
string[NUL]    auth-response
string[NUL]    database
  } else {
string[EOF]    auth-response
  }

Fields

capability_flags (2) -- capability flags of the client as defined in Protocol::CapabilityFlags
max_packet_size (3) -- max size of a command packet that the client wants to send to the server
auth-response (string.NUL) -- opaque authentication response data generated by Authentication Method indicated by the plugin name field.
database (string.NUL) -- initail database for the connection -- this string should be interpreted using the character set indicated by character set field.

Example

11 00 00 01 85 24 00 00    00 6f 6c 64 00 47 44 53    .....$...old.GDS
43 51 59 52 5f                                        CQYR_

Note

if auth-response field is followed by a database field it must be 0-terminated.

Protocol::SSLRequest:

SSL Connection Request Packet. It is like Handshake Response Packet but is truncated right before username field. If server supports CLIENT_SSL capability, client can send this packet to request a secure SSL connection. The CLIENT_SSL capability flag must be set inside the SSL Connection Request Packet.

Payload

4              capability flags, CLIENT_SSL always set
4              max-packet size
1              character set
string[23]     reserved (all [0])

Protocol::AuthSwitchRequest:

Authentication Method Switch Request Packet. If both server and client support CLIENT_PLUGIN_AUTH capability, server can send this packet to ask client to use another authentication method.

Payload

1              [fe]
string[NUL]    plugin name
string[EOF]    auth plugin data

Fields

status (1) -- 0xfe
auth_method_name (string.NUL) -- name of the authentication method to switch to
auth_method_data (string.EOF) -- initial auth-data for that authentication method

Returns

Protocol::AuthSwitchResponse or connection close

Example

If CLIENT_PLUGIN_AUTH was set and the server wants the client to authenticate with the Authentication::Native41 method it sends:

2c 00 00 02 fe 6d 79 73    71 6c 5f 6e 61 74 69 76    ,....mysql_nativ
65 5f 70 61 73 73 77 6f    72 64 00 7a 51 67 34 69    e_password.zQg4i
36 6f 4e 79 36 3d 72 48    4e 2f 3e 2d 62 29 41 00    6oNy6=rHN/>-b)A.

Protocol::OldAuthSwitchRequest:

Old Authentication Method Switch Request Packet consisting of a single 0xfe byte. It is sent by server to request client to switch to Old Password Authentication if CLIENT_PLUGIN_AUTH capability is not supported (by either the client or the server)

Payload

1     [fe]

Fields

status (1) -- 0xfe

Returns

Protocol::AuthSwitchResponse with old password hash

Example

01 00 00 02 fe

Protocol::AuthSwitchResponse:

Authentication Method Switch Response Packet which contains response data generated by the authenticatication method requested in Authentication Method Switch Request Packet. This data is opaque to the protocol.

Payload

string[EOF]    auth plugin response

Fields

data (string.EOF) -- authentication response data

Returns

Protocol::AuthMoreData or OK_Packet or ERR_Packet

Example

If the client sends a mysql_native_password response, but the server has a mysql_old_password for that user, it will ask the client to switch to mysql_old_password and client would reply with:

09 00 00 03 5c 49 4d 5e    4e 58 4f 47 00             ....\IM^NXOG.

In the case it is the other way around (mysql --default-auth=mysql_old_password against a mysql_native_password user) the client will respond with the reply of the mysql_native_password plugin:

14 00 00 03 f4 17 96 1f    79 f3 ac 10 0b da a6 b3    ........y.......
b5 c2 0e ab 59 85 ff b8                               ....Y...

More examples in Auth Method Switch

Protocol::AuthMoreData:

Payload

1              [01]
string[EOF]    plugin data

Fields

status (1) -- 0x01
auth_method_data (string.EOF) -- extra auth-data beyond the initial challenge

PREV HOME UP NEXT