Our client contact from a Web Design Company we develop web applications and business websites for rang us late yesterday with a problem they had encountered. They were migrating a database from one MySQL database server to another and noticed that question marks and other strange characters such as aeons or blackbox question marks are appearing everywhere.
How did this happen?
This happens because the test server’s character set (utf8) did not match the production servers client (which is set to latin1 by default) this is what does all the importing and since UTF8 Unicode has some characters that are missing or are different to latin1, they are replaced with a question mark.
How do you fix it?
Probably not a silver bullet for these kinds of situations, but it’s worked for us for the past 5 times we’ve had to do it. On the export file, you will need to set the MySQL client’s character set to latin1. We do this by inserting a “SET NAMES latin1;” as the first line to be executed.
Here’s a quick step by step guide on how to fix Strange Characters appearing instead of apostrophes in MySQL:
It is assumed that you already have the database to be migrated exported from the source mysql database server.
Step 1: Open the dumped .sql file and find the line that is similar to:
/*!40101 SET NAMES utf8 */;
Set the mysql client’s charset for this import by changing the character set from utf8 to latin1 like so:
/*!40101 SET NAMES latin1 */;
Step 2: Import the file.
And there you have it. The apostrophes and other characters that were replaced with incorrect characters should now be displayed corectly.
Ben Rowe (8)
Bill Karwin (6)
Kevin (6)
James Horne (5)
Martin (4)
Walter (4)
David Penner (3)
Loïc Frering (3)
Mic (3)
San (3)