Bug #18304 it's not possible to set collation_connection in the my.cnf
Submitted: 17 Mar 2006 11:56 Modified: 23 Mar 2006 10:03
Reporter: Arnim Rupp Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Charsets Severity:S4 (Feature request)
Version:5.0.19 OS:Solaris (solaris 8 64bit)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[17 Mar 2006 11:56] Arnim Rupp
Description:
it's not possible to set collation_connection in the my.cnf (or in any other way i know of at startup)

How to repeat:
add 

collation_connection = latin1_german1_ci

to my.cnf

Suggested fix:
accept collation_connection = latin1_german1_ci
 from my.cnf
[22 Mar 2006 14:17] Valeriy Kravchuk
Sounds like a feature request for me. Do you argee? As for the question you asked in a private comment, please, note that bugs database is not a proper place to ask questions on how to use MySQL. Ask them in our forums.
[22 Mar 2006 18:04] Arnim Rupp
to me it sounds like a bug if there's a feature that defaults to swedish and i can't change when i start the database.
[23 Mar 2006 10:03] Valeriy Kravchuk
It is really impossible to set _default_ collation_connection in my.cnf or with option. It will be nice to at least document this properly. But it is surely possibl;e for connection to set the appropriate collation:

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_german1_ci |
| collation_server     | latin1_german1_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> set collation_connection=latin1_german1_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_german1_ci |
| collation_database   | latin1_german1_ci |
| collation_server     | latin1_german1_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

So, it is a verified feature request.
[3 Jan 2008 23:46] Benjamin Eberlei
The problem with my.cnf or any .my.cnf not taking collation information in my case is as follows. On my Master Server all threads use latin1_german2_ci for their connections and create temporary tables with it. This collation_connection option seems to be lost in replication to slave.

So the Slave SQL Thread is running with the default latin1_swedish_ci collation_connection which I cannot change. This leads to replication errors when matching VARCHAR fields with those different collations.

I can of course define each field of the created temporary tables using the correct collation, but this shouldn't be an issue if you could change this variable.
[22 Jan 2008 12:54] Sebastian Nohn
Any progress on this one?
[4 Oct 2008 19:37] Konstantin Osipov
collation_connection global variable is pretty much meaningless -- I don't know how and why you can set it, but if you don't set it, it defaults to global collation_server. The same is with global character_set_connection. 
So instead of attempting to set collation_connection or character_set_connection, in order to set server defaults you should set collation_server and character_set_server. Global collation_connection will inherit from this. Session collation_connection will inherit from global collation_connection.

Example:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

I don't know if this redundancy is documented, but it brings a lot of confusion.
[4 Oct 2008 19:38] Konstantin Osipov
Based on acceptable workaround, setting to "Won't fix"
[5 May 2009 16:43] WIjit Anusasananan
At start up, some, not all variables take values from character_set_server and collation_server. character_set_database and collation_database take both values accordingly. character_set_connection takes does so but collation_connection doesn't. According to http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html, "collation_connection is important for comparisons of literal strings". Ones should have known that the default collation for utf8 character set is "utf8_general_ci". Any utf8's that are set without collation explicitly set will take this value. From MySQL manual, the only way to set this relationship, eg., to make utf8_unicode_ci a default collation for utf8, is to recompile from source. Other two variables, character_set_client and character_set_result have no variables for their collations. We never know which value it takes.
I agree that this is a bug. Setting this to "Won't fix" seems a bit narrow to me. It is true that there are workarounds but capable of setting at start up is preferred still.
[6 Nov 2010 3:44] Jimmy Soho
Another workaround is to use SET CHARACTER SET xxx. It will then default to the value of collation_database, which you can define in the mysqld group in my.cnf.