Solved

Set default collation_connection to non-default collation

Posted on 2014-12-02
11
82 Views
Last Modified: 2016-03-06
We need to make sure that collation_connection is set to utf8_unicode_ci for every client connecting to our mysqld service.  I've set the following options in my.ini:
[mysql]
default-character-set='utf8'

[mysqld]
collation_server=utf8_unicode_ci
character_set_server=utf8

Open in new window


However, on connection, the collation is different:
mysql> show variables like 'character%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | utf8                        |
| character_set_connection | utf8                        |
| character_set_database   | utf8                        |
| character_set_filesystem | binary                      |
| character_set_results    | utf8                        |
| character_set_server     | utf8                        |
| character_set_system     | utf8                        |
| character_sets_dir       | C:\bb\mysql\share\charsets\ |
+--------------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%collat%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |   <====== THIS IS WRONG
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Open in new window


I understand that utf8_general_ci is the default collation for the character set, and that is why the connection is being set that way.  The INI does not recognize any *_connection options.  It is also a real pain to have to "SET NAMES utf8 COLLATE utf8_unicode_ci" every single time.  I cannot use init-connect because I am often connecting as a SUPER privileged user.

How can I force the connection collation to behave?
0
Comment
Question by:Steve Bink
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 40482818
Have you restarted the mysql service after you made the modification in my.cnf file?
0
 
LVL 51

Author Comment

by:Steve Bink
ID: 40483071
Yes.  The problem is not that the existing directives are not recognized - the character set is set correctly when the service starts.  The problem is that the collation for that character set needs to be something different.  Is there any way to have a MySQL connection start with a chosen collation?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40485837
Hi!

Please look at these links

http://dev.mysql.com/doc/refman/5.5/en/charset-collations.html
http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html
http://dev.mysql.com/doc/refman/5.5/en/charset.html

You will find all the info you need there.
Also note that a server can have many databases with different default collation and even tables inside them with different collations. See ALTER/CREATE DATABASE/TABLE DML statements info here
http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-data-definition.html

Although it is recommended to have the same collation throughout the system. :)

Regards,
    Tomas Helgi
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 51

Author Comment

by:Steve Bink
ID: 40487394
Thanks for jumping in TomasHelgi.  

I've looked at all that information.  The only bit relevant to this question is this quote from charset-connection:
Setting character_set_connection to charset_name also implicitly sets collation_connection to the default collation for charset_name.
That snippet explains why the collation is showing up like it is, but I've found nothing out there detailing how I can default a connection to a particular collation.
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40487917
Hi!

In the second link I provided you find this
" Setting character_set_connection to charset_name also implicitly sets collation_connection to the default collation for charset_name "

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_character_set_connection

This is probably what you are looking for.

Regards,
    Tomas Helgi
0
 
LVL 51

Author Comment

by:Steve Bink
ID: 40488088
Well, that's actually the problem.  The default collation for the UTF8 character set is utf8_general_ci.  I need to set it to utf8_unicode_ci.  As I mentioned in the original question, I know I can set it using SQL on every connection, but that is annoying, and doesn't necessarily work for every app that needs to connect.  Is there a way to force the collation to default to utf8_unicode_ci?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40488567
Hi!

You could use the init_connect variable and put into it a call to a stored procedure that
checks where the client is comming from or particular users and set the session collation and charsets
accordingly.

Here are some info
http://www.askapache.com/mysql/init_connect-speed-up.html#Procedure_MySQL
http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_init_connect

Regards,
    Tomas Helgi
0
 
LVL 51

Author Comment

by:Steve Bink
ID: 40489133
As I mentioned in the original question, I cannot use init_connect because I am often connecting as a user with super privilege.
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40489199
Hi!

Does your application/other users connect as super user as well ?

If you have multiple collations in one dbms then your applications need to act accordingly.
You can use the methods mentioned earlier but
as I said earlier it is recommended to have the same collation throughout the system. :)
The same collation that is configured in a session should also be set in database/tables/columns so that
data won't translate into unwanted characters.

Regards,
     Tomas Helgi
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 0 total points
ID: 40489220
As shown in the original question, the server character set and collation are set appropriately.  The database schema is also set to use utf8_unicode_ci.  Everything is homogeneous, with the only exception being the collation on the connection.  The only references I have found to setting the connection collation are:

1) The collation is set to the default collation for the selected character set.  Since my character set is UTF8, and the default collation is utf8_general_ci, that is what my connections are using.

2) The collation can be changed with SET NAMES or similar statements.  This is our current method, which I would like to remove from our workflow since it is a manual, documentation-required step.

3) init_connect can be used to automate connection startup SQL.  We can't use this because the connections are often using accounts with SUPER privilege.
0
 
LVL 51

Author Closing Comment

by:Steve Bink
ID: 41494026
No solution was ever discovered for this issue..  I doubt one exists since collation seemed to be a secondary concern to the character set.  In our specific problem, we addressed this with a patch to the primary application exhibiting the issue.

REF: https://issues.civicrm.org/jira/browse/CRM-15085
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question