MySQL Bugs: #34980: No way to specify default collation for clients

Bug #34980	No way to specify default collation for clients
Submitted:	2 Mar 2008 9:57	Modified:	2 Mar 2008 17:30
Reporter:	Petr Hroudny	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S4 (Feature request)
Version:	4.1, 5.x	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Since MySQL 4.1, all clients are responsible for sending their default character set to server during initial connection handshake. The server performs SET NAMES with the specified charset.

This default charset could be changed via --default-character-set option to
any mysql client, or in my.cnf via

[client]
default-character-set=xxx

The problem is, that the connection_collation is always set to the default collation for the specified charset. There doesn't seem to be any way to specify
--default-collation for any *client*, which makes this charset handshake effectively useless for non-default collations.

The only available workaround seems to be to completely disable this initial
client handshake by setting -skip-character-set-client-handshake on the server side, but this is more a hack than a real solution.

 

The only available workaroud seems to be setting

How to repeat:
Start e.g.

mysql --default-character-set=utf8

connection_collation is set to utf8_general_ci, but we'd need utf8_czech_ci

Suggested fix:
Provide command line option for all *clients* to set default-collation,
allow this also in the [client] section of my.cnf

This looks like a reasonable feature request.

I have found a closely related bug so I thought not to open a new bug report but write it here:

The documentation says: "...For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence..."
(Source: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html)
This makes it impossible to change the order of the result set without affecting the query string itself. This is a major problem, since we are developing multi-language applications using Hibernate as the persistence manager and Spring as the transaction manager. (I think this is common nowadays.)
In Hibernate one can't specify which collation to use. I think this is the way it should be because specifying collations is highly DB specific.

We have a nice technique to handle this situation which works fine in Oracle: We alter the session and set the nls_sort parameter depending on the current user's locale after the transaction has begun. Every further Hibernate query within this transaction will give back the result in the required order.

I thought that collation_connection has analogous behaviour. So for example after "set names 'utf8' collation 'utf8_bin'" every further select should put strings starting with "ő,ű" at the end of the result set. Like as it happens when I say "select * from table1 order by col1 collate 'utf8_bin'".
But it doesn't because of the precedence.
As I mentioned before, Hibernate can't (and shouldn't) manipulate every single sql depending on the collation required in "order by"s.
So I think it would be nice if the collation specified in the connection level should take precedence over the one defined in any other (column, table, database and system) level.

At least in order by. Because I understand that in where clause the collation of the column (and the index) must be used. BTW: the best method to handle this would be to store the collation when creating indexes, so multiple indexes could be created for the same column(s) with different collations and the engine should choose which index to use depending on the required collation.

Maybe I don't understand your use-case, but aren't you just looking for @@init_connect?
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_init_connect

    set global init_connect = "set names 'utf8' collation 'utf8_czech_ci'";

This causes every connection (except those of SUPER users) to initialize itself by running the specified SQL query.