In various discussions I have expressed that I think that there are way too many options and variables in MySQL (and it gets worse for every new release). There are simply too many to know and remember them all. If you manage a server yourself it is (probably) a minor problem as you should know the options you use and not use those that you don’t know. But if you are connected to a server that you don’t manage yourself you may get surprises if some rare option you are not familiar with has been set.
The last one I came across is ‘skip-character-set-client-handshake’. MySQL documentation http://dev.mysql.com/doc/refman/5.5/en/server-options.html says “To ignore client information and use the default server character set, use –skip-character-set-client-handshake; this makes MySQL behave like MySQL 4.0.”
Now what is the idea in having a recent server ‘behave like MySQL 4.0’ ? There could have been some compatibility concerns in early 4.1/5.0 releases, but now (after 8 years) is it stilll relevant to have? I don’t think so.
The ‘chain breaks’ in the case where a client (we are talking about clients compiled with the C-API or a connector having similar functionalities as mysql_options() in the C-API – what means that it is not PHP we are talking about!) requires character_set_client to be utf8. The client will execute “SET NAMES UTF8;” after connection just in case the server has a non-utf8 default charset. It works fine. SET NAMES works as expected. So far so good. Futher the client is using the ‘reconnect flag’. Reconnects may take place after connection was lost in several cases (client was idle for longer than wait_timeout, some tunnel system server (SSH tunnel, VPN tunnel) disconnected client and server, intermittent hardware or networking failures etc.). The important point to note here is that reconnect is handled by the API transparently for the ‘parent code’ where it is linked. Thus the client cannot know when to execute “SET NAMES UTF8;” again (as a regular SQL statement). The client will instead set the connection charset to utf8 in mysql_options() so that utf8 also will also be character set for the connection after a reconnect. But with the ‘skip-character-set-client-handshake’ -option set, it simply has no efffect (the option is intended to work like this).
As a consequense there is no way not to have non-ASCII characters garble in the client after automatic reconnection in such environment as far as I can see.
We had such report recently. It took some time to figure out the reason. There is also an old related bug here http://bugs.mysql.com/bug.php?id=11972 btw, but this is fixed long ago even though I first thought it might have found its way back to PerconaServer (what was used in this particular case), it was not the case.
It is fair enough to have compability options with older servers for some time. But is it still after 8 years? Also in a case like this where it breaks the way a client and the server is intended to ‘negotiate’ multiple charsets?
More old options to get rid of? I am a collector of such! 🙂