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!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Switch default collation?

Discussion in 'Database Discussions' started by MaxFein, Nov 14, 2017.

  1. MaxFein

    MaxFein Member

    Joined:
    Jul 29, 2015
    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    3
    Location:
    Portland, Oregon, USA, Earth, ZZ9PZA
    cPanel Access Level:
    Root Administrator
    Hi, I want to control the charset/collation on my server to match what I want my WP web application to use and I'd like to switch my default collation from utf8mb4_unicode_ci to utf8mb4_unicode_520_ci - wondering if I should expect to be able to do this.

    I currently have no user-created databases (though previously I have) - currenty, I see only following databases on server, with their current collation:

    cphulkd utf8mb4_unicode_ci
    information_schema utf8_general_ci
    leechprotect utf8mb4_unicode_ci
    modsec utf8mb4_unicode_ci
    mysql utf8mb4_unicode_ci
    performance_schema utf8_general_ci
    roundcube utf8mb4_unicode_ci
    tmpdir utf8mb4_unicode_ci

    currently in my.cnf I have:

    [client]
    default-character-set=utf8mb4

    [mysql]
    default-character-set=utf8mb4
    no-auto-rehash

    [mysqld]
    character_set_server=utf8mb4
    collation_server=utf8mb4_unicode_ci
    init_connect='SET NAMES utf8mb4'

    running CENTOS 7.4 w/ WHM/cPanel v68.0.13 and MariaDB 10.2.3

    Everything is working until I try changing the collation_server value to

    collation_server=utf8mb4_unicode_520_ci


    It seems to me that I should be able to do this (eg. this doc), however after making the change and restart of MySQL service I get errors, eg. trying to visit cPanel>MySQL Databases IU panel it says that the MySQL server is down and show an error mssg:

    _________________
    Error while connecting to MySQL: (XID 6cn9gg) The system failed to connect to the “MySQL” database “mysql” because of an error: 2019 (Can't initialize character set unknown (path: compiled_in)) Error while connecting to MySQL: (XID 6cn9gg) The system failed to connect to the “MySQL” database “mysql” because of an error: 2019 (Can't initialize character set unknown (path: compiled_in)) at /usr/local/cpanel/Cpanel/Mysql.pm line 167, <STDIN> line 1.
    _________________

    The restart via WHM showed as successful, WHM service status shows MySQL as up.

    I also got an email from my server:

    _________________
    The system was unable to authenticate to the local MySQL/MariaDB server on “localhost”.

    The connection driver reported the following error: Cpanel::Exception::Database::ConnectError/(XID ym29g7) The system failed to connect to the “MySQL” database “mysql” because of an error: 2019 (Can't initialize character set unknown (path: compiled_in))

    You can reset the MySQL/MariaDB root password in WHM’s MySQL Root Password interface at: https://host.example.com:2087/scripts/mysqlrootpass
    _________________

    (As a note, the script location now seems to be at /scripts2/mysqlrootpass)

    When I reset the password it seems to make no difference.

    I have contacted my host and they think that the issue is basically that cPanel will not work with this setting - hoping for some clarification.

    Thanks, Max
     
    #1 MaxFein, Nov 14, 2017
    Last edited: Nov 14, 2017
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    42,802
    Likes Received:
    1,714
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    Could you open a support ticket using the link in my signature so we can take a closer look?

    Thank you.
     
  3. MaxFein

    MaxFein Member

    Joined:
    Jul 29, 2015
    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    3
    Location:
    Portland, Oregon, USA, Earth, ZZ9PZA
    cPanel Access Level:
    Root Administrator
    So, funny story, I totally forgot about this thread until just now when I had this come up again and threw the error into google...

    Apologies, I never got around to filing a ticket; and atm I'm still too busy to want to go through an extra support proccess if I can just move on instead.

    I had forgotten about this thread, but I did remember that there had been an issue and did a quick google before I tried it again... found a few folks saying it was working for them, and also found this

    [CONC-223] utf8mb4_unicode_520_ci is not compiled in - JIRA

    and so I figured I'd give it a try... obviously it didn't work out ;)

    ...also this: MySQL :: MySQL 5.7 Reference Manual :: 10.3.2 Server Character Set and Collation

    So, this is weird because it should be working, right?

    When I opened the cPanel > MySQL Databases interface to create a new database I got two messages:

    I do not see /usr/local/cpanel/Cpanel/Mysql.pm on my system...

    Code:
    # updatedb
    # locate Mysql.pm
    /usr/local/cpanel/Cpanel/API/Mysql.pm
    /usr/local/cpanel/Cpanel/DB/Map/Rebuild/Mysql.pm
    /usr/local/cpanel/Cpanel/DBI/Mysql.pm
    /usr/local/cpanel/Cpanel/Pkgacct/Components/Mysql.pm
    /usr/local/cpanel/Cpanel/Security/Advisor/Assessors/Mysql.pm
    /usr/local/cpanel/Cpanel/ServiceManager/Services/Mysql.pm
    /usr/local/cpanel/Cpanel/Template/Plugin/Mysql.pm
    /usr/local/cpanel/Whostmgr/Mysql.pm
    /usr/local/cpanel/Whostmgr/API/1/Mysql.pm
    /usr/local/cpanel/Whostmgr/Config/Mysql.pm
    /usr/local/cpanel/Whostmgr/Config/Backup/System/Mysql.pm
    /usr/local/cpanel/Whostmgr/Config/Restore/System/Mysql.pm
    /usr/local/cpanel/Whostmgr/Transfers/Systems/Mysql.pm
    /var/cpanel/perl/easy/Cpanel/Easy/PHP5/Mysql.pm

    So, my /etc/my.cnf is similar to example shown at Setting Character Sets and Collations

    relevant bits:

    Code:
    [client]
    default-character-set=utf8mb4
    
    [mysql]
    no-auto-rehash
    default-character-set=utf8mb4
    
    [mysqldump]
    default-character-set=utf8mb4
    
    [mysqld]
    collation-server=utf8mb4_unicode_520_ci
    init-connect='SET NAMES utf8mb4'
    character-set-server=utf8mb4
    character-set-client-handshake=FALSE
    ...before I made the change, collation-server was =utf8mb4_unicode_ci

    MyDQL restart reported success.

    I then did some work via phpAdmin on a few databases (mysql imports and then some sql to search for values, etc.) then logged into WP sites at associated with various cPanel accounts and all seemed fine.

    WHM > Server Status > Service Status shows mysql as up.

    I can also still do this:

    Code:
    MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
    +--------------------------+------------------------+
    | Variable_name            | Value                  |
    +--------------------------+------------------------+
    | character_set_client     | utf8mb4                |
    | character_set_connection | utf8mb4                |
    | character_set_database   | utf8mb4                |
    | character_set_filesystem | binary                 |
    | character_set_results    | utf8mb4                |
    | character_set_server     | utf8mb4                |
    | character_set_system     | utf8                   |
    | collation_connection     | utf8mb4_unicode_520_ci |
    | collation_database       | utf8mb4_unicode_520_ci |
    | collation_server         | utf8mb4_unicode_520_ci |
    +--------------------------+------------------------+
    10 rows in set (0.00 sec)
    ...so, I think that MySQL is not down.

    So, I wonder whats up with those messages?

    Also, since collation-server has been =utf8mb4_unicode_ci and because "The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose." so despite other efforts to control, this:

    Code:
    MariaDB [(none)]> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'dev_dafish';
    +----------------------------+------------------------+
    | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
    +----------------------------+------------------------+
    | utf8mb4                    | utf8mb4_unicode_ci     |
    +----------------------------+------------------------+
    1 row in set (0.00 sec)
    ...so, I do this:

    Code:
    MariaDB [(none)]> alter database staging_pnqf8atlsi default collate utf8mb4_unicode_520_ci;
    Query OK, 1 row affected (0.00 sec)
    ...and then I get something like this:

    Code:
    +----------------------------+------------------------+
    | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
    +----------------------------+------------------------+
    | utf8mb4                    | utf8mb4_unicode_520_ci |
    +----------------------------+------------------------+
    ...and after seeing the error message I tried altering a few databases, and all worked as expected --> except one, and it is associated with the cPanel account at which I got the error message at cPanel interface after change my.cnf and restarting.

    The default collation name value was still successfully updated, though:

    Code:
    MariaDB [(none)]> alter database dev_dafish default collate utf8mb4_unicode_520_ci;
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:    900
    Current database: *** NONE ***
    
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [(none)]> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'dev_dafish';
    +----------------------------+------------------------+
    | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
    +----------------------------+------------------------+
    | utf8mb4                    | utf8mb4_unicode_520_ci |
    +----------------------------+------------------------+
    1 row in set (0.00 sec)
    So, basically, this all seems weird.

    For now, the best thing to do seems to be to revert the change in /etc/my.cnf so that collation-server=utf8mb4_unicode_ci and to continue my practice of altering the default collation name value for each database (which should be collation-server's job!).

    With change reverted and MySQL restarted, the cPanel > MySQL Databases interface is loading successfully again, and I can create a new database, etc.

    ...this also seems to be saying that either this should just be working already, or might be able to address using --character-sets-dir option ? MySQL :: MySQL 5.7 Reference Manual :: B.5.2.16 Can't initialize character set

    ...also, this was interesting (if perhaps not immediately helpful :) In MySQL, never use “utf8”. Use “utf8mb4”. – Adam Hooper – Medium

    Thoughts? specifically, any problems with my current approach?

    Thanks, Max

    ps. main reason I'm interested in this is for WP plugins that set other collations if they're not controlled, though of course I also use
    define('DB_CHARSET', 'utf8mb4');
    define('DB_COLLATE', 'utf8mb4_unicode_520_ci');
    ... and I really don't want to have need to be modifying all kine WP plugin code if I can help it :)
     
    #3 MaxFein, Apr 5, 2018
    Last edited: Apr 5, 2018
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    42,802
    Likes Received:
    1,714
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    Can you let us know what appeared in your MySQL error log upon restarting MySQL when you encounter that error?

    Thank you.
     
Loading...

Share This Page