CODEWITHSUNDEEP
pythonsqlalchemygoogle-cloud-platformgoogle-cloud-functionsgoogle-cloud-sql
Ahmed Roshdy
Ahmed Roshdy

Reputation: 421

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

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)

Upvotes: 2

Views: 5257

Answers (4)

douggard
douggard

Reputation: 722

I was having this issue. Service account was correct, had the correct permissions, same exact connection string as in my App Engine application. Still got this in the logs.

dial unix /cloudsql/project:region:instance connect: no such file or directory

Switching from 2nd generation Cloud Function to 1st generation solved it. Didn't see it documented anywhere that 2nd couldn't connect to Cloud SQL instances.

Upvotes: 0

Ahmed Roshdy
Ahmed Roshdy

Reputation: 421

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!

Upvotes: 0

kurtisvg
kurtisvg

Reputation: 3565

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.

Upvotes: 1

llompalles
llompalles

Reputation: 3186

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.

Upvotes: 0

Related Questions