Solved

Set default collation_connection to non-default collation

Posted on 2014-12-02
11
60 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
  • 6
  • 4
11 Comments
 
LVL 26

Expert Comment

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

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
 
LVL 50

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 50

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 50

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 50

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 50

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

929 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now