Connecting to Cloud SQL from Google Cloud Function using Python and SQLAlchemy

2
votes

I read all documentation related to connecting to MysQL hosted in Cloud SQL from GCF and still can't connect. Also, tried all hints in documentation of SQLAlchemy related to this. I am using the following connection

con = 'mysql+pymysql://USER:PASSWORD@/MY_DB?unix_socket=/cloudsql/Proj_ID:Zone:MySQL_Instance_ID'
mysqlEngine = sqlalchemy.create_engine(con)

The error I got was:

(pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' ([Errno 111] Connection refused)") (Background on this error at: http://sqlalche.me/e/e3q8)

3
pythonsqlalchemygoogle-cloud-platformgoogle-cloud-functionsgoogle-cloud-sql
can you check whater the hoster server is available to connect to that server using telnet <server ip> <port>\ – AmilaMGunawardana
yes @AmilaMGunawardana, I can connect to it using Kubernetes as well as from my laptop using vpn but not using cloud functions. Of course I am not using UNIX sockets but ip:port format – Ahmed Roshdy

3 Answers

1
votes

You need to make sure you are using the correct /cloudsql/<INSTANCE_CONNECTION_NAME> (This is in the format <PROJECT_ID>:<REGION>:<INSTANCE_ID>). This should be all that's needed if your Cloud SQL instance is in the same project and region as your Function.

The GCF docs also strongly recommends limiting your pool to a single connection. This means you should set both pool_size=1 and max_overflow=0 in your engine settings.

If you would like to see an example of how to set these settings, check out this sample application on Github.

0
votes

I believe that your problem is with the Connection_name represented by <PROJECT_ID>:<REGION>:<INSTANCE_ID> at the end of the con string variable.

Which by the way should be quoted:

con = 'mysql+pymysql://USER:PASSWORD@/MY_DB?unix_socket=/cloudsql/<PROJECT_ID>:<REGION>:<INSTANCE_ID>'

Check if you are writing it right with this command:

gcloud sql instances describe <INSTANCE_ID> | grep connectionName

If this is not the case, keep in mind these considerations present in the Cloud Functions official documentation:

First Generation MySQL instances must be in the same region as your Cloud Function. Second Generation MySQL instances as well as PostgreSQL instances work with Cloud Functions in any region.

Your Cloud Function has access to all Cloud SQL instances in your project. You can access Second Generation MySQL instances as well as PostgreSQL instances in other projects if your Cloud Function's service account (listed on the Cloud Function's General tab in the GCP Console) is added as a member in IAM on the project with the Cloud SQL instance(s) with the Cloud SQL Client role.

0
votes

After a long thread with Google Support, we found the reason to be: simply we should enable public access to Cloud SQL without any firewall rule. It is undocumented and can drive you crazy, but the silver bullet for the support team is to say: it is in beta!