Unable to use utf8mb4 character set with CloudSQL on AppEngine Python

Question

I have set up a CloudSQL instance that I am attempting to use with my Django app on AppEngine. I've confirmed that the server is set to use utf8mb4 character set via the CloudSQL console for my database:

utf8mb4 utf8mb4_unicode_ci

If I connect directly with the mysql cli, I can successfully insert and read emojis. However, if I insert the same emoji characters through the Django admin it's just inserted as "? ? ? ?".

I attempted to ensure the MySQLdb-python client is using utf8mb4 with:

'ENGINE': 'django.db.backends.mysql',
...
'OPTIONS': {
    'charset': "utf8mb4",
}

But this causes me to receive the following error on AppEngine:

(2019, "Can't initialize character set utf8mb4 (path: /usr/local/mysql/share/charsets/)")

My app.yaml is using the "latest" MySQLdb library:

libraries:
- name: MySQLdb
  version: "latest"

Answer 1

I just chatted with google and got everything working for our instance!

The standard way to get utf8mb4 working in Django is to specify it as DATABASES['default']['OPTIONS'] in settings.py, like this:

'OPTIONS': {'charset': 'utf8mb4'},

This causes an OperationalError in App Engine, on MySQLdb 1.2.4b4 / 1.2.4 / 1.2.5; which apparently means that the MySQL C client google is compiling against is missing the utf8mb4 character set.

Remove this OPTIONS setting.

The workaround is to manually call SET NAMES; edit lib/django/db/backends/mysql/base.py and add a conn.query("SET NAMES utf8mb4") line into DatabaseWrapper.get_new_connection, so it looks like this:

def get_new_connection(self, conn_params):
    conn = Database.connect(**conn_params)
    conn.encoders[SafeText] = conn.encoders[six.text_type]
    conn.encoders[SafeBytes] = conn.encoders[bytes]
    conn.query("SET NAMES utf8mb4")
    return conn

Make sure that you also have utf8mb4 enabled on the backend. The migration commands in the App Engine Django tutorial result in a Cloud SQL instance configured for utf8. I needed to run these commands to enable utf8mb4 on the two tables:

ALTER TABLE polls_question CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE polls_choice CONVERT TO CHARACTER SET utf8mb4;