The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

default MySQL collation and character set / charset

Discussion in 'Database Discussions' started by tgavin, Apr 6, 2008.

  1. tgavin

    tgavin Well-Known Member

    Joined:
    Jul 27, 2004
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    running mysql 5 and php 5

    i need to have mysql run as utf-8. Problem is that there are a number of sites on the server that are older and still using latin-1.

    is there a way to enable mysql to use utf-8 on a per-site basis?
     
  2. WebScHoLaR

    WebScHoLaR Well-Known Member

    Joined:
    Dec 14, 2005
    Messages:
    511
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    Planet Earth
  3. tgavin

    tgavin Well-Known Member

    Joined:
    Jul 27, 2004
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    6
    Thanks. I already did that. It didn't help, so I upgraded to mysql 5. That didn't help, so here I am :)
     
  4. section31

    section31 Active Member

    Joined:
    Sep 3, 2003
    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    San Antonio
    mysql default charset

    How can I have cpanel create databases with a default charset of utf8 and collation of utf8_unicode_ci?
     
  5. section31

    section31 Active Member

    Joined:
    Sep 3, 2003
    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    San Antonio
    Nevermind, I figured this out.


    Add this line to your my.cnf file

    default-character-set=utf8

    Amazingly simple really.
     
  6. dexus

    dexus Well-Known Member

    Joined:
    Jan 14, 2006
    Messages:
    169
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    .my.cnf in user's home dir?

    I would like to change mysql default charset for some user and I know that I sould be able to do that with .my.cnf file in user's home dir but I just can't get that to work. Whatever I put there it's just ignored.

    Can someone please help with this?
     
  7. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    That file is only used by the mysql command line client. For connects via PHP, or other programming language, the change needs to happen during the connection. The details will vary according to language and application.

    Now, if you mean to influence the cPanel functions, our functions only use the user, host and password values in that file.
     
  8. dexus

    dexus Well-Known Member

    Joined:
    Jan 14, 2006
    Messages:
    169
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I am not shure that I understood... So there is no way to change default database character set that will be created by cPanel per user only.

    I would just like that cPanel or Fantastico create a database with utf8 character set as default just for some users.

    Is that possible?

    Thanks.
     
  9. guysmiley

    guysmiley Member

    Joined:
    Sep 6, 2008
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    I would also like to do this. Is it possible to set the default charset to utf8 and not have it overwritten by updates from cpanel?
     
  10. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    Set the default encoding and collation in /etc/my.cnf, then restart the mysql service.
     
  11. guysmiley

    guysmiley Member

    Joined:
    Sep 6, 2008
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    Thanks, Kenneth!
     
  12. SageBrian

    SageBrian Well-Known Member

    Joined:
    Jun 1, 2002
    Messages:
    415
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    NY/CT (US)
    cPanel Access Level:
    Root Administrator
    for those looking for recent info for mysql 5.1.x,
    I added this to etc/my.cnf

    [mysqld]
    collation_server=utf8_unicode_ci
    character_set_server=utf8

    After saving and restarting mysql service, I went create a new database using Fantastico (Joomla 1.5 install), and it was now in the proper uft8 format.
     
  13. cPanelDon

    cPanelDon cPanel Quality Assurance Analyst
    Staff Member

    Joined:
    Nov 5, 2008
    Messages:
    2,557
    Likes Received:
    7
    Trophy Points:
    38
    Location:
    Houston, Texas, U.S.A.
    cPanel Access Level:
    DataCenter Provider
    Twitter:
    Friendly Moderator Note

    To better organize related discussions I have merged a few different threads that pertain to the same topic about setting a default character set and or default collation in MySQL.
     
    #13 cPanelDon, Aug 26, 2010
    Last edited: Aug 26, 2010
  14. Defected

    Defected Member

    Joined:
    Dec 14, 2009
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    1
    Sorry for bumping this up, but i have a question. If i add collation_server=utf8_unicode_ci and character_set_server=utf8 to my.cnf in order to change charset and collation for new databases will there be any problems with databases already created as latin1?
     
  15. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Actually, I'm going to revise my prior entry as it might pose an issue to have different character sets for different MySQL databases if the character set hasn't been hard coded for the older databases. It would be better to convert the older databases to the new character set if possible. There is a good article on converting the character set for an existing database at this location:

    http://codex.wordpress.org/Converting_Database_Character_Sets

    Next, please ensure you are running MySQL 5.1 if you are using those variables.
     
  16. lorio

    lorio Well-Known Member

    Joined:
    Feb 25, 2004
    Messages:
    243
    Likes Received:
    3
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    What is the right way to get UTF8 when installing Cpanel from scratch.

    The mysql databases cpanel is using are installed before mysql is changed to utf.

    What is the roadmap there? Wait till MySQL is changing to UTF8 as default?
     
  17. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    If you have a pre-existing /etc/my.cnf file on the machine before you install cPanel, cPanel should not overwrite that /etc/my.cnf file. Have you tried putting the entry into /etc/my.cnf to force that character set, then install cPanel at that point? It should use the existing settings you have already on installation.
     
  18. lorio

    lorio Well-Known Member

    Joined:
    Feb 25, 2004
    Messages:
    243
    Likes Received:
    3
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    There are currently seven databases installed on a fresh installation:
    cphulkd
    eximstats
    horde
    information_schema
    leechprotect
    mysql
    roundcube

    The settings are very mixed. latin utf general unicode . if no my.cnf is set before cpanel installation it should be latin1 all the way.
    I used CentOS6.2 for testing.

    I don't see a clean detection of the settings.
    I will invest some more time but I would like to get some feedback about best practice utf8 on a fresh installation.

    When deleting the databases and let them recreate with upcp and horde reset script the result looks promising.

    The converting of such databases will be much more worse. So a good my.cnf for utf8 all the way would be nice.

    For the test I used
    [mysqld]
    character-set-server = utf8
    collation-server = utf8_unicode_ci
    skip-character-set-client-handshake

    To set everything explicitly seems to be the better way. Feel free to post a better one with MySQL 5.5 and MySQL6 already in mind.
     
    #18 lorio, Feb 1, 2012
    Last edited: Feb 1, 2012
  19. lorio

    lorio Well-Known Member

    Joined:
    Feb 25, 2004
    Messages:
    243
    Likes Received:
    3
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Just to answer that question: When installing Cpanel pre-existing my.cnf file is ignored and renamed and a new my.cnf is created. Still looking for a way to get UTF8 only without deleting the databases and recreating them.
     
  20. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    If a database is already created in another character set, it isn't going to work to simply switch the collation in /etc/my.cnf file. You could try this utility:

    How to change collation in database

    I cannot say how well it works. I'd highly suggest ensuring to have a full backup of the database before using it. Also, you might want to read this post:

    Change Collation On All Tables And Columns In MySQL with Phoca Changing Collation Utility | Vision4Web Blog

    As it states:

     
Loading...

Share This Page