MySQL Bugs: #18304: it's not possible to set collation

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

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

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.

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.

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.

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.

Any progress on this one?

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.

Based on acceptable workaround, setting to "Won't fix"

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.

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.

https://bugs.mysql.com/bug.php?id=98923 marked as duplicate of this one.

Maybe revisit this? Bugs like bug #101220 have the only workaround: change collation_connection?