If you run multiple MySQL instances on a Linux machine, chances are good that at one time or another, you’ve ended up connected to an instance other than what you had intended. It’s certainly happened to me, and I submitted Bug#76512 to deal with the cause which affects me most commonly – that the mysql client will silently ignore the –port option and connect using the default Unix socket instead when the host is “localhost” (default). We’ve recently discussed ways we can make this behavior less surprising to users, and though we’re now past the second RC of MySQL Server 5.7, we’re contemplating making these changes in future 5.7 releases. Please let us know your thoughts!
Here are the basic principles of what we intend to change:
Explicit –protocol option rules all
If a user provides an explicit –protocol option, the client will only attempt to connect using that protocol. If it cannot connect using that protocol, it generates an error. When an explicit –protocol option is supplied, client options related to other protocols will be ignored. That allows you to have a configuration file for both TCP and Socket connection parameters, while choosing which to use by passing the –protocol command-line option. For example, this will not happen:
R:\mysql-5.7.8-rc-winx64>bin\mysql -uroot --port=3310 --protocol=TCP --pipe -e"STATUS;" -------------- bin\mysql Ver 14.14 Distrib 5.7.8-rc, for Win64 (x86_64) Connection id: 26 Current database: Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.7.8-rc MySQL Community Server (GPL) Protocol version: 10 Connection: Named pipe: MySQL ... --------------
Implicit protocol options
Certain options are associated with a single protocol, and going forward, will implicitly set the protocol. For example, using the –port option will imply TCP should be used. This prevents the situation from my bug report where the default socket is used instead of the explicitly requested TCP port, resulting in connection to the wrong instance. Here are the proposed rules:
- –host option defined with value other than localhost implies TCP/IP connection
- –port option defined with any value implies TCP/IP connection
- –socket option defined with any value implies Unix Socket connection on Linux
- –socket option defined with any value implies Named Pipe connections on Windows
- –shared-memory-base-name defined with any value implies Shared Memory connection on Windows
In addition, the current –pipe (or -W) is equivalent to –protocol=TCP, and we propose to deprecate it.
Reject conflicting command-line options
MySQL has historically been very lax in option processing and will generally accept conflicting options instead of producing errors. For example, you can start the mysql client with all of the above options explicitly defined, without error:
R:\mysql-5.7.8-rc-winx64>bin\mysql -uroot --port=3310 --socket=MySQL --shared-memory-base-name=MYMEM --pipe Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 Server version: 5.7.8-rc MySQL Community Server (GPL)
Which protocol is being used here? It’s using a Named Pipe.
There are some combinations which produce appropriate errors already – most notably when connections are attempted using inter-machine protocols while specifying a remote host:
R:\ade\mysql-5.7.8-rc-winx64>bin\mysql -h192.168.2.2 --protocol=MEMORY ERROR 2047 (HY000): Wrong or unknown protocol
I think it makes sense to produce similar errors when conflicting implicit command-line options are given. That would prohibit users from starting mysql with both –port and –socket options defined on the command-line.
Retain platform-specific default protocols
When supplying no options that change the behavior, the standard MySQL command-line clients should continue to prefer Unix Socket connections on Linux for performance reasons, while Windows uses TCP/IP.
We think the proposed changes make sense to clarify protocol selection, and that this is a worthwhile addition to 5.7 – even though two Release Candidate builds have already shipped. With these changes, users (like me) will have better protection against inadvertently connecting to the wrong instance on a multi-instance host, and ambiguity around which protocol is used will be significantly reduced.
Please let us know your thoughts on this proposal!