Re: In fully utf8 unicode MySQL the collation connection makes trouble
Posted by: voter viparmenia
Date: September 21, 2009 05:34AM

What I did to push needed collation as default.
Added to [mysqld]

default-character-set=utf8
default-collation=utf8_unicode_ci
character_set_server=utf8
collation_server=utf8_unicode_ci

After that connections like 'mysql -umyuser -pmypass --execute="CREATE DATABASE newdbase"' using the collation I defined in my.cnf.

But as soon as one gives charset as utf8, the default collation of it the "utf8_general_ci" is used,e.g.:
mysql -umyuser -pmypass --execute="CREATE DATABASE newdbase CHARACTER SET utf8;" generates newdbase with collation "utf8_general_ci".

So I understood it such, that as soon as the CHARACTER SET is given as extra option in query, the default collation is reseted and not the one previously defined in system, but the default for that given charset will be used. In my case CHARACTER SET utf8; results of useing utf8_general_ci independantly what I defined in my.cnf before. Using command line option --default-character-set=charset_name Don't uses collation defined in my.cnf.

To escape this, one need to escape giving charset as option for connection, thatn the one from my.cnf would be used or Collation must be given, too, e.g.: mysql -umyuser -pmypass --execute="CREATE DATABASE newdbase CHARACTER SET utf8 COLLATE utf8_unicode_ci;"

Adding to [mysqld]
skip-character-set-client-handshake

Pushes that for mysql connections from command line or with php funtions "utf8_unicode_ci" in any case used, but for other clients for instance phpMyAdmin still default collation of "utf8" is "utf8_general_ci". PhPMyAdmin sees that global collation is utf8_unicode_ci but for his connections he still uses the default one.

Adding [mysqld]
init-connect='SET NAMES utf8, collation_connection=utf8_unicode_ci'

Brings nothing new, but I prefer to use this than previous skip-character-set-client-handshake which just tells server to ignor any wish of client for other charsets, which means limitation for use of MySQL server only for given charsets.



Edited 6 time(s). Last edit at 09/21/2009 08:21AM by voter viparmenia.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: In fully utf8 unicode MySQL the collation connection makes trouble
9006
September 21, 2009 05:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.