Problems with database UTF8 chars when migrating

Amadex · November 12, 2020, 4:57pm

Hello,

I have Ubuntu 20.04+HestiaCP 1.3.0 on old server. I want to migrate my website and database to a new server with Ubuntu 20.04+HestiaCP 1.3.0 too.

After I do backup on old server, I upload files manually to the new server (ftp). Problems comes when I import database from old server to the new server via phpMyAdmin.

Charaters from my language are not working " š đ č ć ž ", but it’s same charset (utf8 db type) on both (old and new) servers.

When I login to phpmyadmin on old server, I get fu*ked up charaters too. But somehow the website displays them as normal, and everything works fine:

But when I import the same database to the new server, the charaters as seen above are not working and are replaces with that hieroglyphs.

Raphael · November 12, 2020, 5:17pm

Do you have the same charsets for both databases/tables? Could be the issue.

jtdoank · November 12, 2020, 5:28pm

if you just want to migrate to new server, why you not just made a simple backup and restore if you use the same OS an same Hestia as control panel. what i mean is using command line

if got problem using this commandline, there are solution on this forum
if i am not wrong is this

i always use this method all the time and no issue

Amadex · November 12, 2020, 6:09pm

@Raphael both servers (old and new) are with same OS and same Hestia version, also both databases are created with ut8.

@jtdoank I’ve tried that too. Created new user, uploaded backup and did a restore via panel. But same thing happens with database. On the new server the letters š đ ž č ć are not working.

Here is a sample of info inside old server (where the db is working correctly):

Here is a sample of info inside new server (where I want my db to import):

Once again, I’ve never tweaked configs or something, this is the default HestiaCP configs on both servers after the installation.

Amadex · November 12, 2020, 6:23pm

P.S there are errors when installing PHP 7.2 from Hestia interface (web server).

AlwaysSkint · November 12, 2020, 9:04pm

Character sets can be a real pain. Here’s something that may prove useful:

gist.github.com

https://gist.github.com/mdemblani/34d30015f24e1927736f189b88be143a

Mysql Character Set conversion - Latin1 to UTF-8(utf8mb4).md

1. Make sure mysql-client is installed. If not, then :
```
    sudo apt install mysql-client
	or
    sudo apt-get install mysql-client
```
    
2. Open php.ini
    ```
    ; PHP's default character set is set to UTF-8.

This file has been truncated. show original

Section 9 onwards is the main part.

falzo · November 14, 2020, 11:16am

you probably also want to check the locales setting on both systems…

Amadex · November 14, 2020, 10:10pm

It’s using same locales. But when we talk about that, it would be good for hestiacp to generate needed locale for it. Eg to set default EN_US.UTF-8 by automatic.

AlwaysSkint · November 14, 2020, 11:42pm

No. There’s a big World out there beyond Idaho. (Cue the B52s)
EN_GB.UTF-8 is the root - English is the clue.