Switch default collation?
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, 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:
-
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 -
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 -
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.
Comments
3 comments