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

Question

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)

can you check whater the hoster server is available to connect to that server using telnet <server ip> <port>\ — AmilaMGunawardana
– AmilaMGunawardana, Commented Feb 14, 2019 at 10:09
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
– Ahmed Roshdy, Commented Feb 14, 2019 at 10:11

kurtisvg · Accepted Answer · 2019-02-14 19:46:37Z

1

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.

answered Feb 14, 2019 at 19:46

kurtisvg

3,5851 gold badge10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ahmed Roshdy Over a year ago

I am using cloud functions not App Engine

kurtisvg Over a year ago

The relevant code (regarding using a unix socket and engine creation) is the same. You just need to adjust the parameters I mentioned in my answer.

llompalles · Accepted Answer · 2019-02-14 11:41:52Z

0

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.

answered Feb 14, 2019 at 11:41

llompalles

3,19614 silver badges22 bronze badges

4 Comments

Ahmed Roshdy Over a year ago

Thanks for your reply, yes indeed string should be quoted so I have updated my question. I ran the gcloud command you suggested and it complained of the presence of ":" in the name so I removed everything except the instance name itself. The result was the same I am using in my string. Updating the code with instance name only without the project and zone raised the following error instead: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 2] No such file or directory) I am using 2nd generation MySQL instance so it should be available regardless of the GCF zone

llompalles Over a year ago

What do you mean by the gcloud command is complaining? If you are tipping a valid <INSTANCE_ID> it outputs connectionName: <PROJECT_ID>:<REGION>:<INSTANCE_ID>.

Ahmed Roshdy Over a year ago

I tried first with the full path name <PROJECT_ID>:<REGION>:<INSTANCE_ID>, got an error as explained. So I used <INSTANCE_ID> which did work, it bring same <PROJECT_ID>:<REGION>:<INSTANCE_ID> I am using in connection string in my question

llompalles Over a year ago

I still think that you have a typo error in one of the username, password, db_name... variables. However, open a Google Cloud support ticket if you believe the error is not in your code. They have internals tools to check if there is an issue with the connection.

Ahmed Roshdy · Accepted Answer · 2019-03-31 21:34:13Z

0

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!

answered Mar 31, 2019 at 21:34

Ahmed Roshdy

4318 silver badges16 bronze badges

Comments

douggard · Accepted Answer · 2022-03-11 08:31:12Z

0

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.

answered Mar 11, 2022 at 8:31

douggard

7321 gold badge13 silver badges32 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related