[cloud-run-django]: Codelab does not work with MySQL Cloud

[danielrsilveira]

Congratulations for this codelab. It's very well written and I managed to complete it without a hitch.

The reason I'm issuing this ticket is because I couldn't reproduce the steps with MySQL 5.7 Cloud SQL. With Postgres it worked perfectly.

What am I doing wrong?

Here are the steps to reproduce the problem:

STEP 5

Create MySQL Cloud instance

Mysql instance will be named mysqlinstance

gcloud sql instances create mysqlinstance --project $PROJECT_ID   --database-version MYSQL_5_7 --tier db-f1-micro --region $REGION

Create database

gcloud sql databases create mydatabase --instance mysqlinstance

Create user

DJPASS="$(cat /dev/urandom | LC_ALL=C tr -dc 'a-zA-Z0-9' | fold -w 30 | head -n 1)"
gcloud sql users create djuser --instance mysqlinstance --password $DJPASS

Create secret

echo DATABASE_URL=\"mysql://djuser:${DJPASS}@//cloudsql/${PROJECT_ID}:${REGION}:mysqlinstance/mydatabase\" > .env
echo GS_BUCKET_NAME=\"${GS_BUCKET_NAME}\" >> .env
echo SECRET_KEY=\"$(cat /dev/urandom | LC_ALL=C tr -dc 'a-zA-Z0-9' | fold -w 50 | head -n 1)\" >> .env
echo DEBUG=\"True\" >> .env
gcloud secrets create mysql_application_settings --data-file .env

Access for CLOUDRUN and CLOUDBUILD users

gcloud secrets add-iam-policy-binding mysql_application_settings --member serviceAccount:${CLOUDRUN} --role roles/secretmanager.secretAccessor
gcloud secrets add-iam-policy-binding mysql_application_settings --member serviceAccount:${CLOUDBUILD} --role roles/secretmanager.secretAccessor
gcloud projects add-iam-policy-binding ${PROJECT_ID} --member serviceAccount:${CLOUDBUILD} --role roles/cloudsql.client

STEP 6

Added mysqlclient dependency

requirements.txt

gunicorn==20.0.4
mysqlclient==2.0.3
google-cloud-secret-manager==2.1.0
google-auth==1.24.0
django-storages[google]==1.9.1
django-environ==0.4.5

STEP 7

Added apt-get install for dependencies needed by mysqlclient.

Dockerfile

# Use an official lightweight Python image.
# https://hub.docker.com/_/python
FROM python:3.8-slim

RUN apt-get update -y && apt-get install -y gcc libc-dev default-libmysqlclient-dev

#...

STEP 8

Changed reference to mysqlinstance in cloudmigrate.yaml

cloudmigrate.yaml

 #...
- name: "gcr.io/google-appengine/exec-wrapper"
  args: ["-i", "gcr.io/$PROJECT_ID/django-cloudrun",
         "-s", "${PROJECT_ID}:${_REGION}:mysqlinstance",
         "--", "python", "manage.py", "migrate"]
#...

RESULTS

Build runs successfully
gcloud builds submit --tag gcr.io/$PROJECT_ID/django-cloudrun

Migrate fails
gcloud builds submit --config cloudmigrate.yaml --substitutions _REGION=$REGION

Step #2: ---------- EXECUTE COMMAND ----------
Step #2: python manage.py migrate
Step #2: Traceback (most recent call last):
Step #2:   File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection
Step #2:     self.connect()
Step #2:   File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner
Step #2:     return func(*args, **kwargs)
Step #2:   File "/usr/local/lib/python3.8/site-packages/django/db/backends/base/base.py", line 200, in connect
Step #2:     self.connection = self.get_new_connection(conn_params)
Step #2:   File "/usr/local/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner
Step #2:     return func(*args, **kwargs)
Step #2:   File "/usr/local/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 234, in get_new_connection
Step #2:     connection = Database.connect(**conn_params)
Step #2:   File "/usr/local/lib/python3.8/site-packages/MySQLdb/__init__.py", line 130, in Connect
Step #2:     return Connection(*args, **kwargs)
Step #2:   File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 185, in __init__
Step #2:     super().__init__(*args, **kwargs2)
Step #2: MySQLdb._exceptions.OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")

I'm sorry for opening this ticket here, since everything is fine with the codelab itself. This was cleanest way to reproduce this problem that is happening in another project that I'm planning to move to Cloud Run.

Any help is apreciated.

Thanks in advance.

[glasnt]

You've renamed the secret for your settings from application_settings to mysql_application_settings without indicating you've also changed the value in settings.py.

Try changing that and see if it works.

# Pull django-environ settings file, stored in Secret Manager
-SETTINGS_NAME = "application_settings"
+SETTINGS_NAME = "mysql_application_settings"

[danielrsilveira]

Hi Katie! Thanks for the reply!

I forgot to mention but, actually, i had set the correct secret in the settings.py
If I hadn't, django wouldn't have even tried to connect to mysql as shown in the failure message.

# Pull django-environ settings file, stored in Secret Manager
SETTINGS_NAME = "mysql_application_settings"

This problem could be reproduced with less modifications if I had kept the secret name. Sorry for the confusion.
Is there any specific config that I should have in place to work with MySQL?

[glasnt]

I think it might be a problem with that secret value, still, as the culprit seems to me that it's trying to connect on a local MySQL socket

From your own logs

Step #2: MySQLdb._exceptions.OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")

All that should need to change for a different database is:

• the mysql client system package

  # Dockerfile 
  RUN apt-get update -y && apt-get install -y gcc libc-dev default-libmysqlclient-dev
  

• the python package for mysql

  # requirements.txt
  mysqlclient==2.0.3
  

• changing the prefix in the DATABASE_URL value from postgres:// to mysql://

Debugging this further, however, it looks like an issue is with django-environ not parsing sockets in the mysql form.

I ended up adding custom parsing to my settings file to ensure that the socket form in mysql connection strings was supported:

# settings.py
if "/" in DATABASES["default"]["NAME"]:
    DATABASES["default"]["HOST"], DATABASES["default"]["NAME"] = DATABASES["default"]["NAME"].rsplit('/', 1)

Let me know if this helps!

[danielrsilveira]

As you pointed, the django-environ wasn't parsing the DATABASE_URL correctly. I'll report this issue with the django-environ team.

Thank you VERY MUCH Katie!