Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

How can databases be created with utf8_unicode_ci collation as default?

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

  1. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    138
    Likes Received:
    2
    Trophy Points:
    68
    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 Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,167
    Likes Received:
    1,933
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    138
    Likes Received:
    2
    Trophy Points:
    68
    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 Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,167
    Likes Received:
    1,933
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    That matches what you have listed for your /etc/my.cnf file. Where are you seeing the "utf8_general_ci" collation at?

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. Archmactrix

    Archmactrix Well-Known Member

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

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,167
    Likes Received:
    1,933
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    138
    Likes Received:
    2
    Trophy Points:
    68
    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 Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,167
    Likes Received:
    1,933
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  9. Archmactrix

    Archmactrix Well-Known Member

    Joined:
    Jan 20, 2012
    Messages:
    138
    Likes Received:
    2
    Trophy Points:
    68
    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 Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,167
    Likes Received:
    1,933
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  11. fidividi

    fidividi Well-Known Member

    Joined:
    Feb 15, 2013
    Messages:
    46
    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 Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,167
    Likes Received:
    1,933
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  13. fidividi

    fidividi Well-Known Member

    Joined:
    Feb 15, 2013
    Messages:
    46
    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 Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    45,167
    Likes Received:
    1,933
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice