Issue Problem with special chars after migration

[eugenioc]

Server operating system version

Debian 10.13

Plesk version and microupdate number

18.0.54

HI,
I did a migration (through Plesk tool) from an Ubuntu machine to a Debian machine.

In the new environment I have a problem with special characters (è é ò, ...).
At the database level the charsets of the tables are the same between the two servers and the data saved within the database are identical.
However I don't display the special characters correctly.
I tried to set the charset from HTML or via MySQL query before the Select but I still see the characters wrong.

At the MySQL Server level I have these characteristics (using the information that I retrieve by PhpMyAdmin)

ORIGINAL:

• Server: Localhost via UNIX socket
• Tipo di server: MariaDB
• Connessione Server: SSL inattivo
• Versione del server: 10.3.28-MariaDB - MariaDB Server
• Versione protocollo: 10
• Utente: cloud_u@localhost
• Codifica caratteri del server: cp1252 West European (latin1)

NEW SERVER:

• Server: Localhost via UNIX socket
• Server type: MariaDB
• Server connection: SSL is not being used
• Server version: 10.3.39-MariaDB-0+deb10u1 - Debian 10
• Protocol version: 10
• User: cloud_u@localhost
• Server charset: UTF-8 Unicode (utf8mb4)

Could the error be due to the different charset at the MySQL database level?
Or is it due to something else?

Thank you in advance.

 

[Peter Debik]

Yes, very likely it is the different charset. You can define that in /etc/my.cnf, but I am not sure what is best for your case. So this example only shows one option and only to present the different sections and variables:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8mb4_unicode_520_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

It will probably be best to convert the content to UTF-8 before migrating the database(s), because cp1252 as a default character set is not a good choice.

 

[eugenioc]

Thank you for your support.
I tried entering your instructions and restarting the service.

But I still don't see the special characters correctly.

 

[Peter Debik]

The above excerpt was an example for the syntax to use, but it was not an example for a specific charset. In your case you've been working with a Windows charset, and now are facing the situation where the database server is using UTF8. It would be best to convert the database to UTF8. One possible approach could be to export it, then convert the text dump file, the import it into the new server.

 

[eugenioc]

Hi Peter,
I change the focus of the problem.
When I open the connection in PHP i run this code:

$mysqli->set_charset("latin1");

In this way I see the correct character

 

[Hi Ideamatic]

Hi Peter,
I change the focus of the problem.
When I open the connection in PHP i run this code:

$mysqli->set_charset("latin1");

In this way I see the correct character

Thanks, worrked also for me using mysql and pdo
mysql_set_charset('latin1');
and
$pdo->exec("set names latin1");
Thanks!