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 II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,838
    Likes Received:
    276
    Trophy Points:
    193
    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 II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,838
    Likes Received:
    276
    Trophy Points:
    193
    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 II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,838
    Likes Received:
    276
    Trophy Points:
    193
    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,348
    Likes Received:
    60
    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...
    Lobo-X and cPanelLauren like 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 II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,838
    Likes Received:
    276
    Trophy Points:
    193
    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,348
    Likes Received:
    60
    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...
  11. Lobo-X

    Lobo-X Registered

    Joined:
    Aug 15, 2018
    Messages:
    1
    Likes Received:
    2
    Trophy Points:
    0
    Location:
    Tlalnepantla de Baz, Méx.
    cPanel Access Level:
    Reseller Owner
    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.
     
    cPanelLauren and mtindor like this.
  12. peter_petrelli

    peter_petrelli Registered

    Joined:
    May 5, 2013
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    cPanel Access Level:
    Root Administrator
    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!
     
    #12 peter_petrelli, Sep 25, 2018
    Last edited by a moderator: Sep 25, 2018
  13. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,838
    Likes Received:
    276
    Trophy Points:
    193
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    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!
     
    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