Cleanup old options!


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! 🙂

2 Comments

Add yours
  1. 2
    peter_laursen

    In MySQL before 4.1 it is not really a problem – or at least it is another kind of problem. Here people know (or should know) that data are sent to the client encoded same way as they are stored. There is only one character set for a server – it applies for all string data stored and data are sent to clients exactly like that. Clients|applications will either need to use a corresponding codepage internally or convert the data stream to what encoding they use. This can be handled. Reconnection is also no problem here, as there is no change in the encoding of the data stream before and after a reconnect.

    The problem is ‘skip-character-set-client-handshake’ -option in MySQL 4.1 and higher. It ‘overrules’ a charset specification in mysql_options(). SET NAMES works as expected but since clients|applications don’t know when a reconnect took place it also cannot know when to execute SET NAMES.

+ Leave a Comment