Recently I have moved my WordPress site to Amazon EC2, Nginx Ubuntu 16.04. After migrate I found some UTF8 characters are strangely display on the front end.

Most of the weird characters  ( â ’) are double quotes or some symbols. I have a spent more than 3 days to fix the WordPress Strange UTF8 Character issue. I have tried almost all the possible solutions and you might need different solution based on your wordpress version and hosting environment.

I finally get it fixed, here you go all the possible solutions and different attempts that might save your days.

Possible issue 1: Wrong Meta charset

This is a very rare mistake as WordPress will always take care of the meta charset it is utf-8 by default.

<meta charset="UTF-8">

Possible issue 2: Setting the correct charset in your WordPress settings

This is the easiest fix and you are lucky if you can simply solve the character encoding issue by trying this attempt.

Try: Change the charset settings to ‘utf8’ in your wp-config.php:

define('DB_CHARSET', 'utf8');
define('DB_COLLATE', '');

If adding the utf8 charset in the wp-config.php solve your problem. Chances are your old database was storing the data in the utf8 format but your new database charset settings wasn’t utf8.

Try: Change the charset settings to ‘latin1’ in your wp-config.php:

define('DB_CHARSET', 'latin');
define('DB_COLLATE', '');

If adding the latin1 charset in the wp-config.php solve your problem. Chances are all your data was stored in the latin1 format but your new database charset settings is not Latin-1.

Try: Change the charset settings to ” in your wp-config.php:

In some rare case, removing the utf8 character will fix the issue if your charset settings is already utf8:

define('DB_CHARSET', '');
define('DB_COLLATE', '');

Most of the time this doesn’t solve the problem, there might be a different database structure in your new server. Let’s dig in further.

Possible issue 3: Incompatible MySQL database structure

First, check you MySQL database structure to make sure it’s utf-8 compatible:

1. SSH to your server
2. Access to your current/old dataabse:

   # mysql -p

3. In the SQL console, check your database charset and encoding settings:

   SHOW VARIABLES WHERE variable_name like 'char%';
   
   
   Most of the time, all the UTF8 characters in your old database might be stored in a non-utf8 compatible table (latin1). After migrate to a newer server, the database character settings are probably utf8 by default. This is the main reason why the strange characters appear after you have export and import the database to a new server.

Learn more: How to SSH to your server using Putty

Learn more: How to SSH to access and manage your MySQL Database

Solution

Solution for this? It need a little more work to fix the character encoding in your new server since there aren’t stored in the correct charset in the first place.

Before trying this approach, please BACKUP your database first.

Create a convertTable.php in your wordpress directory:

<?php
header('Content-Type: text/plain; charset=utf-8');
$connection= 'mysql:host=localhost;port=3306;charset=latin1';
$user = 'YOUR_USERNAME';
$password = 'YOUR_PASSWORD';
$options = [
 \PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY,
 \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
 \PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET latin1",
];
$dbManager = new \PDO( $connection, $user, $password, $options );
$databasesToConvert = [ 'database1', /** database2, ... */ ];
$typesToConvert = [ 'char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext' ];
foreach ( $databasesToConvert as $database )
{
echo $database, ":\n";
echo str_repeat( '=', strlen( $database ) + 1 ), "\n";
$dbManager->exec( "USE `{$database}`" );
$tablesStatement = $dbManager->query( "SHOW TABLES" );
while ( ($table = $tablesStatement->fetchColumn()) )
{
echo "Table: {$table}:\n";
echo str_repeat( '-', strlen( $table ) + 8 ), "\n";
$columnsToConvert = [ ];
$columsStatement = $dbManager->query( "DESCRIBE `{$table}`" );
while ( ($tableInfo = $columsStatement->fetch( \PDO::FETCH_ASSOC )) )
{
$column = $tableInfo['Field'];
echo ' * ' . $column . ': ' . $tableInfo['Type'];
$type = preg_replace( "#\(\d+\)#", '', $tableInfo['Type'] );
if ( in_array( $type, $typesToConvert ) )
{
echo " => must be converted\n";
$columnsToConvert[] = $column;
}
else
{
echo " => not relevant\n";
}
}
if ( !empty($columnsToConvert) )
{
$converts = array_map(
function ( $column )
{
return "`{$column}` = CONVERT(CAST(CONVERT(`{$column}` USING latin1) AS binary) USING utf8)";
},
$columnsToConvert
);
$query = "UPDATE `{$table}` SET " . join( ', ', $converts );
echo "\n", $query, "\n";
$dbManager->exec( $query );
}
echo "\n--\n";
}
echo "\n";
}

Run the php script and issue shall be fixed!