Learn about Centmin Mod LEMP Stack today
Register Now

MariaDB Foreign Characters

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by Jimmy, Jan 29, 2017.

  1. Jimmy

    Jimmy Premium Member Premium Member

    1,114
    247
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +596
    Local Time:
    5:39 AM
    1.13.x
    MariaDB 10.1.x
    I'm having the weirdest issue with foreign characters on my forum. I'm been racking my brain trying to figure out what the issue is.

    Here is the story.

    I have an IPB v3.4.9 forum on cPanel right now. Everything works fine, foreign characters show up correctly on the site. Databse is MyISAM with utf8_unicode_ci collation.

    Front end of site shows:
    Code:
    Обсуждение
    What shows up in the database:
    Code:
    Цифровые Ñ
    I'm setting up the forum on my dev server for testing with the end result of moving the site to CMM. I exported the db out of cPanel via command line dump and restored it to a db created on Centmin Mod running the default CMM my.conf setup.

    The site works fine on the development server, everything which is English / Spanish (latin characters) shows correctly. Though, I'm having an issue with what is showing up on the front end of the site.

    Front end of dev site shows:
    Code:
    Цифровые токены 
    What shows up in the database:
    Code:
    Цифровые токены 
    I've tried to convert the database a number of ways, but I still get the same thing above.

    Here is the real kicker. On the development server, with the output shown above, if I create a new forum using foreign characters the foreign characters show up correctly on the forum.

    Anyone have any idea what is going wrong?
     
  2. eva2000

    eva2000 Administrator Staff Member

    30,196
    6,789
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,144
    Local Time:
    7:39 PM
    Nginx 1.13.x
    MariaDB 5.5
    Can you clarify the servers you have ?

    seems you have 2 servers here ?

    1. working cpanel forum server
    2. development server with or with centmin mod ?

    What is exact mysqldump command you using ? How did you restore it via command line ? exact command used ?

    Might need to ask on IP.board forums as it could be similar to vBulletin where language setting charset in vB admin does the proper display on front end for what's contained in database. If the language charset isn't correct in vB admin, the display could be off and any attempts of converting the database to other character sets will screw up the data contained in the database as there was nothing wrong with it to begin with.

    That's why you should only mess with mysql character sets if you know what you're doing and also make a backup :)

    But i don't know if that is case with IP.board.

    for working server's forum database name and not working forum database name what is output for these commands where databasename = your database name
    Code (Text):
    mysqlshow -i databasename
    

    use CODEB tag to wrap output for wider viewing
     
  3. Jimmy

    Jimmy Premium Member Premium Member

    1,114
    247
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +596
    Local Time:
    5:39 AM
    1.13.x
    MariaDB 10.1.x
    Yea, I have 2 servers here:
    1. VPS running cPanel with Centos 6 (LIVE)
    2. Local machine running CMM 123.09beta01 (DEV / TESTING)

    Dump
    Code:
    mysqldump -u <username> -p <database> > /root/backups/databases/<database>_backup_date.sql
    
    Restore
    Code:
    mysql -u <username> -p <database> < /path/to/<database>_backup_date.sql
    There is a setting in the IPB admin for the language charaset which is set correctly. I did a test on the dev server after the restore. I created a new topic using foreign language characters they appeared correctly.

    I was only messing with the character set to try to resolve the issue. Since the database is on my testing server there was no harm done to the live site which is still on cPanel.

    The problem with IPB support is that they stopped supporting v3 a month ago and the support forum for v3 are buried on the main forum on the IPS site. Not sure I would get a reply. Most people moved to v4 by now.

    A couple months back I did this same thing - setup a DEV site using CMM. At that time I did the same steps above and had no issues with the characters.

    Today I'm going to try to upgrade the board and see if upgrading to v4 will solve the issue. v4 converts everything to utf8mb4 so hopefully that will solve the issue.
     
    • Informative Informative x 1