Why Is My Django/MySQL Application Showing Unicode as Question Marks?

Back up your database before you try anything here. Sometimes character set conversions can change your data in ways you don’t want. Be sensible and use mysqldump or something to safeguard it before you start messing around. Needless to say, you should try everything in a test environment first.

When you run a Django application (or any other web application, for that matter) on top of a stock MySQL install, you might hit a problem with storing Unicode characters. I saw it in a Django project that had to deal with Arabic text. Instead of the Arabic characters, it just showed a bunch of question marks.

Here’s how to fix it.

Check your MySQL character set

Out of the box, your MySQL character set is probably latin1 . We’re going to change it to utf8 .

First, run this command to check that you are in fact dealing with an incorrect character set:

In the output, you will probably see the following line:

If you do, keep going. We’re going to sort it out.

Edit my.cnf

The main MySQL configuration file is called my.cnf . On Ubuntu it is located at /etc/mysql/my.conf  . You can check where it is on your own system by running locate my.cnf .

The file is divided into sections and the start of each section is indicated with a name in square brackets. We’re interested in the sections [client]  and [mysqld] .

After making a backup of the current state of the file

open it in your text editor of choice and find the [client]  section. Add the following line to it:

Next, find the [mysqld]  section and add the following three lines to it:

Be careful that you add the code to the right sections. If you make a mistake here then MySQL will not start and it won’t write any useful error message to the logs.

Save my.cnf  and restart MySQL. On many systems, you can do this with the service  command:

Alter each table to use the new character set

First, you want to generate the script you are going to use to convert each table one by one to the new character set. Change the database name, username and password to the correct values and run this in the terminal.

It will generate the SQL you need to change each of your tables. For example, if your database contained three tables called users , comments  and posts , the generated code would look this this:

Run that code against the database using your tool of choice. It might take a while, depending on the size of your tables. You’ll know when you try it on your test environment. When it’s done, those question marks should be history.

Download Mastering Decorators

Mastering_decorators_cover

Enjoyed this article? Join the newsletter and get Mastering Decorators - a gentle 22-page introduction to one of the trickiest parts of Python.

Weekly-ish. No spam. Unsubscribe any time. Powered by ConvertKit