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!

SOLVED Charset problem after upgrade to Mariadb 10.2

Discussion in 'Database Discussion' started by plague, May 30, 2018.

  1. plague

    plague Well-Known Member

    Joined:
    Sep 22, 2006
    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    158
    Location:
    Brasil
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  2. cPanelLauren

    cPanelLauren Forums Analyst
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    1,387
    Likes Received:
    92
    Trophy Points:
    103
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. plague

    plague Well-Known Member

    Joined:
    Sep 22, 2006
    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    158
    Location:
    Brasil
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. cPanelLauren

    cPanelLauren Forums Analyst
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    1,387
    Likes Received:
    92
    Trophy Points:
    103
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    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:

    Code:
    cagefsctl --force-update
    cagefsctl --remount-all
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. plague

    plague Well-Known Member

    Joined:
    Sep 22, 2006
    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    158
    Location:
    Brasil
    cPanel Access Level:
    Root Administrator
    Twitter:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  6. cPanelLauren

    cPanelLauren Forums Analyst
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    1,387
    Likes Received:
    92
    Trophy Points:
    103
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @plague


    Please let me know how that goes and if the issue persists.

    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. mtindor

    mtindor Well-Known Member

    Joined:
    Sep 14, 2004
    Messages:
    1,338
    Likes Received:
    56
    Trophy Points:
    178
    Location:
    inside a catfish
    cPanel Access Level:
    Root Administrator
    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.

    M
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    cPanelLauren likes this.
  8. plague

    plague Well-Known Member

    Joined:
    Sep 22, 2006
    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    158
    Location:
    Brasil
    cPanel Access Level:
    Root Administrator
    Twitter:
    @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:
    nd_mysql and nd_mysqli
    Thank you for the help.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    cPanelLauren likes this.
  9. cPanelLauren

    cPanelLauren Forums Analyst
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    1,387
    Likes Received:
    92
    Trophy Points:
    103
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    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!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  10. mtindor

    mtindor Well-Known Member

    Joined:
    Sep 14, 2004
    Messages:
    1,338
    Likes Received:
    56
    Trophy Points:
    178
    Location:
    inside a catfish
    cPanel Access Level:
    Root Administrator
    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
     
    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