Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • Last Modified:

Set default collation_connection to non-default collation

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
Steve Bink
Asked:
Steve Bink
  • 6
  • 4
1 Solution
 
ZberteocCommented:
Have you restarted the mysql service after you made the modification in my.cnf file?
0
 
Steve BinkAuthor Commented:
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
 
Tomas Helgi JohannssonCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Steve BinkAuthor Commented:
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
 
Tomas Helgi JohannssonCommented:
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
 
Steve BinkAuthor Commented:
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
 
Tomas Helgi JohannssonCommented:
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
 
Steve BinkAuthor Commented:
As I mentioned in the original question, I cannot use init_connect because I am often connecting as a user with super privilege.
0
 
Tomas Helgi JohannssonCommented:
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
 
Steve BinkAuthor Commented:
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
 
Steve BinkAuthor Commented:
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now