Skip to main content
cPanel Technical Support has been heavily impacted by hurricane Beryl and our ability to respond to tickets has been hindered as a result. We appreciate your understanding and patience as we address these delays.

Switch default collation?

Comments

3 comments

  • cPanelMichael
    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, line 1.

    Hello, Could you open a support ticket using the link in my signature so we can take a closer look? Thank you.
    0
  • MaxFein
    Hello, Could you open a support ticket using the link in my signature so we can take a closer look? Thank you.

    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 Relates to MXS-951 where the connector is used to connect to a server configured with the following options. character_set_server=utf8mb4 collation_server=utf8mb4_unicode_520_ci When the connection is made, the following errors are logged. MySQL error 2019, Can't initialize character set unknown (path: compiled_in)
    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 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, 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: The MySQL server is currently offline.
    Error while connecting to MySQL: (XID hk8r3u) 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 hk8r3u) 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, line 1.
    I do not see /usr/local/cpanel/Cpanel/Mysql.pm on my system...
    # 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:
    [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:
    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:
    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:
    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:
    +----------------------------+------------------------+ | 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:
    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 :)
    0
  • cPanelMichael
    When I opened the cPanel > MySQL Databases interface to create a new database I got two messages: The MySQL server is currently offline.

    Hello, Can you let us know what appeared in your MySQL error log upon restarting MySQL when you encounter that error? Thank you.
    0

Please sign in to leave a comment.