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.

How can databases be created with utf8_unicode_ci collation as default?

Discussion in 'Database Discussions' started by Archmactrix, Jun 25, 2013.

  1. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    When new databases are created in cPanel they don't get the desired utf8_unicode_ci collation but instead utf8_general_ci even though the former one should be the default collation.

    The databases are created in the control panel but not phpMyAdmin.

    How can I make the database creation tool create the db with the utf8_unicode_ci collation?

    There is also one thing worth mentioning and that is the 'Server connection collation' in phpMyAdmin general Settings on the dashboard, which is utf8_general_ci but should perhaps be utf8_unicode_ci. Please advice me on of how to make it so.

    Output from the character and collation variables query:

    Code:
    mysql> show variables like "%character%";show variables like "%collation%";
    
    +--------------------------+----------------------------+
    | 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       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    8 rows in set (0.00 sec)
    
    +----------------------+-----------------+
    | Variable_name        | Value           |
    +----------------------+-----------------+
    | collation_connection | utf8_unicode_ci |
    | collation_database   | utf8_unicode_ci |
    | collation_server     | utf8_unicode_ci |
    +----------------------+-----------------+
    3 rows in set (0.00 sec)
    Charset in my.cnf:

    Code:
    [mysqld]
    init_connect='SET collation_connection=utf8_unicode_ci'
    init_connect='SET NAMES utf8'
    character-set-server=utf8
    collation-server=utf8_unicode_ci
    skip-character-set-client-handshake
     
    #1 Archmactrix, Jun 25, 2013
    Last edited: Jun 25, 2013
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,852
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    Adjusting the default encoding and collation in /etc/my.cnf should result in the behavior you are expecting. For example, I added the following entries to the /etc/my.cnf on a test server and restarted MySQL:

    Code:
    collation_server=utf8_unicode_ci
    character_set_server=utf8
    Upon creating a new database, the correct values were used:

    Code:
    mysql> show variables like "character_set_database";
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | character_set_database | utf8  |
    +------------------------+-------+
    1 row in set (0.00 sec)
    
    mysql> show variables like "collation_database";
    +--------------------+-----------------+
    | Variable_name      | Value           |
    +--------------------+-----------------+
    | collation_database | utf8_unicode_ci |
    +--------------------+-----------------+
    1 row in set (0.00 sec)
    Are you sure "utf8_general_ci" is output with the above command for new databases?

    Thank you.
     
  3. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    utf8_unicode_ci

    is the output for collation_database like displayed in my previous post, with the command:

    Code:
    mysql> show variables like "%character%";show variables like "%collation%";
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,852
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  5. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I see it in phpMyAdmin.
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,852
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    That does not mean your databases are created with the "utf8_general_ci" collation. It's just what is configured for PHPMyAdmin itself. You can change that value in PHPMyAdmin by selecting a different collation in the drop down box for your session if necessary.

    Thank you.
     
  7. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    :confused:

    Then why does it show the collation utf8_general_ci for the tables in phpMyAdmin, I thought it should be the unicode one?
     
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,852
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Are you sure those databases/tables were created after you modified the default collation in your /etc/my.cnf file? You can open a support ticket if you want us to take a closer look:

    Submit A Ticket

    You can post the ticket number here so we can update this thread with the outcome.

    Thank you.
     
  9. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    132
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Yes I'm sure!

    I've created several databases in cPanel accounts on the server after the configuration changes few months back. All of them seem to have the utf8_general_ci collation instead of utf8_unicode_ci when displayed in phpMyAdmin.

    I must be misunderstanding something.

    But right now I'm not sure if I should submit a support ticket.

    This a screenshot of the structure view of the most recent database in phpMyAdmin, created months after the changes:

    db-table-collation-phpmyadmin.png
     
    #9 Archmactrix, Jul 4, 2013
    Last edited: Jul 4, 2013
  10. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,852
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You can review the following MySQL document regarding the character set and table collation for individual tables:

    MySQL - Table Character Set and Collation

    It explains how MySQL chooses the table character set and collation for each table.

    Thank you.
     
  11. fidividi

    fidividi Active Member

    Joined:
    Feb 15, 2013
    Messages:
    43
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    I have the same problem!

    MySQL 5.1

    conf look like this:

    Code:
    [mysqld]
    .....
    .....
    default-table-type=MyISAM
    default-storage-engine=MyISAM
    collation-server=utf8_unicode_ci
    character_set_server=utf8
    skip-character-set-client-handshake
    And still, when I try to create a table, they are created using "utf8_general_ci" instead of "utf8_unicode_ci".


    In your example, and the way you showed: "show variables like "collation_database";", you are not really showing us the table status, to be able to see the "Collation" under which your database/table is created.

    Any help appreciated to what is causing this.
     
  12. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,852
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Please feel free to open a support ticket so we can take a closer look:

    Submit A Ticket

    You can post the ticket number here so we can update this thread with the outcome.

    Thank you.
     
  13. fidividi

    fidividi Active Member

    Joined:
    Feb 15, 2013
    Messages:
    43
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Thank you for your reply. Ticket created, "Your Request id is: 4400725."
     
  14. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,852
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    To update, this issue was resolved by adding the following to the end of the /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php file:

    Code:
    // Custom Server Settings
    $cfg['DefaultConnectionCollation'] = 'utf8_unicode_ci';
    Thank you.
     
Loading...

Share This Page