Pythian Group

Posted by Sheeri Cabral on Jan 14, 2009

Wheel of protocol, turn turn turn.
Tell us the lesson that we should learn. (with apologies to the original source)

Writing a book comes with many challenges. For me, writing a MySQL book for MySQL beginners, who may or may not be database beginners, has fed my compulsion to research and test bizarre interactions.

Today’s lesson is on what protocol is used when connecting to a local mysqld instance on a non-Windows machine. The TCP/IP protocol is used by default when connecting on a Windows machine, and connecting from any operating system to a non-local mysqld instance. I am assuming the connections are being made by a command line client such as mysql, mysqladmin or mysqldump. Connections made via connectors such as Connector/J, an ODBC connector, DBD::mysql, etc are not covered in this post.

If you seem to be having trouble connecting, check your options against your intentions. Unexpected behavior is usually seen when your intention is to connect using TCP/IP, but the connection is using a socket file instead. Note the following behaviors:

• Terminology:


• host option: -h host or –host=host (default is localhost)


• socket option: -S socket or –socket=socket (default is /tmp/mysqld.sock)


• port option: -P port or –port=port (default is 3306)


• protocol option: –protocol=type (type is either TCP or SOCKET, default is SOCKET)

• Behaviors:


• If there are no protocol, socket, host or port options given, a socket file will be used for connecting.


• If a port option is given and no host or protocol options are given, a socket file is used to connect.


• If the protocol specified is TCP, the connection will be made via TCP/IP even if a socket option is given.


• If the protocol specified is SOCKET and a port is given but no host is given, a socket file is used to connect.


• If the protocol specified is SOCKET and the host option localhost is given, a socket file is used to connect. This is true regardless of whether a port option is given.


• If the protocol specified is SOCKET and a host option other than localhost is given, an error occurs (regardless of whether a port option is given):

  shell> mysql --protocol=SOCKET -h 127.0.0.1
  ERROR 2047 (HY000): Wrong or unknown protocol
  



• A socket file is used when the host and protocol options are not specified, even if the port option is specified.


• If the host, port and socket options are all given, the host and port are used.

Any questions? Class dismissed.