Connecting to Google Cloud SQL from a Google Cloud Function python3.7

Question

I'm trying to connect using pymysql, using code very close to the example provided in the documentation: https://cloud.google.com/functions/docs/sql#functions_sql_mysql-python

The database have public ip, with no authorized networks (since cf should use a socket), and my user created allowing access from anywhere.

Running locally with the Cloud SQL Proxy works just fine, but on Google Cloud I get connection refused.

Edit: updated example with code to run locally with env vars

import logging
import os

import pymysql
from pymysql.err import OperationalError


mysql_conn = None


def __get_cursor():
    try:
        return mysql_conn.cursor()
    except OperationalError:
        mysql_conn.ping(reconnect=True)
        return mysql_conn.cursor()


def get_config():
    return {
        'unix_socket': f'/cloudsql/{os.environ["CONNECTION_NAME"]}',
        'user': os.environ['DB_USER'],
        'password': os.environ['DB_PASSWORD'],
        'db': os.environ['DB_NAME'],
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor,
        'autocommit': True
    }


def mysql_demo(request):
    global mysql_conn

    if not mysql_conn:
        mysql_config = get_config()
        logging.info('Open db with config: %s', mysql_config)

        try:
            mysql_conn = pymysql.connect(**mysql_config)
        except OperationalError as exception:
            logging.warning('Unable to get db connection: %s', str(exception))
            return

    with __get_cursor() as cursor:
        cursor.execute('SELECT NOW() as now')
        results = cursor.fetchone()
        return str(results['now'])


if __name__ == '__main__':
    logging.basicConfig(level=logging.INFO)
    os.environ['CONNECTION_NAME'] = 'my-project:us-central1:cf-test'
    os.environ['DB_USER'] = 'db-user'
    os.environ['DB_PASSWORD'] = 'db-password'
    os.environ['DB_NAME'] = 'db_name'
    logging.info(mysql_demo(None))

Edit: additional output

When running locally, this prints out:

INFO:root:2019-09-24 07:11:54

When running as a cloud function I get the output: `

2019-09-24 09:14:59.279 CEST test-cloud-sql mesuju89uxuw Open db with config: {'unix_socket': '/cloudsql/boeingfda-jal:us-central1:cf-test', 'user': 'enplore', 'password': 'az2labha', 'db': 'content_enplore_auth', 'charset': 'utf8mb4', 'cursorclass': <class 'pymysql.cursors.DictCursor'>, 'autocommit': True}
2019-09-24 09:14:59.856 CEST test-cloud-sql mesuju89uxuw Unable to get db connection: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)")

Just as a debug... 1) Confirm that you're using a 2nd generation MySQL instance on Cloud SQL, right? 2) print out: {CONNECTION_NAME}. Just to be sure it's being set on the environment of the GCF properly. If you run the function in the console as a test, you should see any output to STDOUT. — Gabe Weiss
1. Database instance verified to be a second generation 2. Full mysql config printed and verified to use correct unix_socket (/cloudsql/*redacted*:us-central1:cf-test), user, password and db. 3. Running the function locally, with a cloud SQL proxy up gives me the expected output of todays date and time. — Ilon Sjögren

kurtisvg kurtisvg · Accepted Answer · 2019-09-23T20:37:44

It is often more helpful if you post the error you've received, as it often provides extra context. Here are a few probable causes:

Make sure the Cloud SQL Admin API is enabled.
Verify the service account (defaults to service-[YOUR_PROJECT_NUMBER]@gcf-admin-robot.iam.gserviceaccount.com) you are using to run the instance has the correct IAM permissions ( Cloud SQL Client role or the cloudsql.instances.connect and cloudsql.instances.get permissions).
Finally, verify that CONNECTION_NAME variable isn't misspelled.

Finally, you should consider using a connection pool (like SQLAlchemy) and set the max limit to 1. The connection pool will handle errors more gracefully, and limit the number of connects used per instance.

Connecting to Google Cloud SQL from a Google Cloud Function python3.7

2 Answers