Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Set default collation_connection to non-default collation

Posted on 2014-12-02
11
Medium Priority
?
87 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

670 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