Charset problem after upgrade to Mariadb 10.2
I upgraded a server from MariaDB 10.1 to 10.2 and after that some charset errors appeared on sites using latin1 encoded tables.
This server runs on Cloudlinux and those sites were using alternative php versions from PHP Selector.
While investigating the issue I noticed that MariaDB charset was set to latin1, the same as other machines running version 10.1, so it seemed right
| character_set_client | latin1
| character_set_connection | latin1
| character_set_server | latin1
But when I used PHP to connect to MariaDB and listed the connection and client charset:
mysql_query('SHOW VARIABLES LIKE "%character_set%";')
it showed UTF8 encoding.
So I disable Cagefs (disabling PHP selector for the account) or reverted the PHP version to the server's native and then the connection and client charset on PHP showed Latin1. Unfortunately those site need to run an old PHP version, so I could not just disabled it.
I tried to change default_charset on php.ini for those alternative php versions, but it did not work. Using iso-8859-1 or setting it to blank still showed UTF8 on client and connection charset from MariaDB.
The workaround was to set this line on my.cnf:
skip-character-set-client-handshake
But now some apps that uses UTF8 encoded tables are messed up, because these ones are forced to Latin1 charset. In example, Afterlogic Webmail is throwing charset errors, even though it sets the connection charset to UTF8 in the connection code:
json_encode: [Error Code: 5] [Error Message: Malformed UTF-8 characters, possibly incorrectly encoded]
I don't know if I am missing some configuration to adjust this on Cloudlinux, and I would like to know if anyone else faced this or similar issue after upgrading MariaDB.
-
Hi @plague Have you looked at MariaDB's documentation on how to change the default charset/collation? Setting Character Sets and Collations As well as the supported charsets and collations: Supported Character Sets and Collations Thanks! 0 -
Hello @cPanelLauren Yep, the server's charset is set to Latin1, that's the default for this machine (and it already was, before the upgrade). I can't figure out why the connection_charset is Latin1 for the native PHP but changed to UTF8, on the same site, for PHP Selector's versions after the upgrade. 0 -
If you re-enable alt-php for the domains, ensure they're using the correct charset and update cagefs does the issue persist? You can force cagefs to update with the following: cagefsctl --force-update cagefsctl --remount-all0 -
I have tried setting PHP's default_charset for those accounts using alt-php, but no matter what charset is used (or none), the connection always use UTF8. Global php.ini for alt-php sets default_charset to none (blank) and I changed to ISO using PHP Selector. The connection still showed UTF8. I tried with --force-update, but did not with --remount-all. I'll give it a try as soon as possible. 0 -
Hi @plague Please let me know how that goes and if the issue persists. Thanks! 0 -
Hello @cPanelLauren Yep, the server's charset is set to Latin1, that's the default for this machine (and it already was, before the upgrade). I can't figure out why the connection_charset is Latin1 for the native PHP but changed to UTF8, on the same site, for PHP Selector's versions after the upgrade.
Just curious. You say you are running CL / CageFS / Alt-PHP and MariaDB 10.2. This is a longshot (and may not be related to an issue I had when upgrading MariaDB) but I'll ask. Are you using mysql / mysqli / pdo_mysql modules on the affected sites or are you using nd_mysql, nd_mysqli and nd_pdo_mysql modules? If you are using mysql / mysqli / pdo_mysql, try changing to nd_mysql, nd_mysqli and nd_pdo_mysql and see if your problem goes away. I had an issue where some old Joomla 1.5 sites running on PHP 5.3 which started displaying strange characters when I migrated those sites from a MySQL 5.x CL/cPanel box to a MariaDB 10.1 CL/cPanel box. And in my case, after having the Cloudlinux folks troubleshoot, I had to switch from mysql / mysqli / pdo_mysql to nd_mysql, nd_mysqli and nd_pdo_mysql on the affected sites to get them to display properly. Just throwing that out there. It may not help you at all, since you aren't experiencing the exact issue that I was experiencing. M0 -
@cPanelLauren unfortunately the option --remount-all didn't change anything. Alt-PHP was still connecting using UTF8 charset. But what @mtindor said fixed the issue. After changing mysql and mysqli to nd_mysql and nd_mysql, alt-php is showing character_set_client as latin1 (default_charset set to blank on php.ini). Now those sites are displaying the correct accentuation: mysql and mysqli: [QUOTE] array(2) { ["Variable_name">=> string(20) "character_set_client" ["Value">=> string(4) "utf8" } array(2) { ["Variable_name">=> string(24) "character_set_connection" ["Value">=> string(4) "utf8" } array(2) { ["Variable_name">=> string(22) "character_set_database" ["Value">=> string(6) "latin1" }
nd_mysql and nd_mysqli [QUOTE] array(2) { ["Variable_name">=> string(20) "character_set_client" ["Value">=> string(6) "latin1" } array(2) { ["Variable_name">=> string(24) "character_set_connection" ["Value">=> string(6) "latin1" } array(2) { ["Variable_name">=> string(22) "character_set_database" ["Value">=> string(6) "latin1" }
Thank you for the help.0 -
Hi @plague I'm really happy to hear that the advice provided by @mtindor helped you resolve your issue! Thank you for updating us as well! 0 -
I'm happy to hear that as well. I just wish I knew if it would be wise across the board to switch all my customers' mysql modules to nd_ modules. In the end I figured "don't fix what aint broken" and changed only the problematic sites to use nd_ modules. Mike 0 -
Just curious. You say you are running CL / CageFS / Alt-PHP and MariaDB 10.2. This is a longshot (and may not be related to an issue I had when upgrading MariaDB) but I'll ask. Are you using mysql / mysqli / pdo_mysql modules on the affected sites or are you using nd_mysql, nd_mysqli and nd_pdo_mysql modules? If you are using mysql / mysqli / pdo_mysql, try changing to nd_mysql, nd_mysqli and nd_pdo_mysql and see if your problem goes away. I had an issue where some old Joomla 1.5 sites running on PHP 5.3 which started displaying strange characters when I migrated those sites from a MySQL 5.x CL/cPanel box to a MariaDB 10.1 CL/cPanel box. And in my case, after having the Cloudlinux folks troubleshoot, I had to switch from mysql / mysqli / pdo_mysql to nd_mysql, nd_mysqli and nd_pdo_mysql on the affected sites to get them to display properly.
You saved my life! Thanks so much. Using nd_mysql / nd_mysqli / nd_pdo_mysql instead of mysql / mysqli / pdo_mysql did the trick with my client's Joomla! 1.5 which I moved from a server running MySQL 5.7 into another running MariaDB 10. Wrong encoding was affecting both the latin chars display and (worse) menus structure.0 -
Hello, Unfortunately im facing the same issue with moving Joomla 1.5 from server with MariaDB 10.0 to MariaDB 10.2. I tried this fix: stackoverflow.com/questions/31369276/joomla-1-5-mariadb-10 Which previously worked to mariadb10.0, but now it does not. I tried changing mysql, mysqli to nd_mysql and nd_mysqli, but the problem is the same (i double checked if the php settings in the php selector are updated and yes, they are using nd_mysql now). I tried many other things, but with no result. Is the issue the 10.2 version of MariaDB and how can Joomla 1.5 work with it ? Thank you in advance! 0 -
Hello @peter_petrelli I do need to point out that Joomla! 1.5 is far past being the end of life Joomla 1.5 version history - Joomla! Documentation - we cannot provide support for software this old due to a number of incompatibilities that may be present. If you must use it the only advice I can provide would be to inquire with a qualified system administrator if you don't have one you might find one here: System Administration Services | cPanel Forums. You might also try reaching out to MariaDB though their answer may be the same. Thanks! 0
Please sign in to leave a comment.
Comments
12 comments