?
Solved

Set default collation_connection to non-default collation

Posted on 2014-12-02
11
Medium Priority
?
93 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 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 26

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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 26

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 26

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 26

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

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
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

592 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